CODEWITHSUNDEEP
ssasmdx
AeroX
AeroX

Reputation: 3443

Creating a Calculated Time Periods Hierarchy

In order to give a nicer Cube browsing experience to end users, I am trying to create a Time Periods hierarchy consisting of Calculated Members.

Currently I have used a Calculated Column in my DSV to create a column with the same value on every row in my Dates table (value is All Time). Then within my Date and Time dimension I have created a single level, single member hierarchy using that Calculated Column, which looks like this:

Screenshot of Single level, single member hierarchy

Now what I have already successfully done is add Time Periods to my Calendar hierarchy with the following calculation:

CREATE MEMBER CURRENTCUBE.[Completion Date].[Calendar].[All].[Last 30 Days]
 AS SUM(LastPeriods(30,StrToMember(
    "[Completion Date].[Calendar].[Day]."+
    "&["+CStr(Year(Now()))+"]&["+CStr(Month(Now()))+"]&["+CStr(Day(Now()))+"]"
))),VISIBLE = 1;

This works as expected on the Calendar hierarchy:

Screenshot of Calculation working successfully on Calendar Hierarchy

But I want to move these into the new Time Periods hierarchy to keep them nicely separated.

So far I have tried to do this in two different ways:

  1. Changing the destination Hierarchy of the Computed Member. Changing CREATE MEMBER CURRENTCUBE.[Completion Date].[Calendar].[All].[Last 30 Days] to CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days].

  2. Changing the Visibility of the Calculated Member on the Calendar hierarchy to VISIBLE = 0 and creating a second Caculated Member on the Time Periods hierarchy which references it: (I have tried with and without using the SUM() function)

CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days]
 AS SUM([Completion Date].[Calendar].[All].[Last 30 Days]),
VISIBLE = 1;

But neither of these have worked for me. So my question is, How can I complete what I am trying to achieve?

My end goal is to have a Hierarchy which the user can drag into a pivot table and see the following (but with the Time Periods actually calculated):

Screenshot of Goal

Upvotes: 1

Views: 2008

Answers (2)

AeroX
AeroX

Reputation: 3443

Thanks to Alex Peshik's Answer I have managed to get this to work.

Here is the method I have now used to setup a Calculated Time Periods Hierarchy:

  1. Go into your DSV and create a New Named Calculation on your Date table with a name like TimePeriod and set the Value to be something like All Time.

  2. Within your Date dimension create a new Attribute from that Named Calculation and use it to make a Hierarchy.

  3. Make sure the New Attribute is linked to your Day Attribute in the Attribute Relationships tab.

  4. Within your cube, go to the Calculations tab and create a Dummy Calculated Member in the Time Period hierarchy we have made for each time period you want to add:

CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 30 Days]
    AS NULL, VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 60 Days]
    AS NULL, VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Completion Date].[Time Period].[All].[Last 90 Days]
    AS NULL, VISIBLE = 1;
// etc...
  1. Now at the bottom of your Calculation you add a Scope for each Dummy Calculation we made in the last step (you will need to change the bit within the StrToMember() function to match your day attribute Key):
SCOPE ([Completion Date].[Time Period].[All].[Last 30 Days]);
    THIS = SUM(LastPeriods(30, StrToMember(
        "[Completion Date].[Calendar].[Day]."+
        "&["+CStr(Year(Now()))+"]&["+CStr(Month(Now()))+"]&["+CStr(Day(Now()))+"]"
    )));
END SCOPE;

SCOPE ([Completion Date].[Time Period].[All].[Last 60 Days]);
    THIS = SUM(LastPeriods(60, StrToMember(
        "[Completion Date].[Calendar].[Day]."+
        "&["+CStr(Year(Now()))+"]&["+CStr(Month(Now()))+"]&["+CStr(Day(Now()))+"]"
    )));
END SCOPE;

SCOPE ([Completion Date].[Time Period].[All].[Last 90 Days]);
    THIS = SUM(LastPeriods(90, StrToMember(
        "[Completion Date].[Calendar].[Day]."+
        "&["+CStr(Year(Now()))+"]&["+CStr(Month(Now()))+"]&["+CStr(Day(Now()))+"]"
    )));
END SCOPE;

Upvotes: 1

Alex Peshik
Alex Peshik

Reputation: 1515

I've just created the same this way:

1) Add dummy attribute for unfiltered values with the name 'All Time' (key is int with 0 value)

2) Add 3 empty members

CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 30 Days]
 AS 
null,
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 60 Days]
 AS 
null,
VISIBLE = 1;
CREATE MEMBER CURRENTCUBE.[Report Date].[Time Period].[All].[Last 90 Days]
 AS 
null,
VISIBLE = 1;

3) Than add scopes (I have another key format):

/* SCOPES */
SCOPE ([Report Date].[Time Period].[All].[Last 30 Days]);
THIS = Sum(LastPeriods(30,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;
SCOPE ([Report Date].[Time Period].[All].[Last 60 Days]);
THIS = Sum(LastPeriods(60,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;
SCOPE ([Report Date].[Time Period].[All].[Last 90 Days]);
THIS = Sum(LastPeriods(90,StrToMember("[Report Date].[Report Date].[Day].&["+CStr(Format(Now(),"yyyyMMdd"))+"]")));
END SCOPE;

It works (also add a measure to count members of a level to validate):

LastNDays

Upvotes: 3

Related Questions