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.