Data Warehousing and Business Intelligence

28 September 2010

Ratio in SSAS

Filed under: Analysis Services — Vincent Rainardi @ 7:34 am
Tags:

A ratio is a number divided by another number, e.g. A/B. An example of a ratio in retail industry is profitability, which is profit divided by sales. Another example is capacity utilisation (in telco and manufacturing), which is capacity used divided by total capacity.

The point of this post is perhaps obvious for some people, but for others it may not: in SSAS to create a ratio we need to present both measures (A and B) to the cube, and then create either a measure expression or a calculated measure. We can not calculate the ratio in the data warehouse, and then present the calculation to the cube.

The reason for this is because ratios are not additive. If product1 profitability is 20% and product2 profitability is 10%, the profitability of both products is not 30%. It is a figure between 10% and 20% depending on the volume/sales.

Because ratios are not additive, in the fact table we need to create two measures: the nominator (the top number), and the denominator (the bottom number).

Ratio is not an issue in reports, but it is an issue in cubes. In report it is only 1 level, so if we want to (to boost performance) we can calculate and store the ratio in the fact table. But cubes have many levels. That is the essence of having a cube: so we can aggregate up and drill down. Hence the ratio needs to be correct at all levels. Hence we can’t calculate in advance and store it in the fact table.

In order for a ratio to be correct at all levels, we need to we need to store two measures in the fact table, then do the calculation in the cube. As I said this might be obvious for some people, but not for others. As always I welcome discussion and questions at vrainardi@gmail.com. Hope it’s useful, Vincent Rainardi 28/9/2010.

Advertisements

1 Comment »

  1. […] to view that ratio correctly, at any level in the hierarchy. I’ve written this in the past (link)particularly with regards to an OLAP […]

    Pingback by To Store or Not to Store « Data Warehousing and Business Intelligence — 19 February 2011 @ 10:29 am | Reply


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: