If your BI project has complex reporting and dashboard requirements and if you are struggling for a solution, then it is time to start thinking about SSAS Named Sets. I use Named set in many ways in my BI projects and I have always found the Named Set to be a handy tool in SSAS reporting.
First let’s define Set. A set is collection of objects. Just like a set, a Named Set is a collection or set of dimension members. A special language called Multidimensional Expressions (MDX) is used to create a Named Set. They can define and save as part of the SSAS cube or they can also create in client applications. Named Sets are easy to understand, as they are simply a pre-defined collection of dimension members. We can create named sets by combining cube data, arithmetic operators, numbers, and functions.
You can learn how to define a Named set by reading this TechNet article. http://technet.microsoft.com/en-us/library/ms166594.aspx.
Let’s expand further with an example. A common requirement for a report is to display measured value for the current fiscal year and last year to the same relative point in time. If you have structured your time dimension properly, it will be very easy for you to create PPS and SSRS reports. However what’s required if the dashboard’s requirement is to display all activity by default i.e. for the “past 12 months,” “current month,” “current year,” “today” or whatever the requirement might be? For now, let’s take the scenario of displaying the activity for the “Past 12 months.”
Step1: On the Calculations tab create a new Calculated Member [TodayDate] by clicking the appropriate icon. It will be used to calculate the past 12 month so you can set visible False and add the following code:
Step2: On the Calculations tab create a new Named Set
[CurrentDate] by clicking the appropriate icon. Add the following code:
Filter([Time].[Calendar].[Date],[Time].[Calendar].MemberValue = ([Time].[Date].[All],TodayDate))
This will have to be adjusted to fit your own Time dimension, it will always return the Current Date.
Step3: On the Calculations tab create a new Named Set [Last 12 Months] by clicking the appropriate icon, then add the magic code which will always return the last 12 Months:
ANCESTOR(CurrentDate.Item(0), [Time].[Calendar].[Month]).lag(1):ANCESTOR( CurrentDate.Item(0), [Time].[Calendar].[Month]).lag(12)
You can clearly observe that the above Named Set [Last 12 Months] is nicely placed in the application – within the time dimension. It can be used with any measures and also with all calculated measures.
You can then use the named set as a slicer in multiple client application (ProClarity, Reporting Services, Excel, etc.). In Performance point’s report, it can be placed into the axis area. For any measure in the series area, it will display all activity for the last 12 month by default and the user doesn’t need to select any filter.
The Named Sets are commonly used in SSAS reporting. The following are some scenarios where Named Set can be applicable:
- Create Top (n) reports like called Top 10 Customers, Top 5 denials etc.
- Last 12 months trend
- Last 3 months moving average
- Display amount for some specific dimension members