Differing Frequency of Fact Data

[DSS]

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.

[ngalemmo]

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.

[Jeff Smith]

Maybe add a column to store a flag indicating the row is the most current record for the customer?

[hkandpal]

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

[Sponsored content]