A “vertical fact table*” is a fact table with only 1 measure. There is a dim key column in this fact table which determines what kind of measure that row is. On row 1, that measure could mean “sales”, but on row 2, that measure could mean “discount”. In most cases the measure is financial amount. But in some cases the measure can be quantity.
*I got this name from SSAS forum. Some people call it “Normalised Fact Table”. I believe it is called Vertical because it is very long. If you have 40 measures, after you ‘normalise’ the fact table it will be 40x longer.
A vertical fact table is normally used for accounting/finance cube. It’s unusual to ‘dimensionalize’ the measures into 1 column, especially if they are not additive to each other.
Having said that, if you decided to do it, create a Dim_Measure table (in accounting/finance we have account dimension), containing these 5 columns:
- Measure_Key (PK)
On the DSV, on this Dim_Measure named query, create a relationship from Parent_Measure to Measure_Key (Measure_Key as PK).
In BIDS, create a dimension with 2 attributes: Measure and Parent. On the Measure attribute, set the KeyColumn property to Measure_Key column, NameColumn property to Measure_Name column, Usage = Key. On the Parent attribute, set usage = parent, UnaryOperatorColumn = Unary_Operator column, CustomRollupColumn = Custom_Rollup column.
A vertical fact table contains a few dimensional key column as usual (one of them is Measure_Key), plus 1 column called Amount. It is the value of Measure_Key column that determines what measure it is.
Populate the Dim_Measure table:
- Measure_Key: surrogate (1,2,3…)
- Measure_Name: as per your project
- Parent_Measure: set to 1 (all rows)
- Unary_Operator: set to ~ (all rows)
- Custom_Rollup: ([Dim_Measure].[Measure].&, [Measures].[Amount]) + ([Dim_Measure].[Measure].&, [Measures].[Amount])
 and  are examples only, replace them with the corresponding row.
+ is an example only, replace it with the formula in your project.
There is a property called CustomRollupPropertiesColumn that we can use to set the decimal format etc.