Input and Output calculation contexts – Part 1

I think that calculation contexts are probably one of the most useful and the least understood features in Webi. There are many very helpful posts on the internet explaining the concept but I will strive to provide one more explanation.

In short a calculation context is a set of dimensions with which a measure is placed in a block. By adding or removing dimensions from a block the measure values will be recalculating to adjust to a new context. I think this is pretty simple but then come 2 magic words input and output calculation contexts and this is where it’s getting complicated. Here is the syntax of input and output calculation contexts:

Aggregation( Measure input_ca|cu|ation_context ) output_calculation_context

Think of them as two stages of aggregation.

In the first stage it’s up to you what dimensions you specify however the aggregation function is coming from a projection or sql aggregations from a universe.

pic9.1

In the second stage the results of the first aggregation go through the second aggregation using our chosen function and our set of dimensions.

Let‘s take a look at the following eFashion data example.

=Max([Sales revenue] in ([Year];[Quarter];[Lines])) ln ([Year];[Quarter])

The input cal. context contains 3 dimensions Year, Quarter and Lines. The measure Sales revenue is recalculated with a projection aggregation, in this case SUM, unless we don’t have any other dimensions in the query and this is the lowest aggregation level of the query then the measure is aggregated with the function from the SQL.

The output cal. context contains 2 dimensions Year, Quarter. BO takes all values from the first stage results and recalculate them again using a function provided in the formula, in our case MAX, in the output context which in our case is Year, Quarter.

Stage 1:

Input cal. context = [Sales revenue] In ([Year];[Quarter];[Lines])

pic9.2.1

Stage 2:

Output cal. context  = Max( Input cal. context) In ([Year];[Quarter])

pic9.2.2

In the above example the output cal. context is the same as the set of dimensions in the table body where we placed our calculation. Whenever they match we don’t need to define an output cal. context in the formula because it will pick it up from a table body context.

Now let‘s add some twist to this example and change the output calculation context in our table.

= Max([Sales revenue] In ([Year];[Quarter];[Lines])) In ([Year])

We have the same  input cal. context as above but now the results of it go through the different output cal. context where we are left only with a Year dimension.

pic9.3

Since we have different output cal. context dimensions than in the table body now BO has to do something with the results to fit them in our table. The lowest level of aggregation in the output cal. context is at the Year level and in the table is at the Year, Quarter level. In this case what BO does? It duplicates Sales revenue Year level values for each Year, Quarter record.

pic9.4

What if we reverse the situation where the output cal. context aggregation level is at more granular level than in the table? Let’s stick with our previous formula and insert it in the table body:

= Max([Sales revenue] In ([Year];[Quarter];[Lines])) In ([Year];[Quarter])

We have the same input and output cal. contexts but the table has now only the Year dimension. If we tell BO to insert that output cal. context into a less granular context it will not know how to aggregate records further and will display #MULTIVALUE error.

pic9.5

To fix the error we can apply additional aggregation on top of the entire formula:

=SUM( Max([Sales revenue] In ([Year];[Quarter];[Lines])) In ([Year];[Quarter]) )

pic9.6

Advertisements

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