Differing Frequency of Fact Data

View previous topic View next topic Go down

Differing Frequency of Fact Data

Post  DSS on Wed Nov 28, 2012 3:54 pm

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.

DSS

Posts : 1
Join date : 2012-11-28

View user profile

Back to top Go down

Re: Differing Frequency of Fact Data

Post  ngalemmo on Wed Nov 28, 2012 5:41 pm

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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Differing Frequency of Fact Data

Post  Jeff Smith on Wed Nov 28, 2012 6:32 pm

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

View user profile

Back to top Go down

Re: Differing Frequency of Fact Data

Post  hkandpal on Thu Nov 29, 2012 3:14 pm

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

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Differing Frequency of Fact Data

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