I am just about to start on a new project where the fact table bring back 2 years of data is around 200 million records. I understand that designing the data model right would help me go a long way and hence am seeking advise right at the outset.
I was leaning towards making a qvd for each month and concatenate it finally into a single table. There will be incremental loading to bring back D-1 data.
The final fact table I will have will - I am trying to make it one huge table without any joins to any dimensions.
The fact table contains a transaction date, dim 1, dim 2, dim 3, customer id, amount, no of transactions
There is a need to use the count(distinct customer_id) to identify unique customers and I am not too sure if this will prove costly.
There is a need to slice and dice the measure ‘ number of unique customers’ by dim 1, dim 2 and dim 3.
If not for this measure I can pre – aggregate and the size of the data will be greatly reduced.
I am wondering what would be the best approach towards this.
But I have a question for you, I have a single fact table which contains aggregate daily as follows
Transaction Date, Customer Id, Dim1 , Dim 2, Dim 3, No of transactions, Value of Transactions and the only table I would link to is the Date dimension.
The user would like to see daily detail for M, M-1, M-2, and M Y-1 for the measures No. of Transactions, Value of Transactions and No. of Unique customers per day.
How would you approach this other than having a single fact table?
My load approach is a daily incremental one and for history I propose to have 1 QVD a month.
