locked
Time Calculations in ProClarity RRS feed

  • Question

  • Hi,

    I have a problem when I use my time calculations in ProClarity. I added Time Intelligence w/ the Add Business Intelligence in BIDS. I have trimester growth and % i need to apply to 20 measures. I picked all in the scope and all works fine untill i use ProClarity. For the calculations a need the Subtotals and Grand Totals and ProClarity does not calculate them (the MDX shoes aggregations for them but the total cells are empty). I don't want to change the MDX in ProClarity since when you drill up and down it comes back to what it had before. I tried to change the SOLVE ORDER and SCOPE ISOLATION in the calculations but nothing worked. It works fine in Excel and the cube browser but we have to use ProClarity.
    If i add the same calculations as calculated measures for one measure they work fine in ProClarity but i need calculations that apply to all measures .
    I just started learning MDX and any suggestion would be great.

    Thank you,
    CristinaF
    Thursday, October 9, 2008 2:50 PM

All replies

  • Post the code you have for the calculations so we can see how it's setup so far.

     

    Scott

     

     

    Monday, October 13, 2008 7:01 PM
  • Hi Scott,

    This is what BIDS generates w/ Add BI:

    /*
      Begin Time Intelligence script for the [Time].[Term] hierarchy.
    */

    Create Member
      CurrentCube.[Time].[Term Time Calculations].[Trimester Over Trimester Growth %]
      As "NA"
     
    ;
     
    Create Member
      CurrentCube.[Time].[Term Time Calculations].[Trimester Over Trimester Growth]
      As "NA"
     
    ;
     

    Scope(
           {
             [Measures].[Course registration - Registered],
             [Measures].[Course registration - WD w-o refund],
             [Measures].[Course registration - WD w refund],
             [Measures].[Course registration - Total count],
             [Measures].[Credit hours],
             [Measures].[Billing hours],
             [Measures].[Course FTEs],
             [Measures].[Course CE units],
             [Measures].[Registered enrolments],
             [Measures].[Enrolments WD w-o refund],
             [Measures].[Enrolments WD w refund],
             [Measures].[Enrolments - Total count],
             [Measures].[Degrees conferred]
           }
    ) ;

    // Trimester Over Trimester Growth % 
      (
        [Time].[Term Time Calculations].[Trimester Over Trimester Growth %],
        [Time].[Term].[Term].Members ( 1 ) : Null,
        [Time].[Time Key].Members
      ) =     

      (
        ( [Time].[Term Time Calculations].DefaultMember ) -
        ( [Time].[Term Time Calculations].DefaultMember,
          ParallelPeriod(
                          [Time].[Term].[Term],
                          3,
                          [Time].[Term].CurrentMember
          )
        )
      )
      /
      ( [Time].[Term Time Calculations].DefaultMember,
        ParallelPeriod(
                        [Time].[Term].[Term],
                        3,
                        [Time].[Term].CurrentMember
        )
      ) ;
     
      (
        [Time].[Term Time Calculations].[Trimester Over Trimester Growth %],
        [Time].[Term].[Term].Members ( 0 ),
        [Time].[Time Key].Members
      ) = Null ;
     
      Format_String(
                     (
                       [Time].[Term Time Calculations].[Trimester Over Trimester Growth %],
                       [Time].[Time Key].Members
                     )
      ) = "Percent" ;

     
    // Trimester Over Trimester Growth 
      (
        [Time].[Term Time Calculations].[Trimester Over Trimester Growth],
        [Time].[Term].[Term].Members ( 1 ) : Null,
        [Time].[Time Key].Members
      ) =     

      ( [Time].[Term Time Calculations].DefaultMember ) -
      ( [Time].[Term Time Calculations].DefaultMember,
        ParallelPeriod(
                        [Time].[Term].[Term],
                        3,
                        [Time].[Term].CurrentMember
        )
      ) ;
     
      (
        [Time].[Term Time Calculations].[Trimester Over Trimester Growth],
        [Time].[Term].[Term].Members ( 0 ),
        [Time].[Time Key].Members
      ) = Null ;

     
    End Scope ;

    /*
      End Time Intelligence script for the [Time].[Term] hierarchy.
    */

    It works fine except when i use the calculations in ProClarity the totals are empty. I read that ProClarity does not work well w/ ParallelPeriod so i tried replacing it w/ lag(3). I tried replacing AGGREGATE w/ SUM when I read that aggregate function does not work well w/ as2005 sp2. Not working!

    When i try a simple calculation like:

    CREATE MEMBER CURRENTCUBE.[MEASURES].[1 Year Growth %]
     AS IIF(([Time].[Term].CurrentMember.lag(3),[Measures].[Registered enrolments])=0,NULL,(([Time].[Term].CurrentMember,[Measures].[Registered enrolments]) - ([Time].[Term].CurrentMember.lag(3),[Measures].[Registered enrolments]))/([Time].[Term].CurrentMember.lag(3),[Measures].[Registered enrolments])),
    FORMAT_STRING = "Percent",
    NON_EMPTY_BEHAVIOR = { [Registered enrolments] },
    VISIBLE = 1; 

    that goes under [measures] and specifies the measure to be used in calculations. This shows the correct sub totals and grand totals in ProClarity.

    My question , I guess, is more MDX than ProClarity:

    i there a way to have the calculations under [measures] but scoped for all measures. I tried some stuff but i get null result at best.

    Thanks,
    CristinaF
    Tuesday, October 14, 2008 3:53 PM
  • Christina,

     

    Can you help me understand your Time dimension is a [Term] a month?  Has it been defined as a month in the member properties?

     

    When you say the calculations in ProClarity are you talking about the "New Measure..." option in the Desktop Professional?

     

    Have you been able to replicate this issue using the Adventure Works database? 

     

    Scott

    Tuesday, October 14, 2008 7:05 PM
  • I work in a university and term is the equivalent of a trimester. I have a time dimension [Time] and inside an attribute [Term]
    (like Fall 2007, Winter 2008). I am interested of comparisons between same terms in previous years for almost all my measures.
    I am not making any new measures in ProClarity, I am just trying to use the ones defined in my cube. I would select terms Fall 2005, 2006 and 2007, measure registered enrolments and calcualted member [Time].[Term Time Calculations].[Trimester Over Trimester Growth %] on columns and a degree dimension on rows. The values are correct but when i right-click on the grid and check show subtotals and grand totals the cells for them are empty for my [Time].[Term Time Calculations].[Trimester Over Trimester Growth %]. It seems that when the calculation is placed under a dimension (time or any other - I also tried the approach David Shroyer suggests in 'A Different Approach to Implementing Time Calculations in SSAS') ProClarity can't calculate the grand totals. I have no problem when my calculation is put under [measures].

    I haven't tried Adventure Works since the cube was pretty much set up when I started working.

    Cristina
    Tuesday, October 14, 2008 7:24 PM
  •  

    Ok, it's starting to make sense.  The Growth change shows a total, but the %Growth doesn't and I don't think it should in this manner.  A similar problem came up where I work when they asked for a rolling (4 Week AVG Growth), (Previous Year 4 Week Growth) and of course the (4 Week YoY % Growth). 

     

    To compare you are trying to solve the problem by making a time calculation at the week level and then selecting the 4 weeks (trimesters in your case) individually and using the Sub or Group Total to show the answer.  I was able to solve the problem by using a calculated measure that pre aggregated the change of the 4 weeks.

     

    I don't think this is going to completely solve your problem because it sounds like you never know how many trimesters a user will select on columns to accomodate the overall % growth.

     

     

    Code Snippet

    // Previous Year 4 Week Average

    (

    [Calendar].[Calendar Calculations].[Previous Year 4wks],

    [Calendar].[Week Number].[Week Number].Members,

    [Calendar].[BC_Date].Members

    ) =

    Avg(

    {

    ParallelPeriod(

    [Calendar].[Calendar Week Number].[Week Number], 3,

    ParallelPeriod([Calendar].[Calendar Week Number].[Year], 1, [Calendar].[Calendar Week Number].CurrentMember)

    ) : ParallelPeriod([Calendar].[Calendar Week Number].[Year], 1, [Calendar].[Calendar Week Number].CurrentMember)

    },

    [Calendar].[Calendar Calculations].DefaultMember

    );

     

    // 4 Week Average

    (

    [Calendar].[Calendar Calculations].[4wks],

    [Calendar].[Week Number].[Week Number].Members,

    [Calendar].[BC_Date].Members

    ) =

    Avg(

    {

    ParallelPeriod(

    [Calendar].[Calendar Week Number].[Week Number], 3, [Calendar].[Calendar Week Number].CurrentMember

    ) : [Calendar].[Calendar Week Number].CurrentMember

    },

    [Calendar].[Calendar Calculations].DefaultMember

    );

     
    I then created another Calculated Measure outside of the time based calculations with the following.
     

    Code Snippet

    iif([Previous Year 4wks] > 0, ([4wks] - [Previous Year 4wks]) / [Previous Year 4wks], NULL)

     

     

    Sorry if this doesn't help its all I can think of for your problem,

    Scott

     

    Tuesday, October 14, 2008 9:45 PM