# Performing aggregations and calculations: SQL (CTE) vs MDX

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?

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.