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

Business just wants a subset

3 posters

Go down

Business just wants a subset Empty Business just wants a subset

Post  revdpoel Thu Aug 07, 2014 1:58 am

All

I have a fact table with 20 measures. We get the information in file-format from an external company.
Not every month we get the 20 measures. Has to do with agreements made. External company sends als a kind of steering file, which gives for every measure an indicator if it will be delivered this month or not.

The business only wants each month the delivered measures, so delivered-indicator = 'Y'
So if 15 of the 20 measures are delivered, they only want 15. Seems like a flexible fact table.
How do I model this?

Thanks
Ron

revdpoel

Posts : 24
Join date : 2010-06-11

Back to top Go down

Business just wants a subset Empty Re: Business just wants a subset

Post  nick_white Thu Aug 07, 2014 8:07 am

Add a measure Type as a dimension (possibly a degenerate dimension) and have one fact record per measure rather than 15 or 20 measures per fact?

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Business just wants a subset Empty Re: Business just wants a subset

Post  revdpoel Thu Aug 07, 2014 1:17 pm

Current situation

My fact table

month-key
measure1 number
measure2 number
measure3 number
measure4 number
measure5 number
measure6 number

Steering table

measure nr
measure ind

So suppose the measure ind for measure nr 1, 4 and 6 equals 'J' then they have to be delivered

You say mu fact table must look like

month-key
measure-key
measure

and the dimension like

measure-key
measure-ind??

And then the business has to join those two?


revdpoel

Posts : 24
Join date : 2010-06-11

Back to top Go down

Business just wants a subset Empty Re: Business just wants a subset

Post  ngalemmo Thu Aug 07, 2014 1:32 pm

You can have a fact table with 20 measures… just leave ones you do not get null.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Business just wants a subset Empty Re: Business just wants a subset

Post  nick_white Fri Aug 08, 2014 2:42 am

Absolutely agree with ngalemmo that the obvious solution is to create the fact table with 20 measures - I'm sure the users could live with this plus there is no way of creating a fact table that has a variable number of measures. You could probably hide columns in your BI tool or using DB Views if absolutely necessary.

If your users refuse to accept this then I think the only solution is my previous suggestion to create a fact record per measure. Not sure, from your response, I was clear what I was suggesting.
I assume each measure has a name it is known by - so put this name in a dimension (or as a degenerate dimension on the fact table).
Each month, create one fact record for each measure you have a value for that month. So one month you might create 15 fact records, the next 20 records, the next 17 records, etc.
You would use your measure indicator during your etl process to determine whether to create a fact record or not; it would not appear in your dimensional model.

Regards

nick_white

Posts : 364
Join date : 2014-01-06
Location : London

Back to top Go down

Business just wants a subset Empty Re: Business just wants a subset

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