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

[#2]

That's all kind of irrelevant when it comes to QlikView. QlikView uses an associative database. When you load in your data from whatever model you are currently using, QlikView turns it into an associative database. That is what gives QlikView its blazing speed.

[#3]

NMiller is right, but The standard layout and structure of data presentation is the Star Schema. QlikView is

generally most efficient when working in this space.

Within a Star schema model, the event data (transactions) reside in a central "Fact

Table" and the attributes of the event reside in separate "dimension tables".

This model works well in a simplistic, single event scenario. But as QlikView can handle

multiple data sources from many different source systems and files, we have to work

with multiple event scenarios, or many fact tables.

[#4]

Re-iterating what has already been said, QlikView will work with various data models. For performance reasons, though, you will want to minimize the number of "hops" QlikView has to make to get the data it needs. Join tables where you can. Especially with large datasets, the least number of keys QlikView has to analyze the better.

sjprows

[#5]

Thanks guys for your suggestions..

To my wonder when I tried to do a star schema, i.e. A fact table and a lot of dimension tables..it takes a lot of time. Does this join decreases the performance

DIMENSIONTABLE:

LOAD autonumberhash128(DL_ID & FIRMA_ID) as DL_ID_FIRMA_KEY,
 DL_ID,
 FIRMA_ID,
 FIRMA_FB_KUERZEL as FIRMA_FB_KUERZEL_ARBEITGEBER,
 LEITER_NAME as LEITERNAME_AUFTRAGGEBER;
SQL SELECT *
FROM DVG.DL_DIMENSIONTABLE;


FACT_TABLE:
noconcatenate
Load distinct DL_ID_FIRMA_KEY,
 DL_ID,
 FIRMA_ID
 resident DL_AUFTRAGGEBER;

 Drop Fields DL_ID, FIRMA_ID FROM DIMENSIONTABLE;

I get a failure message like this

" Out of virtual memory allocated 1MB" and it gets stuck.. when I use normal table connections with out building any star schema.. it takes only 45 seconds.. They are only few thousands of records.. it must take same time or less time when I am using Starschema but it is not like that.

why??

[#6]

Your fact table is made based on DIMENSIONTABLE. This is ok.

But you need to add in this table the key and dimension fields of your table DL_AUFTRAGGEBER.

If you don't the links will not be correct between FACT_TABLE, DOMENSIONTABLE and DL_AUFTRAGGEBER.

I think that could be the problem.

[#7]

Hi spastor,

I pasted the code wrong.. Please verify whether it is correct now..

 <pre>
//DIMENSIONTABLE
DL_AUFTRAGGEBER:

LOAD autonumberhash128(DL_ID & FIRMA_ID) as DL_ID_FIRMA_KEY,
DL_ID,
FIRMA_ID,
FIRMA_FB_KUERZEL as FIRMA_FB_KUERZEL_ARBEITGEBER,
LEITER_NAME as LEITERNAME_AUFTRAGGEBER;
SQL SELECT *
FROM DVG.DL_AUFTRAGGEBER:

//Fact table
FACT_TABLE:
noconcatenate
Load distinct DL_ID_FIRMA_KEY,
 DL_ID,
 FIRMA_ID<pre> resident DL_AUFTRAGGEBER;

Drop Fields DL_ID, FIRMA_ID FROM DL_AUFTRAGGEBER;

[#8]

Seems ok, but I would replace the function autonumberhash128(DL_ID & FIRMA_ID) as DL_ID_FIRMA_KEY

by autonumberhash128(DL_ID, FIRMA_ID) as DL_ID_FIRMA_KEY

Seems to be good for me. Just run it, and check the datamodel in Qlikview then, you can send it to me if it seems bad.

[#9]

Hello spastor,

Thanks for ur help..

I tried the way you told but it does not work..See the attached qvw..

Thanks

Sravan

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.00.49.52/Test123.qvw]

[#10]

I am getting out of memory message and allocate 10MB etc..

first it loads well but when the joins start it gets worst and the PC hangs.. I have2 GB of RAM.. That must not be a Problem I guess. I have32 bit..

Hope u can help me..
thanks