Page 1 of 1

MDX for finding "bad" entries in a cube

Posted: Tue Apr 06, 2021 7:19 pm
by PavoGa
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:

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)
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

Code: Select all

FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) , 
            [stage.Employee Info].([Time].[Period05], 
                [Employee].currentmember, 
                [Measure].[Salary]) <> 0)
Then, we return the second tuple (0 based, so 0 is first, 1 is second, 2 is etc..) using .Item(1)

Code: Select all

FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) , 
            [stage.Employee Info].([Time].[Period05], 
                [Employee].currentmember, 
                [Measure].[Salary]) <> 0).Item(1)
Then use TM1TUPLESIZE to return the size of the tuple, which in this case will either be zero or one.:

Code: Select all

TM1TUPLESIZE(FILTER( TM1FILTERBYLEVEL( TM1SUBSETALL( [Job Title] ), 0) , 
            [stage.Employee Info].([Time].[Period05], 
                [Employee].currentmember, 
                [Measure].[Salary]) <> 0).Item(1))
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.

Re: MDX for finding "bad" entries in a cube

Posted: Sat Apr 10, 2021 11:31 pm
by Wim Gielis
Nice application 👍