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