All posts by Dustin Ryan

Dustin is a Senior Business Intelligence Consultant with Pragmatic Works. You can find Dustin blogging and speaking at events like SQL Saturday, Code Camp, and SQL Rally. Follow Dustin on Twitter @SQLDusty.

The MDX Rank Function

If you read my blog regularly, you may have seen this post from last week where a reader asked how to create  set to get the top 5 members while lumping every member outside of the top 5 into an “Others” member. As a follow up question, another reader asked how we can rank the members being displayed.

We can create a numeric ranking of the Customers by leveraging the MDX Rank function. The Rank function returns a one-based rank of the supplied member of the supplied set. So how do we update our original query to accomplish this?

Here is our original query:

with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)



member [Customer].[Customer].[Others] as

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

dynamic set [Top 5 & Others] as

{
[Top 5 Customers],
[Customer].[Customer].[Others]
}

Select [Measures].[Internet Sales Amount] on 0,


[Top 5 & Others] on 1


From [Adventure Works]

To create the ranking, we need to create an additional calculated member to display the rank of the customers (which you can see below highlighted in red):

with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)



member [Customer].[Customer].[Others] as

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

dynamic set [Top 5 & Others] as

{
[Top 5 Customers],
[Customer].[Customer].[Others]
}

member [Measures].[Rank] as

Rank([Customer].[Customer].CURRENTMEMBER,[Top 5 & Others])


Select {[Measures].[Internet Sales Amount],[Measures].[Rank]} on 0,


[Top 5 & Others] on 1


From [Adventure Works]

The Rank function excepts two arguments. The first argument is tuple (or member in this case) that we wish to rank. The second argument would be the set we want to rank. In our case, our set is [Top 5 & Others]. Don’t forget to add your new Rank measure to be displayed on the columns! :)

And here’s our results:

image

If you found this helpful, leave me a comment and let me know! And don’t be afraid to post any questions!

Creating Calculated Members with MDX

This week I got an email from a reader named Brad asking a specific question regarding MDX. Here is his question:

I need an MDX query to return me the top 5 values and then default all others to an “other” group and ranks based on the resulting measure value.  For instance, if we are talking about a measure by month for a full year, I need the output to look like this:

Dim   Measure   Rank

Nov      $500        1
Mar      $400        2
Feb      $300         3
Jan      $200         4
Dec      $100         5
Other  $350         6

Any input will be most appreciated! 

Thanks,

Brad

I thought this was a good question which is why I thought I’d take the time to blog this out. In this example I’ll be using the Adventure Works cube and the Adventure Works Customer dimension.

The first step is to determine what are our Top 5 Customers. To do this, we can use a simple TopCount function to build a set of the Top 5 Customers.

with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)

Once we have our Top 5 Customers, then we can easily determine everyone else. To create our custom “Others” group, we need to create a Calculated Member (Custom Member). To create the Calculated Member, you need to specify the dimension, attribute hierarchy, and member name for your custom member (ie [Customer].[Customer].[Others]).

member [Customer].[Customer].[Others] as 

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

To create our Others custom member, we need to use the Except function to basically specify that we want our custom member to include all our customers except the Top 5 Customers set we previously created. Also, don’t forget to wrap the set in the Aggregate function.

Lastly, we create our set that will actually be used in our query. This set will include our first set containing our Top 5 Customers as well as our Others custom member. We can use the Top 5 Customers set with our custom member because all members in the set are from the same Customers hierarchy.

dynamic set [Top 5 & Others] as 
{
[Top 5 Customers],
[Customer].[Customer].[Others]
}
 
Now all that’s left to do is to use our set in a query. Here’s the entire query so you can see the total syntax.
 
with dynamic set [Top 5 Customers] as 

Topcount(
[Customer].[Customer].[Customer].members,
5,
[Measures].[Internet Sales Amount]
)



member [Customer].[Customer].[Others] as

Aggregate(
Except(
[Customer].[Customer].[Customer].members,
[Top 5 Customers]
)
)

dynamic set [Top 5 & Others] as

{
[Top 5 Customers],
[Customer].[Customer].[Others]
}

Select [Measures].[Internet Sales Amount] on 0,


[Top 5 & Others] on 1


From [Adventure Works]

And here’s the results in the SSMS query results windows.

image

I hope that was pretty straight forward and not too confusing. If you thought this was helpful leave me a comment and let me know! And if you have any questions or comments, feel free to leave a comment.

Thanks again, Brad, for the great question!

Check out my next blog post to see how to return the numeric rank of the customers!

How to Pass Multiple Values from an SSAS Report Drill Through Action to an SSRS Report

These past couple weeks I faced a client requirement to create an SSRS Report Action in an SSAS cube. This requirement dictated that a user should be able to browse a cube in an Excel pivot table and be able to drill through to an SSRS report (which used SQL Server as the data source) and view data at a monthly level. The twist was that if the user drilled from a year or quarter level, the appropriate months would be selected by default. I thought the solution I came up with was useful so here you have it, as recreated using Adventure Works :) . I won’t walk through the step-by-step of creating a Report Action in SSAS, but I will hit the high points of overcoming this requirement.

I created the report action in the Cube Designer in SSDT and called it SSRS Sales Report. Because I want the users to be able to click on a measure, I set the Target type to “Cells” and the Target object to “All cells”.

image

But I really only want the users to see the action if they right-click the Reseller Sales Amount measure, so I added the following conditional logic so the action will only appear for the Reseller Sales Amount.

image

If you wanted to limit the action to appear beneath more than one measure but not all measures, just use “or” and specify other measures as seen in the example below. Also, in my specific requirement, the action should only work for a certain time frame (2007 forward in this example) so I added the logic you see after the “and”.
image

(Measures.CurrentMember is [Measures].[Reseller Sales Amount] 
or Measures.CurrentMember is [Measures].[Internet Sales Amount])
and
COUNT(
Intersect(
{[Date].[Calendar Year].&[2007]:NULL},
[Date].[Calendar Year].CURRENTMEMBER)
) > 0

Next, I added in the Report Server URL and SSRS report link. In my case, the report server was SharePoint Integrated.
image

Now this is where things start to get interesting. I need to dynamically pass the year and month values to the SSRS report. What values to pass from SSAS really depends on the parameters in the SSRS report. If the SSRS report is using SSAS as a data source, then you may be able to use the unique name of the attribute members. In my case, the SSRS report used a regular ole SQL Server database as the data source so I needed to format the parameter values appropriately. The Parameter Name values should reflect the name of the parameters in the SSRS report. Also, if you’re using an SSRS report in SharePoint, don’t forget to include “rp:” as a prefix on the parameter name. For example, if your parameter is named “Year”, the parameter name you would enter into the action would be “rp:Year”. Again, this only applies if your SSRS report server is SharePoint integrated.

In the table below, you’ll noticed I’m using the Right function to return the right four characters of the name of the Calendar Year member. This is because in SSAS the Calendar Year caption appears as “CY 2007” for 2007 for example. In the SSRS report, however, the parameter value would be “2007”. The Month parameter is a little trickier. Because I want the user to be able to view multiple months at a time depending on what level of the Date Hierarchy they click on, I used the Generate function with the Descendants function to create a comma delimited string of the member values at the month level.

Parameter Name Parameter Value
rp:Year RIGHT([Date].[Calendar Year].CURRENTMEMBER.NAME, 4)
rp:Month

GENERATE(

     DESCENDANTS([Date].[Calendar].CURRENTMEMBER,
          [Date].[Calendar].[Month]),

     LEFT(
          [Date].[Calendar].CURRENTMEMBER.NAME,

          3),

     “&rp:Month=”)

So if you’re wondering what the Generate function is doing here, take a look at the following query and the query results and that should clear it up for you.

with member [Measures].[Months param] as 

GENERATE(
DESCENDANTS([Date].[Calendar].CURRENTMEMBER, [Date].[Calendar].[Month]),
LEFT([Date].[Calendar].CURRENTMEMBER.NAME,3),
"&rp:Month=")

Select [Measures].[Months param] on 0,

DESCENDANTS(
[Date].[Calendar].MEMBERS,
[Date].[Calendar].[Month],
SELF_AND_BEFORE
) on 1
From [Adventure Works]

image 
In the results you can see that one of the neat things the Generate function can do is to create a delimited list. The third argument of the Generate function is my delimiter and in this case, I want it to be “&rp:Month=” so I can specify multiple values to pass to the parameter in the SSRS report. So if the user right-clicks a quarter, 3 months will be passed to the report. If the user clicks a semester, 6 months should be passed.

And here’s a screen cap of the Parameters in the Cube Design in SSDT in case you were wondering.

image

The last step is to make the caption for the action dynamic. Set the Caption is MDX to “True” and then you can do neat things with the action like use the CurrentMember’s Name property in the caption of the action.

image

Here’s the complete screen grab of the Action if you need it:

image

So that’s fantastic and all but what does it look like on the end user’s end? Here I have a pivot table with my date hierarchy and a couple measures. If I right-click the Reseller Sales Amount for Q4 2007, select Additional Actions, you’ll see the caption for my brand new action: View SSRS Sales Report for Q4 CY 2007.

image

And the report correctly picks up the values from the Action:
image

Keep in mind that this action works for any level of the Date Hierarchy down to the date level. Very slick!

If you look at the URL, you can see that the parameters being passed to the report through the URL query string:

http://spsqlbi/sites/BI%20Center%20Test/_layouts/15/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/BI%20Center%20Test/Reporting%20Services/Adventure%20Works%20Reports/SalesReport.rdl&rp:Year=2007&rp:Month=Oct&rp:Month=Nov&rp:Month=Dec&rs:Command=Render&rs:Renderer=HTML5

Chances are that you’ll have to struggle through this a few times like I did. So here’s a couple tips to help you troubleshoot:

1. If the action does not appear where you expect it to in the pivot table, then there could be a syntax issue with your MDX in the Action definition in SSDT. Double-check your MDX.
2. If the action takes you to the report correctly, but the values in the parameters aren’t selected, then you probably aren’t passing the correct values to the report for the parameter. For example, if the Month parameter in the report uses Month Number as the values (ie 1 = Jan, 2 = Feb, etc) but you pass “January” from the Action, then the value will not be automatically selected.
3. Lastly, you are subject to the URL query string character limit in place by the browser. For example, IE has a limit of 2,083 characters last I checked, so if you’re trying to pass a selection of 500 customer values to an SSRS report you will probably run into some problems.

I hope that some of you out there found this useful. If you did, share it with a friend or leave me a comment and let me know! Thanks!

AWlogo

Script To Populate AdventureWorksDW DimDate

I do quite a bit of training for Pragmatic Works so I find myself working a lot with the AdventureWorksDW data warehouse database. AdventureWorksDW is a great test database if you’re wanting to demonstrate a concept or test a theory, but one of the things that has always bugged me is that the date dimension (dbo.DimDate) has holes in the data! Having a complete date dimension is important when working with SSAS. Well yesterday was the final straw, so I put together the following script that will fill in the missing dates in DimDate. You can specify a start date and end date and make the AdventureWorksDW DimDate date dimension as big as you like. Enjoy!

BEGIN TRAN 

declare @startdate date = '2005-01-01',
    @enddate date = '2014-12-31'

IF @startdate IS NULL
    BEGIN
        Select Top 1 @startdate = FulldateAlternateKey
        From DimDate 
        Order By DateKey ASC 
    END

Declare @datelist table (FullDate date)

while @startdate <= @enddate

Begin 
    Insert into @datelist (FullDate)
    Select @startdate

    Set @startdate = dateadd(dd,1,@startdate)

end 

 Insert into dbo.DimDate 
    (DateKey, 
        FullDateAlternateKey, 
        DayNumberOfWeek, 
        EnglishDayNameOfWeek, 
        SpanishDayNameOfWeek, 
        FrenchDayNameOfWeek, 
        DayNumberOfMonth, 
        DayNumberOfYear, 
        WeekNumberOfYear, 
        EnglishMonthName, 
        SpanishMonthName, 
        FrenchMonthName, 
        MonthNumberOfYear, 
        CalendarQuarter, 
        CalendarYear, 
        CalendarSemester, 
        FiscalQuarter, 
        FiscalYear, 
        FiscalSemester)


select convert(int,convert(varchar,dl.FullDate,112)) as DateKey,
    dl.FullDate,
    datepart(dw,dl.FullDate) as DayNumberOfWeek,
    datename(weekday,dl.FullDate) as EnglishDayNameOfWeek,
    (Select top 1 SpanishDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as SpanishDayNameOfWeek,
    (Select top 1 FrenchDayNameOfWeek From DimDate Where EnglishDayNameOfWeek = datename(weekday,dl.FullDate)) as FrenchDayNameOfWeek,
    datepart(d,dl.FullDate) as DayNumberOfMonth,
    datepart(dy,dl.FullDate) as DayNumberOfYear,
    datepart(wk, dl.FUllDate) as WeekNumberOfYear,
    datename(MONTH,dl.FullDate) as EnglishMonthName,
    (Select top 1 SpanishMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as SpanishMonthName,
    (Select top 1 FrenchMonthName From DimDate Where EnglishMonthName = datename(MONTH,dl.FullDate)) as FrenchMonthName,
    Month(dl.FullDate) as MonthNumberOfYear,
    datepart(qq, dl.FullDate) as CalendarQuarter,
    year(dl.FullDate) as CalendarYear,
    case datepart(qq, dl.FullDate)
        when 1 then 1
        when 2 then 1
        when 3 then 2
        when 4 then 2
    end as CalendarSemester,
    case datepart(qq, dl.FullDate)
        when 1 then 3
        when 2 then 4
        when 3 then 1
        when 4 then 2
    end as FiscalQuarter,
    case datepart(qq, dl.FullDate)
        when 1 then year(dl.FullDate)
        when 2 then year(dl.FullDate)
        when 3 then year(dl.FullDate) + 1
        when 4 then year(dl.FullDate) + 1
    end as FiscalYear,
    case datepart(qq, dl.FullDate)
        when 1 then 2
        when 2 then 2
        when 3 then 1
        when 4 then 1
    end as FiscalSemester

from @datelist dl left join 
    DimDate dd 
        on dl.FullDate = dd.FullDateAlternateKey
Where dd.FullDateAlternateKey is null 




COMMIT TRAN
I’ve tested the script against AdventureWorksDW2012 and AdventureWorksDW2008R2 and it worked great.
Let me know if you found this useful! Thanks for reading!
null

How To Display 0’s Instead of NULL in your SSAS Cube & MDX Query Results

I’ve done a decent amount of SSAS and MDX query development over my career and one of the requirements that continually pops up is to display zeros instead of NULL when browsing the cube. Take the following query as an example:

Select {[Date].[Calendar Year].Children} on 0,

NON EMPTY {[Geography].[State-Province].members} on 1

From [Adventure Works DW]

Where [Measures].[Reseller Sales Amount]

 

And here are the results:

image

There are a couple ways display 0’s instead of NULL. The first way is to create logic in your MDX script that uses a CASE statement of an IIF expression that manually checks for empty cube space using the ISEMPTY function, similar to the following example:

SCOPE ([Measures].[Reseller Sales Amount]);

THIS=IIF(ISEMPTY([Measures].[Reseller Sales Amount]),0,[Measures].[Reseller Sales Amount]);

END SCOPE;
The problem with this method is that any empty cube space will be populated with 0’s, potentially exploding your results! Rows that should not be displayed are now displayed! In the image below, I’ve highlighted a row that does not have any results that should not be displayed. Queries that usually only return a handful of results could now potentially display thousands or millions of rows! Yikes! And that’s something we definitely want to avoid.
image
There is another way that we can fill the NULL’s with a 0 or any other value we choose: Through the FormatString property. The FormatString property accepts four arguments separated by a semicolon (;). The first argument is how positive value should be formatted, the second is how negative values should be formatted, the third argument is how 0 values should be formatted, and the fourth argument is how NULL should be formatted. The fourth argument is the one we are interested in!
Here is an example of how I am formatting the Reseller Sales Amount: “#,##0.00;-#,##0.00;;0”.
image
By configuring the FormatString property this way, we are no longer filling the empty space in the cube with 0’s, rather we are configuring the measure to only display a 0 should a NULL value appear in the results. Check it out.
image
No more NULL values. If you’d rather display a non-numeric value, you can do this: “#,##0.00;-#,##0.00;;Zero”
image
If you thought that was helpful or enjoyed reading my blog, leave a comment. Feel free to post any questions, as well!

Pragmatic Works Release Doc xPress Server Edition

This month marks the official release of Pragmatic Works’ Doc xPress Server Edition! While everyone knows that Doc xPress gives you the capability to document your SQL Server databases, SSIS packages, SSAS cubes, and SSRS reports in way like no other tool. But with the release of the Server Edition, you can now host your documentation to a hosted web application making it now easier than ever to share documentation, lineage, and data dictionary information across your organization. Imagine being able to provide your technical users as well as your business users a one-stop-shop to all of your organization’s technical documentation without requiring anything to be installed on their desktop! Doc xPress Server Edition provides that capability!

Being able to view your Doc xPress-generated documentation online means that your technical team can quickly and easily assess the impact of changes in your BI environment by conducting a thorough lineage and impact analysis. What SSIS packages, SSRS reports, or SSAS cubes will be affected if I change a single column? Now you know!

How many times have you wondered what your environment looked like in the past? With Doc xPress, you can periodically snapshot your environment and compare snapshots over time so you can quickly and easily assess any changes that have occurred. And now you can view the documentation in your web browser without the need for any desktop configuration or installation.

If Doc xPress Server Edition sounds interesting and you’d like more information on the software, Pragmatic Works is presenting a free online webinar on the technology July 30th at 1:00 pm EST! Head over to PragmaticWorks.com (you’ll need to scroll down a screen or two) to get signed up and registered for the event.

Data Validation Via Data Surf

imageIf you’re a developer like me, you’ve probably at some point had to validate data. Validation is often a tedious and boring job that can involve checking individual records and data values in multiple tables. It’s not fun but its a necessary part of our job after all. Recently I’ve discovered the Data Surf feature of DBA xPress which is part of the Pragmatic Workbench DBA toolset.

Data Surf enables me to begin browsing a database beginning with a single table or even a single record. Using that table or record as a start point, I can navigate to other tables related to my initial selection. I can specify that I’d like to view parent or child records of my selected row in a related table, which makes validating data a snap. So I’d like to show you how we can accomplish that.

I’ll begin by selecting the Data Surf feature from the Pragmatic Workbench.

image

Next I specify the SQL Server and Database I’d like to begin surfing. For this example I’ll start with my trust Adventure Works Data Warehouse.

image

Then I’ll select which table I’d like to begin my surf. Reseller Sales it is. You’ll notice if you hover your mouse over the name of the table, you can view the columns with their data types that exist in the table.

image

I can also optionally specify a specific search criteria. If I’d like to view certain records associated with a product, I can do that. This is a very nice feature for data validation.

image

Now I can see my surf’s starting point. I have a quick view of the records in the table as well as a row count.

image

If I click one of the records in the table , some options appear. On the left side of the screen, I can see related parent tables as well as child tables. In the case of the FactResellerSales, there happen to be no related child tables.

image

In the margin, if I click the dbo.DimEmployee table, I can now see the relationship between DimEmployee and FactResellerSales. I can also see the related Employee record based on the record I had initially highlighted in FactResellerSales. Because I had selected a record with an EmployeeKey of 285, when I click DimEmployee I see the record(s) with EmployeeKey 285. Also, by selecting the DimEmployee table, my related child and parent tables on the left have changed.

image

If I click on the DimSalesTerritory table, I can now see the relationship between DimSalesTerritory and DimEmployee. In the record viewer, I also see the DimSalesTerritory record(s) related to the DimEmployee record with EmployeeKey 285.

image

There’s also some other nice features available with Data Surf. I can customize the colors of the nodes in my diagram in order to produce a simple and easy to view ERD.
image

And once you’re done surfing, validating, etc, you can easily save your diagram as an image.

image

All in all, Data Surf is a very simple and easy to use tool that I can see as being very beneficial to the average developer. As a BI developer, I spend most of my time designing data warehouse solutions and having Data Surf in my back pocket is great. Download the free trial at PragmaticWorks.com and check out Data Surf.