Distinct count at different level than the grain

I am trying to view a distinct count on a level that is different from the grain of my fact table.

If my cube was based on a library, then my grain would be when a book is checked out. I want to report on this grain to determine how many total times each book was checked out to determine their popularity. I also want to have a summary count that shows how many books were actually checked out during a time period regardless if if they were checked out multiple times during that period. This second use case is the issue, as I basically want the distinct count of books checked out during a month and not the total count of check outs, which is the grain of the fact table.

I considered using a snapshot fact to aggregate the data, but the problem is that I want to know the exact day the book was checked out so that I could say that in the last week only 10 of my 500 books were checked out, but the week before 200 of my 500 books were checked out.

Can MDX be used to provide distinct counts? Would a snowflake be the answer? I have also seen some posts where people store a key for each level from the dimension in the fact table, but I am not sure if that is the answer.

Assuming that your FK to the Book dimension is BOOK_FK, you can use a count(distinct(BOOK_FK)) to get the number of books being checked out. This is the way you manage measures at different level of granularity.

I have the same type of question. You can have a measure called "check out status" that contains a 0 or a 1. If this is at the book level, how can I count how many libraries have checked out books. You can't sum the status because that will be at the book level. You could create a separate aggregate store, but in many cases that would be building too many structures. I have a cube that is survey based. The grain is survey question. So, My issue would be the same, in that I want to count how many completed surveys and not how many completed answers. I can write this in SQL, but an MDX definition for a measure would be most helpful.

Please keep up posted.
I am looking for answers too.


debt solutions