topic Re: Document size after changing model in QlikView

Document size after changing model

ramonarcusa36 — Wed, 08 Jun 2016 08:44:02 GMT

Hey guys.

There is something I don't understand. My model consists of one big fact table (around 40 million rows) and approximately 20 dimension tables (products, customers, all those things).

I have made a slight change in my model: I have left joined my fact table to some simple dimension tables. I got a recommendation telling me it's usually a better practice to include the descriptions in the fact table, so I tried. And contrary to what I expected, my document size has decreased a lot! From 3 GB to 1.6 GB.

Is this normal? What is QlikView doing here that results in reducing the file size after including text descriptions in a big fact table? Shouldn't it increase its size because long descriptions are now repeated many times?

Thanks!

Re: Document size after changing model

oknotsen — Wed, 08 Jun 2016 08:56:50 GMT

In contrary to common belief does Qlik not store the data de-normalized, so those text descriptions still get stored once.

Some space will be gained because the key field between that fact and dimension table is now removed, but this huge difference is not explained that way.

Re: Document size after changing model

avinashelite — Wed, 08 Jun 2016 09:05:11 GMT

Check your Joins once ...it might be creating lot of junk data or duplicating data because of which the data size is getting increased ....

Re: Document size after changing model

Digvijay_Singh — Wed, 08 Jun 2016 09:13:32 GMT

Its strange that size is reduced, can you check the information density of the key field for which dimensione table is joined, also normally dimension tables are pretty small but you can confirm the size of dim table being joined into fact table.

Re: Document size after changing model

Anonymous — Wed, 08 Jun 2016 09:18:35 GMT

Hi ramon,

That is because some dims duplicate some values. If you wanna add descriptions to fact tables it is better use Applymap.

But, for app performance is better an Star Model or Snowflake Model.

Kind regards.

Re: Document size after changing model

marcus_sommer — Wed, 08 Jun 2016 09:27:03 GMT

Are there further changes within the application maybe by the compression-settings? Otherwise elaborate a bit more the changes between your tables (number of records, kind of keys and so on) and also if all data are available (this meant, if the left joins were not completely successfully there could data be removed).

- Marcus

Re: Document size after changing model

oknotsen — Wed, 08 Jun 2016 11:08:13 GMT

Manuel Capella wrote:

for app performance is better an Star Model or Snowflake Model.

This depends on your data and partly your hardware.

I can't imaging a Snowflake model to ever be faster (but that might be my lack of experience with those). A star model CAN be faster than a single table model. It can also be slower.

Re: Document size after changing model

Anonymous — Wed, 08 Jun 2016 11:14:48 GMT

I have used ever snowflake and star models, these models ever worked fine than single table for me.

Re: Document size after changing model

sujeetsingh — Wed, 08 Jun 2016 11:17:09 GMT

seems your joins worked or reduced repetiton or redundancy .

Re: Document size after changing model

oknotsen — Wed, 08 Jun 2016 12:00:52 GMT

There is a difference between "fine" and "have the best performance" .

Re: Document size after changing model

ramonarcusa36 — Wed, 08 Jun 2016 14:40:49 GMT

Manuel said it's better to use ApplyMap to add descriptions to fact tables... but will I lose optimized load if I do it?

About performance, Qlik has recommended us to use as few dimension tables as possible, integrating their fields in the fact table. That's what I'm trying to do. I've always used a star model, as normalized as I can in order to have every description in its right place. You're making me think it's not always better, it depends... but on what?

If you guys suggest I shouldn't care too much about the document size as long as it's working properly, I'll follow your suggestion.

Thanks.

Re: Document size after changing model

Anonymous — Wed, 08 Jun 2016 15:22:16 GMT

Hi ramon,

Yes, you will loose optimized load from QVD. If you have incremental load, store your QVD with Applymap done.

If you have more than one dimension for one fact, this dimension will be on another table. But if you have one dimension table with code and description, is better to include in fact table.

Regards.