Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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?
Hi Stephen,
I am not sure about QDF, but many organizations will follow the above process.
Regards,
Jagan.