Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Useful information and case studies covering Data Warehousing, Data Modeling, and Business Intelligence

I started my career in IT nearly 10 years ago and have remained influenced and driven by two particular technology initiatives - Business Intelligence and Data Warehousing. Opportunities for partnership, learning, and innovation will continue to present themselves as we strive to meld people, business, and technology. I look forward to these opportunities as I am fulfilled by my membership to a scientific community that is driven by the development and execution of technology solutions that enhance the way we live and conduct business.

SSAS How-To: Create Dynamic Time Calculations To Reuse Over Any Measure

Rather than making individual time calculations for each measure in an SSAS cube (i.e. YTD, Prior YTD, YTD Variances, etc), one can use a more dynamic approach to time calcs applicable to all measures and save a great amount of dev time and overhead in the process.

1. Create Named Query in DSV:

SELECT      1 AS DateCalculationKey, ‘Selected Date’ AS DateCalculation
UNION
SELECT      2, ‘YTD’
UNION
SELECT      3, ‘PY YTD’
UNION
SELECT      4, ‘YTD Chg’
UNION
SELECT      5, ‘YTD Chg %’
BStep1_NamedSet

2. Create the dimension and add it to the Dimension Usage tab of the cube (no relations to the fact will exist)

3. Add the MDX script calculations:

SCOPE (
[Date].[Calendar Hierarchy].MEMBERS);
[Date Calculations].[Date Calculations].[YTD] =
AGGREGATE
(
    {[Date Calculations].[Date Calculations].[Selected Date]} *
     PERIODSTODATE
     (
       [Date].[Calendar Hierarchy].[Calendar Year],
       [Date].[Calendar Hierarchy].CURRENTMEMBER
     )
);
[Date Calculations].[Date Calculations].[PY YTD] =
AGGREGATE
(
   {[Date Calculations].[Date Calculations].[Selected Date]} *
   PERIODSTODATE
   (
      [Date].[Calendar Hierarchy].[Calendar Year],
      PARALLELPERIOD
      (
         [Date].[Calendar Hierarchy].[Calendar Year],
         1,
         [Date].[Calendar Hierarchy].CURRENTMEMBER
   )
)
);
END SCOPE;
SCOPE ([Date].[Calendar Hierarchy].[All]);
[Date Calculations].[Date Calculations].[YTD] = NULL;
[Date Calculations].[Date Calculations].[PY YTD]=NULL;
END SCOPE;
[Date Calculations].[Date Calculations].[YTD Chg]=[Date Calculations].[Date Calculations].[YTD]-
[Date Calculations].[Date Calculations].[PY YTD];
[Date Calculations].[Date Calculations].[YTD Chg %] =
IIF
(
[Date Calculations].[Date Calculations].[PY YTD] = 0,
NULL,
([Date Calculations].[Date Calculations].[YTD] - [Date Calculations].[Date Calculations].[PY YTD]) /
[Date Calculations].[Date Calculations].[PY YTD]
);
FORMAT_STRING([Date Calculations].[Date Calculations].[YTD Chg %]) = “#,0.0%”;
//Create current year and month in scripts:
CREATESET CURRENTCUBE.[Current Year]
AS {(strtomember(“[Date].[Calendar Hierarchy].[Calendar Year].["+ cstr(year(now()))+"]“))}, DISPLAY_FOLDER = ‘Named Sets’ ;
CREATESET CURRENTCUBE.[Current Month] AS
TAIL
(
NONEMPTY
(
[Date].[Calendar Hierarchy].[Month Name Year],
[Measures].[Fact Sales Count]
)
);
BStep2-Scripts

The result!

BStep2b-CubeBrowser


Comments

Leave a comment on the original post [saldeloera.wordpress.com, opens in a new window]

Loading comments...