Parametric MDX Performance Issue

[ardi]

Hi,

We have a requirement to implement Cascading Filters in websheets, so let's say we have 3 dropdowns ( SUBNM ), one for Region Selection, one for City Selection and another one for Customer Selection

When User Selects Region, the next drop down ( Cities ) should show only Cities of that specific Region and same idea for Customer Drop down.

We have created the Region Attribute in the City dimension and Also City Attribute in the Customer Dimension.

We are using MDX Subsets to populate the dropdowns, but our customer dimension is huge now, more than 600K elements.

In order to use the selection made from user, we have created a 2-dimensional cube called User_Selection with following dimensions :

}Clients
User_Selection_m

and then the MDX statement for my_Custumers subset uses the stored value of City as following:

{FILTER( {TM1SUBSETALL( [Customer] )}, [Customer].[City] = [User_Selection].(StrToMember("[}Clients].["+USERNAME+"]"),[User_Selection_m].[City]))}

This is taking 90 seconds to refresh.

It takes the same amount of time if I just refresh the MDX statement in the subset editor

So I sent some time to troubleshoot the issue it looks like if I just hardcode the City Name in the MDX, then the MDX refreshes in 10 seconds
so the following MDX runs in 10 seconds
{FILTER( {TM1SUBSETALL( [Customer] )}, [Customer].[City] = "London")}

Also I tried removing the USERNAME from MDX by hardcoding the User Name in the MDX statement, just to see if this is part of issue and IT IS. So if I hardcode the User Name, MDX refreshes in 25 seconds. So the following MDX runs in 25 seconds

{FILTER( {TM1SUBSETALL( [Customer] )}, [Customer].[City] = [User_Selection].([}Clients].[Test_User],[User_Selection_m].[City]))}

We did not notice this issue when the dimension was having less elements ( around 30K elements )

Any idea why is this happening and is there any work around?

We need a mechanism that stores the User Selection and the selection needs to be used to filter other dimensions

Thanks

[declanr]

Ardi,

Since you are basing this off attributes anyway - I would assume that those attributes are only altered at certain times. If that is correct you could run a TI after that point and create static subsets for each attribute; then just use those static subsets in your active form instead of the MDX; it would get the same result as you would base what subset is being shown based on the SUBNM selection, you can hide the subsets from standard users by prefixing them all with something like "}AF_SUB_"

HTH

[ardi]

Ardi,

Since you are basing this off attributes anyway - I would assume that those attributes are only altered at certain times. If that is correct you could run a TI after that point and create static subsets for each attribute; then just use those static subsets in your active form instead of the MDX; it would get the same result as you would base what subset is being shown based on the SUBNM selection, you can hide the subsets from standard users by prefixing them all with something like "}AF_SUB_"

HTH

Thanks,

Ideally we need to refresh the subsets after the user made a selection in the prior SUBNM, so if the user Selects a Region then the SUbset used in City and Customer SUBNM formulas should show only Cities/Customers belongin to that Region. In our case we have more SUBM formulas, but I was trying to make it simple with only 3 cascading filters.

MDX works fine if I hardcode the Tm1 User Name, but it becomes really slow if I use USERNAME function in my MDX.

[David Usherwood]

Prebuild all possibilities, let the user select them and give them the prebuilt subset. Live MDX on a 600k dimension will always be painful.