Gautham KAMATH

BI Developer specializing in SSAS and MDX

Posts Tagged ‘Explicit Overwrite

Too many Attributes in the SSAS dimension can spoil the MDX query

leave a comment »

All of us have heard about the importance of having the correct and appropriate attribute relationships and their impact on MDX query performance. There are a few instances when having too many attributes and attribute relationships can backfire. One such instance is with the Explicit and Implicit conversions. Let me explain.

Attribute relationships:

Attribute relationships create dependencies between attributes. In other words, if City has a RELATED attribute State, then City has a many to one relation with State and conversely State has a one to many relation with City. More specifically, if the current city is Seattle, then we know the State must be Washington.

Related and Relating relationship:

If State is RELATED to City, then City is said to be a RELATING to State.

Because RELATING attribute has a many-to-one relation with RELATED attribute the coordinates for RELATING attributes (City in this case) move to [All] member. For RELATED attribute, that has a one-to-many relation with the RELATING attribute, the coordinates move to one specific member.

Explicit and Implicit Overwrite:

An Explicit overwrite takes place in an expression. Related and Relating attributes undergo an Implicit overwrite. Consider the following query

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount]),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM [Adventure Works]
     WHERE [Customer].[Customer Geography].[City].&[Seattle]&[WA]

It is a simple MDX query that returns the Internet Sales Amount for the City of Seattle in Washington.

x Internet Sales Amount
$75,164.86 $75,164.86

Now lets play with it and add [Customer].[Stare-Province].CurrentMemberto the calculated member expression

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount],
                                    [Customer].[State-Province].CurrentMember),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM [Adventure Works]
     WHERE [Customer].[Customer Geography].[City].&[Seattle]&[WA]

The query returns the following results

x Internet Sales Amount
$2,467,248.34 $75,164.86

As we know WHERE clause reduces the dimensionality of cube space and hence changes the cube context for the query. What has happened here is

  • [City].[Seattle] is current context (WHERE Clause reduces the dimensionality of cube space and performs an implicit EXISTS with the AXES).
  • Explicit conversion of [State-Province].CURRENTMEMBER to [State-Province].[Washington] (one member from State)
  • Because [State-Province] is related to [City], [State-Province].[Washington] remains as it is (one member from State).
  • Now because [City] is relating to [State-Province], [City].[Seattle] gets implicitly converted to [City].[ALL Customers]
  • Hence the tuple expression for [Measures].[x] becomes ([State-Province].[Washington], [City].[ALL Customers], [Measures].[Internet Sales Amount])

Out of curiosity, let us see what happens when we have subselects instead of WHERE clause?

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount],
                                    [Customer].[State-Province].CurrentMember),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM  (SELECT [Customer].[Customer Geography].[City].&[Seattle]&[WA] ON 0
           FROM [Adventure Works]) 
x Internet Sales Amount
$29,358,677.22 $75,164.86

Subselects do not reduce dimensionality of cube space and therefore do not change the cube context in which the MDX expressions are to be evaluated. In this case there is no Implicit EXISTS between the Subselect and the AXES and hence the [State-Province].CurrentMember gets Explicitly converted to All member [State-Province].[ALL Customers]. The tuple expression for [Measures].[x] in this query becomes ([State-Province].[ALL Customers], [Measures].[Internet Sales Amount]).

So far so good. This is as per design and how it should behave. Now let us look at the above mentioned scenario but with different dimension ([Product]) and members. Consider the following query

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount]),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM [Adventure Works]
     WHERE [Product].[Product Categories].[Product].[Mountain-100 Silver, 38]

Again a plain and simple MDX query that returns the Internet Sales Amount for the Product “Mountain-100 Silver, 38”.

x Internet Sales Amount
$197,199.42 $197,199.42

If we tweek the expression

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount],
                                    [Product].[Subcategory].CurrentMember),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM [Adventure Works]
     WHERE [Product].[Product Categories].[Product].[Mountain-100 Silver, 38]

We get the same result as the previous expression.

x Internet Sales Amount
$197,199.42 $197,199.42

Result does not seem to be correct as we were expecting $9,952,759.56 but we are getting $197,199.42. It is supposed to be doing the same thing as I mentioned before

  • [Product].[Mountain-100 Silver, 38] is current context (WHERE Clause performs an implicit EXISTS with the AXES).
  • Explicit conversion of [Subcategory].CURRENTMEMBER to [Subcategory].[Mountain Bikes] (one member from Subcategory)
  • Because [Subcategory] is related to [Product], [Subcategory].[Mountain Bikes] remains as it is (one member from Subcategory).
  • Also because [Product] is relating to [Subcategory], [Product].[Mountain-100 Silver, 38] gets implicitly converted to [Product].[ALL Products]
  • Hence the tuple for [Measures].[x] becomes ([Subcategory].[Mountain Bikes], [Product].[ALL Products]).

Yet the result seems to be incorrect. Why? This is because there are TOO MANY ATTRIBUTES in [Product] dimenion and most of the attributes are RELATED to the Product level (leaf level). All those attributes get set by auto-exists when filtering to a specific Product. The following is the subcube information from profiler when the query is fired.

00000000, 000, 000, 010111111111111111111, 000000000000000000000, 00, 100, 000000000000000000000, 000000000000000000000, 000000000000000000000, 00

It equates to the following.

     Dimension:   Product (21)
          Attribute:    Standard Cost
          Attribute:    Color
          Attribute:    Safety Stock Level
          Attribute:    Reorder Point
          Attribute:    List Price
          Attribute:    Size
          Attribute:    Size Range
          Attribute:    Weight
          Attribute:    Days to Manufacture
          Attribute:    Dealer Price
          Attribute:    Class
          Attribute:    Style
          Attribute:    Model Name
          Attribute:    Product Line
          Attribute:    Subcategory
          Attribute:    Status
          Attribute:    Start Date
          Attribute:    End Date
          Attribute:    Large Photo
     Dimension:   Destination Currency (3)
          Attribute:    Destination Currency

There are 21 attributes in the Product dimension of which 15 attributes are related to the Product level. What it means is that the above 15 attributes perform auto exist at their respective all level with the Product Level (leaf level) because of the attribute relationship. In context of the query featuring City and State there was only one related attribute (State) at the City level and hence the issue did not arise there.

ROOT function:

The desired behaviour is got back by application of ROOT function. As defined by mdsn, the ROOT function returns a tuple that consists of the All members from each attribute hierarchy within the current scope in a cube, dimension, or tuple. So by modifying the above query in the following way

     WITH MEMBER [Measures].[x] AS ([Measures].[Internet Sales Amount],
                                    ROOT([Product].[Subcategory].CurrentMember)),
                                    FORMAT_STRING = "currency"
     SELECT {[Measures].[x], [Measures].[Internet Sales Amount]} on 0
     FROM [Adventure Works]
     WHERE [Product].[Product Categories].[Product].[Mountain-100 Silver, 38]

we get the desired results

x Internet Sales Amount
$9,952,759.56 $75,164.86

The profiler subcube trace reads the following

00000000, 000, 000, 000000000000000010000, 000000000000000000000, 00, 100, 000000000000000000000, 000000000000000000000, 000000000000000000000, 00

and it equates to

     Dimension:   Product (21)
          Attribute:    Subcategory
     Dimension:   Destination Currency (3)
          Attribute:    Destination Currency

P/S To read the profiler trace I have used a tool created by James Snape

To summarize:

  • Have the correct attribute relationships.
  • Be particularly careful with CurrentMember and know where the Explicit and Implicit overwrite takes place.
  • As far as possible keep the attributes to the minimum.

Written by Gautham KAMATH

04/12/2011 at 17:27