CODEWITHSUNDEEP
ssasmdxhierarchyhierarchical-data
Shvets
Shvets

Reputation: 71

Aggregate data in a SSAS hierarchy

I have a hierarchy in a dimension in my cube, and a fact table that references that dimension. Let's call that a hierarchy of departments and the fact table contains billing targets set for each department (on a record-per-day basis if that's of any interest). The data is aggregated as in a usual hierarchy in a multidimensional cube.

The problem is that the data in the fact table is not just limited to the "leaf" departments in the hierarchy tree, and so each node/department may or may not have an actual value for a billing target.

I need to have my measure returning the actual value for any node if there is a value, or the sum of the values of it's descendants (if they themselves have actual values, then ignore their descendants). I can return the value at any given node by using the .DATAMEMBER property and add a condition to substitute the null (if that is the case) with the sum of descendants as so:

with member DirectD as (iif(([Department].[Departments].currentmember.datamember, [Measures].[Department Billing Target]) = null, ([Measures].[Department Billing Target]), ([Department].[Departments].currentmember.datamember, [Measures].[Department Billing Target])))

The problem is that I cannot ensure that the same logic applies to every node "down" the hierarchy. The MembersWithData setting is set to NonLeafDataHidden in the dimension. [Measures].[Department Billing Target] is a calculated member, so it looks like Aggregate is not an option.

As an example, I'd like to get the value of 3000 if I query the billing target for department A (image 1): Image 1

For department C on Image 2 I need to get 1400 (E, while having descendants, has an actual value which takes precedence over the sum of it's children). Image 2

Any help would be greatly appreciated.

Upvotes: 0

Views: 670

Answers (2)

Shvets
Shvets

Reputation: 71

In the end, I have added the following solution that is working so far:

    IIF(NOT ISEMPTY(([Department].[Departments].CURRENTMEMBER.DATABEMBER, [Measures].[Department Billing Target Canonical])), 
  ([Department].[Departments].CURRENTMEMBER.DATABEMBER, [Measures].[Department Billing Target Canonical]), 
  IIF(ISEMPTY(SUM(descendants([Department].[Departments].CURRENTMEMBER, [Department].[Departments], AFTER), IIF(
    ISEMPTY(([Department].[Departments].CURRENTMEMBER.parent.DATABEMBER, [Measures].[Department Billing Target Canonical])), 
    ([Department].[Departments].CURRENTMEMBER.DATABEMBER, [Measures].[Department Billing Target Canonical]), 
    NULL))), 
  ([Department].[Departments].CURRENTMEMBER.DATABEMBER, [Measures].[Department Billing Target Canonical]), 
  SUM(descendants([Department].[Departments].CURRENTMEMBER, [Department].[Departments], AFTER), IIF(
    ISEMPTY(([Department].[Departments].CURRENTMEMBER.parent.DATABEMBER, [Measures].[Department Billing Target Canonical])), 
    ([Department].[Departments].CURRENTMEMBER.DATABEMBER, [Measures].[Department Billing Target Canonical]), 
    NULL))))

Granted it may not be the prettiest solution (in fact I'm pretty sure it's one of the ugliest), but it works for me.

The gist of it is that the algorithm checks if the parent node of the current one has any data (currentmember.parent.datamember) and if so, substitutes the current member's value with NULL. All those values are piled up in a set, and the sum of it's "contents" is the resulting value. The outmost IIF is there to cover a sort of an edge case.

EDIT: There is also a feeling that this whole issue is just a matter of some SSAS setting that has to be changed. This setting continues to elude me.

Upvotes: 0

MoazRub
MoazRub

Reputation: 2911

The Hierarchy that you mention seems to be a parentchild hierarchy since you say "The problem is that the data in the fact table is not just limited to the "leaf" departments in the hierarchy tree". Based on this, the problem is that we need to solve is 1)Foreach member of you hierarchy we call the base attribute. 2)If the measure value for the base attribute is null we retuen the summed up value 3)If the measure value for the base attribute is not null then we retuen the indivisual members value. Below is the code piece, there can be some syntax issues but would explain the idea.

The below code is a similar example on adventure works(I did add FullName attribute in the employee dimension). In the picture on right is a parent child hierarchy's, base attribute. This picture show all the employee who had sales. On the left is the hierarchy, notice how the values change in the Test measure for people who are present on the right hand side. For example take a look at "Amy E. Alberts"

enter image description here

The code is 

with member [Measures].test as
case when [Employee].[Employees].currentmember.name='All Employees' 
then [Measures].[Reseller Sales Amount]
when (strtomember('[Employee].[Full Name].['+[Employee].[Employees].currentmember.name+']'),[Measures].[Reseller Sales Amount])=null 
then [Measures].[Reseller Sales Amount]
else (strtomember('[Employee].[Full Name].['+[Employee].[Employees].currentmember.name+']'),[Measures].[Reseller Sales Amount])
end
select {[Measures].[Reseller Sales Amount],[Measures].test}
on columns,
[Employee].[Employees].members 
on rows from [Adventure Works]

Upvotes: 1

Related Questions