Users familiar with Architect/ Perspectives will be familiar with the "Record Expression" capability in the Subset Editor
https://www.ibm.com/docs/en/cognos-tm1/10.2.2?topic=subsets-creating-expression-based-dynamic-subset
We can filter by Level, Attribute, View Extract, Wildcard direct form the interface and also enable View > Express Window to see these changes happening live in the Subset Editor. Furthermore we can Filter based on the values in a selected Cube (picking the elements required for each dimension) and clarifying which criteria we want to display (Order Ascending/ Decending, Top Count, Top Sum etc..)
For example filtering for all leaf level organizations in the 24Retail model where the Default Plant attribute = "Plant A" returns:
{FILTER( {TM1FILTERBYLEVEL( {TM1SUBSETALL( [organization] )}, 0)}, [organization].[Default Plant] = "Plant A")}
Selecting a list of Organizations in Descending Order based on the values in the Income Statement cube for Local Currency, Full Year, Y2 (2020 in this case), for Account code 4999 (Gross Revenue code) for Actual version returns:
{ ORDER( {{TM1FILTERBYLEVEL( {TM1SUBSETALL( [organization] )}, 0)}}, [Income Statement].([Currency Calc].[Local],[Year].[Y2],[Month].[Year],[Account].[4999],[Version].[Actual]), BDESC) }
Switching over to IBM Planning Analytics Workspace we can replicate the first example, if we select the MDX toggle on the page we can view these changes live as per View Expression Window
{FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([organization].[organization]) , 0), ([organization].[organization].CURRENTMEMBER.PROPERTIES("Default Plant") = "Plant A"))}
We can see that the statements differ;
- the default hierarchy is now applied to the dimension name
- Querying against attributes is applied via the .PROPERTIES selected instead of using [dimensionname].[attribute name]
The secondary example, however, is not available (as yet) and this query will have to be manually generated. I can open the Architect generated subset but what if I no longer have access to Architect or have never seen the Subset Editor in Architect.
Traditionally, I would pass users onto the various resources available for manually generating your own MDX subsets (there are many more feel free to comment and I will update the list):
This did get me thinking that these statements could be automated via a TI process and this could be presented to end users who may not be familiar with MDX statements or had no specific need to be familiar.
For Example, I like to use a 'Control' cube to hold configuration for the Model which can drive security/ workflow and importantly, Reporting Periods. I set a Current Month element which can be referenced in all Reports against the model so I know all users will be accessing the appropriate Reporting Period and do not need to manually update a suite of reports each month/ time period.
I have a simple Control cube with a Control.Item dimension and Control.Measures dimension and at the cross section of Current Month and String elements we have '2023-11':
I have an MDX.Config cube which has MDX.Item and MDX.Measures dimensions
MDXPreview - The MDX Statement as generated via the TI process
Select Element from Cube - Flag to confirm type of MDX statement
Cube - Name of Cube for MDX statement
Dimension - Name of Dimension for MDX Statement
Element - Name of Element for MDX Statement
TargetDimension - Target Dimension for MDX Statement
GenerateSubset - Flag to confirm MDX Subset to be Created
SubsetName - Name of Subset to be generated
This is a cut down list of elements in the Cube for brevity, in this example we have requested an MDX statement to call the Current Month element from the Control Cube and create a "Current Month" subset in the "Month" dimension.
When we run the accompanying TI process we can see the MDX in the MDXPreview:
When I open the "Current Month" subset on the Month dimension I can see:
STRTOMEMBER("[Month].[" + [Control].([Control.Item].[Current Month],[Control.Measures].[String]) + "]")
