Which is the best Datamodel in accoding to performance in Qlikview

[#1]

HI guys,

There are different ways to built a Datamodel in BI like

1)Traditionalway of linking the tables

2) Snowflake schema

3) Star schema etc.

As per the QlikView, I want to know the performance and Optimisation of these models and which one is better one to be used in Qlikview..

Thanks in advance

Sravan

[blaise]

You use a lot of noconcatenate statements, instead i think you should rename the fields. F.ex. the first two statements;

//Berechtigungen

ADM_MENUEGRP_NAME:

LOAD MENUEGRP_ID as MENUEGRP_KEY,

MENUEGRP_ID,

MENUEGRP_NAME as MENUGRP_NAME;

SQL SELECT *

FROM DVG.ADM_MENUEGRP_NAME;

//rollenzuordnung zu den auswertungen

noconcatenate

ADM_MENUEGRP_TMP:

LOAD MENUEGRP_ID as MENUEGRP_KEY,

MENUEGRP_ID,

MENUEGRP_NAME as MENUGRP_ROLLE;

SQL SELECT *

FROM DVG.ADM_MENUEGRP_TMP;

If the key between thoose two fields is MENUEGRP_ID, you should only load it in the 2nd statement as MENUEGRP_KEY. The second line there you load the field with the org. name is not of any use. Can you run the script with limited load (press debug in Scrip editor and press run (only 10 rows from each statement will be loaded). If the script runs successfully, please post a screen shoot of the Table Viewer (ctrl+T), and I can have a look at your keys.

[#3]

As there is a lot of table I would do it step by step:

Load your first table ( remove the concatenate).

Then create your fact table

Remove the fields of your first table

Then load the second table

Concatenate the fact table based on the second table

Remove the fiels on your second table...

Etc ...

You should first try with two or three first tables, and check your data model and time response after loading.

[#4]

Thanks.. I am inserting the model. I did the same way as told but after few tables I am getting the same Memory problem..I did one step by one step as spastor told but after few tables and joins.. It gets too slow and Hangs up saying Memory/vitual memory allocated to is 1MB oder 10 MB..

[#5]

Your model seems ok ( there is no synthetic key).

The only thing I can see with the model is that there is a table alone (not linked).

So my question is do you have any table or chart in your document which could use the table alone ? This would create a cartesian product....

Do you have the same error message on an empty sheet ?

[#6]

I just wanted to show how my model ist built.. That empty table will be also linked..

I did not make any sheets with this model. Since I cant run completely.. I am unable to proceed with the sheets and other things..So answer to ur question.. yes I get the same error with a empty sheet..

[blaise]

Did you receive a memory problem with limited load (10 rows) ?

Given the Table View is from the full application it looks good, as told before you don't have any synthetic keys or looping tables. So the only thing I can come up with is lacking RAM.

How many rows is your biggest table, try running a limited load with 100 000 rows, if that works, try with 300 000 and so on.

I have loaded a few of your tables with Inline tables (named the fields and tables as your test123.qvw). I'll get synthetic keys.

My suggestion is that you first store the DB tables into QVD files and then drop them (after the store commando, drop the table). This way, you will only have one table at the time in memory. When you have stored all the tables as QVD files, you can start building your datamodel.

Start with the table/tables you've selected for your fact table and start loading the "dimension" tables, one by one to see if you recieve any synthetic keys or other issues on the way.

[#8]

Hi blaise.. No I did not receive any problem with 10 rows. it starts very fast and becomes slow and slow..My biggest table ist 165000 and others are 10000 etc.. actuallly it must not give a problem..Ya I came to a threshold level where the Datamodel works and does not give the error message.. But if I want more fields it is again a problem..

If Joining is a problem to Performance... Thats not a good sign...

[#9]

A star model does not always work well if you add two fact tables which do not share the same dimension levels...

For instance a fact members and a fact residents which connect to a different level of the geographic dimension, but do share the same age category and sexe dimensions....