Slow MDX subset creation in a TI

[PavoGa]

Ran into this within the past week on a TI that has pretty much been running flawlessly for months.

Processing time went from <4 seconds to over 30 minutes. Yep, a wee bit drastic there. So started digging and here are the results:

The offending code. This takes over 30 minutes to execute:

Code: Select all

subAccountsMaster = 'tmp_Test Subset';
# sMDX = 'UNION( {[Accounts Master].CurrentMember}, TM1SUBSETTOSET( [Accounts Master], "P Accounts"))';

SubsetCreateByMDX(subAccountsMaster, sMDX, 1);

# the SubsetCreateByMDX takes 30 minutes.  The SubsetElementDelete performs in a split second.
SubsetElementDelete('Accounts Master', subAccountsMaster, 1);

The P Accounts subset is also an MDX query and here is its code:

Code: Select all

# [Version].[Default] is also a perm MDX subset
FILTER(TM1SUBSETALL( [Accounts Master] ), 
    [lookup.FPRP].(TM1Member([Version].[Default].Item(0), 0), [sys.Websheet Control].[Effective], [lookup.Measure].[Account Type]) = 'P')

I did not write this code and always use the ALL argument in my UNION mdx. Tried that with no discernible improvement.
But if I import the subset's MDX code instead of using TM1SUBSETTOSET:

Code: Select all

subAccountsMaster = 'tmp_Test Subset';
sMDX = 'UNION( {[Accounts Master].CurrentMember}, FILTER(TM1SUBSETALL( [Accounts Master] ), 
  [lookup.FPRP].(TM1Member([Version].[Default].Item(0), 0), [sys.Websheet Control].[Effective], [lookup.Measure].[Account Type]) = ''P''))';

SubsetCreateByMDX(subAccountsMaster, sMDX, 1);

SubsetElementDelete('Accounts Master', subAccountsMaster, 1);

Back to just a few seconds in execution time. I do not use TM1SUBSETTOSET much, but have never really noticed any issues with it. To expand on the oddity, is the offending MDX with TM1SUBSETTOSET executes instantly in ARC's MDX tool & Architect.

This seems like a bug to me. Anyone know or have an idea about what this is about? @Wim? Thought I'd post it in case someone has a TI that seems to be more like Slow Poke Rodriguez instead of Speedy Gonzales.

https://www.youtube.com/watch?v=wYi_hq2p1Ac

[PavoGa]

Just in case it is not entirely clear, as a now missing response indicated, it is the SubsetCreateByMDX that is taking 30+ minutes. However, in the interest of knowing if there is some interaction between TM1SUBSETTOSET and whether the subset is created as temp or perm, I can definitely say TM1SUBSETTOSET could care less. It takes 30+ minutes regardless of that argument setting.

[burnstripe]

Hi, instead of subset to set does [dimname].[subset] yield the same performance problems? I think it may help knowing how the subset "P Accounts" is created.

What is the P Accounts subset? What size, a few, 100s, 1000s of elements. Is it a dynamic or static subset?

Also does it take a while for the subset "P Accounts" to render in a query

What version of tm1/Planning Analytics is running and has it been upgraded recently

[burnstripe]

Sorry I'm reading this from a phone, I can see you shared the subset content.

If it was fine before, I can only think an upgrade has happened and a bug has been encountered, or a data change has triggered it. Maybe subset "P Accounts" is also an element name, and is somehow causing ambiguity which the tm1subsettoset should avoid, but still may be worth saving the subset P Accounts with another completely unique name and testing performance

[gtonkin]

Late here so a completely random idea but have you tried specifying the dimension name as the fourth argument in the SubsetCreatebyMDX?

[PavoGa]

Late here so a completely random idea but have you tried specifying the dimension name as the fourth argument in the SubsetCreatebyMDX?

No, once I could replicate it by switching the TM1SubsetToSet in and out, I moved on to something else. It could be something else interacting with the TM1SubsetToSet syntax, but simplest solution was to simply bring the subset's MDX into the main query. May visit it some other time or maybe someone else has seen the problem and knows if there is some magic involved. My guess is a problem in the MDX engine in TM1. Wouldn't be the first time.