locked
MDX paging using subset, non empty not working RRS feed

  • Question

  • Hi all,

    I am trying to achieve paging in MDX using subset function. The idea is to start with Index 0 and with a page size (for example I have taken 100 as page size) and keep on querying by increasing the Index until either 0 rows or rows returned is less than page size( <100 in this case). This will tell me that there are no more data.

    I am trying this on Sample Contoso BI Retail Operation Cube.

    The original query is below (I have a lot of cross join :) and it returns me a lot of data where no measures have data (all measures are null)) :

    WITH 
     SET [AllDimensions] as { ([Product].[Product].[Product Name].ALLMEMBERS * [Promotion].[Promotion Name].[Promotion Name].ALLMEMBERS * 
     [Store].[Geography Hierarchy].[Store Name].ALLMEMBERS * [Date].[Calendar YQMD].[Date].ALLMEMBERS ) }
    
     SELECT { [Measures].[Sales Unit Cost], [Measures].[Sales Unit Price] } on columns, SubSet([AllDimensions],0,100) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME on Rows from [Operation] 

    This gives me 100 rows but all the measures values are 0.

    I am trying to get those rows only where either one of the measure is not null. I tried :

    WITH 
     SET [AllDimensions] as { ([Product].[Product].[Product Name].ALLMEMBERS * [Promotion].[Promotion Name].[Promotion Name].ALLMEMBERS * 
     [Store].[Geography Hierarchy].[Store Name].ALLMEMBERS * [Date].[Calendar YQMD].[Date].ALLMEMBERS ) }
    
     SELECT { [Measures].[Sales Unit Cost], [Measures].[Sales Unit Price] } on columns, NONEMPTY(SubSet([AllDimensions],0,100)) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME on Rows from [Operation] 

    But then that does not return me any values.

    How do I achieve this.

    - Girija


    Please mark responses as answered if it helped you.. This helps others... - Girija Shankar Beuria

    Thursday, February 4, 2016 1:03 PM

Answers

  • Hi Girija,

    In this scenario, you need to put the NONEMPTY() inside of SUBSET(), and you need to include both measures in NONEMPTY() function. You query can be like:

    WITH 
     SET [AllDimensions] as 
     { ([Product].[Product].[Product Name].ALLMEMBERS * 
     [Promotion].[Promotion Name].[Promotion Name].ALLMEMBERS * 
     [Store].[Geography Hierarchy].[Store Name].ALLMEMBERS * 
     [Date].[Calendar YQMD].[Date].ALLMEMBERS ) }
    
     SELECT { [Measures].[Sales Unit Cost], [Measures].[Sales Unit Price] } on columns, 
     SubSet(NONEMPTY([AllDimensions],{[Measures].[Sales Unit Cost], [Measures].[Sales Unit Price]}),0,100) DIMENSION PROPERTIES MEMBER_UNIQUE_NAME on Rows from [Operation] 

    However, in SSAS MDX, the NONEMPTY can only evaluate the first set of members to determine "non empty". Since you crossjoin four dimensions, it still can't avoid having nulls. This is the limitation. See my test below:


    Regards,


    Simon Hou
    TechNet Community Support



    Friday, February 5, 2016 8:51 AM