Home > BISM, DAX, MDX, PowerPivot, SSAS > OLAP Rigidity wins against Tabular?

OLAP Rigidity wins against Tabular?


There are many reasons why you might choose a Tabular Model over Multidimensional one. Marco Russo discusses some of the pros of tabular in his blog here which general relate to the flexibility that the tabular model gives you. However, one reason surprising reason for choosing multidimensional may be its rigid dimensional structure and the certainty that this structure gives in changing data environments.

A Multidimensional dimension provides many ways to record information against an attribute. Most importantly these are the attributes KEY, NAME and VALUE which are set under the dimension source properties. For example we can see the [State-Province] attribute from the Geography dimension in Adventure Works as below. The Key is the only property that must be set for an attribute so if no name is specified, the key will automatically be applied as the name.

Note that in the tabular engine, there is no concept of the KEY or Name. These are not specified as native DAX refers to column value(s).

Referring to Attribute Members

When an MDX query refers to members of the attribute it can do so by using the member name or the members unique name. Thus, the two MDX statements are equivalent;

– query using members unique name

select

[Measures].[Internet Sales Amount] on 0,

[Product].[Product Categories].[Category].&[4] on 1

from [Direct Sales]

 

– query using members name

select

[Measures].[Internet Sales Amount] on 0,

[Product].[Product Categories].[Category].[Accessories] on 1

from [Direct Sales]

 

There are many reasons why you would choose the first query over the second, namely, the second can give unexpected values when there is more than one member with the name being sought. That is, if there were two product categories (with different keys) and the same name (as Accessories), then the query would return only the first member. One might expect that the query would aggregate the results but this does not occur.

Once the query has been written, the members unique name is confusing and many people complain that it lacks reference to the underlying data (after all its impossible to know what does .&[4] means anyway). However, it is still the best way to refer to a member and this is the way most client tools generate MDX (using the unique_member_name).

Note that since the tabular engine has no concept of a key for an attribute (you don’t uniquely specify the key or name for an attribute) the MDX equivalent passed to the tabular engine uses what we would otherwise consider the name as the key. Thus, as in the example above, the tabular equivalent for this member is [Product].[Category].[Category].&[Accessories]. To an OLAP (multidimensional) developer, this type of key (ie the name as the key) is generally considered a real NO NO and contravenes best practice.

 

Client Queries and the interesting stuff.

For tabular models, the unique name for a member is generated by the name. Furthermore, most client tools refer to the key in their script. I have even seen some tools hard code the published member for parameters. So, what happens when the underlying attribute name changes? The change could break your report.

We can easily demonstrate how this error would occur using Excel as a client. Suppose I have a pivot based on a multidimensional cube and I convert the simple pivot to formulas (as below). The Accessories member in Cell A2 is hardcoded (the formula is in D2 and uses the key [4] as above). If the name of this member changes, the spread sheet still works fine, and the new name is displayed.

However, for the tabular example (as below), when the hardcoded name is changed, the MDX returns a #N/A error for the CUBEMEMBER function.

In the second picture, the name of the member has changed to [Accessories & Other] however the ‘report’ still relies on the hard coded value .&[Accessories] and the error occurs.

Conclusion

The use of a ‘key’ for each member would offer a way to solve the problem as addressed above and this is only available in multidimensional dimensions. To be fair though, the problem is as much a function of design as it is of structure. A multidimensional dimension would still raise the issue if the attributes name had been used as a key (but no-one does that do they?).

About these ads
  1. Ralf
    August 21, 2013 at 12:52 am | #1

    I am not quite sure what you mean by unique_member_name here. If it is whatever the UniqueName function returns, then coding that one as in your Excel example is quite a bad idea because the results depend on the server-side settings. If you mean something else, then that’s not clear and it would be good to know what is the way to build some unique name to refer a member.

  2. August 21, 2013 at 1:36 am | #2

    Hi Ralph. The unique_member_name (or should i say member_unique_name) is an inherent property of each member. Basically, its key which is set in the attributes properties. By default this also appears as the name .. but if you want to build a performance cube, its better to explicitly set the key (as an int).
    See http://msdn.microsoft.com/en-us/library/ms145528.aspx

  3. Ralf
    August 21, 2013 at 6:37 am | #3

    Hi. That’s the thing – that unique name is generated by the provider and you cannot have control over it to be sure that it does not change. It’s a kind of internal representation and, if so, it cannot be used in a formula because the provider representation can change.

    • August 21, 2013 at 6:52 am | #4

      Hi Ralph, Not true. The unique name is determined by the data (based on the key) – although the key is numbered by index. This was the point to the article … Rather than relying on the name to define the key (in fast changing environments) .. Your better of using a key this is especially true for type 1 scd’s

  4. Ralf
    August 21, 2013 at 7:27 am | #5

    From the reference you added: MEMBER_UNIQUE_NAME
    The unique name of the member. For providers that generate unique names by qualification, each component of this name is delimited.

    That is, the provider decides that unique name and, as far as I can tell, this is the same name that the UniqueName function returns, which is documented even clearer to depend on the server settings.

    It is probably possible to build an unique name that does not change, but not the “MEMBER_UNIQUE_NAME”, at least, that’s how I understand the documentation. I couldn’t find any documentation though about how a client application can construct a unique name without getting it from the server.

  5. August 21, 2013 at 7:54 am | #6

    OK … I can see I haven’t won you over and perhaps my explanation is not as clear as I think it is. The choice is yours of model is yours and I use both – both have merits and hindrances.

    The unique name is easily definable for the following syntax dimension.hierarchy.&key (although it is changeable).

  1. April 1, 2013 at 2:09 pm | #1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: