locked
Scope Doesn't See the filter on Time dimension RRS feed

  • Question

  • Hi everyone,

    There was a problem with our inventory cube. We are trying to build a new one. The old one's fact table was a daily snaphot of all items even if there was no transaction on item. Then we decided to make the calculations over transaction. Now I have a transactions fact table. Qty is the measure of transaction amount like +/-

    I tried scope like below

    SCOPE(MEASURES.QTY);
      SCOPE([H_DATE].[YIL-AY-GUN].[DAY]);
        THIS= SUM({[H_DATE].[YIL-AY-GUN].PREVMEMBER,[H_DATE].[YIL-AY-GUN].CURRENTMEMBER},MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[MONTH]);
        THIS = AVG(DESCENDANTS([H_DATE].[YIL-AY-GUN].CURRENTMEMBER,[H_DATE].[YIL-AY-GUN].[DAY]),MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[YEAR]);
        THIS = AVG(DESCENDANTS([H_DATE].[YIL-AY-GUN].CURRENTMEMBER,[H_DATE].[YIL-AY-GUN].[DAY]),MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[ALL]);
        THIS = AVG(DESCENDANTS([H_DATE].[YIL-AY-GUN].[ALL],[H_DATE].[YIL-AY-GUN].[DAY]),MEASURES.QTY);
      END SCOPE;
    END SCOPE;
    

    I will also make the calculations on another time dimension. When I drag Qty as measure and H_Date.YIL-AY-GUN on rows. It gives me the right numbers. But when I filter It on succesive three days in lets say 3,4,5 January 2010. It doesn't give me the the three days average on MONTH level. It gives the averages of days 1 Jan to 31 Jan. How can we solve this problem. After that I will make a closing and ending measures over qty using [H_DATE].[YIL-AY-GUN].CurrentMember.FirstChild or LastChild but I think It will give 31 Jan 2010 Instead of 5 Jan.

    Best Regards,

    Erdem

    Friday, January 28, 2011 3:00 PM

Answers

All replies

  • At first I tried existing(descendants(.... but it also didn't work out.

    Then I tried AverageofChildren instead of Sum but It gave me the average of actual fact table.

    At this point QTY's aggregation type is sum

    Friday, January 28, 2011 3:03 PM
  • Scope does not honor Filters applied. example: In the cube browser add the measure and then the dimension on row axis and then in the filter section of cube browser select the members. It will filter the members and will show only the members selected on row axis, but it does not change the totals or sub totals or grand totals.

    http://sqlblog.com/blogs/mosha/archive/2007/09/26/how-to-detect-subselect-inside-mdx-calculations-aka-multiselect-in-excel-2007.aspx


    vinu
    Friday, January 28, 2011 4:35 PM
  • There is any workaround that allow the scope to honor the filter applied ?

     

    Many thanks

    Friday, January 28, 2011 6:07 PM
  • In adventureworks if you select a filter on date and drag date to rows. It show only the selected members' total. I want to make my calculations like that. Is there any way to detect filter while programming in calculation tab.

     

    Regars,

    Erdem

    Friday, January 28, 2011 7:31 PM
  • May be have dynamic sets as mentioned in this article.

    http://sqlblog.com/blogs/mosha/archive/2007/08/25/mdx-in-katmai-dynamic-named-sets.aspx


    vinu
    • Marked as answer by Erdem Zengin Sunday, February 6, 2011 1:57 AM
    Saturday, January 29, 2011 12:27 AM
  • Hi,

    see here if it helps (in excel when I select multiple quarters or months I have wrong calculation)

    http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b6da88a0-922b-4608-a73b-c2b409e21057/#e3088fd2-edd3-42d1-a313-6e9d03728dcf

    HTH,

    Hrvoje Piasevoli

    • Marked as answer by Erdem Zengin Sunday, February 6, 2011 1:56 AM
    Saturday, January 29, 2011 2:53 AM
  •   dur        
    star_time 1 2 3 4      total
    1 19 20 29 31 99
    2 0 17 18 20 55
    3 7 9 20   36
    4 0 9     9
    5 9       9
    Total,wrong 35 64 85 89
    Correct 35 55 67 51  
               

    I think  I am facing the same problem as many other people.. Is there a simple way to change the wrong total into that correc total?

    So basically I have a calculated measure that is a running value. I think this causes the total to be  incorrect.  For example, 64 (pivot has calculated it 55+9), because for some reason it does not understand that the cell is NULL. What can I do?! Is the dynamic set the only option here?!

    I would really appreciate help!

    Saturday, January 29, 2011 9:19 AM
  • I used the code below it worked, but cube's response got slower.

    CALCULATE; 
    CREATE DYNAMIC SET FILTEREDSETS AS [H_DATE].[YIL-AY-GUN].[DAY];
    SCOPE(MEASURES.QTY);
      SCOPE([H_DATE].[YIL-AY-GUN].[DAY]);
        THIS= SUM({[H_DATE].[YIL-AY-GUN].PREVMEMBER,[H_DATE].[YIL-AY-GUN].CURRENTMEMBER},MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[MONTH]);
        THIS = AVG(INTERSECT({DESCENDANTS([H_DATE].[YIL-AY-GUN].CURRENTMEMBER,[H_DATE].[YIL-AY-GUN].[DAY])},FILTEREDSETS),MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[YEAR]);
        THIS = AVG(INTERSECT({DESCENDANTS([H_DATE].[YIL-AY-GUN].CURRENTMEMBER,[H_DATE].[YIL-AY-GUN].[DAY])},FILTEREDSETS),MEASURES.QTY);
      END SCOPE;
      SCOPE([H_DATE].[YIL-AY-GUN].[ALL]);
        THIS = AVG(INTERSECT({DESCENDANTS([H_DATE].[YIL-AY-GUN].CURRENTMEMBER,[H_DATE].[YIL-AY-GUN].[DAY])},FILTEREDSETS),MEASURES.QTY);
      END SCOPE;
    END SCOPE;

    Can I use a different code which can give the same the same result. I'm planning to use the calculations on this post. http://www.ssas-info.com/analysis-services-articles/62-design/367-inventory-management-calculations-in-sql-server-analysis-services-2005-by-richard-tkachuk

    Then create my avg qty measure based on these calculations

    Create Member Measures.[Running Delta Sum] as
    sum(
      generate
      (
        ascendants([Time].[Calendar].currentmember),
        iif(
          [Time].[Calendar].currentmember IS
    [Time].[Calendar].firstsibling,
          {},
    [Time].[Calendar].firstsibling:
    [Time].[Calendar].prevmember
        )
      ) + [Time].[Calendar].currentmember
      ,
      [Measures].[Units Delta]
    )
    , visible=false;

    In this part of the article Richard says that above code can improve the performance of summing. Is there any other thing can be done instead of intersect?

    By the way thanks for your advice vinuthan and Hrvoje. It worked. I hope performance can be improved.

    Best Regards,

    Erdem

    Sunday, January 30, 2011 2:36 AM