Should I replicate my data warehouse tables as QVDs?

[stvegerton]

Hello,

New to Qlikview and we're thinking about expanding. I've built a sweet application pulling from our data warehouse. The application is based off a QVD which pulls from a SQL view, joining 20 dimensions and one fact. It has 10m rows and is wicked fast.

I imagine that this is not the most scalable model, but our data warehouse has 400+ tables. Am I supposed to create a QVD for each of those tables as a best practice? Or can I instead continue having a big QVD per data mart? Seems a shame to replicate all those tables

Thanks,

Steve

[jagan]

Hi Stephen,

If you have multiple Dashboards and if you are using specific views for the Dashboard, then the same data from specific tables is pulled many times, so there will be additional load on the Database.  To overcome this try like this

Layer 1 OR Tier 1:

Load only required tables not all tables (May be some tables are not required for Dashboard) from databases and save it as QVDs like below.

QVD Generators.qvw : Pulls the data from database and save it as QVDs.

Table1:

LOAD

*

FROM Table1;

STORE  Table1 FROM Table1.qvd;

DROP Table Table1; (Since we are not using in this Qlikview, we are doing this to reduce the file size and to free RAM.)

Table2:

LOAD

*

FROM Table2;

STORE  Table2 FROM Table2.qvd;

DROP Table Table2;

'

'

'

'

TableN:

LOAD

*

FROM TableN;

STORE  TableN FROM TableN.qvd;

DROP Table TableN;

Layer 2 OR Tier 2:

Dashboard.qvw

Now in this Qlikview file load all the required tables and create the Datamodel.  (Note: Qlikview automatically creates the relation between the tables by using common column names in both the tables so adjust the column names as per your requirement.

Table1:

LOAD

FactID,

Dim1ID,

'

'

FROM Table1.QVD (qvd);

Table2:

LOAD

PrimaryKey AS Dim1ID, (Alias the column names to establish the relation between the two tables)

'

'

FROM Table2.QVD (qvd);

'

'

'

'

Hope this helps you.

Regards,

Jagan.

[stvegerton]

Thank you Jagan for you detailed explanation. Could you also comment on QDF framework? If we only have one DW that pulls from one ERP, do we really need it?

[jagan]

Hi Stephen,

I am not sure about QDF, but many organizations will follow the above process.

Regards,

Jagan.