transforming data to be oracle epm ready with just a few clicks · 2019. 10. 9. · • improved...

Copyright © 2019 OneCloud, Inc. All rights reserved.

Webinar:Transforming data to be Oracle EPM ready with just a few clicks

QuinEddy


Agenda

Real-life Example

How can OneCloud help?

Approaches to Transformation

Demo

Q&A


Typical Approaches

• Manual - Leverage formulas and input templates to map data the EPM Cloud Format

• Tools - FDMEE and/or Data Management

• Custom Code - Combination of languages - Jython, PowerShell, etc


What is the EPM Cloud or Planning format?1. Proprietary format designed for Oracle EPM Cloud to import and export data.

2. Starts with a single “Sparse” dimension.

3. Contains a series of data columns that hold the “Dense” data.

4. Contains a unique nested “Point-of-View” column that embeds dimensional columns.

5. The source or target cube within EPM Cloud


What is the most portable format?Answer: Standard tabular data (aka CSV or tab delimited)





A simple easy approach to convert tabular data to the EPM Cloud format is required





• 3 primary batch scripts to load data from SQL Server to PBCS

• ~270 lines of batch code per script

• 100+ log entries created per each run and more if errors occurred

• 2 hours of weekly care & feeding to maintain automation environment

Day in the Life of an EPM Cloud Customer...


Fast to implement, Easy to use & Save time on production support!

Resilient Integration

Reduce complexity

Secure, scalablable & automatable

Requirements


Better TogetherSome Solutions to consider

9

Build with alternate technologies

• On-premise job schedulers (CA Autosys, Control M, Automate)

• Improved custom-coded solution with alternatives such as Python, Java, PowerShell

Oracle Hyperion on-prem solution alternatives

• Integrate with FDMEE

OneCloud

• iPaas technology to integrate and automate on-prem and cloud applications


OneCloud sounds like an option, but how does it support transformations?





OneCloud Transformations

• Prepare data prior to loading into a target

system

• Massage tabular data to get into the

required format

• Split data sets based upon content in a

record

• Setup rules-based filtering

• Combine data sets from multiple sources

What are they and how do they help me?


OneCloud Transformations

• Find and Replace - Find and replace column values in the data.

• Column Filter - Filter the CSV columns with headers that match the specified pattern

• Filter Rows - Filter rows of the CSV by a regex or exact match of one or more columns in the row

• Copy Column - Copy column from CSV

• Insert Column - Insert a column into a CSV

• Join Columns - Join multiple columns of the CSV and optionally discard the used columns

• Map Headers - Takes a list of headers, and a list of headers to replace them with. Separate headers with commas

and order matters.

• Split Values - Takes the specified column and value delimiter, and splits the column into multiple columns

• Stack Files - Stack Files takes a list of comma separated CSV files, and stacks the values of each on top of each other

in the order they are specified.

• Add Row Numbers - Adds row numbers to the DSV in the first column

Powerful operations to manipulate a tabular data:

• EPM Cloud Format Conversion - Bi-directional conversion of tabular to EPM format (and vice-versa)


Does it work for all “flat files”

• Data stored in plain text using a character set such as ASCII,

various Unicode character sets (e.g. UTF-8), EBCDIC, or Shift

JIS

• Consists of records and one record per line

• With the records divided into fields separated by delimiters

(typically a single reserved character such as comma,

semicolon, or tab; sometimes the delimiter may include

optional spaces)

• Where every record has the same sequence of fields

• Typical “CSV” sources: flat files, relational data report

outputs

Data needs to be proper “CSV”. What is proper CSV?

How are transformation built in OneCloud?

● Outputs from prior commands are available to all subsequent commands

● For transformations, OneCloud Commands provide file outputs that are used in subsequent transformations.

● Each command has a preview to see the results of the transformation.

What are OneCloud “Outputs”?

● Outputs are artifacts from previous Commands

● Outputs can be:○ Strings (eg: formatted HTML)○ Integers (eg: record counts)○ Lists (eg: List of Files)○ Dates ○ JSON (web services data exchange)○ Map (pair:value)○ File

● Outputs appear as OneCloud variables


Transformation Demo





Decision: OneCloud Recommendation of trusted advisor• interRel compared

implementation an on-going costs and OneCloud was a clear winner.

Fast to implement, Easy to use & Save time on production support!

Resilient Integration

Reduce complexity

Secure, scalablable & expandable

Requirements Met

Time to Market and ROI• OneCloud was fully

implemented, tested, and in production in 10 days

Functionality• Out-of-the-box integration with

Oracle PBCS and On-prem relational databases as well as many other technologies

Data Security• No data is stored in OneCloud• Data masking and encryption


Better Together

Use the webinar Q & A window to

submit your questions.


Better TogetherGetting Started

19

Product Documentation: https://docs.onecloud.io

Demos: https://www.onecloud.io/video-blog/

Support: [email protected]

MORE RESOURCESFREE TRIAL

Visit: www.onecloud.io

Or: https://app.onecloud.io/new-user

https://docs.onecloud.io

https://www.onecloud.io/video-blog/

mailto:[email protected]

http://www.onecloud.io

https://app.onecloud.io/new-user

transforming data to be oracle epm ready with just a few clicks · 2019. 10. 9. · • improved...

Documents