Cube View Filter Using MDX

Post Reply
mlorini
Posts: 15
Joined: Tue Apr 06, 2021 8:44 pm
OLAP Product: TM1
Version: 2.8
Excel Version: 365

Cube View Filter Using MDX

Post by mlorini »

I would like some ideas/suggestion about solving the following issue:
I have a cube with the following dimensions: A,B,C,D, M (Measure)
The Measure M has fields called: Amount, Dispute Flag, Dispute Status

I would like to create a Cube view that show only data when M. Dispute Flag > 0

Any help will be appreciated.
Thanks
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Cube View Filter Using MDX

Post by Wim Gielis »

Hello,

What is your view layout / how do you select elements ?
Do you have several elements in the rows / columns ?
Or for example, 1 dimension in the rows and the other dimensions in the titles ?
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
mlorini
Posts: 15
Joined: Tue Apr 06, 2021 8:44 pm
OLAP Product: TM1
Version: 2.8
Excel Version: 365

Re: Cube View Filter Using MDX

Post by mlorini »

This is the type of filter I am using:

TM1SORT(FILTER(TM1FILTERBYLEVEL(TM1SUBSETALL([Product]) , 0) , [Product Cube].([Versions].[Actual],[Period Daily].CURRENTMEMBER,[Prod Types].CURRENTMEMBER,[Invoice Dates].CURRENTMEMBER,[Product Category].CURRENTMEMBER,[Product Cube Measures].[Open Invoice Flag]) = 1) , ASC)

When this MDX statement is applied to the cube view It still show products that have [Open Invoice Flag] 1 and 0.

Cube has 5 Dimensions on Rows and 3 on Column, Product is on a row

Thanks
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: Cube View Filter Using MDX

Post by PavoGa »

Which dimensions are used in context (title dimensions) and which are used in rows/columns?

There are a couple of approaches. The easiest, by far, is with one row dimension and one column dimension, one of which being the measure dimension. That MDX is pretty straightfoward and does not require any reference to the title dimensions as those are automatically taken into account as context. If your business need can be fitted within this framework, this is by far the fastest performing option.

Assuming the Measure dimension is the column, it gets a wee bit more difficult if two dimensions are used on the row. In this case, you'd need two MDX statements, one for each dimension which joins the other row dimension, using GENERATE and FILTER. Depending on the size of the dimensions, this could be a bit slow.

Code: Select all

# assume A and B are title dimensions, C & D are row dimensions, M the column dimension.
# subset for [B]
GENERATE( TM1FILTERBYLEVEL( TM1SUBSETALL( [A] ), 0),
    FILTER( TM1FILTERBYLEVEL ( TM1SUBSETALL( [B] ), 0),
        [cube].([A].currentmember, [B].currentment, [M].[Dispute Flag]) > 0))
        
# Need the same subset as above for [A] just reverse the dimensions in the query.
In this case, if you want three or four dimensions on the rows, then an MDX view is going to be the ticket, although you could continue to wrap GENERATE around the queries described above, and it, too, may be a bit slow at times. I've pretty much quit using MDX views in TM1 except in special circumstances (like this!). I'll get an example in a bit if someone else does not provide one.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: Cube View Filter Using MDX

Post by PavoGa »

MDX example View as promised:

Code: Select all

SELECT
	[M].members ON COLUMNS,
	FILTER( [A].members *
		[B].members *
		[C].members *
		[D].members,
		[M].[Dispute Flag] > 0)
		ON ROWS
FROM [cubename]
Ty
Cleveland, TN
mlorini
Posts: 15
Joined: Tue Apr 06, 2021 8:44 pm
OLAP Product: TM1
Version: 2.8
Excel Version: 365

Re: Cube View Filter Using MDX

Post by mlorini »

Thanks a lot "PavoGA" I was able to follow your suggestions and I created a view based on you MDX query

SELECT
[M].members ON COLUMNS,
FILTER( [A].members *
.members *
[C].members *
[D].members,
[M].[Dispute Flag] > 0)
ON ROWS
FROM [cubename]

The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.

Thanks
User avatar
PavoGa
MVP
Posts: 616
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: Charleston, Tennessee

Re: Cube View Filter Using MDX

Post by PavoGa »

mlorini wrote: Tue Aug 03, 2021 9:09 pm

The only odd think is that I was not able to create such view using ViewCreateByMDX in a TI process.

Thanks
This should be a fairly straightforward MDX view, but there are some differences in how the various user interfaces handle MDX in TM1. I do not have one readily available, but you might search the forum for some of the discussions on it.
Ty
Cleveland, TN
Post Reply