Setting a Default Member using Tabular mode SSAS and Excel 2013
Using the multi-dimensional mode of SQL Server Analysis Services (SSAS), setting the default member for a dimension attribute hierarchy can be as simple as selecting the dimension attribute hierarchy and then setting the default member in the properties window. Setting a default member can be useful if it is desired to set the default date for a Date/Time dimension to today’s date thereby relieving users of the necessity of making the selection themselves. In the tabular mode version of SSAS, setting a default member is problematic partly because the option does not exist. So, how can this issue be addressed?
For the purposes of this discussion, a very specific reporting use case will be addressed with the following constraints:
- A default Date member will be implemented
- The report is an Excel based and rendered via Excel Services
- The Tabular mode instance of SSAS will be used
- It should not be necessary to select the current date
To demonstrate the approach, the ever popular AdventureWorks database will be used in a simplified form, loaded into a Tabular model (ref Figure 1).
Figure 1: Tabular model diagram
This model allows analysis of Reseller sales by Product, Product Category and Product Subcategory over Time. The following calculations have also been added to the model:
Total Reseller Sales := SUM(‘Reseller Sales'[SalesAmount])
YTD Reseller Sales := CALCULATE([Total Reseller Sales],DATESYTD(‘Date'[FullDateAlternateKey],”08-31″))
MTD Reseller Sales := CALCULATE([Total Reseller Sales],DATESMTD(‘Date'[FullDateAlternateKey]))
One additional entity has been included in the model; a “Default Date” entity. Note that this entity contains the default date attributes (Default Date, Default Fiscal Year and Default Fiscal Month). These attributes correspond to the default members that can be set in the Date dimension. Also note that the entity is not connected to any other entity in the model. Figure 2 shows the contents of the entity. It contains a single row populated with the default values for Date, Fiscal Month and Fiscal Year.
Figure 2: Default date member entity
This entity can be populated numerous ways; manually (not the preferred method) or in this particular case via a step in the data extract job that sets the default date based on the current date.
It is now time to address the Excel report. Here is the Excel report that will be used to demonstrate the default date; assuming that the default date is “5/31/2008”. It can be seen in Figure 3 that values are being calculated for the current date, month to date and year to date periods.
Figure 3: Excel report using manually selected date.
To appreciate how the default date approach works, remember that behind the scenes, MDX can be used to retrieve data from that Tabular model and MDX is what will be used along with Excel Sets to set the default date. Excel Set functionality can be accessed from the Excel ribbon under PIVOTTABLE TOOLS -> ANALYZE
(make sure that a cell is the pivot table is selected in order to get the pivot table context menu).
Figure 4: Excel Sets menu
Then select the Manage Sets option, followed by New -> Create set using MDX
Figure 5: Fields, Items, & Sets
Figure 6: Create a set using MDX
Now, it is time to build MDX to use the “Default Date” and “Date” entities in the Tabular model. First here is the MDX.
STRTOMEMBER(‘[Date].[Calendar Date].[‘ + [Default Date].[Default Date].FirstChild.name + ‘]’)
Figure 7: Default date MDX
The left hand pane displays the entities from the Tabular model and the available MDX functions. The right hand pane is where the MDX is entered. The values displayed are for calendar date and represent the member names for calendar dates. The fully qualified name for a calendar date member is:
[Date].[Calendar Date].[Member Name]
In this particular case the value would be: [Date].[Calendar Date].[5/31/2008]
It is important to note that the format for the “Default Date” attribute is identical to the format for the “Calendar Date” attribute member name. Maintaining identical formats eliminates the need to reformat the “Default Date” attribute value using MDX.
The “Set Name” is “Default Date” and the MDX in Figure 7 is dynamically creating the “Calendar Date” member as a string value. The name of the “Default Date” member is being retrieved using the following MDX:
[Default Date].[Default Date].FirstChild.name
This returns the value “5/31/2008” (ref Figure 8)
Figure 8: Default Date member name
The STRTOMEMBER(String to Member) function must be used to convert the string representation of a member into an actual member reference that can be understood by the Tabular model.
Once the set has been created, a “Sets” folder will exist in the Excel pivot table field list under the Date entity. Excel automatically creates the folder under an entity based on the content and references within the MDX (ref Figure 9).
Figure 9: Pivot table sets
Now it is time to use the “Default Date” set. In the following figure. The top pivot table is using “Calendar Date” as a filter. In the bottom pivot table, the “Default Date” set has been placed columns. Note that the same values are returned. Furthermore, if the value for the “Default Date” changes, pivot table will automatically reflect the changes when the pivot table is refreshed.
Figure 10: Default member usage
This solution may not be optimal as indicated by the following Pros and Cons, but it is a technique that should be considered when there is a need to establish default members in a Tabular model.
Tabular Model Default Member Implementation Using Placeholder Entities | |
Pros | Cons |
| Uses native Excel and Tabular model functionality | Requires additional planning during ETL phase |
| Flexible implementation for any entity | Adds additional maintenance to Tabular model |
| Not necessary to update Tabular model XML code | Requires knowledge of MDX |
