Business just wants a subset

View previous topic View next topic Go down

Business just wants a subset

Post  revdpoel on 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

View user profile

Back to top Go down

Re: Business just wants a subset

Post  nick_white on 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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Business just wants a subset

Post  revdpoel on 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

View user profile

Back to top Go down

Re: Business just wants a subset

Post  ngalemmo on Thu Aug 07, 2014 1:32 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Business just wants a subset

Post  nick_white on 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 : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: Business just wants a subset

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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