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


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)

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)
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?
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.