If you are a QlikView developer, it’s highly likely that you use mapping load quite frequently in your script. In fact, it is always better to use mapping load in QlikView whenever possible instead of using a join.

But, if QVD is used as a source, QlikView won’t support optimized load. With that said, if you load a large QVD and use that as a source for mapping load, QVD will not load in super fast mode.

In fact, in the older versions, a developer was required to add a WHERE 1=1 clause to force un-optimized QVD load.

If you have a large dimension table stored in QVD and if you have to use it as a mapping table for a fact table with relatively low subset ratio, it will take longer to reload since mapping load will take longer since QVD load won’t be optimized. Since relatively a small % of rows will be used for mapping, I thought of a different approach to reduce reload time.

The approach is a three-step process:

1. First load just dimension value from that fact table that will be used to map the key field from the mapping table.

2. Load QVD as an optimized load using exists function since exists function with single parameter is allowed as an exception for an optimized load.

3. Now, create a mapping load using table created in step 2 as a resident load. Since the assumption is that fact table contains low subset ratio, the table in step 2 should have relatively small # of rows. Yes, this will require you to drop table that was created in step 2 but load time will be significantly less especially if you have multiple mapping loads that fit this criteria.

So, let’s walk through the script 

Step 1:

I  generated a large data set using test script. You can create a large test data set in your script with test script.

Then I created a fact and dimension table from this data.

In this example, fact table contains one million rows whereas dimension table used for the mapping load contains 100 million rows.

Step 2:

Now, load dim1 from the fact table and load dim QVD as an optimized load using the exists function to limit # of rows that are needed to map key values in the fact table.

Step 3:

Now, create a mapping load…and drop both tables.

 Summary:

By using exists function along with an optimized data load for a large dimension table, we can reduce the size of the mapping load significantly and thus improve reload time when dealing with large dimension tables.

Elsewhere

1. Warning for QVD as a mapping load via Rob Wunderlich

2. Qonnection 2015 dashboard via LearnAllBI

3. QlikView Training for developers via LearnQlickView

4. QlikView Optimized QVD Loads via QuickIntelligence