MDX View not performing as expected

[mlorini]

Was wondering if someone can help me here. We just upgraded to the latest PA/PAW version (2.0.72) and our MDX view seems slower then ever. This is the MDX view statement:

Code: Select all

SELECT 
  NON EMPTY 
   {[Versions].[Actual], [Versions].[Budget]}
   * {[Dimension Measures].[xxxxx Flag], 
    [Dimension Measures].[Total Hours],
    [Dimension Measures].[xxxxx Dispute],
    [Dimension Measures].[xxx Comments],
    [Dimension Measures].[xxxx Status],
    [Dimension Measures].[Last Updated By],
    [Dimension Measures].[xxxx Comments],
    [Dimension Measures].[xxxx Comments Date],
    [Dimension Measures].[xxxx Date],
    [Dimension Measures].[xxxx Date],
    [Dimension Measures].[xxxxxxx date]}
   * { [Product Types].[A],
       [Product Types].[B],
       [Product Types].[C],
       [Product Types].[D],
       [Product Types].[G],
       [Product Types].[E],
       [Product Types].[H],
       [Product Types].[L],
       [Product Types].[M]} 
  ON COLUMNS , 
  NON EMPTY 
   FILTER(
    TM1SORT(TM1FILTERBYLEVEL(TM1SUBSETALL([Periods Dates]) , 0), ASC) *
    TM1FILTERBYLEVEL(TM1SUBSETALL([Regions]) , 0) *
    TM1FILTERBYLEVEL(TM1SUBSETALL([Cities]) , 0),
    [Dimension Measures].[Sales Flag] > 0
  ) 
  ON ROWS 
FROM [Cube Sales] 

The goal of this view is to show only the data where [Dimension Measures].[Sales Flag] > 0. I am new to MDX, and I am wondering if there is another way that I can write the query to be more efficient. Any help or advise is welcome.

Thanks

[Wim Gielis]

Can I ask you why you created a new topic ?
https://www.tm1forum.com/viewtopic.php?t=16113

As to the MDX: what happens if you put the condition on a strictly positive "Sales Flag" in the WHERE clause of the view ?

[mlorini]

So if I rewrite the query as

Code: Select all

NON EMPTY

TM1SORT(TM1FILTERBYLEVEL(TM1SUBSETALL([Periods Dates]) , 0), ASC) *
TM1FILTERBYLEVEL(TM1SUBSETALL([Regions]) , 0) *
TM1FILTERBYLEVEL(TM1SUBSETALL([Cities]) , 0)

ON ROWS
FROM [Cube Sales]
where [Dimension Measures].[Sales Flag] > 0

It say that it cannot create the view because [Dimension Measures] is already in the SELECT Query

[Wim Gielis]

So if I rewrite the query as

Code: Select all

NON EMPTY

TM1SORT(TM1FILTERBYLEVEL(TM1SUBSETALL([Periods Dates]) , 0), ASC) *
TM1FILTERBYLEVEL(TM1SUBSETALL([Regions]) , 0) *
TM1FILTERBYLEVEL(TM1SUBSETALL([Cities]) , 0)

ON ROWS
FROM [Cube Sales]
where [Dimension Measures].[Sales Flag] > 0

It say that it cannot create the view because [Dimension Measures] is already in the SELECT Query

Please provide the full query, not just a part of it.
Code tags make it more readable, thanks.

[Wim Gielis]

Anyway, this code works fine for me in the Prolog tab:

Code: Select all

vCube = 'Cube Sales';
vView = 'Test';

vFile = 'test.txt';

vMDX = 'SELECT {[Dimension Measures].[Measure]} ON COLUMNS,
NON EMPTY FILTER( TM1SUBSETALL([Periods Dates]) *
TM1SUBSETALL([Regions]) *
TM1SUBSETALL([Cities]), [Dimension Measures].[Sales Flag] > 0) ON ROWS FROM [' | vCube | ']';


ViewCreateByMDX( vCube, vView, vMDX, 1 );

If( ViewExists( vCube, vView ) > 0 );
   DataSourceType = 'VIEW';
   DataSourceNameForServer = vCube;
   DataSourceCubeView = vView;
Else;
   DataSourceType = 'NULL';
   ProcessError;
EndIf;

And in the Data tab:

Code: Select all

TextOutput( vFile, V1, V2, V3, V4, NumberToString( vValue ));

wim.PNG (85.63 KiB) Viewed 2797 times

[mlorini]

Thanks for your answer Wimm, I will try your solution later on and will let you know if it works. Again, thanks a lot