Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Distinct count at different level than the grain

4 posters

Go down

Distinct count at different level than the grain Empty Distinct count at different level than the grain

Post  acobb Wed Jun 23, 2010 12:52 pm

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.

acobb

Posts : 6
Join date : 2010-06-23

Back to top Go down

Distinct count at different level than the grain Empty Distinct count at different level than the grain

Post  alex.caminals Fri Jun 25, 2010 6:00 am

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.
alex.caminals
alex.caminals

Posts : 15
Join date : 2009-02-25
Age : 48
Location : Barcelona (Spain)

Back to top Go down

Distinct count at different level than the grain Empty Re: Distinct count at different level than the grain

Post  Bill.Wimsatt Tue Jul 06, 2010 2:49 pm

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.

Bill.Wimsatt

Posts : 1
Join date : 2010-07-06

Back to top Go down

Distinct count at different level than the grain Empty Re: Distinct count at different level than the grain

Post  joetraff Fri Jul 16, 2010 3:49 am

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


debt solutions

joetraff

Posts : 2
Join date : 2010-07-15

Back to top Go down

Distinct count at different level than the grain Empty Re: Distinct count at different level than the grain

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum