Fact Table Comparisons and what is better for your business?

Fact Table Comparisons and what is better for your business?

There are three type of Fact Tables:
1. Transaction Fact Tables ( Transaction Grain)
2. Periodic Snapshot Fact Tables ( Periodic Snapshot Grain)
3. Accumulating Snapshot Fact Tables ( Accumulating Snapshot Grain)

First question to ask yourself , what is your main business ? and what Industry you are in ?

Retail Business , Inventory , Procurement , Order Management , Customer Relationship management, Accounting , Human Resource Management , Financial Service , Telecommunication , Transportation , Education , Health Care , E Commerce , Insurance , Manufacturing , etc.

Second Question:
What do you want to achieve in the Data warehouse / DSS (Decision Support System) / Business Intelligence (BI) system ?
Determine the goal of your Data Warehouse.

Third Question:
Who are all the users of the data warehouse / BI System ? And their roles such as CFO , CTO , VP(Sales & Marketing) , Sales Manager , Production Manager , Area Manager , Business Analyst , Business user , IT Support , etc.

Fourth question:

What kind of reports expected out of this new data warehouse ? And also if the users already using some excel type reports manually prepared by Business users to report to their higher officials. List out all of them & if you already have the samples keep it.

next , list out all of the business process , identify what are the measures /facts used in those reports. If there are any calculations involved list each one of them. And identify how often it changes , where is the source system it captures these measures and also changing criteria. This step is to identify the grain i.e Level of detail specified business process can't be divide further. (Lowest Level of detail)
Ex: Order processing may have Customer , Product , Order Date , Order Number .. etc , these become dimensions in the dimension model

Next , against each business process list out the measures / facts and identified Dimensions in the previous step.

Ex: Order Quantity , Product Unit Price [ Let us just consider this as measure but it is really Non-Additive measure]

[I will continue tomorrow]