Disadvantage of Fact table with 44 columns

View previous topic View next topic Go down

Disadvantage of Fact table with 44 columns

Post  wizard on Wed Dec 01, 2010 11:11 pm

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.

wizard

Posts : 13
Join date : 2010-11-30

View user profile

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  ngalemmo on Thu Dec 02, 2010 12:17 am

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  Jeff Smith on Fri Dec 03, 2010 3:55 pm

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.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  wizard on Fri Dec 03, 2010 5:31 pm

ngalemmo wrote: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.

wizard

Posts : 13
Join date : 2010-11-30

View user profile

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  ngalemmo on Mon Dec 06, 2010 10:43 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  wizard on Mon Dec 06, 2010 2:20 pm

ngalemmo wrote: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.

wizard

Posts : 13
Join date : 2010-11-30

View user profile

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  hang on Mon Dec 06, 2010 4:27 pm


hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Disadvantage of Fact table with 44 columns

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum