Too many columns in fact table

[Guest]

How many columns are 'too many' in a fact table.
Is it advised to keep 100 columns in a fact table. What are the impacts of a fact table with too many columns.

Thanks.

[ngalemmo]

Fact tables are the largest tables in a dimensional model. The basic concern with any fact table is performance. Most of the time, due to the nature of analytic queries, DBMS's will scan the full table rather than use indexes. The wider the table the more data needs to be handled, so it takes longer to query and longer to load.

100 columns seems to be excessive. Use of degenerate dimensions should be kept to a minimum. Rather than store orphan attributes in the table, they should be grouped into junk dimensions.

[ngalemmo]

Fact tables are the largest tables in a dimensional model. The basic concern with any fact table is performance. Most of the time, due to the nature of analytic queries, DBMS's will scan the full table rather than use indexes. The wider the table the more data needs to be handled, so it takes longer to query and longer to load.

100 columns seems to be excessive. Use of degenerate dimensions should be kept to a minimum. Rather than store orphan attributes in the table, they should be grouped into junk dimensions.

[zoom]

Most RDBMS allow more than 1000 columns in a table. If performance is not the issue then you can have 100 columns in your Fact table. I had more than 600 columns in DB2, but performance started to degrade so I had to move some columns to a new fact table.

[ron.dunn]

Just out of curiousity, Zoom, what type of business fact can have around 600 dimensions or measures?

Even when I think of some of the snapshot facts I've delivered over the years, 600 is a LOT.

[Sponsored content]