Calculated Members in Subselects and Subcubes

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/Power BI Premium

A calculated member is a dimension member whose value is calculated from an expression at run time, and can be used in subselects and subcubes to more precisely define the cubespace of a query.

Enabling calculated members in the subspace

The SubQueries connection string property in ConnectionString or the DBPROPMSMDSUBQUERIES property in Supported XMLA Properties (XMLA) defines the behavior or allowance of calculated members or calculated sets on subselects or subcubes. In the context of this document, subselect refers to subselects and subcubes unless otherwise stated.

The SubQueries property allows the following values.

Using values of 1 or 2 in the SubQueries property allows calculated members to be used to filter the returning subspace of subselects.

An example will help clarify the concept; first a calculated member must be created and then a subselect query issued to show the above mentioned behavior.

The following sample creates a calculated member that adds [Seattle Metro] as a city to the [Geography].[Geography] hierarchy under Washington state.

To run the example, the connection string must contain the SubQueries property with a value of 1 and all MDX statements must be run in the same session.

First issue the following MDX expression:

  
CREATE MEMBER [Adventure Works].[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg]   
   AS  AGGREGATE(   
                 {   
                   [Geography].[Geography].[City].&[Bellevue]&[WA]  
                 , [Geography].[Geography].[City].&[Issaquah]&[WA]  
                 , [Geography].[Geography].[City].&[Redmond]&[WA]  
                 , [Geography].[Geography].[City].&[Seattle]&[WA]  
                 }  
                )    

Then issue the following MDX query to see calculated members allowed in subselects.

Select [Date].[Calendar Year].members on 0,  
       [Geography].[Geography].allmembers on 1  
from (Select {[Geography].[Geography].[State-Province].&[WA]&[US].[Seattle Metro Agg]} on 0 from [Adventure Works])  
Where [Measures].[Reseller Sales Amount]  

The obtained results are:

As said before, the ascendants of [Seattle Metro] do not exist in the returned subspace, when SubQueries=1, hence [Geography].[Geography].allmembers only contains the calculated member.

If the example is run using SubQueries=2, in the connection string, the obtained results are:

As said before, when using SubQueries=2, the ascendants of [Seattle Metro] exist in the returned subspace but no values exist for those members because there is no regular members to provide for the aggregations. Therefore, NULL values are provided for all ascendant members of the calculated member in this example.

To understand the above behavior, it helps to understand that calculated members do not contribute to the aggregations of their parents as regular members do; the former implies that filtering by calculated members alone will lead to empty ascendants because there are no regular members to contribute to the aggregated values of the resulting subspace. If you add regular members to the filtering expression then the aggregated values will come from those regular members. Continuing with the above example, if the cities of Portland, in Oregon, and the city of Spokane, in Washington, are added to the same axis where the calculated member appears; as shown in the next MDX expression:

Select [Date].[Calendar Year].members on 0,  
       [Geography].[Geography].allmembers on 1  
from (Select {  
               [Seattle Metro Agg]  
             , [Geography].[Geography].[City].&[Portland]&[OR]  
             , [Geography].[Geography].[City].&[Spokane]&[WA]  
             } on 0 from [Adventure Works]  
     )  
Where [Measures].[Reseller Sales Amount]  

The following results are obtained.

In the above results the aggregated values for [All Geographies], [United States], [Oregon] and [Washington] come from aggregating over the descendants of &[Portland]&[OR] and &[Spokane]&[WA]. Nothing comes from the calculated member.

Remarks

Only global or session calculated members are allowed in the subselect or subcube expressions. Having query calculated members in the MDX expression will raise an error when the subselect or subcube expression is evaluated.

See Also

ConnectionString
Subselects in Queries
Supported XMLA Properties (XMLA)