Long and Thin v's Wide and Short?

[AndyPainter]

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

[ngalemmo]

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?

[AndyPainter]

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

[ngalemmo]

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.

[Sponsored content]