Calculation Engines

Introduction to Calculation Engines

In this section we will discuss Calculations Engine or Engines in PowerOLAP®, the performance of each engine and what can be done to optimize calculation by switching from one engine to another.  It is important to remember that performance in PowerOLAP® is based on the aggregation or summation of Aggregates from Detail members. Bear in mind that with any OLAP model the Aggregates are always calculated by summing their Detail members. The discussion will be about the three standard calculation modes that a PowerOLAP cube can be perform: Pure Aggregation Cube, Formula Engine, and Dependency Engine.

Pure Aggregation Cube

A Pure Aggregation Cube is a Cube that has no formulas and no dependencies. Pure Aggregation Cube is by far the best performing aggregation engine. This is because it aggregates using only the Detail elements that reside in the database. So in a Pure Aggregation Model, we can aggregate and calculate High level aggregates very quickly by simply looking at the database and knowing what values actually exists.

When building your model, using a Pure Aggregation Cube is the fastest of all models.

In the example below, the values 100 for Canada and 200 for USA sum up to the value 300 for the parent member North America.

Scrolling further in the Slice we can see that there are no values for the Members Argentina and Columbia.

Since there is no value for Argentina and Columbia, there is nothing saved in the database and nothing adding up to the Aggregate for South America. In other words, this zero value merely represents a null or missing value. Only actual typed-in numerical data elements (that are non-zero) are actually saved inside the database. This is unlike a transactional system where you can have a transactional record that has a zero value. In this Cube, every time the user changes or enters a value of zero or doesn't have a value to certain intersections, this zero actually means an empty value and therefore does not have to be calculated. This is because, when we store data in the in-memory database, we make sure to optimize it, so that the points that actually exist are easily calculated and summed into the total.

In computing for the value 300 in North America (as first pictured), it needs that aggregation engine to say that there are values for Canada and USA. Since there are no there are no other values for the other Detail members, then the Aggregate value for the Member World would total 300 also (The Detail members with value in the example being Canada and USA only). It does not need to sum up or 'aggregate' Argentina and Columbia because given the internal storage mechanism, there are only values for Canada and USA that exists. This works along all axis of the Cube, all Dimensions of the Cube along each axis.

Formula Engine

A Formula Engine is a model whose calculations are triggered by a formula entered into the Cube. This model is more complex than the Pure Aggregation Cube. (Formula Editor below)

Using the Formula Engine, there is a slower performance calculation due to the fact that the Formula language is extremely flexible. Given the formula language, you can have values at any location in the entire space of the Cube. We can't make any assumptions about what the user may do with the formula language. If there is a Formula residing in the Formula Editor that can apply to any intersection within the Cube, the PowerOLAP cannot skip or exclude calculation of any Detail members because there may be a value or a formulated value that governs a point somewhere in the Cube.

In the given example we have a Slice containing values for the Members Units and Price for both Canada and USA regions. As per the illustration, there are 10 Units for a Price of 100 per unit for the Region Canada and there are 0 units for a Price of 200 per unit for the Region USA. Our objective here is to calculate for the Sales value for the Regions Canada and USA.

To calculate for the Sales value for the Regions Canada and USA we must first write a formula that will describe how to get or to compute for the Sales value. We will apply the formula:

Sales = Unit x Price to get the Sales value.

Writing the formula in the Formula Editor:

       Go to Model Menu, then Cube. 
The Cube dialog box appears.

       Select a Cube, 
(In the example we selected Future Year Model cube) 
then click on Formulas button.           
Future Year Model' Formulas dialog box appears.

Under the Formula Editor go to the Formulas tab then write a formula on the screen below.

In the example we will compute for the value of Sales by multiplying Unit by Price.

This will be written in the Formula Editor screen in this manner:

       Click on Check Syntax button to check for syntax errors.
A message box will prompt the screen indicating if there are syntax errors or none in the formula written.

       If your formulas are free from any syntax errors, select OK button.
This will return you to the Cube dialog box.

       From the Cube dialog box, click OK.

       Return to your Slice and click F9 or Recalculate button.

The computed value will now be brought up to the intersection for Sales value.

Notice in the Slice that the intersection for Sales for Region Canada now contains a value of 1000. This was the result of the Formula written on the Cube. For USA though, no values have been brought up by the engine. This is because there was no unit indicated. Therefore multiplying Price of 200 by zero Units will give us zero Sales value. This is how the formulas engine in PowerOLAP works—it calculated whatever equation you write for it. This number will always override a pure calculation engine.

Dependencies Engine

This model is the optimization of the Formula Engine model. The Dependencies Engine allows the user, basing on a certain formula in the Cube, to write dependencies to tell the engine what values it needs to calculate. For full description of the Dependency feature refer to the Dependencies part of the Formulas section, as this is simply a description of the engine.

It is important to know that dependencies values must be written correctly and it must be valid before it could work. However, the improvements that may be seen could easily be several orders of magnitude based on the complexity of your model and how many detail points one is trying to calculate and roll into aggregate. It is imperative to know that dependencies can only be written on Detail level formulas. If a formula can be written and performed at the Aggregate level or the 'ALL' level, then there is no need to write a dependency formula for that particular Cube.

In this example we have a formula in the Cube that computes for Sales value by multiplying Units by Price. We are going to tell the engine to consider only Detail members that has a value for Members Units and Price.

       Go to the Model menu then select Cube.           
The Cube dialog box appears.

       Select a Cube, 
in the example we will select the Future Year Model cube,      
and then click on Formulas button.

This will bring up the Future Year Model' Formulas dialog box.

Upon viewing the Formulas tab it contains a formula that says Sales= Units x Price.

       Click on the Use Dependencies check box.

       Select the Dependencies tab and write a dependency formula:

The formula written above tells the engine to consider only calculating this formula when there is a value for Units.

       Check for Syntax errors by clicking on the Check Syntax button. 
Click OK button to return to the Cubes dialog box.       
In the Cubes dialog box select OK.

       Referring back to the Slice click F9 or Recalculate button to refresh or update the Slice.

Caching Engine

In PowerOLAP there is a caching feature embedded in each Cube. When users calculate values, the values are rolled up into aggregates and based on the costing algorithms those values are cached. By default, this feature is embedded in each Cube.

There are certain instances when people make request in a Cube where Caching would be a very expensive operation. Such calculation usually involves requesting or accessing large number of data points from a Cube. An example would be creating a constrained Slice and nesting multiple Dimensions among rows and columns and then constraining that Slice.

Literally, what it does is it is turning the multidimensional space into a transactional report writer. In that instance you may be asking for thousands or even millions of data points and asking the engine to remove any of those values that are zero and return only the non zero values.

Another instance where this might be the case is with the use of API. Through the API, users can ask for large quantities of data points for purpose such as exporting them into another system.

Exporting PowerOLAP is another feature. Exporting a large section of Cube might be an instance for user to ask for numerous data points. By un-checking the Enable Caching checkbox located in the Cubes dialog box, you are telling PowerOLAP that upon receiving request for data points, not to cache values and instead simply take the large number of data points being requested, pass it directly to the calculation engine and perform a large calculation on each of those points or all of those points in a single pass. In other words, PowerOLAP will not do a point by point calculation but will do a single large calculation for all the requested data points.

Caching Engine is an advance feature and it highly advisable that it should only be used when the user understands its implications and when they have an understanding of when and where to use it.