Should I make Lookup Tables into QVDs?

[Delestia]

Hi Community,

For my first app I am working with a MySQL connection containing a primary table with half a million records stored into a QVD. The database has lookup tables of various sizes for that primary table. For example a 2 record lookup table that changes a 0,1 in the primary table to a Yes or No. On the other spectrum there are tables that contain ~100 record lookups.

Question: Should all tables be created into QVDs regardless of size or should I denormalize the primary table with a join during the store into the main QVD? When is it appropriate to make a data connection table into a QVD?

[Kushal_Chawda]

As a best practice you should always create QVDs for all the table regardless of to avoid dependency on database.  Also to make it reusable in other apps. You can make de-normalized model if your Fact table is not huge and involves minimal joins. Denormalized model increases the size of app and makes script complex although it is good for front end performance. Rather you can always go with start schema model which is recommended. 

[Vegar]

For simple lookup tables with two values I would suggest you to add the values direct to your transaction table, but to exclude the ID. 

I sometime do this trick in order to automaticly get text values on to my 1/0 flag transaction fields.

TMP_FlagTable:
LOAD Dual(FlagName, FlagID) as Flag
FROM FlagLookupTable;

Transactions:
LOAD 
  Dim1,
  Dim2,
  FlagID as Flag
FROM TransactionTable;

Drop table TMP_FlagTable;

What I do is to first introduce the Flag as an Dual with both an numeric and a text value. When I later load numeric data to that field it will automatically get the dual text value given in the first load.  Finally I after loading all flag transactions I drop the temp table which I no longer need. Using this method does not need a join or other functions that break optimized load which could be good working with large data sets.

BR
Vegar

[Vegar · Accepted solution]

Regarding performance I you should try to balance between jointing information and keeping it normalized. You will often get recommendations to keep a star schema in a qlik application. The image below is scissored from one of the Qlik Deployment Framework -documentation documents talking about Data Modeling.

 

You could also look at the Qlik Tips: Denormalize for performance blog post by @stephencredmond . Its from 2011 but still valid.