Oracle Essbase Starter Series (iv)

Member Store Type

In addition to outline calculations, another important attribute of dimension members is the storage type, which determines how the dimension member-related cells are physically stored. The "Data Storage" property of the member is edited in the Gallery, with 5 options available in the drop-down menu, plus the shared member, with a total of 6 storage types.

Store : stores data. When the storage type for all members of a cell is store, the cell data is physically stored. The previous description of the input cells and calculated cells seems to have the impression that the cells that are not level 0 and that have a member formula are not input and must be calculated by a descendant member merge or a formula. In fact, deciding whether a cell can be entered is determined by the storage type. Store-type cells can enter data, except that the input data is overwritten by the computed results after the calculation is performed.

Dynamiccalc : dynamic calculation. The relevant cells do not store data and are calculated only at query time. Dynamically calculated members must define calculation methods, non-level 0 members can be merged through descendant members, and level 0 members must have member formulas. For the purpose of dynamic computing, one is to reduce storage space, and the other is to prevent data from being entered on cells. However, if a query contains dynamic calculated members, the query response time is extended.

Dynamiccalcandstore : dynamic querying and storage. When the first query or data is identified as dirty, the calculation results are saved and the stored data can be used in subsequent queries. Balances query performance and storage space compared to Dynamiccalc,dynamiccalcandstore.

labelonly : label only. Neither compute nor store the data. Only labels that are grouped or graded in the hierarchy exist. When querying LABELONLY members, the data for the first child member is usually displayed.

Shared : shared members. A shared member is a reference to a member of the same name in the same dimension, whose data is derived from the referenced member. If you use the same member in multiple places in a dimension, you can use shared members. For example, revenue and expense in account dimensions are referenced in the member formula for net income, and shared members can be used to achieve the same calculation. Design outline, shared members under net income refer to expenditure and income in the same dimension, and in accordance with the combined calculation, the calculation method of the member formula can be substituted. To join a shared member in the Gallery, right-click a member and select Insert Shared member > as descendant or as sibling in the right-click menu.

Nevershare : data is not shared. In addition to defining shared members that are displayed using the shared type, some members in Essbase are implicitly shared. Referring to the dimension structure of Figure 3-48, the home member value is actually equal to the Zhang Daming member value. Essbase can identify this relationship and automatically use Zhang Daming as a shared member of the home, which is called implicit sharing. To avoid implicit sharing, you can set the storage type of home to Nevershare.

Example : Zhang Daming needs to make a family budget of 2013 years, in the year dimension, only need to do monthly income and expenditure plan, do not need to be refined to every day; on the home dimension, the family's budget is allocated to individuals to retain a portion of the mobile funds. There is also a need to regularly check the variance between the projected revenue and expenditure targets and actual income and expenditure, which should be less than the target expenditure and the actual revenue should be greater than the target income. The table is the budget and actual income and expenditure data for January.

This example requires the database to enter data on a non-level 0 member of year, Home, and account dimensions, so first ensure that the storage type of the related dimension member in the table must be store.

Because of mobile funds, home members spend more than the sum of the child's spending. Also, in the year dimension, the data is not affected by the monthly members even if the data is entered incorrectly in the Japanese member. Therefore, the input data of a non-level 0 member cannot be overwritten by the result of the merge calculation of the descendant member when calculating. To do this, the combined calculation of the budget under the Scenario dimension is subscript character "^", and all budget-related cells are not calculated using the merge calculation.

But at the same time, you want to summarize the monthly budget to the quarter, and then the whole year. Edit the default calculation script for the database, as follows:

Then look at the definition of the difference in the table, the difference is the difference between the actual and the budget, but the expenditure and income have different direction of calculation. For expenditure, the actual budget is less than the best, the difference is equal to the budget minus the actual, for the income, the actual greater than the budget is superior, the difference is equal to the actual budget reduction. This calculation can be calculated using the expense report calculation (Expense Reporting) function, which is another unique computed attribute of the account type dimension in addition to the time balance. You can control the direction of calculation in the @var and @varper functions by labeling the members of the account dimension as either cost or non-fee types.

The Expense Reporting property of the expense and its child members is set to Expense in Vecuronium, and the revenue and its child members are the default nonexpense. The storage type that modifies the difference is set to Dynamiccalc, and the member formula is set to:

At this point, the Homefinancial database has basically met the requirements presented in the example. By summarizing the changes in these two sections, the final structure of the database model is as follows:

Dimension structure:

Member Formula:

Default calculation script:

In the dimension library, check that the Homefinancial application is modified as above, if no problem is deployed to Essbase Server. EPMA is currently unable to edit the Essbase calculation script, so the default calculation script is modified after deployment.

Oracle Essbase Starter Series (iv)