Search This Blog

Saturday, May 23, 2009

Accessing duplicate members in dimensions

In the last post we had an issue with multiple members sharing the same name but with different key attributes. Every approach of addressing such members with MDX like

[Dimension].[Hierarchy].[Name] or [Dimension].[Hierarchy].&[Key]

fails because this fetches only one particular member while the measure data may be associated with one of the other members sharing the same key.

Ok, usually you shouldn't be in this situation. If for example you're using the business key (instead of the surrogate key) as the attribute key, you only get one member and all the fact data is associated to that member. Therefor the above member expressions work fine.

But with parent-child attributes you cannot do this easily and writing the MDX from the previous post in order to filter all elements with a given name is somehow painful. And if you're keeping historical elements in the parent-child hierarchy you may want to find all elements with a given name.

The last post showed a solution to achieve this using MDX by filtering the elements by name, but this is somehow painful especially with many elements. So for today's post we're trying a different approach with a stored procedure.

The stored procedure simply takes a hierarchy and a string with comma separated member names and returns all matching members in the correct order. This may also be helpful for report development if you need to provide a specific subset of elements (say cost centers, regions, branches etc.) in a given order. Usually you can put those elements in a report parameter (maybe a hidden one) and use the strtoset-function to expand the elements to a set. But as stated above, this doesn't work with elements that share the same name and it might also be difficult to achieve a manually given sort order.

So, here's how the function works in the Adventure Works cube:

select {[Measures].[Amount]} on 0,
ASTools.GetMembers([Account].[Accounts],'Assets,Current Assets, Other Assets') on 1

from [Adventure Works]

The result looks like this:

image_thumb1

Note that although we just specified 'Other Assets', both accounts with that name are now in the list. This is the behavior we wanted to achieve. As stated in the last post, in reality, the two account are not the same, but for our example, we pretend they are as the situation would be exactly identical with slowly changing parent-child structures.

For clarification, take a look at the following query:

select {[Measures].[Amount]} on 0,
{
[Account].[Accounts].[Assets],
[Account].[Accounts].[Current Assets],
[Account].[Accounts].[Other Assets]
} on 1

from [Adventure Works]

In this case the account 'Other Assets' is specified by it's member's unique name which refers to only one member of the dimension. As expected, the result looks like this (note the missing $75K):

image_thumb2

Finallly, here is the source code for the procedure:

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.AnalysisServices.AdomdServer;

namespace SSASTools
{
public class SSASLibrary
{
public Set GetMembers(Hierarchy h, String memberList)
{
SetBuilder sb = new SetBuilder();
Expression ex = new Expression();

String[] members = memberList.Split(',');

foreach (String cmembername in members)
{
ex.ExpressionText = "filter(" + h.UniqueName + ".allmembers, " + h.UniqueName + ".currentmember.name='"+ cmembername.Trim() + "')";
Set s = ex.CalculateMdxObject(null).ToSet();

foreach (Tuple t in s) {
sb.Add(t);
}
}

return sb.ToSet();
}
}
}

6 comments:

  1. Hi Hilmar

    I am a newbie in SSAS so hope I'm coherent.
    I'm havig a Dimension (SCD)Type2 keeping history and I also have a parent-child hierarchy on that table.Of course the key in the dimension should be unique and it is the surogate key of the table.The dimension is self-linked using a parent attribute.because I'm using surogate key as a key of this dimension and set the Name column of the key attribute to a NameEntity field.

    Having same name for different surogate key's(because of history changes on the same entity) whenever I browse the cube I get members with the same Name for each surogate key diffeent.

    How can I group this members with the same name for a parent-chilr hierarchy.
    Is it possible to use your MDX(from previous post) or this procedure somehow for this issue?

    Thanks in advance!

    ReplyDelete
  2. When browsing the cube, it's sometimes just ok to slice by time to avoid those duplicated scd2 members on parent-child hierarchies. For report (hand-written MDX) you can use the above mentioned procedure but it doesn't help you with Excel or other pivot tools. One approach could be to have no further attributes in your scd2 parent-child dimension (just hierarchy and name) and to resolve this issue in the ETL process.
    Hope this helps a little.
    Hilmar

    ReplyDelete
  3. Thx a lot for the quick answer.
    Well after browsing a little more the internet discovered that my problem it is almost similar with this but still complicate :).
    I have a fact table Customer which will be measured by counting only distinct customers. Each customer can be part (in time) of multiple groups (e.g. X, Y, and Z).In the same time there is a “time”link between groups (per customer).
    Customer (ID=1) was first time part of group X and then later on he is part also from group Y (so parent of Y group is X) and then later on he is also part of group Z(with parent Y). So we have until now X->Y->Z for this customer.
    Another Customer (ID=2) can have only groups Y and X in this dependency Y (first) ->X (with parent Y) and another customer (ID=3) can have X->Z
    Whenever I browse this I need all this parent-child hierarchies grouped (in members area) no matter of customer and just a simple distinct Customer count in measures.
    It’s like answering to question: How many distinct customers has been part of group X and after that of group Y and later of group Z.
    In our example on first level we will have 2 parent groups X (with 2 distinct customers being part of this group on this level (customer 1 and 3)) and Y (1 customer (ID=2)). When I expand X group on second level I will have Y group (customer 1) and Z (of customer 3).And so on for Level 3 I will get when expanding Y from level 2 I will get Z group of customer 1. Same for second parent on Level 1 Y group (of customer 2)
    For this I have a table Dim_CustomerGroups (GroupId, CustomerID, ParentGroupId) where ParentGroupId links to GroupId. Primary key is complex(GroupId,CustomerID) for this table.
    I detailed a little :) so .. maybe you’ll have an ideea how can I define this parent-child hierarchy on Dim_CustomerGroups considering the key of dimension contains customerId.. so members in hierarchy will be grouped also by customerId not only by GroupId.. as I would like
    Thx a lot again for your time!

    ReplyDelete
  4. It seems like you can work with a "simple" parent-child hierarchy because in your situation shouldn't have duplicate member names at each level. In your example, you have three members with the name 'Z' (with different parents). The only tricky thing would be to build your parent-child dimension in the ETL process. For each combination of groups (eg. X->Z->Y) you need to check if there already is a path in your parent-child tree that matches the combination. If so, you can link the fact record (simple count) to the leaf element of this path. If not, you have to create the path recursively starting with the topmost element. For example, if you have a combination like X->Z->Y you start checking for a top-level node 'X'. Let's assume this exists, then you need to check for a child 'Z' below 'X'. If it doesn't exist, you have to create it now and so on.
    And maybe you should think about the CustomerID in your customer group dimension. In most cases it could be better to put the customer in a separate dimension and to link customer and group together with the your facts (counter).
    Hope this was helpful.

    ReplyDelete
  5. Hi Hilmar,

    Do you mean that there is nothing we can do for those duplicated scd2 members on parent-child hierarchies in pivot?

    ReplyDelete
  6. Hi Ewen, You only have this situation if there are multiple instances of the same node in your parent-child hierarchy. This could happen because of an scd2 change (historical view of the hierarchy). However, using the above technique you can still display data for all instances (covering the complete scd2 history) without getting separate lines.

    ReplyDelete