CODEWITHSUNDEEP
ssasmdx
Logan
Logan

Reputation: 1

count records in MDX using 2 date dimensions

I'm totally new to MDX (My background is T-SQL). I have a fact table / cube with basically 2 columns

[Start Date], [Finish Date]

20140101, -1

20140101, 20140401

20140301, 20140501

...

I have a simple measure counting the records based on those columns

Counting how many people started (or finished) in some date is a simple task, but I want to know the cumulative total in a period.

Looking at the example above:

Month/Year, Started, Finished, CumulativeTotal

01/2014, 2, 0, 2

02/2014, 0, 0, 2

03/2014, 1, 0, 3

04/2014, 0, 1, 2

05/2014, 0, 1, 1

The "-1" is a Foreign Key to a "Not Defined" value in my date dimensions. This means the record is not finished yet, therefore, it must appear in every month after the started date.

How can I achieve this? I just can't find any reference for it. Or I just don't know what to look for (probably true).

Upvotes: 0

Views: 128

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

Not to avoid answering your question, but this isn't a good design for a fact table to be used to populate a cube. You want to create a star schema, so a better design would be something like this:

PersonId  Month  Count

Where PersonId is a foreign key to your People table, Month is a DateTime that is a foreign key to a Time dimension table, Count is either a 1 or 0 depending on if that person should be counted in that month or not.

To get the results you want from the table you have, you're better off using straight SQL. This isn't the kind of thing that Cubes and MDX are meant to handle.

Upvotes: 1

Related Questions