Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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

17 Replies
Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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. Smile

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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.





Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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??

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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.

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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;





Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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.

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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]

Not applicable

Which is the best Datamodel in accoding to performance in Qlikview

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