To Hard-Code or Not To-Hard-Code - The Sequel

Keeping to the theme from my previous post, I thought of sharing more tips and tricks on how to avoid hard coding your business rules.  In this post, I'll share with you the following techniques that I have used in the various projects that I've worked with previously:

Reverse-mapping using Smartlist

This is essentially relying on dimension-based smartlists to resolve to the intended member.  This option gives the users the ability to maintain the mappings via a webform without having the underlying business rules hard-coding to any target members.  Use case for this method can be applied to any allocation-type business rules.  Details of this techniques can be found in this post and this one.

User Variables & Planning Expressions

The User Variables feature in Planning can be a useful tool to give users more control over what they wish to view in a webform.  Forms configured to use these variables will give users the ability to dynamically select a member at runtime (e.g. a parent member of a hierarchy) which could then be used to display the descendants of the selected parent.  To take it one step further, we can use the user variables in business rules as well.  This helps us to limit the calculation to only the selected member(s), thus, making the rule run faster.

Let's see how this can be achieved in the business rule.  The trick here is to use a Planning Expression to resolve to the selected member of the User Variable.  The Planing Expression in question here is the [[PlanningFunctions.getUserVarValue("User_Variable")]].


Consider this.  I have defined the following User Variables:

And I wish to limit my calculation scope to level 0 members of the selected Entity and Cost Centre parents.  Here's how I would code my rule:

The "PlanningFunction.getUserVarValue" Planning Expression will resolve to the base members of  entity and cost centre that the user has selected at runtime.  You could also use other Planning Expressions to further your quest in avoiding any hard-coding in your rules.  Here are some other examples of Planning Expressions that you could possible use:

Periods

The following is a list of Period-related Planning Expressions:

I have personally not used any of the above Planning Expressions except for [[Period("FIRST_PERIOD")]] & [[Period("LAST_PERIOD")]].  Here's an example of how I've used them in my rule to control how I get the prior period value for an account.

Scenarios

Here's a list of Planning Expressions linked to Scenarios:

The last I used the above expressions, they require the Scenario Name to be a string (i.e. hard-coded) but the latest reference guide from Oracle shows that the expressions now accepts variables such as Runtime Prompts (RTP).  Try it out and let me know we can now use variables.  Would be awesome if this is true.

Here's an example code I've lifted from the Oracle's guide.  Looks like it accepts RTPs.

That's all folks for this blogpost.  Let me know if you find this post useful and any other feedback is also welcomed.  Thank you and see you in my next post.

To hard-code or not to hard-code?

I'm not a fan of hard-coding my business rules as I find that more often than not, those hard-coded beasts will turn around and bite you.  You'd struggle to remember which hard-coded rules need updating when new hierarchies are added to your planning application.  Sure, hard-coded scripts tend to be easier to read but I'd still steer away from having hard-coding where possible.

Enough said, let's get down to business... This post, I'd like to share with you a technique I often used to resolve an assumption that is set globally for a group of common parent members (e.g. Entities).  Normally, one would create an "NA" member to hold the global assumptions for these members as shown below:

I could write a rule that hard-codes to ANZGROUP_NA to resolve the global assumption like this:

Or, I could create a rule that will dynamically resolve to ANZGROUP_NA. 

The code will resolve to ANZGROUP_NA by first resolving to the ANZGROUP node using the @ANCEST("Entity",2) function and followed by concatenating the resulting member with a suffix of "_NA".  The @MEMBER will then convert the concatenated string to a member before used in the crossdim with "Annual_Salary".  The series of functions will result in the following crossdim:

"Monthly Salary" = "Annual_Salary"->"ANZGROUP_NA" / 12;

This of course, is a very simple example with the entity dimension having just 3 generations (Entity, Group and Individual Level 0 entities) and you might ask what's the big deal with hard-coding the rule to point to ANZGROUP_NA?  You're right, this isn't a big deal to have it hard-coded in this instance as the chances of you adding another NA member in this hierarchy is quite minimal.  However, it will be a different conversation if we had an entity structure like the one shown below:

Each of the country entities (i.e. AU and NZ) having their respective NA members to capture their country-specific assumptions.  The hard-coded rule needs to be updated to evaluate if the current entity being calculated is part of the AU or the NZ hierarchy and then apply the correct country-specific assumption to the rule.

Any subsequent addition to the entity dimension (i.e. adding of new countries), will require the rule to be amended to also consider the newly added countries.  Now, compare it with the non-hard coded rule.  You would update the code to use Generation 3 that will resolve to either "AU" or "NZ" before concatenating with the "_NA" suffix.

With just 3 lines of code, the rule will resolve to AU_NA for all entities under the AU hierarchy and to NZ_NA for all NZ entities.

With the above code, any subsequent addition of siblings to the AU or NZ nodes will NOT require you to amend your rules as it will work perfectly as long as you maintain the same hierarchy structure as we have for AU or NZ.

There! A short and sweet post.  Hope you found it useful and keep a look out for more tips and tricks on how to avoid hard-coding in your rules.  Till then, adios!

Integrating Oracle Financials Cloud into PBCS and a Drill Through back to source

First off, sorry for the long hiatus from blogging about Essbase.  To resume where I left off, I thought I'd share my first attempt to configure the data integration between Oracle Financial Cloud with PBCS and a drill through back to the data source.  Here goes...


Establish the link to Oracle Fusion in PBCS via Data Management

Start with setting up the connection to Oracle Fusion to allow us to then load the GL balances from the ERP system.  We will begin with adding a Source System in Data Management.  Select "Oracle Financials Cloud" from the "Source System Type" dropdown list and then click "Save".

Click on the "Configure Source Connection" to define the login credentials and the URL to the source system.  The Oracle Fusion user you use here should be granted the "OA4F_FIN_GL_DETAIL_TRANSACTIONS_ANALYSIS_DUTY" role access.  Fill in the details and click the "Test Connection" to ensure a successful login to the source system.  Click "Configure" to conclude the configration.

Click on the "Initialize" button to create the necessary Essbase cube required to stage the data extracted from Oracle Financials Cloud.

This step will create the Essbase cube that will appear in the Target Applications section of Data Management.

Create the Target Applications to store the extracted data

This section of Data Management contains all the target systems where the raw extracted data and the final mapped data are stored.  The "COA" is a Target Application created by Data Management when we initialized the connection between Oracle Financials Cloud and PBCS.  Leave the settings as they are.  We do not need to make any changes to the settings for "COA".

You will now need to manually add the Target Applications for the final destination of the extracted data.  We will define one for an Essbase ASO cube and another one for the PBCS BSO Planning cube.

Let's begin with the ASO cube called "FinRpt".  Here's the settings that you will need to be aware of to ensure that data is loaded to the ASO cube and have the ability to drill back to the source data.  Note that we need to select "Essbase" as the Type for an ASO Target Application.  Click on the "Create Drill Region" check box for the relevant dimensions.  Please ensure that the following dimensions are tagged with the correct "Target Dimension Class".  

• Account_Segment should be tagged as Account
• Cost_Centre_Segment should be tagged as Entity (if this dimension was defined as an Entity type dimension in PBCS)
• Scenario should be tagged as Scenario (note that Data Table Column Name for this dimension will disappear when it is tagged to Scenario dimension class.  This is fine as the scenario in Data Management is derived from the Category code)
• Version should be tagged as Version
• Years should be tagged as Year

Failing to do this may result in an unsuccessful drill through.  Click "Save" when done.

Click on the "Application Options" tab and turn on the Drill Through.  Click "Save" when done.

Repeat the same for the Planning cube Target Application (in this example, named as "Fin").

Define the Import Formats required

Now that you have the Target Applications defined, let's proceed to adding the required Import Formats.  This allows you to define the links and dimension mappings between the source and target applications.  Let's add one for the FinRpt cube.

Just select the relevant Source Dimensions from the dropdown list and map them to the correct Target.  You can also define Expressions to pad in zeroes to the values when importing to Data Management.  Click "Save" when done.  Repeat the same process for the Fin Planning cube.

Build the Locations

Locations allow you to create different data load rules and mappings using the same Import Format.  In the example, we will be creating three locations for the ERP_FinRpt Import Format and a single location for the ERP_FinPlan Import Format.

For ERP_FinRpt, we will create

• ERP_FinRpt_PL (for P&L load)
• ERP_FinRpt_BS_CB (for Balance Sheet Closing Balance load)
• ERP_FinRpt_BS_Mvmt (for Balance Sheet Movement load)

We will create ERP_FinPlan_PL for P&L load.

Let's start with ERP_FinRpt_PL.  Add the location and link the ERP_FinRpt Import Format to this location.  Click "Save" when done.

Now repeat for the rest of the locations.  Remember to select the ERP_FinPlan Import Format for the ERP_FinPlan_PL location.

Confirm the period mappings

The Period Mapping is used to map to the correct Period and Year in the Target Application.  In this example the application is defined with the Financial Year starting in July and ends in June the following year.  So make sure that the Year is mapped correctly.  Click "Save" when done.

Build the Category Mappings (if required)
Categories in Data Management maps to the scenario dimension in the Planning or ASO applications.  Data Management will automatically create several categories and you can add new ones or update the ones created by Data Management as required.  Just make sure that the name of the Category matches the Scenario name in your Target Applications.  As usual, remember to click "Save" when you are done.

Create Data Load Rules

For each Location that you've define, create a Data Load rule and define the filters for each of the dimension from the Source System.  Click "Save" when you are done.

When selecting the filters, please make sure you use fully qualified member names to avoid any duplicate member names that is possible in Oracle Financials Cloud.

Repeat for the rest of the locations that you've defined.

Configure the Dimension Mappings

Define the mappings like you would for any Data Management configuration for each of the locations created.  Remember to click "Save" when done.

Repeat the mappings for the rest of the dimensions.

Extract and Load the Data into PBCS

We are now ready to extract the data from Oracle Financials Cloud and load them into PBCS.  You can either do this via the Workbench or the Data Load Rule.  In this example, I'm loading the data via the Data Load Rule screen.  Click "Execute".

Fill in your selections then click "Run".

Drilling back to the source!  Woohoo!!

Now that we've loaded the data into PBCS, let's look at how we can drill through from the summary balances in PBCS to the underlying details that exist in Data Management.

Notice that each cell that is "drillable" has an indicator at the top-right of the cell.  Right-clicking on the drillable cell will reveal an option for you to "Drill Through".

Select "Drill Through" from the pop-up menu and the underlying details of the selected cell will be revealed.

The top half of the screen shows the data intersection of the selected cell while the bottom half of the screen shows the underlying records that make up the amount shown in the selected cell.

There you go, the step by step guide to integrating Oracle Financials Cloud data into PBCS with the ability to drill back to the data source.  Hope you find this blog useful and provides some guide for you.  Cheers!

Getting your way around a Planning Application using Navigation Flow

I'm back!  In this blog I we will not discuss about SmartLists but another cool feature within PBCS.  Gone are the days when Tasklists are the only way to go to define the planning flow in the Planning application.  Don't get me wrong, Tasklists get the work done pretty well but lacks the visual appeal that one is used to now with the icon-based navigation in all our mobile gadgets.

Make way for "Navigation Flow" (affectionately known as Nav Flow, well... at least to me).

So, what's this Nav Flow and does it stack up to our dear old friend the Tasklists?  For one, it is visually more appealing and intuitive to end-users.

Instead of defining them as a list of tasks, the planning models can be grouped and represented by icons.  The screenshot above shows that the application has models for Revenue, Expenses, Workforce, Capital, and Income Statement, all with their distinct icons.  Going from left to right, one will be able to complete their planning activities in the sequence that is intended by the designer of this application.

Drilling into the Revenue model reveals individual revenue sub-models.

The vertical tabs on the left represent the different revenue sub-models such as Property Management Income, Property Sales Income, etc.  The tabs across the top of the form list forms/reports/dashboards within each sub-model.  What's cool about this set up is that a user can have direct access to all the models and sub-models easily instead of the sequential access via a task list.

Now you must be wondering how do I control access to the models as not all users should have access to all the models in the application.  This is pretty awesome, the access rights set in the individual artifact (i.e. forms, reports, dashboards, rules) are honoured by the Nav Flow.  What this means that a user having access to the Revenue and Workforce models will only see these 2 icons in addition to the system icons (i.e. User Variables, Reports, Rules, and Jobs).  Cool, huh?  To take it one step further, assuming that this user has access to only the Property Management Numbers form within the Property Management Income model, he will only get this form shown and the other 4 forms are hidden from him.  All the other revenue sub-models are also hidden from this user.

To make the application more self-service oriented, you can also embed instructions to each of the form attached to this Nav Flow.  Just click in the "Information" icon beside the form name to reveal the instructions.

Now, let's get on to the nitty-gritty details of how we set this up but before that, I'd like to give you some context about the navigation flow discussed in this blog.

The Application
Our discussion is based on an application that contains the following models:

1. Revenue
• Property Management
• Property Sales
• Ancillary Income
• Agent Plus Income
• Maintenance Income
2. Expenses
• Office Rentals
• Management Fees
3. Workforce
4. Capital
5. Income Statement

User Groups and Security Access
As mentioned earlier, the Nav Flow uses the access rights for each user to render the relevant icons that he or she has access to.  Here's what I've done:

• Create groups for Admins and All Users and the relevant built-in user roles to groups

• Create groups for each model and assign individual users to the relevant Functional User groups.  

• Go ahead to then assign the Functional Groups to the relevant Forms, Reports, Dashboards, and Rules.

Navigation Flow
Now it's time to define the Nav Flow and I've chosen to build one for Administrators and the other for the rest of the user community.  Since the Nav Flow honours the security access rights that we've established in all the artifacts, we should only need a single Nav Flow that contains all the models and relevant system icons.  However, I've decided against that simply because I'd like to hide the Rules icon from the end users and rely on the rules to run automatically each time a form is saved.

So, in this example I have 2 Nav Flows defined while the "Default" came with PBCS:

• 10_All_Admins
• 20_All_Users

You'd ask why I have named the Nav Flow with a numerical prefix?  Well, that to help me control which Nav Flow gets resolved first.  You see, the system will evaluate the Nav Flow in the order in which they appear in the Navigation Flow page to resolve to the correct Nav Flow that a user has access to.  So, by having them named with a numerical prefix, I am ensured that they get evaluated first before landing on the Default Nav Flow.

With the above set up, PBCS will first check if the user has access to the first Nav Flow and if he is an Administrator of this application, he will be shown the 10_All_Admins Nav Flow and will use the second Nav Flow (20_All_Users) if he is a planner.  In the rare event if a user wasn't assigned to either the ALL_Admins or the ALL_Users group, he will gain access to the Default Nav Flow.

Now, let's see what under the hood of 10_All_Admins Nav Flow.  I have the User Variables as the first icon so that I can guide the users to first ensure that they have the necessary Users Variables set up prior to accessing any of the models.  Subsequent 5 icons are all the models that exist in this application while the remaining icons are 3 other system icons that I wish to expose to the users (i.e. Reports, Rules (only for Admins), and Jobs.

I will now show you how the sub models are defined in the Nav Flow and we'll use the Revenue as the example.

The Revenue model has 5 sub models and they appear in the vertical tabs on the left of the form:

1. Property Management
2. Property Sales
3. Ancillary Income
4. Agent Plus
5. Maintenance

Clicking on "Property Management" will reveal the horizontal tab settings as shown below.  They will appear as tabs across the top of the form:

Each of the items in the horizontal tab is linked to either a Form, Report, or a Dashboard.  Clicking on the "Property Management Assumptions" item will reveal the definition for this item and in this case is a reference to the "Property Management Assumptions" form.  Don't be fooled by the grey-coloured field in Artifact.  It does not mean that the content cannot be edited.  Click on the search icon beside the Artifact field to associate an artifact to the item.

When you are done defining all models and sub-models, click "Save and Close" and then activate the Nav Flow.  Just so you know, the Nav Flow needs to be in an "Inactive" state for you to be able to modify it and it has to be in an "Active" state for users to be able to access it.  Click on the status to toggle between Active and Inactive state.

Another cool feature that I'd like to tell you about the Nav Flow is that the models you've defined in the Nav Flow can be re-used in another Nav Flow thus, eliminating the need to repeat you definitions and makes updating them much easier where you need to only make the updates in the main Nav Flow and they will be applied to all other flows that refer to the main one.  In my case, 10_All_Admins is the main Nav Flow while 20_All_Users is the secondary one that reuses the definitions that were done in 10_All_Admins.

Notice that the models are referring to 10_All_Admins.  To reuse a definition from another Nav Flow you would click on "Add Existing Card/Cluster" instead of "Add Card" and then choose a card or cluster from an existing Nav Flow.

That's it!  We're done.

What's missing from the Nav Flow?
Now that I have sang praises about this neat feature, I'd certainly like to see additional features added to the Nav Flow.  One main one that is missing from here is the ability to link a specific rule to a card within the Nav Flow.  That was the reason why I had to expose the Rules system icon but limit it to the Administrators only.

Hope you have enjoyed reading my blog post and hope to post the next one real soon.  Until then, sayonara till we meet again.