Common QVDs

[#1]

We are trying to have a set of common dimensional QVDs that any application can use rather then having each app pull product hierarchy from the database. This is causing us to have large "star" schema applications. Some of our larger Qlikview applications (5-10GB QVWs) perform very slowly and I think if I could get rid of the star table schema it would really help.

To solve this the fact table must contain all the dimensions but in order to do that they must be joined by the database. Does anyone know how to join QVDs so we can continue to use our common dimension process? My end problem is the performance issue so if I'm tackling this the wrong way please let me know. We have used Qlikview optimizer to eliminate any large columns we can.

[johnw]

Yes, you can join the QVDs together into a single table in the QVW. The "left join load" and mapping loads that we were discussing are a couple ways to handle things. Here's an example combining the two approaches as I might do it in practice, though it depends on the sizes of the various tables. Apologies if I have any bugs.

[Segment Map]:

MAPPING LOAD

[Customer Segment ID]

,[Customer Segment Description]

FROM Seg (QVD)

;

[Segment Lifestyle Map]:

MAPPING LOAD

[Customer Segment ID]

,[Customer Lifestyle ID]

FROM Seg (QVD)

;

[Lifestyle Map]:

MAPPING LOAD *

FROM Lifestyle (QVD)

;

// I probably got this part wrong:

MAP [Customer Segment ID] USING [Segment Map];

MAP [Customer Segment ID2] USING [Segment Lifestyle Map];

MAP [Customer Lifestyle ID] USING [Lifestyle Map];

[Main]:

LOAD *

,[Customer Segment ID] AS [Customer Segment ID2]

FROM LOC_CLASS (QVD)

;

LEFT JOIN LOAD *

FROM Class (QVD)

;

LEFT JOIN LOAD *

FROM Date (QVD)

;

LEFT JOIN LOAD *

FROM Loc (QVD)

;

The left join tells it to join with the main LOC_CLASS table where there's something to join with. So the result is a single table with all of the fields in it. On the down side, this will take significantly more time than loading them without joining them. On the up side, reporting using the resulting table should be quicker. If Seg and Lifestyle are small tables, you might want could probably handle them both with mapping loads to avoid one of the left joins.

As for the part where I said I probably got it wrong, you just need to apply various maps in sequence. I'm not sure if I have the right syntax, or if QlikView is smart enough to go in sequence (mapping a segment ID to a lifestyle ID, then the lifestyle ID to a lifestyle description, all in one load). If that doesn't work, I'm sure you can get more explicit with the applymap() function. It's one of those things that I'm confident can be done, but would have to experiment to get it exactly right. All of that is just to save time on the load. If the load time isn't important, you can just left join every table after the first. And as I said earlier, it all depends on the size of your tables. I'm assuming your Seg table is smallish, for instance, as I load it twice to produce two of the maps.

[rwunderlich]

John Witherspoon wrote:Apologies if I have any bugs

Beware the very subtle limitation on using QVDs as input to MAPPING loads. Optimized QVD loads, which are usually desirable, will not work as a mapping table. You will receive no error or warning. For example, this will not map correctly:

[Lifestyle Map]:
MAPPING LOAD *
FROM Lifestyle.qvd (QVD);

The solution is to add something (like WHERE 1=1) that forces an unoptimized load.

[Lifestyle Map]:
MAPPING LOAD *
FROM Lifestyle.qvd (QVD)
WHERE 1=1;

This one can drive you crazy if you don't know about it. Attached is a sample that demonstrates the problem and the solution.

-Rob

[Oleg_Troyansky]

I would be curious to know the counts of the rows for each of those tables. Also, I'm almost sure that some of the attribute fields out of "Locations" might not be needed.

From what I can see, I wouldn't say that the schema is the reason for a slow performance - probably the design of the objects on the screen. Three biggest things to watch for:

1. Overly detailed pivot tables.

2. Numerous small objects - text objects, little gauges and charts. Many small objects on the screen usually require more time and memory than a single chart.

3. Heavy calculations with IF functions or other "heavy" functions inside.

Oleg

[#5]

Nice tip Rob.

[#6]

Hi Rob,

I took note of your recommendations regarding the Optimized Load for mappings. I think they have fixed the problem with recent versions (I am using QV 10 SR2). If you re-run the script that you have posted, it will work for now.

However, there is still a problem (even worse for me; I don't know if it is a new one), but you can't apply map on a table that you have loaded from a qvd (when the loading is optimized).

Rob, I have played with your sample qvw file, and modify it to demonstrate the problem.

Anyone would have an idea?

Thanks in advance,

[rwunderlich]

Yes, I recently discovered (the hard way) that MAP USING will not work with an optimized load. The workaround is to force an unoptimized load or use ApplyMap() in the LOAD -- which forces un-optimized anyways. I'm going to submit it as a bug and see it gets any love.

-Rob

[#8]

Hi Rob,

I have created a new case through the Customer Portal. Will update this thread asap,

[#9]

Hi,

Here is the answer from QV Support:

This is according to design. Mapping features require unoptimized load to be applied, since the optimized load is put straight into memory without any processing.

In this case the easy work around is to either add the dummy WHERE 1=1 to trigger unoptimized load or add a preceeding load as then the map will be applied on the table while being reloaded. Find sample attached to demonstrate the two options to solve the issue.

A bug (40908) has reported for the need to clarify this in the manual.

Unfortunately, it does not sound like this limitation is going to be fix soon.