In the overview article we already mentioned that there is a difference from the account model used
in BPC standard model and the key figure model usually used in BW. We elaborated on the differences like replacing the account dimension with its normal and calculated members with base, restricted and calculated key figures in BW in the more detailed article e.g. in chapter 3.2.
Let us start with an concrete example to illustrate this topic. In financial for the profit and loss planning or reporting you look at different accounts. The accounts adding to the profit are of type income while the once adding to the loss are of type expense. Both usually should be visualized as postive numbers(value increase of relative accounts), but summing income and expenses they do not add but compensate each other of course. The accounts are structured by an hierarchy. Additional to the accounts one wants to see certain key performance indicator (KPI) like profit or margin which are calculated often by only taking a subset of accounts into account.
|Account type||Debit(+ sign in db)||Credit(- sign in db)|
↑ – value increase
↓ – value decrease
In BPC standard the account dimension therefore is a special dimension. As for any arbitrary dimension you can create a hierarchy for the account dimension. The dimension has an account type as attribute which specifies if it is an income or an expense account(an asset account or a liability&Equity account for balance sheet planinng). This attribute controls the sign handling on the user interface. The increase of profit accounts(income accounts) itself is stored with minus sign on the database. The KPIs are modelled as calculated member of this account dimension.
In BW/BPC embedded you can also have the account as a dimension or in BW words characteristic but it is not a special dimension. But as for any dimesion you can use a hierarchy for the account characteristic and use sign flipping by the reverse sign funciton for Hierarchy Nodes to distinguish profit and loss accounts.
To archieve this you have to have two restricted key figures dependent on the sign of the hierarchy. Like in BPC standard you can use an attribute for the account type
Both key restricted key figures are added and multiplied by an internal variable ‘ReverseHierarchySign’ coming from the hierarchy.
The 2 restricted key figures are necessary since we do planning and we also have to write back the value. For this we create two inverse formulas
They both use the Hierarchy sign to calculate back again using the ‘ReverseHierarchySign’ variable and multplying it by the difference of the sum and the other restricted key figure.
We find for example
Now we put a drill down into the characteristic from the key figure and we are done with our first query calling it ‘Normal Account Hierarchy Query’
So far we archieved a way to show the normal account hierarchy. I want to name this query ‘Normal Account Hierarchy Query’.
But what how to add the KPI which are calculated members in BPC standard?
The usually way to model KPIs in BW is to use calculated key figures. For each accounts contributing to the KPIs one can first create restricted key figures on those accounts. This restrictions can also have filters on hierarchy nodes or ranges of accounts. One can now build up a query we call here ‘KPI Query’. But how to include the normal account hierarchy we modeled above in the ‘Normal Account Hierarchy Query’? The traditional answer was to create for each account a restricted key figure which is restricted to exactly to this account. Then create a Display Hierarchy for the key figure structure. In this way you can combine the normal account hierarchy with the KPIs.
We see a solution is possible but a drawback of this is that it is very cumbersome to maintain. Also every master data change of the hierarchy requires a change in the IT controlled query.
With upcoming AO version we will have the new feature of axis sharing. With this is is now easily possible to combine our first query ‘Normal Account Hierarchy Query’ and the KPI section of our ‘KPI Query’
A small video illustrates how this can work. It has some limiations with respect of calculations or exceptions needed informations from both sides of the query. Therefor we plan a more advanced feature to drill down below one restricted key figure into the hierarchy of the filtered characteristic.