SQL Server 2005 | SQL Server 2008
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:
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):
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();
-
}
-
}
-
}
Kommentare (0)