Need more than one Account-Type dimension in a SAP BPC model – it is possible!

It’s been a while since I’ve written a purely technical article, so I thought I’d share a very handy solution to a sometimes common problem in SAP BPC – how to get around the standard system limitation on the number of SAP-delivered key dimension types that can be included in a SAP BPC data model.

As you might be aware, depending on the types of model you elect to use when configuring your BPC environment, BPC requires that certain “standard” dimension types always be included in the model. This is because these standard dimension types are critical to the underlying functioning of and business logic contained within BPC. So, for example, if you want to use a Reporting Model of type “Financial”, you are required to always include the SAP-delivered Account (A), Category (C), Entity (E), Time (T), and Currency (R) dimension types. This leads us to an important constraint – BPC only allows us to include one occurrence of each of these “standard” dimension types in a model.

So, what do you stand to do if you need to include more than one standard dimension type in your model? By way of illustration let’s examine one example – imagine for a minute that your business requirements demand you to have more than one Account-type dimension in your model, one of these dimensions representing regular GL accounts and another representing functional accounts (where we have many individual GL accounts mapping into a single functional account based on the function code of the cost center). As you can only have one account-type dimension in your model you will typically model GL account as an account-type dimension, which means you are forced to model functional account as a user-defined (U) dimension type. This of course becomes problematic when creating reports, to get the account type of the functional account correct you will then be forced to always include GL account alongside functional account in the report drilldown, which is not very efficient. Where GL account is simply contained in the page-axis of the report the signage of the functional account will simply follow the account type (ACCTYPE) property of the GL account from the page axis, which will not necessarily correspond to the intended account type of the functional account, leading to incorrect signage and roll-ups in your report.

This is where a custom measure comes to the rescue. By simply creating a custom measure we can directly influence how BPC treats a dimension in reporting and business logic, and applied to our example above we can use a custom measure to make our user-defined (U) type functional account (FUNC_ACC) dimension behave like a true account-type (ACCOUNT) dimension. Here is how we solve this:

1. Add a custom property to represent the standard account type (ACCTYPE) property to your FUNC_ACC dimension. As ACCTYPE is a reserved property it can only be used in a standard account-type dimension, so you will need to give your custom property a slightly different name. In my example, I will call it ACCTTYPE. 
2. Populate the new ACCTTYPE property with the account types (INC, EXP, AST, LEQ) appropriate to each of your FUNC_ACC members and hierarchy node roll-ups.
3. Next you need to create your custom measures, this is where you configure the logic which enables to system to treat the custom ACCTTYPE property on the user-defined FUNC_ACC dimension in the same manner as it would the standard ACCTYPE property on the standard ACCOUNT dimension. Using transaction code SE38, execute the following program: UJA_MAINTAIN_MEASURE_FORMULA
4. Select your environment and model. To mimic all measure types in BPC you will need to create three custom versions of the standard measures, one each for PERIODIC, QTD, and YTD. Let’s start with creating an equivalent for PERIODIC – select PERIODIC and copy the formula from the existing measure. Now create a new custom measure, let’s call it PERIODICFUNC and paste the formula from PERIODIC into this new measure.
5. The original formula should look as follows:’IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”INC”,-[MEASURES].[/CPMB/SDATA],IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”EXP”,[MEASURES].[/CPMB/SDATA],IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”AST”,([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES(“2/CPMB/ACCTYPE”)=”LEQ”,-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))’;SOLVE_ORDER=3
6. Now edit this to the following (changes marked in bold – for the property name use the technical name of your new ACCTTYPE property):’IIF([%FUNC_ACC%].CURRENTMEMBER.PROPERTIES(“2/CPMB/JPPRMZ2“)=”INC”,-[MEASURES].[/CPMB/SDATA],IIF([%FUNC_ACC%].CURRENTMEMBER.PROPERTIES(“2/CPMB/JPPRMZ2“)=”EXP”,[MEASURES].[/CPMB/SDATA],IIF([%FUNC_ACC%].CURRENTMEMBER.PROPERTIES(“2/CPMB/JPPRMZ2“)=”AST”,([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),IIF([%FUNC_ACC%].CURRENTMEMBER.PROPERTIES(“2/CPMB/JPPRMZ2“)=”LEQ”,-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),-[MEASURES].[/CPMB/SDATA]))))’;SOLVE_ORDER=3
7. Save your changes and repeat the same steps to create new QTDFUNC and YTDFUNC custom measures as well.
8. These new custom measures can now be used in reports and will provide the correct signage and roll-up when reporting on FUNC_ACC, without the need to include ACCOUNT in the drilldown as well. So, your FUNC_ACC dimension now functions as a true account-type dimension!

Beyond the scope of the above example, custom measures can also be used in a similar fashion to influence the behavior of other user-defined dimension types and allow them to be resolved as fully fledged standard dimension types in BPC reporting and business logic.