Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Advise on data model

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.

2 Replies
Not applicable
Author

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.