Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
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.
Any advise is appreciated.
Hello,
I would question the reason to have one single big table (=> higher strain on resources)
Have a look at those:
Reducing the Size of a Data Model
Autonumber Handling for QVD Optimized Load (via Key Storage)
5 Essential Tools for QlikView Developers
http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity
How to do Optimization of QVD load in qlikview.
http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load
Performance w/ multiple fact tables - star schema vs. master link
Calculating rolling n-period totals, averages or other aggregations
Best Practices for Data Modelling
*** 6 Weeks in to QV Development, 30 Million Records QV Document and Help Needed!!! ****
I hope this helps,
Good luck,
Antoine
Thanks erudite for the links.
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.
Would appreciate your thoughts on this.