Introduction
In multidimensional SSAS cubes the default member of a dimension is the ‘All’ member. This can be overridden through the Default Member property to force a particular member to be the default choice rather than the ‘All’ member.
In multidimensional cubes there are at least 2 methods to construct default members in a dimension.
Set as property of the dimension
Declare the DefaultMember in the calculation script (method used in the Financial data layer)
By way of contrast, ONLY the ‘All’ member is handled by default in the Tabular model. One must implement any other setting in DAX. The following are several examples of constructing default members in a tabular cube using DAX to establish a default.
Use Cases
For a Parameter Table
In the Financial/Accounting data layer, a number of parameters drives the computation of the Amount measure. The value chosen determines the amount column reported from the fact. ‘Amount Type’ is one such parameter. ‘Amount Type’ is a standalone Dimension in the model, i.e., it joins to no other tables. It contains the members: Accounting, Original, Transaction, Company, Home and Group. The value ‘Transaction’ should be used if one Amount Type is not available in any given filter context. To set the default the following DAX expressions are set up as Measures (pseudo names/code are used, please check before using the following code explicitly):
Example calculations (as measures)
[Amount Type Default] := “Transaction”
[Amount Type Is Selected] := HASONEVALUE ( ‘Amount Type'[Amount Type] )
[Amount Type Selected] := IF ( [Amount Type Is Selected], MINA ( ‘Amount Type'[Amount Type] ), [Amount Type Default] )
[Amount] := SWITCH ( [AmountTypeSelected], “Transaction”, SUM ( [TransactionAmount] ), … )
As an Additional Filtering Requirement (a Conditional Default)
The Period Calc filters for calendar period calculations are an example of a case where filters need to be applied as a pair or not at all. The design used combines the calendar and Period calc dimensions as one dimension. Relationships are created in the data that permit values to be summed in order to generate period calcs. This table is set up in such a way that for each calc type, an ‘As Reported’ Posting period is a collection of all the periods that are necessary to produce the period aggregation. For example, to report YTD for period 2016-03, there would be 3 records with the Posting Periods being 2016-01, 2016-02 and 2016-03. See the diagram for the structure implemented. This design does not expose The ‘Monthly Calendar’ and ‘Period Types’ dimensions (shaded in diagram). They are simply there to be able retrieve the required attributes into the ‘Posting Period’ dimension. This improves performance (subject of another blog post in this series). The Bridge table is also not exposed. It is there solely for the purpose of bridging the fact to the Posting period through two 1:M relationships rather than the natural direct connection of the M:M relationship.
Calendar-PeriodType ER Diagram
In a tabular cube, the issue arises because there are no ability to set a default member for a dimensional attribute. Applying a filter to only one of the attributes causes incorrect results. The issue surfaces as repeated values for a measure where distinct values are expected. When filters are not applied, everything works as expected. Suppose that some member of the calendar hierarchy (the ‘as Reported’ Posting Period hierarchy) is filtered. In this case, a filter must be applied to [Period Calc Type] for everything to hang together. There is no guarantee that the user will select one, and there is no default for [Period Calc Type] (or any other attribute for that matter) therefore values will be duplicated. So it is required that the condition where something in the calendar is filtered and the [Period Calc Type] is not filtered must be detected and an appropriate filter applied (in this case the filter was for a [Period Calc Type] = ‘Current Period’).
Example calculations (as measures)
[DefaultPostingCalc] := “Current Period”
[Posting Periods IsFilteredA] := OR ( OR ( ISFILTERED ( ‘Posting Period'[Month] ), ISFILTERED ( ‘Posting Period'[Quarter Name] ) ), OR ( ISFILTERED ( ‘Posting Period'[Year] ), ISFILTERED ( ‘Posting Period'[Year Name] ) ) )
[Posting Periods IsFiltered] := OR ( [Posting Periods IsFilteredA], OR ( ISFILTERED ( ‘Posting Period'[Year] ), ISFILTERED ( ‘Posting Period'[Month Name] ) ) )
[Period Calc IsFiltered] := OR ( ISFILTERED ( ‘Posting Period'[Period Calc Key] ), COUNTROWS ( SUMMARIZE ( ‘Posting Period’, ‘Posting Period'[Period Calc] ) ) = 1 )
[RequiresDefaultApplied] := AND ( [Posting Periods IsFiltered], NOT ( [Period Calc IsFiltered] ) )
[Amount_1_PostDfltApplied] := IF ( NOT ( [RequiresDefaultApplied] ), [Amount_RAW_Base], CALCULATE ( [Amount_RAW_Base], FILTER ( ‘Posting Period’, ‘Posting Period'[Period Calc Key] = [DefaultPostingCalc] ) ) )
Summary
The SSAS Tabular model (TOM) is new and evolving, particularly in SSAS 2016. Many features that are available and relied upon in Multidimensional cubes are not available as of this writing in the Tabular model. One of these missing features is the DefaultMember property of dimensions. With a little DAX coding, it is possible to implement the missing behaviors presented by these limitations.
_________________________________________________________________________________________
To learn more on how Tallan can help transform your data into knowledgeable insights with SQL Server 2016, CLICK HERE.