I'm working at an insurance company which has several different dimensionally modelled warehouses using differing techniques.
One of these is using a method for measures in the underlying table structure which I've not come across before and I'm interested in getting feedback on whether it is a good design methodology.
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
My question is does this provide any performance issues if reports are using SQL queries rather than querying cubes built on top of the database?
I can see the advantages in this method such as making it easily extendible with new measures, but as the bulk of the SSRS reports use SQL and not MDX I'm worried by them aggregating measures across such a table could cause problems.
Any advice will be appreciated.