How do I write an mdx expression that counts the occurrences of a fact

Question

Suppose I have a dimension called library books and a fact called days overdue. How do I write an mdx expression that will return the number of books that exist at each num days overdue value? In other words:

Days Overdue | Num books
1            | 3498
2            | 237
3            | 57

In SQL, the answer is simple:

select days_late,count(*) from days_overdue_fact group by days_late

In mdx, I have tried this:

with member [Measures].[Overdue Count] as 'count([Book].[System Id].members) '
select  [Measures].[Overdue Count] on 0,
[Measures].[Days Late] on 1
FROM [myCube] 

But I get a complaint from Mondrian: '[Measures]' appears in more than one independent axis.

I simply want to group by the fact values.

Answer 1

The solution is to recognize that this overdue measure is simply part of an accumulating snapshot fact (see The Data Warehouse Toolkit, 2n ed., Ralph Kimball or http://www.kimballgroup.com/2008/11/05/fact-tables/), that tracks the status of a book over time as it moves through its checkout/in process.

There's nothing wrong with having an accumulating snapshot fact like this in a cube. You just have to understand what it is and how to query it. In this case I want to count the occurrences of each value that appears in this fact and the easiest way to do that is with a sql group by rather than mdx.

The answer is he sql in the original question. There is no mdx solution for grouping by a measure value that I am aware of. MDX is the wrong tool for this query.