Generating an MDX subset from cube and user information

[TM1Dunk]

All

It has been a while since I've been around, and after much forum scouring and Google hunting, I pose the following MDX puzzler for your deliberation...

Aim: I am looking to generate a MDX subset over the "Calendar" dimension which returns the years between a 'Start year' and 'End year' (held in a control cube) via an individual's current selections "Scenario" and "Org" (held in a different control cube).

1) The user's current scenario and org are stored in a 2D cube "UserControl", comprising "}Clients" and "UserControlMeasure" dimensions (using picklists ):

}Clients = "Admin"
UserControlMeasure = "Current scenario"
Cube value = "Scenario 1"

and

}Clients = "Admin"
UserControlMeasure = "Current org"
Cube value = "ORG001"

2) A different cube "ScenarioControl" holds the 'Start year' and 'End year' for a scenario / org combination, comprising "Org", "Scenario" and "ScenarioControlMeasure" dimensions:

Org = "ORG001"
Scenario = "Scenario 1"
Measure = "Start year"
Cube value = "2010"

and

Org = "ORG001"
Scenario = "Scenario 1"
Measure = "End year"
Cube value = "2020"

3) I can create a MDX query on the "Scenario" dimension to return the correct element

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Scenario] )}, [UserControl].(StrToMember("[}Clients].["+USERNAME+"]"),[UserControlMeasure].[Current scenario]))} = "Scenario 1"

4) And can create a MDX query on the "Org" dimension to return the correct element

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [Organisation] )}, [UserControl].(StrToMember("[}Clients].["+USERNAME+"]"),[UserControlMeasure].[Current organisation]))} = "ORG001"

5) I can even create a MDX subset on the "Calendar" dimension to return the correct 5 year elements, but only when I hard code the "Scenario" and "Org" members (over another control cube, which uses rules to determine whether a particular year is greater-than-or-equal-to the 'Start year' but less-than-or-equal-to the 'End year')

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)}, ScenarioCalendarControl.(OrganisationSummary.[ORG001],Scenario.[Scenario 1]) > 0) = "2010, 2011, ... , 2020"

6) However, if I attempt to paste the MDX expressions from 4) and/or 5) into this final expression, I get an error message. I have tried saving the prior two expressions as subsets and then insert the subset names into the final expression i.e.

{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)}, ScenarioCalendarControl.(OrganisationSummary.[My current org],Scenario.[My current scenario]) > 0)

and although these subset expressions work in the original dimensions, they do not seem to work when integrated into the greater whole...

I have a deep-seated suspicion that I am attempting this in a somewhat "verbose" manner and than a more elegant solution evades me. All and any pointers are gratefully recieved,

TM1Dunk

[lotsaram]

It seems to me that you could have an additional lookup control cube "UserScenarioYears" populated via rule with the dimensions:
}Clients
Year
UserScenarioYears_Measure

A measure "IsScenarioYear" would be Boolean True/False or 1/0 and calculated via rule by looking up the cubes you already have. You would then be doing the "heavy lifting" via rules not MDX and your MDX expression for the Year dimension would then be a quite simple cube value filter on the Boolean value in the "UserScenarioYears" cube. The only function reference that would be needed would be a StrToMember for username in }Clients.

I think this will work and be more "elegant". Does this make sense?

[TM1Dunk]

Lotsa

Thanks for the response. It seems I was working toward your way of thought independently, having created a cube which uses rules to do the "heavy lifting", but with a few more dimensions.

On paper, I believe your suggestion (and my own attempts) would be functional. However, 2 points prevent this from being so:

1) Access to the "Org" dimension is controlled on a user-by-user basis, none of whom can see the very top level Org and so omitting it from the MDX means that they cannot see the summed boolean 1's
2) An individual user may have access to more than one leaf "Org", against which they can have different year ranges for each scenario

Progress so far...

a) I've expanded the control cube "ScenarioCalendarControl" to have 4 dimensions; "}Clients", "Org", "Calendar" and "Scenario"

b) Rules on this cube reference a user's "Current scenario", "Current org", "Start year" and "End year" (for that scenario) to flag the pertinent years with 1's. All other cells are zero i.e. even if a user has multiple Orgs and scenarios running in parallel, this cube only flags the years against the current Org and scenario chosen

This means I can rely on "All scenarios" to show only the correct years, and theoretically ditto for "All Orgs" (were it not for the security settings on this dimension). All I need to pass in the MDX is the client name and it will filter the non-zero years to return:

{
FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)},
ScenarioCalendarControl.(StrToMember("[}Clients].["+USERNAME+"]")) > 0
)
}

This works great for "admin" as this user has access to "All Orgs". However, when I connect as a user, the Calendar subset is blank as the MDX cannot see the "All Orgs" element.

I have thusly tried:

FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)},
ScenarioCalendarControl.(StrToMember("[}Clients].["+USERNAME+"]"),[Org].[ORG001]) > 0
)
}

Which works as expected (where "user1" has security access to "ORG001"). But when I try to make this dynamic, referencing the original control cube (see previous post, "UserControl" cube and successful referencing in Org MDX) it errors:

{
FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)},
ScenarioCalendarControl.(StrToMember("[}Clients].["+USERNAME+"]"),{TM1FILTERBYPATTERN( {TM1SUBSETALL( [OrganisationSummary] )}, [UserControl].(StrToMember("[}Clients].["+USERNAME+"]"),[UserControlMeasure].[Current organisation]))}) > 0
)
}

Giving the well recognised "error at character 345" which is the very last character.

To simplify my plight:

I have 2 successfully running dimensional MDX statements, which read fields from a 2D cube:

i) Org: {TM1FILTERBYPATTERN( {TM1SUBSETALL( [OrgDim] )}, [2D Cube].(StrToMember("[}Clients].["+USERNAME+"]"),[MeasureDim].[Current org]))} = "ORG001"

ii) Scenario: {TM1FILTERBYPATTERN( {TM1SUBSETALL( [ScenarioDim] )}, [2D Cube].(StrToMember("[}Clients].["+USERNAME+"]"),[MeasureDim].[Current scenario]))} = "Scenario 1"

I want to "plug in" the first of these MDX statements into an otherwise working, yet hardcoded, third MDX statement which references a 4D cube:

iii) Calendar: {FILTER({TM1FILTERBYLEVEL( {TM1SUBSETALL([CalendarDim])}, 0)}, 4D Cube.(StrToMember("[}Clients].["+USERNAME+"]"),INSERT IN HERE) > 0)}

QUESTION: Have I made an obvious syntax error when inserting the statement above or am I attempting the impossible?

Again, all comments gratefully recieved...

TM1Dunk

[TM1Dunk]

Update for those who have been able to follow the jist of this thread!

I have managed to combine the 2 perviously obtuse MDX statements to give the following "working" statement:

{
FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)},
ScenarioCalendarControl.(
StrToMember("[}Clients].["+USERNAME+"]"),
StrToMember("[OrganisationSummary].["+UserControl.(StrToMember("[}Clients].["+USERNAME+"]"),[UserControlMeasure].[Current organisation])+"]")
)
> 0
)
}

However, for some bizarre reason, when using the dynamic reference to find "Current organisation" (the bit in blue above), it seems to return all years i.e. does not correctly filter the "ScenarioCalendarControl" cube for non-zero values.

Indeed, if this cube contains only zeros and very large numbers (achieved by changing the rule generated boolean from 0 / 1 to 0 / 999), then it only returns *any* years when the filter is set to = 1 (as > 0 or < 999 etc.). How is this possible as absolutely *no* values within the cube are exactly 1!!!

Pasting this dynamic code into the Org dimension returns, as required, a single element i.e.

{TM1FILTERBYPATTERN( {TM1SUBSETALL( [OrganisationSummary] )}, UserControl.(StrToMember("[}Clients].["+USERNAME+"]"),[UserControlMeasure].[Current organisation]))} = "ORG001"

If I hardcode this element in the original MDX statement, the query returns the correct 5 year range. This correctly applies the non-zero filter, whether the boolean is 1 or 999:

{
FILTER(
{TM1FILTERBYLEVEL( {TM1SUBSETALL([Calendar])}, 0)},
ScenarioCalendarControl.(
StrToMember("[}Clients].["+USERNAME+"]"),
StrToMember("[OrganisationSummary].["+"ORG001"+"]")
)
> 0
)
}

The solution is sooo close now that I can taste it...

TM1Dunk