Oracle Essbase Starter Series (iii)

Database calculation

The data for a cell in a essbase can be an external input or calculation, and the cell is thus divided into input cells and calculated cells. Calculated cells can be calculated by combining the calculation of the dimension members in the outline with the formula script definition, which is called the outline calculation definition.

Example 1 : Zhang Daming Home in Shanghai and Pudong have a set of rooms, January 5 Zhang Daming received the urban House water and electricity bills, and then pay, Zhang Daming wife Wang Tila also received and paid the Pudong House bill. The cost and quantity of water and electricity in the two places are different.

In the Homefinancial application, the corresponding cells enter data in the following table. In the table, the gray-bottomed cell is the input cell, where the data is manually entered, and the utility fee, as a parent member, should be consolidated by its descendants, so the cells in the corresponding table are calculated cells.

The calculation of a descendant member in a essbase that is merged into a parent member can be set by combining the calculation character. Edit the Homefinancial application in Vecuronium. Any non-1-generation member in the dimension structure is checked with an "consolidation" attribute, which is the merge operator. Double-click this property for editing, for example, in the drop-down list there are 7 merge operators to choose from, and the following table lists the meanings of these calculations. When you create a member, the default merge calculation is the addition "+".

In Example 1, the utility fee needs to be consolidated as follows:

Public operating expenses = electricity per unit x electricity consumption + water per unit x water usage

Is it enough to change the with great difficulty of a public utility member into a set? The member name in the diagram is followed by the merge operator.

This idea is right, but the practice does not work in Essbase. The reason is that the sibling members in Essbase are not calculated in the order of the first multiplication in mathematics, but simply in the order of member sort. The calculation of utility costs, as shown in the figure, becomes:

Public operating expenses = (electricity price × electricity consumption + water price) x water consumption

There are two ways to correct this error. One is to fuss over the dimension structure, and according to the redesign dimension structure, get the correct calculation order as follows:

The second method is to use member formulas. A member formula allows you to write a script-defined calculation that overrides a descendant member's consolidated calculation. Moreover, Essbase provides rich computational functions and perfect scripting syntax, which can be implemented in a variety of complex calculations in member formulas.

Select the public operating expenses member in the dimension structure, modify its Member Formula (BSO) property, and enter the script in the Equation Editor:

= "Water Price" * "Water consumption" + "Electricity Unit Price" * "Electricity consumption";

If you are writing complex scripts, it is recommended to use EAS Console. The formula Editor in EAS console is more powerful and can prompt for calculation functions and operators that can help validate script syntax. Essbase's computational functions and syntax can be see the Official Handbook.

With a member formula, a descendant member's merge calculation is meaningless. For the sake of rigor, the subscript character of a descendant member can be calculated as "~", that is, the child member does not participate in the consolidation calculation.

Back to Example 1, the original table, the horizontal and vertical axes are two dimensions, carefully observe the table of calculated cells, you will find there are two questions.

In the first question, the two dimensions in the table represent the two directions of calculation, which are inconsistent in both directions. Specifically, the lower-right cell represents the total public utility fee for the entire family. If the direction of the arrow ①, that is, along the account dimension calculation, according to the member formula, the result is 584.5; Conversely, by the direction of the arrow ②, that is, along the home dimension calculation, according to the combined calculation, the result is 300.5.

Obviously the second direction of calculation is correct, so how do we make essbase calculate the way we want it to be? The answer is to control the order in which Essbase are calculated. is to calculate the account dimension first, get the data for the bottom row of cells in the table, and then calculate the home dimension to get the data for the rightmost column of cells in the table. The result of the calculation will overwrite the result of the previous calculation, that is to say 300.5 as the bottom right cell of the final calculation results. The Essbase determines the order of the computations between the dimensions according to the following conditions.

• If the outline contains both the account and time two types of dimensions, and the member formula is used in the account dimension, the order of calculation is:

1. 1. Account dimension
   2. Time dimension
   3. Dense dimensions, based on the sort calculations in the outline
   4. Sparse dimensions, based on sort calculations in the outline

• In other cases, the order of calculation is:

1. 1. Dense dimensions, based on the sort calculations in the outline
   2. Sparse dimensions, based on sort calculations in the outline

Therefore, the storage type of the dimension and the ordering in the outline are two important means of controlling the order of the calculations. If the homefinancial is calculated in the order of account > Year > Home > scenario, this will ensure that the correct calculation results are generated. If you do not know the order in which the data is calculated, you can view it from the Essbase log file after the calculation command is executed.

Also, to change the order in which individual members in the dimension are evaluated. For example, the utility fee is calculated after the home dimension, which requires that the cell in the lower right corner of the table be calculated as 584.5. Then you can enable the "both Pass calculation" attribute of the utility fee. This property indicates that the member needs two calculations, and Essbase will calculate the relevant cell for the second time by using the member formula of the utility fee after calculating it in normal order.

The second question is the unit price of electricity and the price of water. According to the calculation method on the home dimension, the calculation result is meaningless on the parent member by using the addition of "+" for the combined calculation. We have set the combined calculation of the unit price and the water unit price to "~" so that it does not merge on the account dimension, so how do you make them not merged in other dimensions? In fact, what you want to illustrate here is the difference between the "~" and "^" of the combined calculation, because these two calculations are confusing and confusing. In this example, to let the unit price and the water price in the home, year, scenario do not perform the consolidation, you need to set the merge calculation character "^", such as.

After the change to "^", the Unit price and water unit price related cells will not produce any combined calculated value during the calculation. Only formula scripts or input values can be used to write data to these cells.

In addition to recording the daily flow of revenue and expenditure, we also want to add the function of calculating the current balance for homefinancial. Therefore, 4 members were added under the cash flow level of the account dimension.

• Cash at the beginning of a period: the amount of cash that begins at a given time;
• Net income: gross income minus total expenditure;
• Adjustment: When it is found that the balance is not the same as the cash balance, used for adjustment;
• Cash at the end of a period: the amount of the last cash;

Example 2 : The following table records Cash flows for Zhang Daming in the 2013 Q1 quarter.

On January 1, Zhang Daming had $500000 in cash, and he spent $10000 in January, earning $30000, with net income of $20000. At the end of January 31, he should have a cash of 520000 yuan, but because of the omission, or the transaction fee generated by the bank transactions, Zhang Daming found that the actual amount is 519980 yuan, and then recorded the adjustment of 20 yuan. So there is the following equation:

Net income = Revenue – expenditure

End of term cash = beginning cash + Net income + adjustment

To achieve net income and end-of-term cash calculations in Essbase, you can use member formulas. In the dimension library, edit the member formula properties for the net income, and enter the script:

= "income"-"expenditure";

Edit the member formula properties for the ending cash, enter the script:

= "Opening Cash" + "net income" + "adjustment";

By the end of January, Zhang Daming had cash, which was originally owned in February. Then there is the following equation:

Early cash = cash at the end of the period

Change the member formula of the opening cash to:

IF (Not @ISMBR ("January 1"))    "Beginning Cash" [email protected] ("end-of-term cash", 1); ENDIF;

This script requires us to enter cash at the beginning of the period on January 1, and the beginning of each period is equal to the cash at the end of the previous period.

Once you have defined the 3-member formula, you will then discuss the order of the 3 members. In the preceding example, we use examples to illustrate the order of evaluation between dimensions, the same type of dimension, which is determined by their sort order in the outline. The same rule applies to the order of calculation among members in the same dimension. Between members of the same dimension, the Essbase is calculated from low to advanced, from front to back. So the cash flow this part of the member's calculation order is as follows.

The following table uses arrows to indicate the calculated path of the cell.

Based on such a path, the member formula for the beginning cash is referenced to the end-of-term cash that is later than it calculates, resulting in incorrect calculations. For example, when the beginning of February cash is calculated, the end of January cash is not calculated, resulting in a null value.

When you place a reference member before the referenced member, the phenomenon of incorrect calculation order is referred to as a "predecessor reference". To work around this problem, make the end-of-term cash and the beginning cash together, and the formula script to modify the opening cash is:

IF (Not @ISMBR ("January 1"))    "Beginning Cash" [email protected] ("end-of-term cash", 1); ENDIF; " Net income "=" revenue "-" expenditure ";" Closing Cash "=" cash at the beginning of the period + "net income" + "adjustment";

Then remove the member formula for net income and end-of-term cash. According to this formula, when cash is calculated at the beginning of January, net income in January and end-of-term cash are calculated simultaneously. Then in the beginning of the February period of cash, you can get the correct calculation results.

The cash flows for February and March are then calculated. In the Q1 quarter, the beginning cash should be the beginning of January cash, the final cash should be the end of March cash, net income and adjustment is 3 months total. In the same vein, throughout 2013, beginning cash was the beginning of Q1 cash, and the end-of-term cash was Q4 at the end of the year, with net income and adjustments of 4 quarter totals.

As can be seen, beginning cash and end-of-term cash take a special combination in the year dimension-taking the first child member value and the last child member value respectively. In the actual application scenario, often encounter time-related calculations, such as the year, the chain, the cumulative and so on. Essbase provides a number of computational functions and methods for the time type dimension. In this case, in addition to using member formulas, you can use time Balance to calculate the beginning and end of a calculation. The time-balance calculation requires that the database must contain a type-a dimension and can only be used on the account type dimension. Time-balanced calculations do not affect how other dimensions are merged outside of the type. Time balance has 3 kinds of calculations, meaning the following table.

In the dimension library, set the time Balance property of the beginning cash to first, and the period Balance property of the ending cash to last.

The time balance calculation also has a property of "Skip value", which controls whether to ignore null values missing or 0 values. For example, the cash flow is from February onwards, the beginning of January cash is empty value. If the "Skip value" property of the beginning cash is none, and the null value and the 0 value are not ignored, the beginning cash of the Q1 will be null for January, and if it is missing, the January null value is ignored for the beginning cash of the Q1, and the February value is taken.

With the outline calculation, each time you enter and modify data, Essbase is not automatically calculated by combining the calculation and member formulas. The calculation command must be executed for Essbase to calculate the relevant cell. Calculation commands can be written in a calculation script, and the calculation script can accurately control the range and order of calculations, and can override outline calculations. You can create multiple calculation scripts for a database, and perform different calculation scripts to achieve different calculations. Each database has a default calculation script.

In the EAS console, right-click the y2013 database under Homefinancial, and choose Settings > Default calculation from the menu. You can see that in the default calculation script, the Calc All statement is used to calculate the entire database, and we can modify the default calculation script as needed. To create a new calculation script, select Create > Calculate script in the right-click menu of the y2013 database.

There are several ways to execute a calculation script, such as through the MAXL shell,smart view,eas console. To execute the calculation script in the EAS console, select "Perform calculation" from the right-click menu and select the script to be executed.

Oracle Essbase Starter Series (iii)