topic Re: Data layer design in QlikView

Data layer design

Wed, 11 Apr 2012 09:31:24 GMT

Hi,

I have a data model with 2 fact tables and 10 dimnsion tables. Primary fact table is connected to all 10 dimension tables through foreign keys and secondary fact table is connected to 6 dimesion tables (out of 10) through foreign keys only. When I make such joins in Qlikview , it is making a synthetic key and table of common 6 foreign keys, resulting into very bad performance of the file.

Please guide regarding best approach of design of this data layer.

Thanks.

Data layer design

jvitantonio — Wed, 11 Apr 2012 09:35:21 GMT

You can duplicate the dimension tables. So DimensionA1 will join to one fact table and DimensionA2 (which is the same dimension) join it to the other fact table

Re: Data layer design

magavi_framsteg — Wed, 11 Apr 2012 13:44:13 GMT

Or you can, if possible, combine (concatenate) the two fact tables into one fact table and join all 10 dimension tables to the one fact table.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Data layer design

Wed, 11 Apr 2012 13:50:55 GMT

Will duplicating dimesion tables not affect perforamnce of report?

I have tried concatenation but that is not giving results in further calculation on fields from both fact tables.

Does any body has any idea about link table?

Re: Data layer design

magavi_framsteg — Wed, 11 Apr 2012 14:00:10 GMT

Hi.

Duplication of dimension tables will not affect performance - only file size and memory usage.

Link tables is one way of handling two or more fact tables, but if you can, try and combine (concatenate) fact tables into ONE fact table.

You can use a field ROWTYPE for the different types of fact rows.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Data layer design

Wed, 11 Apr 2012 14:49:58 GMT

Thanks for your help!

I need to calculate division of one field of primary fact table with another field of secondary fact table at UI level in pivot table. Will concatenation of these 2 fact tables give me correct results?

Re: Data layer design

magavi_framsteg — Thu, 12 Apr 2012 08:13:15 GMT

I reckon it will give the same correct result.

You could always do a quick test with inline tables.

Test 1 with two fact tables.

Test 2 one fact table consisting of the two concatenated fact tables.

Hope this helps.

Kind regards

Magnus Åvitsland

BI Consultant

Framsteg Business Intelligence Corp.

Data layer design

fosuzuki — Fri, 13 Apr 2012 03:29:46 GMT

As Magnus mentioned, I'd try to:

1-concatenate both fact tables into one. As a general rule (sometimes it may not be true), the star schema will have better overall preformance

2-create a link table to join the dimensions to the 2 fact tables

I'm not really an expert (yet... ) but the duplicated dimension solution seems kinda strange to me...