individual day calculations ,in MDX, with Start and End date in fact table

0
votes

I have a ssas cube with the fact table containing:

  • FactID
  • Status
  • StartDate
  • EndDate

the dates are linked to a date dimension (status to the status dimension).

Im trying to get a report that shows the amount of facts at a status on each day over a two week period, eg:

         01 May 2011, 02 May 2011, 03 May 2011 etc...
status1          300          310          320    ...
status2          250          240          265    ...
status3          125          546          123    ...

I can obtain the data for a single day using the following:

select 
{
 [TOTAL NUMBER FACT]
} on 0  

,{
descendants([DIM STATUS].[STATUS DESCRIPTION])
} on 1
from [DW_CUBE]
WHERE   
([DIM HISTORY START DATE].[YEAR MONTH DAY].FirstMember:[DIM HISTORY START DATE].[YEAR MONTH DAY].&[20110501], 
 [DIM HISTORY END DATE].[YEAR MONTH DAY].&[20110501]:[DIM HISTORY END DATE].[YEAR MONTH DAY].LastMember)

but do i get this working for more than a single day?

Many many thanks

1
datessasmdx

1 Answers

1
votes

Have a look at the following links:

http://www.bp-msbi.com/2010/10/avoiding-multiple-role-playing-date-dimensions/

http://cwebbbi.wordpress.com/2011/01/21/solving-the-events-in-progress-problem-in-mdx-part-1/

In brief, you can use MDX to do this with LinkMember, or if you are counting events in progress - with counting from begin of time till now and subtracting one event from another.

You can also solve the problem with modelling - in my post by pivoting and in Chris's follow-up with role-playing measure groups.