MDX for finding "bad" entries in a cube

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
PavoGa
MVP
Posts: 548
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Cleveland, Tennessee

MDX for finding "bad" entries in a cube

Post by PavoGa » 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:

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.
Ty
Cleveland, TN

Wim Gielis
MVP
Posts: 2792
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

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

Post by Wim Gielis » Sat Apr 10, 2021 11:31 pm

Nice application 👍
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis

Post Reply