topic How big a Fact table can be? in QlikView

How big a Fact table can be?

shyamcharan — Sat, 02 May 2015 23:57:52 GMT

Hi Experts,

I have a Data Model created for our client which is quiet big and contains at least 70 tables. Due to recent performance issues I had to look at making QlikView app run faster.

Now, I am trying to reduce the number of tables by 'Left joining' where required with the used columns.

For example, the fact is connected to 3 dimensions and one of these dimensions in turn is connected to 3 other dimensions.

All these tables are connected to a bridge table. Now, I am thinking can do a left join(fact tabe) and reduce the number of tables

Also, how big a fact table can be?

Sorry if my questions are too basic, I am just started trying to understand Data Modelling.

Any help is highly appreciated. Thanks in advance.

Regards,

Shyam.

Re: How big a Fact table can be?

ziadm — Sun, 03 May 2015 00:53:35 GMT

Hi shyam

I have an experience with a fact table of 2 millions records.

Number of records in your tables does not mater as long as its optimized in terms reduced no of fields and having date time field in two fields. Try make your link keys as numbers and avoid text keys.

Also if you could implement incremental loads into QVD files this would be 10 times faster

Thanks

Ziad

Re: How big a Fact table can be?

MK_QSL — Sun, 03 May 2015 09:04:19 GMT

There is no limit on number of tables or number of records for any tables.

I have worked 250 millions records in past. What you need is good hardware support for your application.

Re: How big a Fact table can be?

Anonymous — Sun, 03 May 2015 15:36:24 GMT

Shyam,

If your concern is performance, reducing the number of table is helpful. Try to implement the classic star data model - one fact table, and dimension tables without "snow-flaking". Try to avoid link (bridge) table. It is helpful in some cases, but it hurts the performance.

Regards,

Michael

Re: How big a Fact table can be?

shyamcharan — Sun, 03 May 2015 22:15:12 GMT

Thank you all for your responses.

We have a Data Model with 8 FACT tables and these in turn are connected to many other dimensions.

I am following your suggestions and could reduce the tables by including most of the columns into the Fact tables and dropping the dimensions. Could reduce the tables by at least 30. Thanks heaps for your suggestions.

As obvious the performance issues come out only at the end and I am now concerned to do any last minute changes.

I am able to reduce the number of dimension tables.

Thanks heaps,

Shyam.