Disadvantage of Fact table with 44 columns

[wizard]

Hi all,

First of all this question my be super easy for you guys but I am new with data warehousing.
Our fact table, let name it SALES_FACT has 296 million rows and 44 columns. Out of 44 column 36 columns are foreign key values joined to diffrent dimensions. 4 colums are neither facts nor FK's joined to dimension but only for dispaly purpose. 3 columns are facts and a primary key column.

What could be the disadvante of havving 44 columns? Is there anythingelse we coould do for performance reasons.

[ngalemmo]

There is nothing inherently wrong with a 44 column fact table (although I would question what the 4 'display purposes' columns are all about). The downside of a wide fact table is they do not perform as well and a thinner one because it is bigger. But the differences are not that significant as the FK columns are relatively small. Where you run into problems is when you place text fields in fact tables that are better kept in a dimension (degenerate dimensions excluded).

[Jeff Smith]

Imagine cutting the number of columns down to 15. The overall size of the table would dramatically shrink plus you would need fewer indexes, further shrinking the size of the database. And the fewer indexes would perform better.

[wizard]

There is nothing inherently wrong with a 44 column fact table (although I would question what the 4 'display purposes' columns are all about). The downside of a wide fact table is they do not perform as well and a thinner one because it is bigger. But the differences are not that significant as the FK columns are relatively small. Where you run into problems is when you place text fields in fact tables that are better kept in a dimension (degenerate dimensions excluded).

The 4 display purpose column in fact table are 1). correspondence number(Taken directly from source table and does not have a dimension of its own). 2). Two Date/time columns that will never be reported off. Only for display purpose. PK column.

[ngalemmo]

Those are fine... but as Jeff suggested, less is more. If possible, try to consolidate some of the dimensions to reduce the number of FKs. If there are a bunch of small dimensions that have little use outside the particular subject area, you may be better off consolidating them into a few junk dimensions.

[wizard]

There is nothing inherently wrong with a 44 column fact table (although I would question what the 4 'display purposes' columns are all about). The downside of a wide fact table is they do not perform as well and a thinner one because it is bigger. But the differences are not that significant as the FK columns are relatively small. Where you run into problems is when you place text fields in fact tables that are better kept in a dimension (degenerate dimensions excluded).

Could you please explain more when you say "run into problems when you place text fields in fact table". What kind of problems?

Thanks.

[hang]

There is another post relevant to the topic, http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/star-schema-vs-all-in-one-table-t812.htm#3481

[Sponsored content]