locked
Calculation based on users selection RRS feed

  • Question

  • Hi,

    i´m pretty new to SSAS and i wonder if the following is possible to achieve (I have lots of experience with TM1 and Infor PM, where my answer would have been "Not possible - has to be done in frontend" :-) )

    OK, here it goes:

    I have a time dimension which goes down to half hours, and i want to calculate an average over that dimension. For example i have 480 items shipped over the whole day, the "whole day avg" i want to calculate is 10 (480 / 48 [48 = 24 hours*2] ). This works smoothly with a calculation script.

    Here is the problem: My users want to select lets say 4 different days und have the avg calculated for exactly that days that are selected:

    Day1: 100 pcs (in 48 intervalls of 30 minutes)

    Day2: 150 pcs

    Day3: 200 pcs

    Day4: 100 pcs

    --> (100+150+200+100)/(4*48) = 2,86 items per 30minutes

    Is it even possible to reference the "currently selected items" in the calculation script?

    Best regards

    Tuesday, March 11, 2014 12:18 PM

Answers

  • Hi,

    in cube calculation script define calculated member such as

    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue]
     AS [Measures].[YourSumMeasure]/Count(Existing([Date].[Calendar].[Date].Members)*48), 
    VISIBLE = 1  ; 
    and don't forget about divide by zero. In this case your result will be calculated for selected days only


    Tuesday, March 11, 2014 2:46 PM
  • Unfortunately, I can't answer all your questions. 

    1. Regard to Excel, you can use "Convert to formula" on Analysis tab in Excel (but i am not sure about menu item name, because I haven't got the English Interface). After converting you will see correct count. (All the same Date dimension should be in filter area).

    2. You can create in cube calculation script the hidden dynamic set, e.g. for AdventureWorks:

    CREATE HIDDEN DYNAMIC SET [Days_1]
    AS [Date].[Calendar].[Date].Members;

    And then calculated member:

    CREATE MEMBER CURRENTCUBE.[Measures].[CountOfDays_1]
    AS count(existing([Days_1])), VISIBLE = 1 ; 
    Thereafter both in SSMS and Excel  [Measures].[CountOfDays_1] measure will show the correct value (without "Convert to formula" function)



    Wednesday, March 12, 2014 10:56 AM

All replies

  • You may not have to do anything special. Write a simple calculation like below and see if it works.

    [Measures].[Item Count]/[Measures].[Interval Count]

    Hope that would be helpful.

    Arun

    Tuesday, March 11, 2014 1:01 PM
  • Hi,

    in cube calculation script define calculated member such as

    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue]
     AS [Measures].[YourSumMeasure]/Count(Existing([Date].[Calendar].[Date].Members)*48), 
    VISIBLE = 1  ; 
    and don't forget about divide by zero. In this case your result will be calculated for selected days only


    Tuesday, March 11, 2014 2:46 PM
  • Hi,

    thank you very much for your assistance - i admit that i have been thinking too complicated, seems that my OLAP-skills are a little rusty :-(

    I´ve tried several variations of your suggestions, but it simply doesn`t work and i can`t figure out why. Here is the code i used (without the division, just trying to get the "4" in my example above):

    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue]
     AS Count(EXISTING([Datum SR REG].[Datum].[Tag].Members))
    ; 
    
    
    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue2]
     AS Count(EXISTING([Datum SR REG].[Datum].[Halbestunde].Members))
    ; 
    
    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue3]
    AS count(DESCENDANTS([Datum SR REG].[Datum].CurrentMember))
    ; 
    
    CREATE MEMBER CURRENTCUBE.[Measures].[AvgValue4]
    AS count(EXISTING(DESCENDANTS([Datum SR REG].[Datum].CurrentMember)))
    ; 

    And here are the results:

    Looks good, especially "AvgValue2". According to your post, there shoud be only 4x48 = 192 for AvgValue2 on the 201102-Element when i filter on just 4 days? But when i do it this is the result:

    Can you tell me what i´m doing wrong?

    Thanks again!

    Wednesday, March 12, 2014 7:52 AM
  • Move Date dimension from rows to filter area.
    Wednesday, March 12, 2014 8:19 AM
  • Tried that:

    Wednesday, March 12, 2014 8:30 AM
  • What is the result if you do the same in SQL Server Management Studio cube Browser ? 3198 days too or 4 days ?
    Wednesday, March 12, 2014 8:59 AM
  • It works :-) :

    But again, only when you put the dimension in the filter, not in rows:

    Is there any way to make it work regardeless of where the dimension is placed? And why on earth is it diffenrent when i use Excel as client? How could you ever rely on a calculated measure like that if it behaves differently depending on the client you use?

    Thank you for your patience!

    Wednesday, March 12, 2014 9:09 AM
  • Unfortunately, I can't answer all your questions. 

    1. Regard to Excel, you can use "Convert to formula" on Analysis tab in Excel (but i am not sure about menu item name, because I haven't got the English Interface). After converting you will see correct count. (All the same Date dimension should be in filter area).

    2. You can create in cube calculation script the hidden dynamic set, e.g. for AdventureWorks:

    CREATE HIDDEN DYNAMIC SET [Days_1]
    AS [Date].[Calendar].[Date].Members;

    And then calculated member:

    CREATE MEMBER CURRENTCUBE.[Measures].[CountOfDays_1]
    AS count(existing([Days_1])), VISIBLE = 1 ; 
    Thereafter both in SSMS and Excel  [Measures].[CountOfDays_1] measure will show the correct value (without "Convert to formula" function)



    Wednesday, March 12, 2014 10:56 AM
  • Hello,

    after some more "research" (more like try and error) i found a solution that works for me - in all interfaces i use and all hierarchies i create in the Date-Dimension. I thought that i would share it here, perhaps someone can use it in the future.

    I did the following:

    - Create a new Measure, Aggregation function is "Sum"

    - Overwrite the contents of the measure in the calculation script:

    SCOPE [Measures].[Anz Datum Stand];
      SCOPE NONEMPTY([Datum Stand].[Datum].[Halbestunde].Members,[Measures].[Anz SR offen]);
        This= 1;
      END SCOPE;
    END SCOPE;

    - use this measure for the division

    Thats it :-)

    Tuesday, April 1, 2014 5:27 AM