Should I replicate my data warehouse tables as QVDs?
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
Re: Should I replicate my data warehouse tables as QVDs?
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.
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.)
STORE Table2 FROM Table2.qvd;
DROP Table Table2;
STORE TableN FROM TableN.qvd;
DROP Table TableN;
Layer 2 OR Tier 2:
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.
FROM Table1.QVD (qvd);
PrimaryKey AS Dim1ID, (Alias the column names to establish the relation between the two tables)