Data

Data Management transformations on text value loads to EPBCS/PBCS

Loading text data into Hyperion Planning has always had limits. You had to leverage the format of the outline load utility, which had no transformation functionality. Sure, you could use ODI to do amazing transformations, but were limited on automation unless you had purchased the full use license.

Data Management for PBCS and EPBCS was enhanced in late summer 2016 to facilitate text loads. This enhancement simplified the file requirements for the IT department and put some control in the hands of the system administrators.

There are excellent blogs out there that show the end to end process for loading text values using Data Management.

I have not seen a blog that shows you how to transform the text values. Although, I did not look very hard. That is what I will cover in this brief blog.

Scenario:

I have a Position cube. Attributes are assigned to each position. I am going to focus on Job Code which is a Smart List and assigned to each position.

The Job Code Smart List is mapped to a dimension in an ASO cube.

When we run our data map, the Smart List assignment in the Position cube is mapped to the corresponding member of the Job Code dimension in the ASO cube.

My goal is to add a prefix to the Job Code and shorten the name during the position detail file load.

Why do I want to do that?
- The job code values overlap with the member names in my Program dimension, so I want to keep everything unique and clear.

Example: Change Job Code ‘101159 Assistant to the Mayor’ to ‘JC_101159’.

I will use Data Management to load the position detail flat file and transform the values in the field ‘Job_Code_Input’.

Source File:

Data Management :

The key is using the option to write your own SQL (#SQL – in this context this is not a Twitter reference).

SQLRULE

About the SQL below… DATA is the source data field in the Data Management table and DATAX is the target data field.

I am using the Account mapping as a pipeline to the data fields. I still need to have a valid map for Account.

I mapped accounts using this statement ‘CASE WHEN 1=1 THEN ACCOUNT END’ (there are other ways to do this same mapping).

Once Account is mapped, I am free to transform the data.

DATAX =
CASE
WHEN ACCOUNT = ‘Job_Code_Input’ THEN SUBSTR(CONCAT(‘JC_’,DATA),0,9)
ELSE DATA
END

The CASE Statement allows me to find the specific account to transform.

I use the Sub-string and Concatenation syntax to transform the data for the account defined in the CASE Statement.

SQL

I define all other mappings and execute my load rule…

Here are the results per the workbench:

The Target Data has been transformed.

Workbench

This is a basic example. The key is knowing where to write the SQL and the field names.

I hope this was helpful as a starting point and you can build from this.

As always feedback, alternative methods are welcome.

Regards,

Scott Williams

P.S. I remove Case Statement for the ACCOUNT and use the SQL below and the result is the same.

ACCOUNT,
DATAX =
CASE
WHEN ACCOUNT = ‘Job_Code_Input’ THEN SUBSTR(CONCAT(‘JC_’,DATA),0,9)
ELSE DATA
END

FDMEE AftLoad script – HFM to PBCS

Hello and welcome to this post.

Recently I was working with a client who wanted to pull EBS data as well as data from other sources into HFM conversion and consolidation. Ok, so the data load is standard process work for FDMEE, and the rest is standard HFM functionality; so its all good.

After the period is closed in HFM the data needs loaded to PBCS, simply and seamlessly by a user….OK the last part took some thought.

Thought process:

The data flow was/is straight forward.
I knew I was restricted to flat files for moving data into this instance of PBCS.
How could I get this all linked together and keep it simple enough for a user to execute?
I had an on-premise version of FDMEE to work with, which gave me that added flexibilty of scripting.
Version is 11.1.2.4, so the functionality to extract the data from HFM using FDMEE was in play.

With the goal of keeping the user process simple and seamless I came up with the process below.

The End User Process:

Executes the Data Load Rule after the period/month has been closed.

FDMEE on-premise

Run the Data Load Rule:

Select the Location:

Execute

Select parameters and run it.

Start and End Period must be the same.

Seems straightforward for a non-technical user.

If inclined the user can review the process details.

Process Details:

On-premise output:

Extracted record count for rows of data = 3548

Now jump to PBCS.

Imported record count = 3548

The data was loaded to Planning.

Users validated the data.

Seems we are good.

All done from the users point of view.

The “black box”, how did the data loaded to the PBCS application (the cloud – ooh ahhh)?

What the user does not see:

Disclaimer: There are always different ways to complete same tasks. I am not claiming the process I decided on below is the best, but it satisfied the requirement and created a positive experience for the end user and was not taxing on the system. I also think it is pretty slick.

I would like to hear other suggestions or improvements, so I welcome your comments/feedback.

Also the process detailed below pulls in local currency. An identical process was created for the global currency values. I tried to link them together, but ran into an issue with the AftLoad script running only once, rather than for each currency. In short I could not link them to run in succesion.

DATAFLOW:
After data has been loaded, consolidated and translated for a closed month in HFM, the data set needs to be moved to PBCS. FDMEE on premise is used to extract the data from HFM into a file. EPMAutomate is used to move the file to PBCS and use FDMEE on PBCS to process and load the file. The entire process is executed from the on premise FDMEE environment, leveraging a Jython script to create batch files and execution of them.

1st I started with the batch script to call EPMAutomate and load the file to PBCS Data Management (FDMEE on the cloud). Then parameterized what I could pass from another batch script.

This script is named HFM_to_PBCS_Local_Detail.bat

REM ***** Set Environment Variables
call E:\Oracle\PBCS_Data_Loads\Scripts_Data\env.bat

SET Month=%1
SET Year=%2
SET File=%3

copy E:\Oracle\FDMEE\outbox\%File% E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Load_Files\HFM_to_PBCS_Local_FY%Year%_%Month%.dat

SET Period=%1
SET FY=FY%2

CD /D %~dp0
C:

REM run business rule to clear data, variables passed for runtime prompts
REM Variable hardcoded for currency (Local_$ for local load, Reporting_$ for USD load)
Call epmautomate runbusinessrule Int_Clear_Act_data_befload_from_HFM Period=%Period% Year=%FY% GCurrency=”Local_$”

REM FDMEE PROCESS:
CALL EPMAUTOMATE UPLOADFILE
E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS Load_Files\HFM_to_PBCS_Local_FY%2_%1.dat inbox
ECHO File loaded to FDMEE Inbox
Call EPMAUTOMATE RUNDATARULE BB_HFM_to_PBCS_Actual %1-%2 %1-%2 REPLACE STORE_DATA HFM_to_PBCS_Local_FY%2_%1.dat

ECHO Data Load Rule Executed
REM run business rule to aggregate data, variables passed for runtime prompts
REM Variable hardcoded for currency (Local_$ for local load, Reporting_$ for USD load)
Call epmautomate runbusinessrule Int_Agg_data_aftload_from_HFM Year=%FY% GCurrency=”Local_$”

REM IF %ERRORLEVEL% NEQ 0 goto :ERROR

OK, so now I have a batch script to load the data to PBCS and run the required Business Rules. However there are variables that need to be passed into this script, where do they come from?

2nd I created a script to call HFM_to_PBCS_Local_Detail.bat and pass variables. The intention behind this was to create a simple script that could be recreated every time the parameters/variables changed. The parameters change each time the Data Load Rule is executed in FDMEE. I knew I could use an AftLoad script in FDMEE and leverage Jython(Python) to recreate an this batch script with new parameters. Also I could use the Jython to execute the newly created batch script.

HFM_to_PBCS_Local_Execution.bat – This script will call HFM_to_PBCS_Local_Detail.bat and pass variables.

Batch Script contents:

E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\Detailed_Scripts\HFM_to_PBCS_Local_Detail.bat>E:\Oracle\PBCS_Data_Loads\Script_Datas\HFMtoPBCS\Output\HFM_to_PBCS_Local_Detail.txt Dec 14 Act_to_File_453.dat

Script Breakdown:

String 1: E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\Detailed_Scripts\HFM_to_PBCS_Local_Detail.bat

-This value never changes. The value is hardcoded in the AftLoad script. It calls the detailed batch script that moves the file from on premise to PBCS and processes it.

String 2:

>E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Output\HFM_to_PBCS_Local_Detail.txt

-This value never changes. The value is hardcoded in the AftLoad script. The “>” command redirects the output from the command line session to the stated path\file “E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Output\HFM_to_PBCS_Local_Detail.txt”.

String 3:

Dec

– The period value is a variable. It changes every time the Jython AftLoad script creates the batch script and uses the “MONTH = (POV[:3])” command.

String 4:

– The year value is a variable. It changes every time the Jython AftLoad script creates the batch script and uses the “YEAR =(POV[-2:])” command.

String 5:

Act_to_File_453.dat

– The dat file name is a variable. It changes every time the Jython AftLoad script creates the batch script and uses the the following commands.
LOC = str(fdmContext[“LOCNAME”])
LID = str(fdmContext[“LOADID”])
TARGET + “_” + LID + “.dat”
3rd Add the coding to the AftLoad script define the creation of the HFM_to_PBCS_Local_Execution.bat described above.

I skipped the basic FDMEE configuration of the Source Systems, Target Applications and Import Formats.

Location:

The Location links the Source System, Target Application and Import Format together.
Act_to_File_Local_$ – File for local currency

Script Editor:

An AftLoad script is used to generate a batch file and executed it. The batch file contains unique parameters that are generated when the Data Load Rule is executed.

AftLoad Jython Script:

#————————Begin Act_to_File_Local_$————————–#

if fdmContext[“LOCNAME”]==”Act_to_File_Local_$”:

#Set Variables
POV = str(fdmContext[“PERIODNAME”]) #POV = the Period Name selected in FDMEE example Jun-14
MONTH = (POV[:3]) #MONTH and YEAR are assigned using the slice command against the POV
YEAR =(POV[-2:])
LOC = str(fdmContext[“LOCNAME”])
LID = str(fdmContext[“LOADID”])
TARGET = str(fdmContext[“TARGETAPPNAME”])

#File creation for PBCS Load – Local filename=”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\HFM_to_PBCS_Local_Execution.bat”
FILE=open(filename, “w”)
#File contents for PBCS Load

FILE.write(“E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\Detailed_Scripts\HFM_to_PBCS_Local_Detail.bat>E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Output\HFM_to_PBCS_Local_Detail.txt”+” “+ MONTH +” “+ YEAR + ” ” + TARGET + “_” + LID + “.dat”)

FILE.close()

#Execute Batch Script to load local data to PBCS
import os
import subprocess
import shutil
os.chdir(r”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files”)
myScriptName = r”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\HFM_to_PBCS_Local_Execution.bat”
command = (myScriptName)
fdmAPI.logDebug(“Running PBCS Local_$ Load: ” + command)
p = subprocess.Popen(command)
retcode = p.wait()
fdmAPI.logDebug(“PBCS Local_$ Load completed”)
#———————End Act_to_File_Local_$————————–#

#———————-Begin Act_to_File_USD————————–#
elif fdmContext[“LOCNAME”]==”Act_to_File_USD”:
#Set Variables
POV = str(fdmContext[“PERIODNAME”]) #POV = the Period Name selected in FDMEE example Jun-14
MONTH = (POV[:3]) #MONTH and YEAR are assigned using the slice command against the POV
YEAR =(POV[-2:])
LOC = str(fdmContext[“LOCNAME”])
LID = str(fdmContext[“LOADID”])
TARGET = str(fdmContext[“TARGETAPPNAME”])
#File creation for PBCS Load – Local
filename=”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\HFM_to_PBCS_USD_Execution.bat”
FILE=open(filename, “w”)
#File contents for PBCS Load
FILE.write(“E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\Detailed_Scripts\HFM_to_PBCS_USD_Detail.bat>E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Output\HFM_to_PBCS_USD_Detail.txt”+” “+ MONTH +” “+ YEAR + ” ” + TARGET + “_” + LID + “.dat”)
FILE.close()
#FILE Management
#Execute Batch Script to load local data to PBCS
import os
import subprocess
import shutil
os.chdir(r”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files”)
myScriptName = r”E:\Oracle\PBCS_Data_Loads\Scripts_Data\HFMtoPBCS\Batch_Files\HFM_to_PBCS_USD_Execution.bat”
command = (myScriptName)
fdmAPI.logDebug(“Running PBCS USD Load: ” + command)
p = subprocess.Popen(command)
retcode = p.wait()
fdmAPI.logDebug(“PBCS USD Load completed”)

#———————–End Act_to_File_USD————————–#

To summarize, the script is executed when we run a Data Load Rule for certain Locations.

The AftLoad script runs after FDMEE has pulled data from HFM, mapped it and written (loaded) it to a file.

The Jython script creates a batch script with parameters that call another batch script and passes the parameters as the variables.

In the end there is not that much too it. Some setup time and testing but the overall process runs pretty quick and the client is happy with the outcome.

I hope you found this post helpful.

Regards,

Scott Williams

Oracle PBCS – EPMAutomate v15.08 Encryption now available

Hello all,

Quick update.

EPMAutomate version 15.08 includes password encryption.

You may need to upgrade, but it is worth it.

Run the command to encrypt the password and store it in a file.

Update your script(s) by replacing the hard-coded password or the variable to point to the password file (path and file).

Syntax per Oracle documentation:

epmautomate encrypt P@ssword1 myKey C:\mySecuredir\password.epw

It actually works!!

Screenshot with password reference in a script:

Check the URL for details:

http://docs.oracle.com/cloud/latest/pbcs_common/CSPGS/apbs05s01.html

Regards,

Scott Williams

Oracle PBCS – Data Load Toolset #2 – Export & Import Data/Meta Data through the web interface

Hello,

Welcome to installment #2 in my series on loading data to Oracle Planning and Budgeting Cloud Service (PBCS).

In this installment I will cover the exporting and importing of both data and meta data to PBCS from flat files through the web interface.

Series:

Installment #1 – Smart View to load data and meta data

Installment #2 – Export Data/Meta Data and Import Data/Meta data from the web interface

Installment #3 – FDMEE data loads

Installment #4 – Automated loads

Data and Meta Data:

I will cover four topics:

Meta Data Export
Meta Data Import
Data Export
Data Import

——————-Meta Data——————-

Meta Data Export

Meta Data can be extracted from the web into a file.

Helpful hint: If I need to load Meta Data from file and I don’t know the file layout I will run an extract to get it.

Exporting Meta Data is very simple.

In this example I am using the Standard (soon to be legacy) Interface instead of the Simplified Interface.

Select the Dimension and then run it.

The file is saved to the default directory for downloads defined in my web browser.

File layout (and template for load)

Meta Data Import

(Using the file I just exported)

Edit and save the file.

Select Import Metadata from File

Select the Dimension to import.
Map to the file to load.
Then Validate to verify syntax.

Run the load:

Summary of results are displayed.

Errors can be viewed by clicking on the “View Status” button.

Also with a Meta Data load, you need to remember to refresh Essbase..

——————-Data——————-

Exporting Data

Exporting data is almost as simple as exporting meta data.

Helpful hint: If I need to load data from file and I don’t know the file layout I will run an extract.

Leveraging the Simplified Interface:

The Create button allows me to create a job or one time process.

On the options screen if I select “Planning Outbox” as my Location, then I can save this process as a Job. If I intend to run this process through the scheduler or scripting then I need to save this as a Job. I will cover the use of jobs and automation in installment #4.

I selected “Local” which tells PBCS that I want to save the file to my computer.

Straight forward selection:

Plan types
File Type – Delimiter

Selecting the dimension members is a little more involved.

You can select members or use functions.

From the screen shot below you can tell that I selected “Level 0 Descendants” by looking n the left hand panel.

The Point of View selection:

Go through each tab for the remaining dimensions and select the member(s) &/or function(s).

The main page shows all member and function selections.

Then Export

File is downloaded to my computer.

Output:

Importing Data

Finally we load a data file through the web.

Navigate to the Console:

Under Actions select Import Data

Select Create

Import Data Options:

Location:

Local – Allows you to map to a file. You will use this more often when doing a manual load.

Planning Inbox – The planning inbox is a location on the cloud. You would need to load your data there prior to running a manual load. I used the inbox for automation.

Source Type: There are different file layouts. Screens shots are pasted below.

Planning layout

Note: Able to choose delimiter

Sample Layout

Note:

1 dimension on rows
1 dimension on columns
The rest of the dimensions are assigned POV.

Essbase layout

Note: The delimiter option is gone.

Unhelpful hint: We are no longer able to export data in the Essbase layout. Which tells me that there is a chance that it may not be supported layout for load in the future.

Each column represents a dimension.

This is an “out of the box” currency application, which is why HSP_InputValue is present in the Essbase file load.

Loading the data:

After selection the Location, Source Type then select the file for load.

Run the Validate step to verify file syntax.

Select the Import button to begin the load process.

Viewing status of a load:

The status of the file load can be viewed by selecting the Jobs tab (left hand panel).

The Validate and Import Data are visible on the right hand side.

If there is an error the status will read Error. Click on the item to view the errors.

Errors are displayed in the following format.

I hope you found this installment informative and helpful.

Coming soon FDMEE on PBCS.

Regards,

Scott Williams

Oracle PBCS – Data Load Toolset – Smart View to load data and meta data

Hello,

Welcome to installment #1 in my series on loading data to Oracle Planning and Budgeting Cloud Service (PBCS).

In this series I will cover the current Oracle supported methods for loading data and meta data to PBCS.

How do we load data to the cloud?

We either input data or load a file.

Can we source from a table or view?

At this time we either input data directly or load a file.

Let me show you the options.

Installment #1 – Smart View to load data and meta data

Installment #2 – Export Data/Meta Data and Import Data/Meta data from the web interface

Installment #3 – FDMEE data loads

Installment #4 – Automated loads

Series 1 – Smart View

Direct loads:
Depending on your application size manual maintenance may be the most efficient.

The Oracle Smart View add-in for MS Excel is an extremely powerful tool for both users and administrators.

Meta Data (Administrators only)

1. Select the dimension(s) to edit

2. Make your updates

Updates:

Parent/Child layout
Type or past in member names
Active drop down menus for selecting member properties

Advantages:

Move members just by changing the parent in the spreadsheet
Create alternate hierarchies

Current Smart View limitations (These task can be completed through the web interface):

Attribute assignment
Member renaming
Member deletion

Member Properties drop down menu.

3. Submit the updates to the relational database

4. Don’t forget to refresh the Essbase application

Data (Administrators and Users)

Data can be entered through Forms and Ad hoc connections.

1. Select your vehicle for data entry

Open a Web Form (data entry template)

Forms may have Business Rules attached to them. (Be sure you know your companies business process for data entry)

Or create your own input sheet using Ad hoc analysis.

2. Enter the data

Since this is a planning connection we can enter data and non-numerical data.

3. You need to Submit

This is the end of this installment.

Coming soon:

Installment #2 – Export Data/Meta Data and Import Data/Meta data from the web interface

Installment #3 – FDMEE data loads

Installment #4 – Automated loads

Best Regards,

Scott Williams

	mr scott i williams on Meta Data Management in Data M…
	Jake Turrell on Meta Data Management in Data M…
	L Autrique on ClearCube EPBCS/PBCS…I h…
	Corey Cain on Oracle PBCS – Data Load…
	mr scott i williams on Oracle PBCS – Data Load…

EPM4ALL

When did you get here?

Data

Data Management transformations on text value loads to EPBCS/PBCS

Data Management :