SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.

Create Display Sub Folder for Tabular SSAS Measures

If you have a SSAS tabular cube with a large number of measures in a particular measure group, you may want to group specific measures into display sub-folders.  Display sub-folders help keep a cube more organized and reduce the amount of scrolling end-users need to do to locate the measures… Read more

0 comments, 3,908 reads

Posted in Salvo(z) SQL on 13 May 2014

SharePoint 2010 Excel Services and SSIS

As increasing amounts of data are stored in SharePoint in various formats such as lists, Excel documents and Word documents, gaining access to this data is an on-going challenge for Business Intelligence developers.  Fortunately, SharePoint 2010 provides some nice features that make accessing this data a bit less onerous. 

For… Read more

0 comments, 1,855 reads

Posted in Salvo(z) SQL on 7 January 2014

MDX Root Function

This week I discovered an incredibly useful MDX function, the function is called Root.  Full documentation can be found here.

In the past, I have encountered scenarios where I want to override filtering and use the All member for specific attribute hierarchies.  I have usually accomplished this by hard-coding… Read more

1 comments, 2,024 reads

Posted in Salvo(z) SQL on 5 January 2014

MDX Implementing Logical AND On Members of the Same Hierarchy

Reference: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook by Tomislav Piasevoli

In my prior blog post, I discussed implementing logical OR in MDX on members of different hierarchies.  As a follow-up to that post, I will now demonstrate how to implement logical AND on members of… Read more

0 comments, 1,786 reads

Posted in Salvo(z) SQL on 24 December 2013

MDX Implementing Logical OR On Members of Different Hierarchies

Reference: MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook  by Tomislav Piasevoli

In MDX, when two or more members of the same set are placed on a slicer, OR logic is automatically applied.  For example, if you have two members (e.g. sizes ‘Small’ and ‘Large’) on a slicer,… Read more

0 comments, 1,550 reads

Posted in Salvo(z) SQL on 15 December 2013

MDX Order by Multiple Criteria

The MDX language includes an ORDER function that orders members of a set in ascending or descending order based on a numeric (or string) expression.  The syntax of the Order function is displayed below:

Numeric expression syntax:

Order(Set_Expression, Numeric_Expression [ , { ASC | DESC | BASC | BDESC }… Read more

0 comments, 760 reads

Posted in Salvo(z) SQL on 8 December 2013

SQL Server Data Mining: Time Series Algorithm Tips

  • The Time Series algorithm has several parameters that may be adjusted if needed.  I have tried adjusting several of these parameter settings and determined that the following appear to have the most impact:
    • MISSING_VALUE_SUBSTITUTION:  If you have any blanks or gaps in your data and the MISSING_VALUE_SUBSTITUTION parameter is…

Read more

0 comments, 2,173 reads

Posted in Salvo(z) SQL on 4 December 2013

PASS Summit 2013

Adam and I attended PASS Summit for the first time this year in Charlotte, NC.  We had a great time and enjoyed meeting new people.  The following are a few highlights from our trip:

The Journey to Charlotte

Adam has his private pilots license and his instrument rating, so he… Read more

0 comments, 936 reads

Posted in Salvo(z) SQL on 22 October 2013

SSRS: Ignore Series Grouping in Chart

In SSRS, when you add a Series Group to a chart the data for each series is aggregated based on the series grouping specified. In some situations, you may have a series that you want aggregated differently than the series grouping. 

For example, you may want to display a series… Read more

1 comments, 11,145 reads

Posted in Salvo(z) SQL on 31 August 2013

SSRS: Report Actions, URL Actions and SharePoint

I recently encountered a scenario involving a SSRS report that contains a report action to launch a sub-report.  The child / sub-report originally had two mandatory parameters and the values for these parameters were passed from the parent report to the child.  I wanted to add a third parameter to… Read more

0 comments, 4,612 reads

Posted in Salvo(z) SQL on 7 August 2013

SSRS: Display Series on SSRS Chart Based on Parameter Value

I recently modified an SSRS report to display average run times for various SQL Agent jobs over the past 30, 14 and 7 days.  Displaying all the averages simultaneously would make the chart cluttered and confusing; instead, I opted to control the displayed average via a parameter that the user… Read more

1 comments, 3,251 reads

Posted in Salvo(z) SQL on 5 August 2013

SSAS Data Mining – Time Series Algorithm

At TechEd 2013, I attended a few sessions on the topics of Data Mining and Predictive Analytics.  I thought I’d try out the data mining functionality in SSAS using warranty data from a data mart to predict the quantities of failed parts over the next few months.   To accomplish this,… Read more

0 comments, 7,062 reads

Posted in Salvo(z) SQL on 19 June 2013

TechEd 2013–Favorite Sessions

At TechEd 2013, I attended many excellent Business Intelligence / Data Platform sessions and thought I’d share an overview of a few of my favorites:

1. Overview of Business Intelligence in Microsoft Office & SharePoint 2013

Presented by: Peter Myers

This session provides an excellent overview of various Business Intelligence…

Read more

1 comments, 700 reads

Posted in Salvo(z) SQL on 11 June 2013

Sum Result of SSRS LookupSet Function

SSRS provides several built-in Lookup functions:  Lookup, MultiLookup and LookupSet.  Lookup returns a single value while MultiLookup and LookupSet return a set of values. If you want to learn more about the SSRS lookup functions check out this blog post

In some situations, we may want to sum the… Read more

10 comments, 4,158 reads

Posted in Salvo(z) SQL on 27 May 2013

SSAS Event Type Calculation Dimension

Calculation dimensions in SSAS are used to store calculations that can be applied to any measure in the cube.  For example, we can define a ‘Year to Date’ calculation in a calculation dimension and it will work for any measure.  Alternatively, we could define a new ‘Year to Date’ calculated… Read more

0 comments, 1,221 reads

Posted in Salvo(z) SQL on 22 May 2013

Count Weekdays in MDX

I recently implemented an MDX expression to count the number of weekdays in a given timeframe.  This logic is likely easier to implement in SQL, but in this instance it was necessary to implement in MDX.  I thought I’d share the MDX since it may prove to be useful to… Read more

0 comments, 2,526 reads

Posted in Salvo(z) SQL on 25 March 2013

Implementing a ‘Time Calculations’ Dimension in Tabular via DAX

A common request from business users is ‘Time Intelligence’ functionality in an SSAS cube.  For example, users may wish to compare current year sales with the prior year or calculate sales year-to-date.  DAX includes a wide range of time intelligence functions to perform these types of calculations.  We can create… Read more

0 comments, 5,642 reads

Posted in Salvo(z) SQL on 11 March 2013

Introduction to the SQL BCP Utility

SQL Server BCP is a command line utility that bulk copies data between instances of SQL Server and data files.  Data can be imported into SQL Server tables or exported to a data file.  The BCP utility can also be used to generate format files which specify the number and… Read more

0 comments, 21,632 reads

Posted in Salvo(z) SQL on 4 March 2013

PerformancePoint ‘Named Set’ Filter

PerformancePoint provides several options to create filters.  One of the options available is ‘Named Set’.  Within SSAS cubes we can define ‘Named Sets’, which are MDX expressions that return a set of dimension members.  These ‘Named Sets’ can be used to populate filters within PerformancePoint. 

Defining a named set within… Read more

0 comments, 2,102 reads

Posted in Salvo(z) SQL on 25 February 2013

Configuring PerformancePoint Data Source for Time Intelligence

PerformancePoint provides functionality to map several time dimensions across multiple data sources to a ‘Master Time Dimension’.  Data sources that are mapped to a ‘Master Time Dimension’ can be referenced in Time Intelligence filters within PerformancePoint. 

As a first step, you will need to launch ‘Performance Point Dashboard Designer’ and… Read more

0 comments, 1,820 reads

Posted in Salvo(z) SQL on 18 February 2013

Older posts