THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Microsoft OLAP by Mosha Pasumansky

Default members, MDX Scripts, Security, KPIs and Perspectives

What happens to the cube after the processing is complete but before cube is ready for the querying ? There are actually quite a few steps that should be taken in order to prepare the cube, like create calculated members and named sets, apply security, factor in actions and KPIs etc. In this article we will provide an overview of the most important steps and discuss issues directly following from how these steps are done and how the ordering of these steps is important.

Cube initialization sequence

At the very high level the following events happen before cube becomes available for querying:

  • System determined default members are applied
  • MDX expressions for user specified default members in DDL are evaluated
  • User specified default members are applied
  • MDX expressions for dimension security (allowed set, denied set etc) are evaluated.
  • MDX expressions for dimension security default members are evaluated
  • Dimension security is applied to all attributes
  • Default members are adjusted if necessary (if default member became secured)
  • Dimension security visual totals are applied
  • MDX Script is executed
  • KPI driven calculated measures are created
  • Actions are added
  • Cell security is applied
  • Perspective measures restrictions are applying
  • Default measure is adjusted to the perspective
We will go now over each and every step and briefly discuss it.

System determined default members are applied

This is kind of bootstrapping process. We really need to set up an execution context before we can go ahead with following steps. Many of the following steps require evaluation of various MDX expressions, and MDX expression always relies on the current coordinates. These coordinates need to be initially set to something. You may ask why not to use directly default members from DDL. The answer is that these could be by themselves MDX expressions, which need to be executed in some context, so we get Catch 22. How does the system determines the "system determined" default members ? Strictly speaking, this could be arbitrary, therefore no application should ever rely on the exact details. But currently the algorithm is following

  • If the attribute is aggregatable (i.e. IsAggregatable=true), then the All member will be chosen as a default member on that attribute
  • If the attribute is not aggregatable then some random member from this attribute will be chosen as a default member. Usually this random member is the first member which was seen during attribute processing, and since AS doesn’t issue ORDER BY statement for attribute processing there is no way to tell which member would be “first”.
  • For the default measure, the first non-hidden measure is chosen. If all measures are hidden, then the first measure is chosen. “First” here refers to the first measure in the measures collection in AMO and DDL (but not in MDSCHEMA_MEASURES, which is always alphabetically sorted).

MDX expressions for user specified default members in DDL are evaluated

From the previous paragraph, it is obvious, that normally the user will always want to overwrite the [almost random] system selection of default members on non-aggregatable attributes. And perhaps the default measure as well (although this one is not as random). Default members can be MDX expressions, and they are evaluated in the context of the system determined default members established at the previous step.

But things are not as simple as they look. Let’s consider a simple example. Have you tried to define default member in measures one of the dimensions (dimension attributes really) to be a calculated member ? If you do it by using Analysis Services 2005 UI from BI Studio – you will get an error similar to this one:

DefaultMeasure: Member object ‘Profit’ not found when parsing [Measures].[Profit]

What is going on ? Is it a bug ? It very well might be perceived as a bug, especially given a fact that in Analysis Services 2000 UI Analysis Manager such operation worked fine. But actually, amazingly enough, from the engine perspective, this is not a bug – it is By Design behavior. And, of course, it is possible to set default members to be calculated members – it’s just that the UI for it is not obvious. I agree that for the user, trying to change default member through the property called "Default Member" is the most natural thing to do, and it appears as a bug when it doesn’t always work, but there is an explanation. What happens here is the order of applying default members vs. executing MDX Script. The default members specified by the user in UI get translated into attribute property DefaultMember or cube property DefaultMeasure in DDL, and they are applied before MDX Script is executed. Since all calculated members are created inside MDX Script, if default member refers to calculated member – the error is raised, because there are no calculated members yet. This isn’t the only scenario when such an ordering is not desired. In my book, "Fast Track to MDX", there is chapter about default members, and the example we give there is to set the default member of the Time dimension at the latest day for which there is a data in the fact table and it works with any cube in which this dimension is used, even if different fact tables have different latest date recorded. This is achieved by the following MDX expression for AS2000:

Tail(Filter(Time.Day.MEMBERS, NOT IsEmpty(Measures.DefaultMember)),1).Item(0).Item(0)

By the way, in AS2005 this expression could be simplified to


However, this won’t work in AS2005 for the same reason – since default members are evaluated before MDX Script was executed – it means that the CALCULATE statement wasn’t executed yet, therefore most of the cells in the cube are still empty, therefore Filter (or NonEmpty) function will return empty set, and default member cannot be NULL. On the other hand evaluating and applying DDL default members after MDX Script execution has its drawbacks. For example, the named sets and SCOPE subcubes need a context to be evaluated in, and the default members need to be factored in it. So there is really no "right" decision whether or not to apply DDL default members before or after MDX Script – either way, there will be scenarios when it will be wrong. And sometimes it is desirable to set default members in the middle of MDX Script, such that calculations before affect it (like CALCULATE) and calculations after are affected by it (named sets). Or even for different attributes default members should be set at different points of the MDX Script. It is, of course, possible to do – from the engine point of view pretty much the same way as it was done in AS2000. In AS2000 setting default members in the UI of Analysis Manager simply generated new ALTER CUBE command in the cube commands collection. Same can be done in AS2005, only that instead of collection of commands we have more convenient MDX Script itself. So what user needs to do is to insert into MDX Script at the right place (say, at the end of MDX Script) the appropriate ALTER CUBE statement, like the following example


One nice touch about ALTER CUBE statement, is that it operates on the hierarchy, therefore it allows setting default member on multiple attributes in one statement. (Of course, if all levels in the hierarchy have their underlying attributes defined relationships between each other – this is a moot point, because changing default member on one such attribute will anyway affect the others).

The disadvantage of using ALTER CUBE statement in the MDX Script is that it needs to be added to the MDX Script of every cube where the default member needs to be set. However, it also can be argued, that in this case anyway default member depends on the MDX Script (either by being a calculated member or derives from the calculations) and therefore probably should be tailored separately to each cube anyway.

As we have explained above, the MDX expressions for attribute default members in DDL should be such that they can be evaluated without cube context. For example, if Year is non aggregatable attribute, and we want to set default year to be the last year from the dimension, then the following expressions are fine:


Or if we want to tie it to the current year, then

or, even better, call to the server stored procedure which returns the member object for the Year hierarchy based on whatever logic the designer put into this stored procedure

All these expressions don’t need to get anything from the cube, so they are fine.

User specified default members are applied

Default members evaluated in the previous step are applied to the context. It is important to separate these two steps, although it seems natural to combine them together. However, default members are evaluated in isolation one from another. Therefore, until all the expressions are evaluated, they are not applied.

MDX expressions for dimension security are evaluated.

Dimension security is applied before the MDX Script is executed. Therefore all the static expressions inside MDX Script (such as named sets and calculation SCOPE subcubes) will be resolved in the context of dimension security. In dimension security you can use arbitrary MDX expressions for specifying allowed and denied sets. The UI normally generates simple member enumeration sets, but using Advanced tab you can define more complex set expressions. At this point, the system already determined all the roles to which the user belongs (called active roles), and it evaluates all the MDX expressions for all AttributePermission objects at all active roles. These expressions are resolved in the context of the current user, therefore function Username resolves to that user. But not only that, also if you are using stored procedure inside MDX expressions for dimension security, and this stored procedure has ImpersonationLevel=ImpersonateCurrentUser, then it will work as well. Both of these things are the foundation for the dynamic dimension security techniques. More information about it can be found at

MDX expressions for dimension security default members are evaluated

Dimension security can specify its own default members. This feature is more about personalization then about security (as we will quickly see in the next paragraph).

Note, that while the MDX expressions for dimension security were evaluated at the previous step, the dimension security itself wasn’t applied yet. Therefore expressions for dimension security default members are still evaluated in the unsecured context.

Dimension security is applied to all attributes

Now it is time to apply dimension security to all attributes. This means that the evaluated allowed and denied sets in every attribute and every active role are applied to form virtual bitmask over the attribute members. It is bitmask in a sense that it is a data structure which tells for every attribute member whether it is secured or not. It is virtual, because the physical implementation is not necessarily a bitmap, many times it is more sophisticated data structure, however, it is guaranteed to never require more memory then single bit per attribute member. Usually it requires much less memory then that. Merging of active roles is simply a virtual OR operation between these virtual bitmaps, and it is very efficient. Merging security implied default members is different, because at the end there could be only one default member, therefore if there is a conflict between the roles in this respect, one of them overwrites the others. It is not possible to control which default member will win. Process of merging active roles has other interesting rules about how visual totals settings are merged etc.

Default members are adjusted

It is possible to set definitions in such a way that DDL defined default member or even dimension security defined default member ends up to be secured one. Obviously this would be a security hole, therefore after dimension security is applied, AS goes over all the default members and makes sure that they are all allowed. If previously defined default member is not allowed, it is changed using similar rules as in system determined default members.

Dimension security visual totals are applied

Dimension security visual totals now are applied to the current context, therefore all the calculations which will be done from that point on will be done with visual totals on the attributes defined in dimension security

MDX Script is executed

This is obviously most important step, where all the calculations are created etc. Since discussion about MDX Scripts can easily occupy few more blog articles, or even a book, we shall not say anything more here.

KPI driven calculated measures are created

KPIs are one of the most publicized features in AS2005, and they are very user appealing. Yet, their implementation is very simple and straightforward. When the user defines KPI object properties such as KPI value, KPI goal, KPI trend etc, the user can specify the MDX expressions for these. What happens behind the scenes is that AS creates hidden calculated measures for each one of these properties and assign the MDX expression specified in the property to the calculated measure. Later, when user queries KPIs, the KPI browser generates queries using MDX functions KPIValue, KPIGoal, KPITrend etc, which return these hidden calculated measures. Now, we are at the step when these hidden calculated measures are created. As you noticed, this step happens after MDX Script was executed. One interesting consequence of it is that the calculated measures created at this step will be executed after all other calculations in the MDX Script. (Or to put it in AS2000 terms, they will have higher solve order and/or pass). Is it a problem ? It could be. Let’s suppose that our MDX Script looks like following:

CREATE Account.Variance = (Account.Budget – Account.Actual)/Account.Actual;

I.e. we want to compute percentage how far budget deviated from the actuals. Now, let’s also suppose that we have KPI called Profit, which will have the following simple expression for its value

Measures.Sales – Measures.Cost

There will be hidden calculated measure called Profit_Value created behind the scenes, and when the user will want to see the variance of the Profit, he will navigate to the intersection of Variance and Profit_Value calculated members – and since Profit_Value is executed after Variance, the user will get difference of ratios instead of ratio of differences ! So how this problem can be solved. Actually, it can be solved very easily if we know one additional piece of information. KPI trigger creation of hidden calculated measures only if the MDX expression for the KPI property is not a simple reference to some measure (either calculated or physical). However, if the expression is a simple reference, such as [Measures].[Sales], then no hidden calculated measure will be created, and KPIValue, KPIGoal, KPITrend etc functions will simply return that measure. Therefore, one can (perhaps even should) create all the calculations in the MDX Script (where they really belong in the first place), and resolve all the precedence rules there using power of MDX Scripts. Therefore in our example the MDX Script would be rewritten as following:

CREATE HIDDEN Profit = Measures.Sales – Measures.Cost;
CREATE Account.Variance = (Account.Budget – Account.Actual)/Account.Actual;

And define the expression for the KPI Profit value simply as [Measures].[Profit] (there is no problem with KPIs having same names as calculated members – they belong to different namespaces). Since calculated measure Profit was defined in the MDX Script before the calculation for Variance, it will also be executed before, and therefore the results will start to make sense.

Actions are added

Actions are really noop as far as calculations or current context are concerned. All the MDX expressions used in actions are evaluated dynamically upon invocation of MDSCHEMA_ACTIONS schema rowset.

Cell security is applied

Unlike dimension security, cell security is applied after the MDX Script was executed. Therefore all the static calculations (such as named sets and calculation SCOPE subcubes) were resolved without taking cell security into account. Of course, all the dynamic expressions (i.e. everything at the right hand side of the assignment operator – expressions of calculated members, custom member formulas etc) will be executed using cell security. Cell security is applied by applying virtual OR operator on top of cell security boolean expressions in all active roles.

Perspective measures restrictions are applying

We are close to the end now. The last piece deals with perspectives. Perspectives are really almost the same as the cube. It is mostly about hiding some objects, i.e. MDSCHEMA_DIMENSIONS, MDSCHEMA_HIERARCHIES, MDSCHEMA_MEASURES, MDSCHEMA_SETS etc return less rows. But the MDX is not affected by these changes (almost!). However, there is one important difference, and it deals with how measures (but not calculated members !!!) are hidden. When MEASURES.MEMBERS is used as axis expression, then only the measures and calculated measures included in the perspective are included in the set. But if same is used deeper inside expression for calculation, for example defining calculated member which does MEASURES.MEMBERS.COUNT, then the number which will be returned will count all the measures, including the hidden ones. Such behavior should sound familiar to the people who worked with MDX subselects and CREATE SUBCUBE. And indeed, the way perspective restrictions on measures are implemented is doing internal

(SELECT PerspectiveMeasures ON 0 FROM CubeName)

This is pseudocode, because in AS2005 CREATE SUBCUBE cannot change the name of the cube on which it operates, but perspectives obviously have different name from the cube

Default measure is adjusted to the perspective

Perspectives, of course, can overwrite default measures, since they may exclude the cube default measure. And even if default measure for perspective is not explicitly specified, it still may change. Just like with any other subselect and CREATE SUBCUBE statement, after it is done, the default members are adjusted to fit into the space defined by the subcube. Since for perspectives the subcube restriction is on measures only, the default measure is adjusted.

Published Saturday, December 31, 2005 12:00 AM by mosha
Filed under:
Anonymous comments are disabled
Privacy Statement