Meta Data Management in Data Management for PBCS/EPBCS

In the December patch release for PBCS and EPBCS functionality has been introduced to allow for Metadata builds using Data Management. This is a wonderful addition to our tool box.
It is pretty straight forward to use.
Here is basic run down.
Loading a flat file to build a hierarchy:

I am going to add a new sub hierarchy to the Entity dimension.

Navigate to Data Management
(By the way I heared a rumor that the Data Management GUI is being written to work through the Simplified Interface. So, it would accessible through a Card or Cluster. Also heard Oracle may be consolidating the Job Consoles for planning and DM)

There is a new option under the Add menu in the Target Application.

The Prefix is optional, but is helpful since the list displays in alphabetical order.

The Dimensions show up. Also in case you noticed, now Smartlist can be built using Data Management.

I am building a hierarchy in the Entity dimension

If you start looking through all the dimensions you will notice on thing in common.
For every dimension:
Alias: Default = ICP
The dimension name = ACCOUNT (in my screenshot Entity=ACCOUNT)
Parent = “ENTITY”
Don’t change the default values for these three!!!

Select the other properties you want to load.
I selected Data Storage and assigned UD2 as the value for Data Table Column Name.
Next move on to Import Format

File Type must be set to “Delimited – All Data Type”
File Delimiter – select from the drop down.
I mapped the columns to my file:
Alias is in column 4 and the Target is “Alias: Default”
I leave blanks for Data
Storage is not in my file but I map it to column 1, so I can take care of it in mappings later on.
Entity is column 2 and the Target is “Entity” – which is the member
Parent is column 1 and the Target is “Parent”
Header – I am using the Target of Skip so Data Management will ignore the header row in my file. (Logic is skip row where value in column 2 = ‘Member’)

Next step would be to create the location:
Very straightforward.

Let’s move on to “Data Load Mapping”
My example is simple * to * mapping with the exception of storage.

Alias: Default Mapping

Although we are not loading data values (such as amounts), we still need to map Data.

Data Storage, if you recall, there is no value in my source file. I want to map the value with logic. I use SQL to set the storage for the parent to “Dynamic Calc” and the Lv0 to “Never Share”.

I am very pleased to see that we can use logic to map properties that are not in the file. I have all types of ideas to complete Meta Data loads leveraging a data load files.

Entity(Member) Mapping * to *

Parent Mapping * to *

Almost ready to load…now let’s create the rule.

Name it as you please – I chose Entity, since that is the dimension I am working with.
Category, is required, but has no bearing on the values you load since is it not data. You just need to remember which Category you use so you can see the values in the Workbench.
Select the Import Format
Select the File Name

Then move to the ‘Target Options’ tab

Enter the Dimension Name
Refresh Database Yes or No (if you are running multiple dimension builds you may want to wait and run the Refresh after the last load).

Time to Run it:

The start and end period are required, however since you are not loading data it is merely a bucket in Data Management.

Process Details look good:

Now lets look at the Workbench:
Couple of items:
1. The header row has been skipped (cool!)
2. Storage mapping has been applied (Jackpot!!!)

The Export was also successful, so let’s look at planning.

The new hierarchy looks good.

I set the Refresh to run, better check that too.

Success!!!!

I hope this is helpful and appreciate you taking a look.

ClearCube EPBCS/PBCS…I have been waiting on this for so long.

Hello All,

The July 2017 patch-set for EPBCS/PBCS includes new options to execute Clear Cube definitions/jobs. Oracle has been slow to get the new features out. The delay was driven by the need to add options to address the relational data as well as the Essbase data.

Now that the functionality has been updated/enabled what can we do?

We can use the REST APIs and EPMAutomate to call the Clear Cube job(s) externally…FINALLY!!!!

1st – The new look for the Clear Cube definition inside the Simplified Interface:

BEFORE:

ClearCubeOLD

AFTER:

ClearCube

2nd – An observation:

Sometimes all the Clear Options are available and sometimes they’re not. (Basic or Advanced)

ClearCubeDefinitionBasic

ClearCubeDefinitionAdvanced

3rd – Using EPMAutomate to run the Clear Cube Job

Install the EPMAutomate version 17.07.74 (or greater, depending on when you read this).

Per Oracle’s updated documentation:

clearcube

Deletes specific data from input and reporting cubes using the settings specified in a job of type clear cube. This command does not delete the application definition in the application’s relational tables. See Clearing Cubes in Administering Planning for Oracle Planning and Budgeting Cloud.

Usage: epmautomate clearcube JOB_NAME, where: JOB_NAME is the name of a job defined in the application.

Example: epmautomate clearcube ClearPlan1

The command is straight forward and works!!!

EPMAutomate

I hope this brief update was helpful.

RELEASE DATES:

Application to Test environments – July 07,2017
Application to Prod environments – July 21, 2017

/*****BTW if you are testing the patch updates and find an issue; be sure to include the word “Regression” in you SR. This will help route your ticket to Oracle Product Development.*****/

Thanks for reading.

Scott Williams

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

PBCS – New Financial Reporting Web Studio

——Update February 18, 2016—————-

I received some additional information related to this post for the new FR Web Studio.

As stated approximately 85% of the functionality has been moved to the new Web client.

Which begs the question, what is the remaining 15%.

Here you go:

The following functionality is not currently available in FR Web Studio:

•Related content
•Conditional formatting and suppression
•Auto Calculations
•Sorting
•Row/Column templates, saved/linked objects
•Edit multiple reports at the same time

•‘Same As’ member selection function
•Grid Format Cell > Replace

The plan is to move that functionality into the new studio sometime in Summer 2016.

The FR Studio Client is planned to be available for a few months after the Summer release. FR reports in the web and Windows Studio can be updated interchangeably, there is no migration or conversion, as the report definition format stays the same.

———–End of update——————–

Hello,

It has been awhile since I posted anything. I have some drafts, but need more hands on examples to finish them.

In the meantime, I am re-posting details Oracle just sent out for the March PBCS release. I know that may not be helpful. Normally I at least like to validate and tinker with the product rather than regurgitating public knowledge. Not this time however, perhaps some of you have not heard about this or read your email(s).

Per Oracle in regards to reports for PBCS:

***Update although the statement from Oracle says “March update” they also state “The next monthly update for your service will be applied to your test instance on Friday, February 5 and to your production instance on Friday, February 19.” ****

“New Financial Reporting Web Studio

The March update of the service will include the new Financial Reporting Web Studio. The Financial Reporting Web Studio is a web-based report authoring solution that has a similar look and feel as the current Windows-based Financial Reporting Studio. Additionally, it incorporates some usability improvements and report design paradigms. This release includes about 85% of the functionality currently available in the Windows Financial Reporting Studio.

The existing Windows Financial Reporting Studio will still be available. Because the report definitions remain the same, you can edit existing reports using the Windows or web version of the Financial Reporting Studio.”

So take it for what it is worth. I have not had a chance to validate the new functionality for Financial Reporting Studio. However, considering in a 10-minute span last week I lost my connection to PBCS in the reporting studio client 3 times, I am hoping for improvement.

Here is the URL for the documentation for the web based client:

http://www.oracle.com/technetwork/middleware/bi-foundation/fr-webstudio-2874377.pdf

Also here is the screenshot I grabbed from the documentation…gives you a little idea of the look and feel.

HFR_WEB

Also per Oracle:

“Upgraded Financial Report Rendering Engine

Starting with the March update of the service, Financial Reporting will use an upgraded charting engine to improve report visualization. Because of the inherent differences between the old and new rendering engines, this upgrade will have some impact on the rendered chart output and behavior. While Oracle has focused on maintaining the existing charting functionality, chart outputs from the upgraded engine may differ from those created using the old engine. “

Hopefully I will get something substantial out to you very soon.

Regards,

Scott Williams

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.09 loads to FDMEE on PBCS

Oracle released EPMAutomate v15.09 this month.

Although you don’t have to upgrade EPMAutomate you should be aware of one new key feature/change and the potential impact if you don’t upgrade in the next few months.

History: 1st came EPMCopy.exe as the automation tool for PBCS. Very soon after EPMAutomate was released and most of the functionality was removed from EPMCopy.

EPMCopy was left standing with a single purpose. The remaining purpose was to talk to FDMEE on PBCS. It would load files to FDMEE Inbox and execute Data Load Rules.

Not a big deal, however if you had to do multiple steps, like clear data in planning, load data using FDMEE and finally calculate and/or aggregate the data you had to leverage two tools in a script:

Sample script:

____________________________________________________________

Call epmautomate login %USER% %PASSWORD% %URL% %DOMAIN%

Call epmautomate runbusinessrule Int_Clear_Act_data_befload_from_HFM Period=%Period% Year=%FY% GCurrency=”Local_$”

REM FDMEE PROCESS:

C:\Oracle\EPMCopy.exe USERNAME=%USER% PASSWORD=%PASSWORD% IDENTITY_DOMAIN=ABC123 FROM=E:\Oracle\PBCS_Data_Loads\Load_Files\HFM_to_PBCS_Local_FY%2_%1.dat TO=https://ABC123.pbcs.us2.oraclecloud.com/files/inbox/HFM_to_PBCS_Local.dat LOADDATA=HFM_to_PBCS_Actual:%1-%2:%1-%2:REPLACE:STORE_DATA

Call epmautomate runbusinessrule Int_Agg_data_aftload_from_HFM Year=%FY% GCurrency=”Local_$”

____________________________________________________________

So you can see EPMAutomate and EPMCopy being called right? I know not a big deal.

Here are some issues:

Minor issue: Oracle is not supporting any new development on EPMCopy. If you read my post on EPMAutomate encryption, you are aware that you can now encrypt passwords. The encryption feature does not work with EPMCopy, so you are stuck with passwords hardcoded in the script. (Assuming you are not using a 3rd party tool or feature I am not aware of)
Major issue: per Oracle “the File Transfer Utility will be deprecated starting with the January 1, 2016 service update.” As a result you will need to update any scripts using EPMCopy.

EPMAutomate now works great with FDMEE.

Here is the same script from above re-written using EPMAutomate.

____________________________________________________________

Call epmautomate login %PBCS_UID% %PBCS_PWD% %URL% %PBCS_DM%
REM IF %ERRORLEVEL% NEQ 0 goto :ERROR

Call epmautomate runbusinessrule Int_Clear_Act_data_befload_from_HFM Period=%Period% Year=%FY% GCurrency=”Local_$”
REM IF %ERRORLEVEL% NEQ 0 goto :ERROR

REM FDMEE PROCESS:
CALL EPMAUTOMATE UPLOADFILE E:\Oracle\PBCS_Data_Loads\Load_Files\HFM_to_PBCS_Local_FY%2_%1.dat inbox
Call EPMAUTOMATE RUNDATARULE HFM_to_PBCS_Actual %1-%2 %1-%2 REPLACE STORE_DATA HFM_to_PBCS_Local_FY%2_%1.dat

Call epmautomate runbusinessrule Int_Agg_data_aftload_from_HFM Year=%FY% GCurrency=”Local_$”

____________________________________________________________

Here is the url for the EPMAutomate command line:

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

I hope this is helpful information and you read it before 01/01/2016.

I would hate to start the new year off with calls, emails and texts about jobs failing.

Thanks for visiting.

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?

Meta Data Management in Data Management for PBCS/EPBCS

ClearCube EPBCS/PBCS…I have been waiting on this for so long.

Data Management transformations on text value loads to EPBCS/PBCS

Data Management :

PBCS – New Financial Reporting Web Studio

Oracle PBCS – EPMAutomate v15.09 loads to FDMEE on PBCS