Cases where MDX is a better alternative to cube rules

[Adam]

I wanted to start a dedicated clean thread to discuss a great topic:

I'm genuinely interested if there are use cases where an MDX view can deliver things that are not possible or obviously better than in a rule / consolidation?

I would encourage friends to provide other use cases.
Likewise it would be educational for those that prefer Rules to provide the alternative.

• [Year of First Sales] - Using a single "Year" dimension.

  Code: Select all

  (NONEMPTY([Year].[Year].[(All)].CHILDREN , {([Measure].[Measure].[Sales])}).ITEM(0).ITEM(0).NAME) 

• [Year of Peak Sales] - Using a single "Year" dimension.

  Code: Select all

  (TOPCOUNT([Year].[Year].[(All)].CHILDREN , 1 , ([Measure].[Measure].[Sales])).ITEM(0).ITEM(0).NAME) 

• [Peak Sales] - Using a single "Year" dimension.

  Code: Select all

  MAX([Year].[Year].[(All)].CHILDREN , ([Measure].[Measure].[Sales]))

[gtonkin]

Think this example falls into the "better" category.

I had a case where users need to track a measure as a percentage of Sales Revenue.
Adding a rule to do this across any number of scenarios, periods, SKUs, Customers etc. etc. would have added huge calculation overhead.

The users who review the percentage also only review the results at a very high level so much of what is being calculated would have been irrelevant.

The simple solution here was to use MDX to derive and add the percentage measure and then display this in the view that the users need.

I cannot remember the exact impact in terms of memory usage but consider that in this case, the Sales cube is approaching 10GB (on disk) and we have a few thousand SKU and Customers extrapolating to brazilians of intersections.

For a case like this one where a measure can be derived and displayed using MDX, I think MDX is the clear winner rather than bloating and burdening the cube.

[declanr]

I use it all the time to display values from 1 cube alongside an input view for another cube with some related dimensions.
No point in duplicating data in various points across a model, if the purpose is just to present something to a user for info then I tend to try and do it in the presentation layer (in this case the MDX views)

Code: Select all

WITH 
	MEMBER [Cube1_Measures].[Cube1_Measures].[Example] 
		AS [Cube2].(...,[Dim].[Hier].CURRENTMEMBER,[Cube2_Measures].[Example]) 
SELECT 
	<RowDefinition> ON 0, 
	<ColumnDefinition> ON 1 
FROM 
	[Cube1]
WHERE 
	(
		<WhereClauses>
	)

[gtonkin]

100% Declan - I think that developers really need to think about what should be presentation layer vs rule engine and consider various factors like re-usability, ease of use, performance (short and long term with data growth) etc. etc.

[MarenC]

Hi,

very interesting, not really thought about using MDX in this way.

If I need to show values from 2 different cubes that is when I decide a PAX websheet is required and I display that in PAW, but I suppose this is an alternative to that, in certain cases.

Maren

[Steve Rowe]

I'll play devil’s advocate in the interest of keeping the debate going! No criticism intended!

My use case for MDX views, wouldn’t be for calculations but using picklists to deliver advanced filtering of records / cells and PAW wide synchronisation across user sessions. Picklists are a great way of guiding users to make selections and avoids pushing them into the set editor. These picklists values persist and so books don’t reset to the default when the user begins their next session.
Strictly not an MDX calculation of course.

@Adam
These are all great examples of MDX sets delivering something that is hard / not sensible to use in rules.

@GTonkin
Lightly used KPIs. Not convinced on this one...In terms of calculated data points there would be no difference between a MDX and ruled approach, only the data points that are requested are calculated. With the rules approach we would have the feeder overhead which might be large. This could be minimised using a C level KPI / feederless approach…. I'm not sure I'm buying the cube bloat argument. Delivering it quickly without recompiling the rules and maybe getting tangled up in the customers change control are clear benefits though.

@declanr
Avoiding duplicating data for presentation reasons, clearly something we should all be aiming for. I'm not quite getting your use case though. Are you returning values from two different cubes in a single exploration or is this a more sophisticated way of synchronising two side by side explorations?

Some general concerns about leaning heavily on MDX calculations.

• Do we know where the work happens, TM1 DB, PAW, client side? Are we making best use of our hardware? I assume its on the DB, but I can also recollect a conversation where I was surprised to learn that at least some of the MDX happens in the client or PAW.
• I assume they are not cached in the same way that ruled values are.
• Not available for onwards use in the application. For example, in the KPI situation they are only available in the specific view, if I then want to display them somewhere else, I’m stuck. (Or I have to redo the work risking error).

[lotsaram]

There are functions which MDX can deliver quite easily which TM1 rules either can't deliver unless you go back to 1st principles algebra (e.g. standard deviation) or where it simply isn't worth writing a separate rule for or for things like min/max/count/average where the relevance of the analysis is on the current set and level of the members beig queried and not on the whole cube.

And creating a MDX based calculation is as easy as this.

Screenshot 2022-10-12 150124.png (15.39 KiB) Viewed 2228 times

[Adam]

I had a case where users need to track a measure as a percentage of Sales Revenue.
Adding a rule to do this across any number of scenarios, periods, SKUs, Customers etc. etc. would have added huge calculation overhead.

Rarely do we disagree, but here I am on the same page as Steve. For us this would be a c-level rule. It would have no storage impact as consolidations are not stored. Does it have a calculation impact? I am assuming the actual consolidation would also want to be reviewed by the person viewing the report, so that's all pre-calculated and then there's a one step X \ Y. I suppose there is also a RAM impact if the result of the calculation is stored.

• Do we know where the work happens, TM1 DB, PAW, client side? Are we making best use of our hardware? I assume its on the DB, but I can also recollect a conversation where I was surprised to learn that at least some of the MDX happens in the client or PAW.
• I assume they are not cached in the same way that ruled values are.

These are great questions. We may have an AMA with Hubert coming up where I can ask because I'm also curious.

[gtonkin]

I'll play devil’s advocate in the interest of keeping the debate going! No criticism intended!

...
@GTonkin
Lightly used KPIs. Not convinced on this one...In terms of calculated data points there would be no difference between a MDX and ruled approach, only the data points that are requested are calculated. With the rules approach we would have the feeder overhead which might be large. This could be minimised using a C level KPI / feederless approach…. I'm not sure I'm buying the cube bloat argument. Delivering it quickly without recompiling the rules and maybe getting tangled up in the customers change control are clear benefits though.
...

In the view I am talking about, the user is looking at a high level requiring all underlying points to calculate.
Tested this quickly again to be sure and may be a nuance with the particular system/rule being added.

Essentially have this:
['Log Fee %'] = C: ['Sales Measures':'Log Fees'] \ ['Sales Measures':'Total Sales Revenue'];

After about 10 minutes the rule finished saving and memory usage had doubled, no feeders. Opening the view also took ages with the 3 measures above.
Does not make sense, so maybe this in an edge case and some other dependencies are causing this behaviour and need to be further investigated.

Happy to be wrong here but still think there is value in having infrequently used calculations only needed at certain levels, done in the presentation layer.

Edit/Update: Looks like an edge case as in this particular model, Log Fee % is populated at a Leaf level via process.
In live plans, the percentage is used to derive Log Fees. There are multiple sources too, Input used to store the % from the process, Rule-Derived to store the derived Log Fee and you then have layers for adjustments, restatements etc.
Where there is not leaf level value, proxies are used e.g. Brand on the product dimension, Customer group etc. etc. These are C levels.
The Log Fee % at a C level is then trying to be calculated which is in turn used in Log Fee and in some cases I picked up circular references when drilling into the data - depends where a proxy is being used.
So either I would need to write a couple of rules to do this between live and static rules or add another measure where we have the seeding for the Log Fee %.
In short, probably an isolated case but using the presentation layer was a quick (and possibly dirty) way around having to jump through some hoops to get the rules working as required.

[gtonkin]

Leading on from Lotsa’s post, I have a requirement to calculate the probability of default in a Risk Weighted Asset model.

Unfortunately there is no normal distribution or inverse normal distribution function but if there was, you could create an MDX view and use it as a source to a TI.

Essentially use MDX functions to bridge the gap with rules and TI functions.