Model not-fixed-step for process in a wide datawarehouse. Extend fact-table ?

View previous topic View next topic Go down

Model not-fixed-step for process in a wide datawarehouse. Extend fact-table ?

Post  maxpinato on Tue Jul 29, 2014 12:13 am

I've read about star schema for process flow, and your suggestion to use “accumulating snapshot fact table”.

http://forum.kimballgroup.com/t3001-star-schema-for-process-flow

I've a similar need, but the process is not in a fixed sequence.
I have to model the action to manage the credit of a customer.
So, in short, we have the following entities:

Invoice --< Dun
Invoice --< Debit Collection
Invoice --< Payments

Each of these entities have at least attributes of invoice (date document, date due, value) and process (date of dun creation, id of dun creation, dunning level for dunning, code of debit collector and date of debit collection for DC, date of payment and mode of payments for Payments).

The tipical use of this datawarehouse is …
1. Give me the value of invoice in a period with a specific dunning level, and for these invoices give me the payments and the actual state of debit collection
2. Give me the value of invoice in a period that are in a debit collection started in an interval of DC date creation and for these invoices give me the payments and the last dunning level.

Actually exists the datamart of Invoice. We would try to find a way to “extend” the fact-table with dimension of consecutive process (time-depentent of course) without impact on the actual fact-table.

Any suggestions ? (I hope the scenario I clear, if not tell me).

MAX

maxpinato

Posts : 1
Join date : 2014-07-28

View user profile

Back to top Go down

Re: Model not-fixed-step for process in a wide datawarehouse. Extend fact-table ?

Post  nick_white on Wed Jul 30, 2014 8:27 am

When designing something I would normally start off with a fact table for each type of activity I'm modelling - so in your case I would probably have 3 fact tables: invoice, debit collection and payment. Using these three fact tables you should be able to deliver any reporting requirement as your conformed dimensions will allow you to select related data from each fact and join them together in your query tool. The process of joining them together may not be simple so you then look at creating combined/aggregated/accumulating snapshot fact tables (based on your original fact tables) that give you a more sophisticated view of your data.
As your design matures you may decide that all your reporting requires are met by your more complex fact tables and you could remove your original fact tables from your design - however I wouldn't do this as having these low level tables will almost certainly make it easier to build more complex fact tables in the future. Just because you don't report off them directly doesn't mean that there is no point having them in your DW.

In your particular case, if your basic requirement is to manage the credit of a customer then as well as having the basic fact tables I might look at creating a customer snapshot table - which shows the credit status of a customer at the end of every day/week/month, whatever time period(s) suit your business.

Hope this helps?

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

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