Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Apr 30 '12 at 12:39

This question came from our site for professional and enthusiast programmers.

add comment

1 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.

share|improve this answer
    
Excellent advice...I will probably end up using a combination of both SQL and MDX, since my MDX skills are not that good,neither is my SQL but at this point it seems an easier language... and more used. Nevertheless I was relieved to find that It's not too link –  MartKa May 26 '12 at 19:15
    
Excellent advice...I will probably end up using a combination of both SQL and MDX, since my MDX skills are not that good,(neither is my SQL but at this point it seems an easier language... and more used.) Nevertheless I was relieved to find that both approaches are being used for calculations Doing the calculations in ETL seems to be the preferred approach when feeding multiple cubes. –  MartKa May 26 '12 at 19:21
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.