3

Hi I have been playing around with MDX and need some very high-level getting-started type guidance. I already have a SQL-Server 2008 R2 DB up and running with data in it. I want to try something very simple in MDX to get familiar with the flow. I am not a DW or Cube expert so I need to start slow. (Facts/Dimenions are new to me)

I have a view from my relational DB that I wish to use as the single source for MDX. It's simple:

TABLE WEEKLY_GAIN
  PCTGAIN (DECIMAL) -- The pre-calc'd pctg gain in price from day x to day x+1  
  DATE (DATE)       -- The date the stock pctgain was generated  
  WEEK(INT)         -- Contiguous/Consecutive integer that is keyed to the last trading day of each week.  
  YEAR (INT)        -- The year of the current PCTGAIN from the DATE column

Sample Data: 

PCTGAIN          DATE           WEEK   YEAR  
0.01709791       2011-01-14     2      2011  
0.01102063       2011-01-07     1      2011  
0.0006922469     2010-12-31     52     2010  
0.01033836       2010-12-23     51     2010

I would like to use MDX to generate a cube and some functions (MEDIAN, MEAN, STDEV, etc). I have successfully identified the above view as a source in MDX. However I need help defining the (MDX) dimenions, keys, cube definitions, and whatever else needs to be done, etc.

I believe this should be straight forward (the date, WEEK, YEAR would be dimensions?) but I am not sure.

One "Cube" I would like to generate is...All of the years (1950-2011) are rows, all of the weeks (1-52) are columns and then generate some MIN, MAX, MED, aggregates of PCTGAIN for each week number across all years. I recognize there are ways to do this with T-SQL. However, I wish to do this in MDX to get the hang of setting MDX up and becoming productive with it.

I am happy to provide additional detail as needed. Thanks

Preet Sangha
  • 64,563
  • 18
  • 145
  • 216
Frank Zappa
  • 451
  • 2
  • 11
  • 23

2 Answers2

4

There are some good resources for learning MDX at this question here: How to learn MDX

With your final example it's generally much easier to work with rows rather than columns for your periods so I'd recommend UNPIVOTing your table so each week is a rows and then building a date dimension with the right levels. Once that is done you'll be able to use the MDX functions to calculate MIN, MAX, MED, PCTGAIN etc with relative ease.

Community
  • 1
  • 1
Craig
  • 1,327
  • 1
  • 13
  • 15
0

Read this book:

Microsoft SQL Server 2008 MDX Step By Step

Is the best.

Lucas Motta
  • 135
  • 2
  • 11