richardkeithmoss (11/27/2012)
Hi,For reference, the system is using SQL Server 2008 R2 and the method in question basically uses a separate table to store measure names, while the fact table holds a measure field holding the ID of the measure a particular value is associated with. This makes the fact table very long and thin as opposed to wide, which I've traditionally seen in other solutions.
e.g. Fact table structure
FactID DimensionID1 DimensionID2 MeasureID Value
Measure Table
MeasureID MeasureName
It would seem that this would result in a cross-tab sort of setup where you have multiple rows for one dimension and fact combination instead of one fact table row with multiple measures. Is there only one shared fact table for everything? It honestly seems like a very specialized solution to me and not something that I would use by default. You would have a lot more reads to return data. Even if you only had one set of entries per customer per month, you would go from normally having 12 rows a year to 12*(# measures) per year. If you have a lot of measures then this would grow quite large. While narrow tables are better than wide ones, I think this sort of special design might be taking that a bit far. It looks like they decided to build the fact table around specific reports instead of making it flexible enough to use multiple ways.
To sum things up, I would only consider a table design like this as a special reporting layer. I would still have my regular fact and dimension structures and then this separate structure. I would only use this structure if a report could not be implemented using the normal tables.