Long and Thin v's Wide and Short?

View previous topic View next topic Go down

Long and Thin v's Wide and Short?

Post  AndyPainter on Tue Jul 27, 2010 12:46 pm

I've potentially got a 1000+ facts to store in a fact table, at a particular grain, and i'm sure that trying to create a column for each one in a fact table is not going to work, (I'd have a wide but short fact table), if i was able to do this it would make reporting easier, but i could end up creating an inflexible and costly storage structure, even if the DB systems allows it.

what i'm thinking is to create a very long but thin fact table, such that i only store the fact value and a link to a dimension that desrcibes the fact. It would look like this:

Indicator Skey Value
------------- -----
1 100
2 2000
3 30
4 455

rather than

Indicator 1 Indicator 2 Indicator 3 Indicator 4
---------- ---------- ----------- ----------
100 2000 30 455

Does anybody have any advice/comments as i'm sure this has been done before. The overall purpose of the dimensional model is to allow benchmarking to take place, it's just that there are lots of measures for each benchmark.

TIA

AndyPainter

Posts : 7
Join date : 2009-10-19
Location : Cambridge, UK

View user profile http://enterpriseinformationmanagement.wordpress.com/

Back to top Go down

Re: Long and Thin v's Wide and Short?

Post  ngalemmo on Tue Jul 27, 2010 1:12 pm

The purpose of a dimension is to provide context to a measure... so without knowing the particulars of your application, I would tend to prefer using an indicator dimension and a thin fact. After all, would it make sense to design a General Ledger fact table that has one column for each account?
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Long and Thin v's Wide and Short?

Post  AndyPainter on Wed Jul 28, 2010 6:23 am

Thanks for the response, i would agree with this approach. Can you foresee any potential issues with this approach of a thin fact table?

AndyPainter

Posts : 7
Join date : 2009-10-19
Location : Cambridge, UK

View user profile http://enterpriseinformationmanagement.wordpress.com/

Back to top Go down

Re: Long and Thin v's Wide and Short?

Post  ngalemmo on Wed Jul 28, 2010 11:41 am

Queries may be a bit more complex... following the analogy of a General Ledger, reporting off a GL structure can be cumbersome, but it is certainly doable. But these shortcomings pale in comparison with trying to deal with a fact table with 1000's of columns.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Long and Thin v's Wide and Short?

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