# 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.