topic Re: Advise on data model in QlikView

Advise on data model

Fri, 08 Aug 2014 08:25:46 GMT

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.

Re: Advise on data model

Anonymous — Fri, 08 Aug 2014 15:18:23 GMT

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

How to do Incremental Load.

*** 6 Weeks in to QV Development, 30 Million Records QV Document and Help Needed!!! ****

How delete the qvd file.

I hope this helps,

Good luck,

Antoine

Re: Advise on data model

Sat, 09 Aug 2014 18:40:34 GMT

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.