Performing aggregations and calculations: SQL (CTE) vs MDX

Question

I’ve hit a wall and would like some expert advice.

I’m building a data warehouse and don’t quite understand whether the calculations (also time aggregations) should be done in SQL (T-SQL and CTE) or MDX. So Currently I have the following approach

1. Define the level of time aggregation needed for the calculations. (currently I’d like to do it in SQL)

   • Day
   • Week
   • Month
   • Quarter
   • Year YTD (year-to-date)
   • QTD (quarter-to-date)
   • YTW (year-to-week)
   • YTM (year-to-month)

2. Calculate the calculations that can be calculated on the column level and at the level of each time aggregation (again I’d like to do it in SQL)

3. And then do the row level calculations (for this I’m thinking about using MDX

Changes with previous period:

• Day change with previous day
• Week change with previous week
• Month change with previous month
• Quarter change with previous quarter
• Year change with previous year

Changes compared to same period of last period

• Day vs same day last week
• Day vs same day last month
• Day vs same day last year
• Week vs same week last month
• Week vs same week last year
• Month vs same month last year
• Quarter vs same quarter last year

The reason I’m thinking about doing steps 1 and 2 in SQL is that I’ m visualizing it as a table that has all the variables grouped to a level of time aggregation of YTD, YTM, YTW, etc and then in the same table there would also be calculations in columns...and then if I use this table as one of the underlying tables of the DW then in the DW I would use MDX for the parallel period calculations.

I would like to know if this is a good approach...or what is the best approach/place to do calculations?

Answer 1

Row-level YTD calculations on a database table are not very useful unless you have a unit of analysis for your YTD figures that can be processed additively (i.e. you're not going to find your reports trying to aggregate two YTD figures). This essentially limits their usefulness to summary tables.

This type of aggregate table is of limited use with a cube, as the cube gives you the aggregation for free. If you have a time dimension on the cube you can make calculated YTD measures that encapsulate the calculations. with an expression such as Aggregate (YTD([Date].[Date].[CurrentMember]), [Measures].[SomeMetric]). You don't even have to specify the date.

The calculations you describe can all be done relatively easily in MDX with hierarchy functions such as PrevMember and ParallelPeriod, and this is more flexible and much less effort to implement than working off an aggregate table generated in the underlying database. You're better off doing al the YTD and period-period comparisons in the cube.