MDX for finding "bad" entries in a cube
Posted: Tue Apr 06, 2021 7:19 pm
Ha! Another way to use that little-used TM1 MDX function, TM1TupleSize.
So the scenario is this: a lookup cube that stores certain data and allows adjustments to data along dimension lines. For a simple example, imagine an Employee Information cube that has Employee, Job Title, Time and measure dimensions. And let's say that in any given period, an Employee may only have data stored at one Job Title. However...
...for whatever reason, it is possible for data to find its way onto a second Job Title for a given employee and, if this happens, data will not flow correctly through the model. How to find the offending intersection?
One way is to have or write a TI that runs through the cube, possibly with lots of nested loops to find the offending intersection. Or try this:
It uses a double filter, with the inner filter being tested to see if it returns more than a single tuple.
So what is happening?
The outer filter simple sets up the inner filter to test every employee in the inner filter ([Employee].currentmember) for the Job Titles with a Salary entry and determines if there is more than one Employee/Job Title intersection with a salary entry.
How it works:
This code returns a set of Job Titles with a salary for a given Employee
Then, we return the second tuple (0 based, so 0 is first, 1 is second, 2 is etc..) using .Item(1)
Then use TM1TUPLESIZE to return the size of the tuple, which in this case will either be zero or one.:
And refer back to the original query to see the overall filter on the returned tuple size.
There may be easier ways and a TI may be faster. Running this against an employee dimension with 50K+ elements and 40 Job Titles took about sixty seconds. There may be faster MDX, but this illustrates a usage of TM1TupleSize.
So the scenario is this: a lookup cube that stores certain data and allows adjustments to data along dimension lines. For a simple example, imagine an Employee Information cube that has Employee, Job Title, Time and measure dimensions. And let's say that in any given period, an Employee may only have data stored at one Job Title. However...
...for whatever reason, it is possible for data to find its way onto a second Job Title for a given employee and, if this happens, data will not flow correctly through the model. How to find the offending intersection?
One way is to have or write a TI that runs through the cube, possibly with lots of nested loops to find the offending intersection. Or try this:
Code: Select all
FILTER(TM1SUBSETALL( [Employee] ),
TM1TUPLESIZE(FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) ,
[stage.Employee Info].([Time].[Period05],
[Employee].currentmember,
[Measure].[Salary]) <> 0).Item(1)) > 0)
So what is happening?
The outer filter simple sets up the inner filter to test every employee in the inner filter ([Employee].currentmember) for the Job Titles with a Salary entry and determines if there is more than one Employee/Job Title intersection with a salary entry.
How it works:
This code returns a set of Job Titles with a salary for a given Employee
Code: Select all
FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) ,
[stage.Employee Info].([Time].[Period05],
[Employee].currentmember,
[Measure].[Salary]) <> 0)
Code: Select all
FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) ,
[stage.Employee Info].([Time].[Period05],
[Employee].currentmember,
[Measure].[Salary]) <> 0).Item(1)
Code: Select all
TM1TUPLESIZE(FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) ,
[stage.Employee Info].([Time].[Period05],
[Employee].currentmember,
[Measure].[Salary]) <> 0).Item(1))
There may be easier ways and a TI may be faster. Running this against an employee dimension with 50K+ elements and 40 Job Titles took about sixty seconds. There may be faster MDX, but this illustrates a usage of TM1TupleSize.