How to model customers having contracts (factless fact table?)

View previous topic View next topic Go down

How to model customers having contracts (factless fact table?)

Post  ds on Fri Feb 08, 2013 6:26 pm

Hi,
We would like to understand how many of our customers had a contract with us at a specific point in time (i.e. on a certain day). Contracts can have a varying length (for each customer I will get a record with a contract name and a start and end date).
At first it all sounded like a factless fact table to me. I thought I could take a contract record and derive from the start and end date the contract length in days and then create for each day a record in a factless fact table. So let's say customer X bought contract Y which is valid from 2013-01-01 to 2013-01-15, so 15 days, then I would end up having 15 records for this customer and contract in the factless fact table. But this sounds like data explosion to me (usually contracts are much longer, say 2 years and we have millions of customers). There must be a better way to do this, or? Is the periodic snapshot fact table the way to go, where one record would have the contract, customer, start date, end date?
Thanks,
ds

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to model customers having contracts (factless fact table?)

Post  BoxesAndLines on Sat Feb 09, 2013 12:12 am

Don't put a row for each day. Create a row for each contract. Create two date dimensions relationships, start and end date.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to model customers having contracts (factless fact table?)

Post  ds on Sat Feb 09, 2013 4:08 am

Many thanks for your reply! The other point I had to keep in mind was how this could be accessible to the user in an easy fashion via the Olap GUI. Currently with the drag and drop GUI we are using there is no way to define a static date and use it for a between start and end date. You can only define constrains using the dimension values. I certainly can do this directly in MDX, but this is not suitable for end users. This is why my first solution would have worked for the GUI. But as mentioned, I was not happy about this approach because of data explosion. I will follow your suggestion and create a dedicated report which allows the user to define a date parameter which will be used in the MDX query underneath (start date <= date param <= end date).
Thanks a lot,
ds

ds

Posts : 8
Join date : 2011-05-15

View user profile

Back to top Go down

Re: How to model customers having contracts (factless fact table?)

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