topic Re: Best QVD Architecture ? in QlikView

Best QVD Architecture ?

AbhijitBansode — Thu, 05 Jul 2012 04:50:54 GMT

My datawarehouse fact contains near about 70 million records .

Each month fact is loaded with nearly 1.2 million data and is always contains data of 5 years (current+last 4 years).

I want to create architecture to load this data in QVW application.

So i decided to go with QVD files for fetching data from database and QVD files will be source of my QVW application.

I want to know which are the best prectices to created QVD file. Below are some of my queries

1. How may QVD files to create?

2. In case if i choose to go with multiple QVD option. How shold i implemet inceramental load in this case?

Any help on this will really help me to proceed with robust architecture.

Re: Best QVD Architecture ?

Anonymous — Thu, 05 Jul 2012 05:03:10 GMT

This document gives you some nice script examples for incremental loads:

http://community.qlik.com/docs/DOC-1870

As for the amount of QVDs.. one per table basically.

I guess the most important ones are fact tables since they are larger and grow faster. (i.e. more load on the DB if pulling the whole thing in every iteration).

Re: Best QVD Architecture ?

AbhijitBansode — Thu, 05 Jul 2012 05:18:31 GMT

is it good practice to have only one qvd for near abou 70 million rows.

In this case, If 1 Lac more records are inserted in database table and i peerformed incremental load,

It will laod new 1 Lac records from database while 70 million rows from one qvd ..

I mean, If new insertion (Of 1 Lac records) in databse table is of latest month in this case also i need to load data of previous 5 years.

Re: Best QVD Architecture ?

Anonymous — Thu, 05 Jul 2012 06:03:07 GMT

70 Million should be fine.

1 Lakh = 100.000 right? or is that a crore?

Since you always want to keep just the past 5 years in the QVD it shouldn't grow a lot in size (except if you suddenly start generating a lot more records every month).

How to store it (one QVD vs. split QVD) depends on how you want to use it. Like if you only want to display detailed granular info from the last 3 months, 6 months or 1 year maybe you only keep that amount in one QVD and then separate the others into yearly QVDs or similar.

It also depends on if your historical data changes over time, which would mostly affect dimension tables I guess, depending on how your fact table is defined.

Re: Best QVD Architecture ?

AbhijitBansode — Thu, 05 Jul 2012 06:17:32 GMT

Thank you for reply..

Yes

1 lakh= 100000.

My fact table is insert only.. there are no updates to fact table.

End user of the report might see the detailed information of months in last year or last to last year.