Data Warehousing and Business Intelligence

18 June 2010

Vertical Fact Table

Filed under: Analysis Services,Data Warehousing — Vincent Rainardi @ 5:17 pm
Tags: ,

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)
  • Measure_Name
  • Parent_Measure
  • Unary_Operator
  • Custom_Rollup

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].&[8], [Measures].[Amount]) + ([Dim_Measure].[Measure].&[9], [Measures].[Amount])

[8] and [9] 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.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: