Too many columns in fact table

View previous topic View next topic Go down

Too many columns in fact table

Post  Guest on Wed Nov 04, 2015 9:48 am

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.

Guest
Guest


Back to top Go down

Re: Too many columns in fact table

Post  ngalemmo on Wed Nov 04, 2015 2:51 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Too many columns in fact table

Post  ngalemmo on Wed Nov 04, 2015 2:52 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Too many columns in fact table

Post  zoom on Wed Nov 04, 2015 4:32 pm

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

Re: Too many columns in fact table

Post  ron.dunn on Wed Nov 04, 2015 10:27 pm

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.

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Too many columns in fact table

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