Differing Frequency of Fact Data
4 posters
Page 1 of 1
Differing Frequency of Fact Data
Hi, I have a question regarding a scenario that I am fiddling with and would like your opinion about it.
Say I have a customer dimension and a date dimension and a Fact tables with SKs to customer dimension and date dimension.
On a daily basis the accounting system sends a feed which we religiously store in the fact table. There are some customers for which data is provisioned on a daily basis whereas for some, the data is provided only on month-ends. For example, Customer C1's data is available only on month-ends whereas Customer C2's data is available for everyday. The fact table has a look of
Customer_ID, Date_ID, Facts................
C1, 20120331,.............
C2, 20120331,.............
C2, 20120401,.............
C2, 20120402,.............
.
.
.
.
.
C1, 20120430,............
C2, 20120430,............
As you can see in the above example, the population of C1 appears only on month-ends. Business needs to have a visibility of both customers at any given point in time. They would want the latest data of C2 whereas the prior month-end data of C1 would suffice their needs, but the would want to see them together. One option would be to replicate C1's data from prior month-end until the next month-end so that C1 and C2's data are available together, but this poses a challenge that data volume increases, the vendor provided data has to be persisted in a staging area so that everyday the replication would point to a latest snapshot of customer dimension (were it to be of Type2).
Would you suggest any other approach that circumvent this data replication process.
Regards.
Say I have a customer dimension and a date dimension and a Fact tables with SKs to customer dimension and date dimension.
On a daily basis the accounting system sends a feed which we religiously store in the fact table. There are some customers for which data is provisioned on a daily basis whereas for some, the data is provided only on month-ends. For example, Customer C1's data is available only on month-ends whereas Customer C2's data is available for everyday. The fact table has a look of
Customer_ID, Date_ID, Facts................
C1, 20120331,.............
C2, 20120331,.............
C2, 20120401,.............
C2, 20120402,.............
.
.
.
.
.
C1, 20120430,............
C2, 20120430,............
As you can see in the above example, the population of C1 appears only on month-ends. Business needs to have a visibility of both customers at any given point in time. They would want the latest data of C2 whereas the prior month-end data of C1 would suffice their needs, but the would want to see them together. One option would be to replicate C1's data from prior month-end until the next month-end so that C1 and C2's data are available together, but this poses a challenge that data volume increases, the vendor provided data has to be persisted in a staging area so that everyday the replication would point to a latest snapshot of customer dimension (were it to be of Type2).
Would you suggest any other approach that circumvent this data replication process.
Regards.
DSS- Posts : 1
Join date : 2012-11-28
Re: Differing Frequency of Fact Data
Is this the same feed of the same data from the same system? If the business need is to see daily information, why would the source not provide daily information?
If these are different feeds from different systems, you may want to consider different fact tables.
If these are different feeds from different systems, you may want to consider different fact tables.
Re: Differing Frequency of Fact Data
Maybe add a column to store a flag indicating the row is the most current record for the customer?
Jeff Smith- Posts : 471
Join date : 2009-02-03
Re: Differing Frequency of Fact Data
Hi,
The best solution is to have only one row for a account/month if you are intrested only in one row per customer/month.
Any reason why accounting system is sending some data only oce a month and for others more than one time ?
If you get a 2nd record for a particular then what is the differnace between the 2nd and the 1st record that you received?
thanks
The best solution is to have only one row for a account/month if you are intrested only in one row per customer/month.
Any reason why accounting system is sending some data only oce a month and for others more than one time ?
If you get a 2nd record for a particular then what is the differnace between the 2nd and the 1st record that you received?
thanks
hkandpal- Posts : 113
Join date : 2010-08-16
Similar topics
» Differing grain dimension tables with the same transactional fact
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» clickstream fact data coming in with different levels of dimensional geography data
» Loading data from one fact to another summary fact
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» clickstream fact data coming in with different levels of dimensional geography data
» Loading data from one fact to another summary fact
» Dimension Attributes and Fact attributes storing same data in multiple data marts??
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|