How to model customers having contracts (factless fact table?)
2 posters
Page 1 of 1
How to model customers having contracts (factless fact table?)
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
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
Re: How to model customers having contracts (factless fact table?)
Don't put a row for each day. Create a row for each contract. Create two date dimensions relationships, start and end date.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to model customers having contracts (factless fact table?)
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
Thanks a lot,
ds
ds- Posts : 8
Join date : 2011-05-15
Similar topics
» Factless Fact table to model 1:M relationships between Type 2 SCD
» Fact Table or Factless Table: Please Suggest
» Same attribute in multiple dimensions or Create new dimension?
» difference between factless fact and bridge table
» Help with design of Factless Fact Table SCD
» Fact Table or Factless Table: Please Suggest
» Same attribute in multiple dimensions or Create new dimension?
» difference between factless fact and bridge table
» Help with design of Factless Fact Table SCD
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum