Handling Account Ratio Hierarchy in SSAS Tabular

Question

Handling Account Ratio Hierarchy in SSAS Tabular

jordan 32624

Valued Member

Points: 61
More actions
March 23, 2018 at 11:34 am

#344508
Hey Folks,
I'm considering migrating a few of our solutions over to SSAS Tabular from Multidimensional to gain some performance in reporting and dashboarding. From initial tests everything works great, much faster, easier to develop, and we don't use drillthrough actions. My issue is in how I can handle an account ratio hierarchy in Tabular. Here are my thoughts:
- I could create a bunch of measures specifically for these ratios and create a "hierarchy" visually through display folders, but I don't think this is the correct solution.
- I can also create a parameterized measure using switch that checks which row it's on and returns a different measure, but this doesn't work great with hierarchies as there's no concept of parent/child. I would have to first determine which level I'm navigated to, which would significantly complicate the switch formula, unless I left all ratios in a single level, but then there's no hierarchy.
- Lastly I could create a separate fact table designed specifically for ratios and use a calculated table to create the row members based on the fact table and this way I don't need separate measures per ratio, each member should have their values present in their own row.
In multidimensional, I can create any number of members and use customrollup formula to calculate how that member presents its value, including its format. The style of hierarchy I'm thinking of, ratio wise, is along the lines of:
- Total Volume
- Total Volume per day
- Total Revenue
- Total Revenue per Volume
- Total Royalties
- Total Royalties per Volume
- Total Expense
- Total Expense per Volume
- Royalty Rate (Royalty per Revenue)
- Net Profit
- Net Profit per Volume
Each parent level would have similar child levels below per category. How should I handle this? Does a custom fact table make the most sense? Process the calculations in the SQL view or in a calculated table?
NGL Volumes Per Day
Gas Volumes Per Day

The parent levels can't always simply be the sum of their children (IE, If Oil was 5 BOEpd and Cond was 2 BOEpd, that doesn't necessarily mean Oil, Condensate parent is 7 BOEpd). This is the case for a lot of rates (expense/revenue, profit/volume, etc)

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 1

This sounds similar to something I just did. I have a "measures" dimension and a fact table that has two measures: Amount and Denominator. If denominator is blank I use 1 for the non-ratios. The measures dimension has rollup capability also. I have an "IsTotal" hidden attribute in the dimension which says whether it is the value that rolls up. Hope this helps.

jordan 32624 Valued Member Points: 61 More actions · Answer 2

Right, I was thinking this is the right direction, but I'm curious how I'd handle the per day items. Thinking of Volumes Per Day, you could have:

Total Volumes Per Day

Oil, Condensate, NGL Volumes Per Day

Oil, Condensate Volumes Per Day

Oil Volumes Per Day
Condensate Volumes Per Day

Brian Carlson SSCarpal Tunnel Points: 4170 More actions · Answer 3

That's right. That's the reason for keeping Amount and Denominator separate. Create a measure for each and hide them. Then the final measure is Amount/Denominator.

DAX pseudo-code:
CALCULATE(DIVIDE([Amount], IF(ISBLANK([Denominator], 1, [Denominator]))))
[Amount] := SUM(Amount)
[Denominator] := SUM(Denominator)

Denominator is blank for non-ratios.
DAX may not be precise but that's the general idea. My formula actually has a lot more in it than that for other requirements. You may end up needing some filter context manipulation also.

One catch to this approach though...one measure is one data type. I just used decimal and then manually format the numbers. We're using Excel and it remembers the formatting at a tuple. It works for our purposes.

jordan 32624 Valued Member Points: 61 More actions · Answer 4

So just expanding my thought process here, cause this will work. I would have to really increase the size of the fact table due to each dimensions requirement. Entity, account, accounting period, activity period, gross/net, hmn. I'll take this away and see what I can virtualize or normalize down.