Write Back Budgets from PBCS to Fusion ERP Cloud

As explained in my previous blog on loading Actuals from Fusion ERP Cloud to PBCS, the GL Actuals data is stored in an ASO Essbase cube, referred to as the GL Balances cube. What we didn’t cover in the previous blog is that, as well as holding the GL Actuals values, this Essbase cube can be configured to also hold Budget and/or Forecast data, which can be sourced from PBCS.

The main advantage of doing this, is that the Budget and Forecast data would be available alongside real-time Actuals for FRC/OTBI reporting.

Integration Pre-requisites

Before this integration can be setup in Data Management:

• The ERP Essbase cube must have been created using job ‘Create General Ledger Balances Cube’ when configuring the ERP system
• Budget/Forecast Scenarios must be added to the CoA via ‘Manage Chart of Accounts Value Sets’ tasks
• Scenarios must be deployed to the ERP cube using job ‘Create Scenario Dimension Members’ – these should be visible in Smart View under the ERP Scenario dimension

When pushing Budgets to the same cube as the GL Actual balances, we can use the same source system and period mappings as with the Actuals load:

Setting up with Fusion ERP source system is explained here 

Create Import Format

Next, we need to setup the import format. This is where we map the dimensions between Source and Target systems and will be the basis for creating the Data Load Mappings. Remember here, the Source is the PBCS Application and the Target is the Fusion ERP system.

Make sure to add a mapping for the ‘Ledger’ dimension here, even if it is not relevant. If there are other dimensions which exist in the Target but not in the Source, leave these blank and we can address them in the Data Load Mappings area.

Location

Now we can create a location to store all the data load rules and mappings for this integration, referencing the import format just created. If the ERP cube contains multiple currencies, make sure to specify a Currency in the ‘Functional Currency’ field. In this case, all data in PBCS was GBP.

Data Load Rule & Source Filters

As with the Actuals load, when setting up the data load rules, it is recommended to have one data load rule for each ERP Ledger, including the Consolidation Ledger – this is held as a separate member in ERP and does not automatically equal the total of the Primary Ledgers.

Within each data load rule, specify the Source Plan Type (PBCS) and complete the data filters for each dimension in the chosen plan type. Keep in mind that this integration will use the source data filters listed here for the Scenario and version, rather than using the Category mappings (as it does for a data file load).

As the source is PBCS here, we can reference functions e.g. @Lvl0Descendants and Substitution Variables, referenced with ampersand (&) e.g. &CurrBud

Data Load Mappings

Any data load mapping type can be used for this integration, including SQL mappings, and are setup in the same Data Load Mappings area as when loading data from a file.

For dimensions which are present in the Target (ERP) but not the Source (PBCS), map all data to one member in ERP (e.g. map all data to “Default” Intercompany)

Remember, in this example the Scenario is mapped from the Version (set in import format) and the integration will use these mappings instead of the Category mappings.

Executing the Data Push

Once the above setup steps are complete, the Budget push from PBCS to ERP can be run just like any other file data load in Data Management, by executing the Data Load Rule.

It’s also possible to use EPM Automate command RunDataRule to kick off the data loads using a script. This can then be automated overnight if required. See RunDataRule command for more information. 

Note! It is important that the ERP cube remains unlocked during the data load. Please bear this in mind when scheduling data pushes using EPM Automate.

Clearing Data in Fusion ERP

When pushing Budget or Forecast data from Planning (PBCS) to a defined Accounting Scenario in ERP, the data will only be overwritten for combinations which have non-zero data in the push. This means that if data has been cleared to #missing in the PBCS source data, then existing values in ERP will not be overwritten.

Unfortunately, in Fusion ERP this isn’t as simple as running a business rule to clear out a specified slice of data, unlike in the agile world of PBCS.

The quickest way we have found to clear out an entire Accounting Scenario in ERP is to essentially delete and re-add the scenario in ERP. As you can imagine, this is a manual process that cannot be scheduled, making it difficult for this integration to be fully automated.

Common Errors

Note! If an error is encountered during this integration, then no data is loaded at all and the target intersection will remain empty.

1. Missing or Disabled Members

If an ERP member (segment value) is missing or has been disabled, then the data push will fail.

Checks/Resolution:

·         Check mappings to ensure correct member name is referenced

·         Re-enable or add member into CoA segment and deploy to cube

2. Parent level data

Upper level members are tagged as being a ‘Parent’ value in ERP, whether they have any children or not. This will cause an error if data is stuck in a childless parent value in PBCS and has been picked up by the data load source filters because it is technically a level zero member.

Checks/Resolution:

·         Check mappings to ensure correct member name is referenced

·         Confirm if there are any “childless parents” in PBCS. If so, remove member or data before re-running the data load.

3. RunTimeError

Although quite generic, a “RunTimeError” is usually caused by the ERP Essbase cube being locked at the time of running the data load. Some potential causes of the ERP Essbase cube being locked are:

• ERP cube restructure in progress
• Scheduled Job running on the ERP system
• ERP Environment is currently unavailable

To proceed, ensure the ERP cube is unlocked and no jobs are currently running on the ERP system before submitting the data load again.