Infosys’ blog on industry solutions, trends, business process transformation and global implementation in Oracle.

« What to watch out while implementing Close Manager | Main | Road-Map and New Upcoming Features In EPM Cloud - 2019 & 2020 »

Automation of customized PBCS Data Load

Introduction

 

I am working on the Oracle EPM PBCS implementation project which is focused on integrating Fusion application. The data load process is automated using EPM utility. Users wants to load the data for the specific version in PBCS application. The version value will be set through the substitutional variable. For example, if the version value is set to "Final", then the data should be loaded to Final version in PBCS target application. User will update the version value based on the data load requirements.

 

PBCS Data Load Process

 

The sources files are generated from Fusion cloud system and loaded into PBCS application through data management.

The data load process is automated using EPM utility and PowerShell scripting.

The Account, Entity, Version dimension mappings are updated in the data load mapping of data management.


Picture1.png


Business requirements

Business users are loading the forecast data into PBCS application on weekly basis.

PBCS application has Week1, Week2, Week3, Week4 and Final versions and users are loading the data into these version based on the forecast week.

       To load the data into specific version, the data load mapping should be updated based on the weekly load. If the user wants to load the data for the Week1 version, then the target mapping for version dimension should be updated to Week1 in data load mapping.

But users can't update the data load mapping every time manually. It should be updated automatically based on the weekly load.

 

 

Solution

 

To automate the data load process based on the version, we need to automate the data load mapping update process.

This can be achieved using Version substitutional variables. The data load mapping file is generated based on the substitutional variable value and imported it into Version dimension data load mapping in data management.

PowerShell script is used to get the substitutional variable value and generating data load mapping file.


Data load mapping file template

 

The data load mapping file should be generated automatically based on the version substitutional variable value.

The mapping file should have the below details

 >> Source Value, Target Value, Rule Name, Description, Apply to Rule


Picture2.png


PowerShell Script to generate mapping file:

- The EPM command "getsubstvar" is used to get the substitutional variable value  from the PBCS application.

- The substitutional variable value may be set to "Final" or "Week x". Using pattern matching, we can identify which variable is set.

 

  $ealoc = "E:\Oracle\EPMAutomate\bin"

  $ret = & $ealoc\epmautomate getsubstvar ALL name=ForecastVersion

  $ver = ([regex]::matches($ret, "Final") | %{$_.value})

 

 

- The below PowerShell script generate the data load mapping file based on the

   substitutional variable value.

- The substitutional variable value may be set to "Final" or "Week x". Using pattern

   matching, we can identify which variable is set.

 

 

if ($ver -eq 'Final')

{  $FVersion = $ret[1].Split("=")[1].substring(0,5)  }

Else

{   $FVersion = $ret[1].Split("=")[1].substring(1,6)   }

$FVersion  = "*," + $FVersion + "," + $FVersion + "," + $FVersion + ","

$FVersion > 'FVersion.txt' (Get-Content E:\FVersion.txt) | Set-Content -Encoding Default  E:\FVersion.txt

 

PowerShell Script to import mapping file

The below command import the data load mapping into x location in Data management.

  $ret = & $ealoc\epmautomate importMapping VERSION             inbox/Location/FVersion.txt REPLACE TRUE Location


Picture3.png



Data Load into PBCS

Once the data load mapping is imported into data management , the PowerShell command executes the data load rule in sequence.

  $ealoc\epmautomate rundatarule "Location" $Period $Period REPLACE   STORE_DATA "SourceFile"

The data will be loaded into target application to the specific version based on the substitutional variable value. If the value is set to "Final", then the data will be loaded into Final version.




Post a comment

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)

Please key in the two words you see in the box to validate your identity as an authentic user and reduce spam.

Subscribe to this blog's feed

Follow us on

Blogger Profiles