locked
Excluding Filtered Members From A Dimension RRS feed

  • Question

  • I have written a code like below but I don't know how to exlude members from All member. Then I solved it with a dynamic set. But some experts saying that using dynamic set couses performance issues. How can I do the same thing in the second code. (This is not the actual code, just an example) I will do it in the calculations tab of the project. How can I get rid of named sets and still catch the filtered members.

    WITH 
     MEMBER [Measures].[Qty] AS 
      CASE 
       WHEN 
         [STOCK_STORE].[STORE CAPTION].CurrentMember
        = 
         [STOCK_STORE].[STORE CAPTION].[All] 
       THEN 
        Sum
        (
         [STOCK_STORE].[STORE CAPTION].[STORE CAPTION]
         ,[Measures].[SALESQTY]
        )
       ELSE 
        [Measures].[SALESQTY]
      END 
      ,FORMAT_STRING = '#,##0' 
    SELECT 
     [Measures].[Qty] ON COLUMNS
     ,[STOCK_STORE].[STORE CAPTION].MEMBERS ON ROWS
    FROM 
    (
     SELECT 
      [STOCK_STORE].[STORE CAPTION].&[M201  ANKARA - KIZILAY] ON COLUMNS
     FROM [SalesStock]
    );
    

     

      Qty
    All 26,843,839
    M201    ANKARA - KIZILAY 225,222

    I solved it like,

    WITH 
    DYNAMIC SET STORES AS [STOCK_STORE].[STORE CAPTION].[STORE CAPTION]
     MEMBER [Measures].[Qty] AS 
      CASE 
       WHEN 
         [STOCK_STORE].[STORE CAPTION].CurrentMember
        = 
         [STOCK_STORE].[STORE CAPTION].[All] 
       THEN 
        Sum
        (
         STORES
         ,[Measures].[SALESQTY]
        )
       ELSE 
        [Measures].[SALESQTY]
      END 
      ,FORMAT_STRING = '#,##0' 
    SELECT 
     [Measures].[Qty] ON COLUMNS
     ,[STOCK_STORE].[STORE CAPTION].MEMBERS ON ROWS
    FROM 
    (
     SELECT 
      [STOCK_STORE].[STORE CAPTION].&[M201  ANKARA - KIZILAY] ON COLUMNS
     FROM [SalesStock]
    );
    
      Qty
    All 225,222
    M201    ANKARA - KIZILAY 225,222

     

    Thanks in advance.

    Erdem Zengin

    Friday, March 25, 2011 1:25 PM

Answers

  • could you do your calculation on bottom-level already in your relational fact-table?

    this would solve your problem with subselects and also perform much better

    otherwise you have to create a dynamic named set within your cube and base your calculaiton on top of that set

    CREATE DYNAMIC SET [UsedStockStores] AS {
    EXISTING [STOCK_STORE].[STOCK_STORE].members
    }

    CREATE DYNAMIC SET [UsedMonths] AS {
    EXISTING [Time].[Months].members
    }

    and then use those sets within your calculated member

    hth,
    gerhard


    - www.pmOne.com -
    • Proposed as answer by HrvojePiasevoli Saturday, March 26, 2011 8:01 AM
    • Marked as answer by Jerry Nee Saturday, April 2, 2011 7:27 AM
    Friday, March 25, 2011 2:49 PM
    Answerer

All replies

  • if you do a subselect you can simply use [Measures].[SALESQTY] in your query

    doing a subselect will apply VisualTotals automatically to your All-Member

    this query will give you the same result as your second query:

    SELECT
    [Measures].[SALESQTY] ON COLUMNS
    ,[STOCK_STORE].[STORE CAPTION].MEMBERS ON ROWS
    FROM
    (
    SELECT
      [STOCK_STORE].[STORE CAPTION].&[M201  ANKARA - KIZILAY] ON COLUMNS
    FROM [SalesStock]
    );

    greets,
    gerhard


    - www.pmOne.com -
    Friday, March 25, 2011 1:46 PM
    Answerer
  • Gerhard thanks for the reply. I think my example was so simple, it couldnt express my problem. I will do some calculations based on { STOCK_STORE * TIME} set. Qty is something different in the real case. Qty's calculation will be more complicated than that one. I must get in to bottom level of STOCK_STORE  and my time dimension, make some calculations and then sum these calculations to show in All member. But I can't catch the filtered members.
    Friday, March 25, 2011 2:00 PM
  • could you do your calculation on bottom-level already in your relational fact-table?

    this would solve your problem with subselects and also perform much better

    otherwise you have to create a dynamic named set within your cube and base your calculaiton on top of that set

    CREATE DYNAMIC SET [UsedStockStores] AS {
    EXISTING [STOCK_STORE].[STOCK_STORE].members
    }

    CREATE DYNAMIC SET [UsedMonths] AS {
    EXISTING [Time].[Months].members
    }

    and then use those sets within your calculated member

    hth,
    gerhard


    - www.pmOne.com -
    • Proposed as answer by HrvojePiasevoli Saturday, March 26, 2011 8:01 AM
    • Marked as answer by Jerry Nee Saturday, April 2, 2011 7:27 AM
    Friday, March 25, 2011 2:49 PM
    Answerer