Aggregation Based on Dimensions

Sometimes because of the nature of the data in our system we need to have different types of aggregations (sum, max, last…) for one single measure. Therefore the aggregation type needs to be different depending on the dimensions (Date, Product…).

One clear example of this is in dealing with stocks. Stocks have different kinds of aggregations depending on how you look at it.

The aim of this article is to know how to deal with these different aggregation level measures and implementing a solution in the OBI repository to solve the issues that those measures bring.

The Scenario

Let’s imagine this case to take place in a big manufacturing company. Their current stock level for their products is entered into their system on a weekly basis; this stock is also located in different warehouses within two different regions:
Figure 1

Figure 1 – Weekly stock by warehouse

 

In this case if we want to see the total quantity of stock for all warehouses in a region by week we only have to sum up all quantities. Therefore, we will apply a sum aggregation to our measure:

 

Figure 2

Figure 2 – Weekly stock by region

 

But what happens if we want to look at the stock by month? If we sum it up we would be adding up stocks that are the same (usually the same stock stays for more than one day in a warehouse). As we can see below, applying a sum aggregation gives us the wrong stock on a monthly basis:

 

Figure 3

Figure 3 – Monthly stock by region

 

The best approach in this case would be to take only the stock of the last week of each month as it will better reflect the monthly stock, rather than summing all the weekly stocks for each month. Therefore in this case the aggregation should be last.

The Challenge

At this point we can clearly see that we need to apply two different types of aggregations for the stock measure:

· Warehouse dimension: Sum
· Date dimension: Last

To achieve this we are going to set an aggregation based on dimensions to our measures in the OBI repository.

The Solution

In our repository we double click on the measure that we want to change (in our scenario Quantity) to open the properties window:

Figure 4

Figure 4 – Stock Business Model

Next we click on the Aggregation tab:

5

Figure 5 – Logical Column Properties Window

 

Here we need to enable the Based on dimensions option, and a new bar will appear below. Then we click new.

6

Figure 6 – Aggregation tab

 

A new screen will pop up, asking for which dimension we want to add a new aggregation. In this case we select Date.

 

7

Figure 7 – Selecting dimension for new aggregation

 

Finally we select the new aggregation for Date. In this case as we have seen we need (last). Notice that we can have as many different aggregations as we have dimensions and that the default aggregation for all the other dimensions is the one that we selected when we created the measure (sum).

8

Figure 8 – Setting the new aggregation

 

If we now go back to our stock report by month and region we will see that it has changed:

 

Figure 9

Figure 9 – Monthly stock by region

 

Below we can see the wrong result that we got before, by using only sum aggregation.

Figure 10

Figure 10 – Monthly stock by region (Figure 3)

 

At this point the report shows the last stocks for all warehouses of that region. We can see that we are summing up the warehouse’s stocks by region but getting the last one by month and not the sum of all weeks as before.

 

Conclusion

Thanks to aggregation based on dimensions we have a quick and easy way to solve the issues that measures with more than one type of aggregation bring in our reports when combining different dimensions and levels.

Axel B
axel.bernaus@clearpeaks.com