All Member Properties - Name, Key and Level

I just tried to find some more information about the All Member in SSAS dimension hierarchies and since it was not readily available, I had to experiment a bit, so I thought I may as well share my findings. For some these may be obvious, but for some they could as well be interesting.

So in brief, I will explore the Name, Key and Level of an All member in a dimension hierarchy. The one of choice was the Customer dimension and Customer Geography hierarchy in Adventure Works. There is an All member, called All Customers. As expected, .PROPERTIES("MEMBER_NAME") gives us "All Customers":

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.PROPERTIES("MEMBER_NAME")
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The first row shows us: All Customers.

Now, let's see what its key is:

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.PROPERTIES("KEY")
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

This gives us 0.

And its level:

WITH
MEMBER [Measures].[test] AS
  [Customer].[Customer Geography].CurrentMember.Level.Name
SELECT
{
  [Measures].[test]
} ON 0,
{
  [Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The result this time is: (All).

So far so good. Now let's try using these to get only the All member:

SELECT
{
  [Customer].[Customer Geography].[All Customers]
} ON 0
FROM [Adventure Works]

This works. Now if we try the Key:

SELECT
{
  [Customer].[Customer Geography].&[0]
} ON 0
FROM [Adventure Works]

Interestingly, since the All member is a calculated member and has no physical key, if we try to use the one that SSAS gave us does not actually work - we get nothing on Axis 0.

Using the level works:

SELECT
{
  [Customer].[Customer Geography].[(All)].Item(0)
} ON 0
FROM [Adventure Works]

Also, after experimenting a bit further:

SELECT
{
  [Customer].[Customer Geography].[All]
} ON 0
FROM [Adventure Works]

This query also works even though the All member name is [All Customers], not just [All]. However, Analysis Services does recognise [All].

In summary, the most robust options for referencing the All member in a dimension hierarchy that I have found are:

1. [Dimension].[Hierarchy].[(All)].Item(0)
2. [Dimension].[Hierarchy].[All]

These will always work - regardless of the dimension and hierarchy names.

Another option is using [Dimension].[Hierarchy].[] - e.g. [Customer].[Customer Hierarchy].[All Customers]

And, one that does not work - referencing through its alleged key: [Customer].[Customer Hierarchy].&[0]

Please let me know if there are any better alternatives, or why it would give me a key of 0 for the All member and would not work when actually using this key.

An Open-Source Approach to Business Intelligence

Since Microsoft discontinued PerfromacePoint Planning, it is slightly unclear what is their strategy in this field. I have made a few suggestions in the past, some of which:

• Stored-Procedure write-back from Excel
• SSAS write-back from Excel
• Better support for dimensional and measure group write-back

Whether, when and how these may get implemented is an open question. I firmly believe that we should continue learning from any possible source – including our competitors. This is why I am quite interested in any possibility to extend my knowledge in the BI space regardless of who is presenting the ideas and solutions.

Being lucky as I am, I received an open invite to the PALO Australia Roadshow 2010, presented by a company I recently posted about – Naked Data. In a brief, what you can expect from PALO and the presentation is:

• In-memory MOLAP
• Rule-based forecasting and planning
• Some sci-fi GPU parallel processing
• Integration with Excel (PALO formulas and Pivot tables)
• .NET API
• Free breakfast

I will be there trying to get some ideas for my future forecasting and planning implementations. Seats are limited, so make sure you register soon!

Average Aggregation in Analysis Services

In SSAS we do not have a measure Average aggregation type. We do have AverageOfChildren (or Average over time), however it is semi-additive and works only along a Time dimension. Fortunately, we have Sum and Count, and since Average = Sum / Count, we can build our own Average aggregation when we need one.

To do that:

1. Create a measure using the Sum aggregation type (which is also the default). In our example, let's call it Amount.
2. Create a Count of Non-Empty Values (or Count of Rows) measure. In example - [Measure Count].
3. Create the actual calculation - [Measures].[Amount]/[Measures].[Measure Count]

We can either create a calculated measure, which performs the MDX calculation above:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Amount]
AS
[Measures].[Amount]/[Measures].[Measure Count]
,NON_EMPTY_BEHAVIOR = {[Measures].[Measure Count]}
,VISIBLE=1;

, or if we really do not need the Sum base measure, we can set it to be replaced by the calculation with a SCOPE statement:

SCOPE([Measures].[Amount]);
  This = [Measures].[Amount]/[Measures].[Measure Count];
NON_EMPTY_BEHAVIOR(This) = [Measures].[Measure Count];
END SCOPE;

Voila! We have created a measure simulating an Average aggregation type. Then, we can hide the Count helper measure and from user point of view there is no evidence of our effort.

Since the count will never be 0, we do not have to say "If not 0, divide, else - do not" and the NON_EMPTY_BEHAVIOR query hint may in fact improve performance, since the calculation will not be performed when the Count measure is NULL (instead of resulting in NULL/NULL=NULL).

Mosha has previously blogged about NON_EMPTY_BEHAVIOR and division by zero and I strongly recommend reading his post.

Another important consideration, which depends on the business scenario is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values (Column Bound). The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well.

I realise that this is a well-known approach, but since it is hard to find the solution online I thought it may be interesting for some less-experienced developers.