3 Replies Latest reply: Oct 22, 2014 10:28 PM by jagan mohan rao appala RSS

    Should I replicate my data warehouse tables as QVDs?

    Steve Egerton


      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

        • Re: Should I replicate my data warehouse tables as QVDs?
          jagan mohan rao appala

          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.