Introduction
Architectural overview
Oracle EPM Cloud Adapter is retrieving metadata and data from the Oracle EPM Cloud source by starting jobs at the source’s side and later downloading metadata and data files. The extracted data is then transformed and loaded into a fact database that is connected to a SSAS cube. This cube providing the information to show reports in the CXO Dashboard.
In order to define which data should be extracted when, the integration administrator sets up the adapter using a web-based Configuration UI. This UI also allows applying some filtering and transformations on the extracted data. Once the Adapter is set up, the CXO Software Agent will (per user-defined extraction schedule) invoke the adapter to start the extraction process.
Prerequisites
The following components already have to be installed
CXO version 20.4.4 or later
CXO Identity provider
.NET 5.0 has to be installed
Account to connect from the adapter to the Oracle Cloud system
Installation (under construction)
(Details to be added)
Installing Oracle EPM Cloud Adapter
Configure the license key
Create a new source system
Configuring Oracle EPM Cloud system
User guide
Setting up Oracle EPM source (under construction)(Details to be added)
Two sources should not share a fact database - we do some rudimental checks, but don’t fully guarantee configuration UI prevents creating two sources with the same fact DB
Configuring the adapter
Configuring the adapter is done using the web-based Configuration UI. The access to this the UI is limited only CXO Admins to the users from user group specific during source creation - other users are not authorized to see these pages.
After opening the Configuration UI, all the Oracle EPM Cloud sources are visible on the left. For the currently selected source, the navigation on top leads a specific aspect of the adapter configuration: Connection, Dimension Mappings, Metadata, Data, Scheduling, and Logs. All dates & times in this UI are shown in the user’s local timezone.
Configuration
The goal of this page is to set up the connection to the Oracle system so that the adapter knows from where (and using what credentials) the data can be extracted.
API URL: access point from the Oracle Cloud, the URL used to log into Oracle Cloud (see FAQ for more details)
Cube: name of the oracle cube from which the data is extracted, found in Oracle Cloud under Tools → Overview → Cubes.
Application name: Application that contains the given cube, found in Oracle Cloud under Tools → Overview
User: credentials used by the adapter to retrieve information from the Oracle Cloud.
Dimension Mappings
The goal of this page is to define how CXO dimensions are mapped to the Oracle dimension. There are a few limitations to the mappings:
Some CXO standard dimensions (such as Account) are by default mapped to a certain Oracle EPM standard dimensions.
CXO custom dimensions (such as A01) can be mapped to an Oracle EPM analytical dimensions, or left unmapped
CXO dimension Value can be mapped to Oracle EPM dimension Currency, or left unmapped
If an Oracle EPM dimension has an alias defined in the Oracle EPM Cloud system, then this dimension alias also needs to be added when making the dimension mappings
More info about PBCS dimensions can be found here, and FCCS dimensions here
Aggregation
In some oracle systems the data on child members does not sum up properly to the value on parent member for Entity dimension.
In such situation you can disable default aggregation for entity dimension and specify data extraction for all members (instead of leaf members only) for entity dimension. using “IDescendants” method
Metadata
The goal of this page is to define general properties for the metadata extraction job and filters that are applied when extracting the metadata from Oracle EPM Cloud.
Export job general settings
Export job name: name of the job that extracts metadata from Oracle EPM Cloud. Useful to change mainly for the troubleshooting purposes.
Export job file delimiter: delimiter used in the downloaded metadata file (CSV), will be typically either comma (,) or semicolon (;)
Dimension Alias: Column name in the extracted CSV that corresponds to a dimension alias
CXO allows for 3 dimension aliases to support multiple languages. When metadata is extracted from Oracle EPM, aliases in CSV are typically stored in a column named “Alias: Default“ or “Alias: English“.
Here’s a snippet of the extracted metadata CSV file, to illustrate the file structure
| Code Block |
|---|
Account, Parent, Alias: Default, ... <rest of the header row> FCCS_System Account, Account,, ... <rest of the data row> Exchange Rates, Account,, ... ... FCCS_Inventories, FCCS_Current Assets, ... A410, FCCS_Inventories, Raw materials, ... A420, FCCS_Inventories, Work in progress, ... |
Filters
By default the whole dimension hierarchy will be loaded. As this might be too much data, there is an option to exclude unwanted parts of the hierarchy (or unwanted members). This can be done by adding filters. For example, if filter for Entity Europe is added, then member Europe and all of its descendants will be excluded from the metadata extraction,
Data
The goal of this page is to define general properties and filter that are applied when extracting the data from Oracle EPM Cloud
Export job general settings
Export job name: name of the job that extracts data from Oracle EPM Cloud. Useful to change mainly for the troubleshooting purposes
Export job file delimiter: delimiter used in the downloaded metadata file (CSV), will be typically either comma (,) or semicolon (;)
File structure
In order to load the data in CXO, CSV file structure for the extracted data from Oracle EPM has to be defined. The structure is best explained by looking at the following fictive example
| Code Block |
|---|
Account|Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec|Point-of-View|Data Load Cube Name DSO|#missing|#missing|#missing|#missing|#missing|#missing|45|45|45|45|45|45|"FY15|Forecast|Working|000|P_000"|Plan1 1290|27010144.3|27010144.3|27010144.3|27010144.3|27010144.3|27010144.3|#missing|#missing|#missing|#missing|#missing|#missing|"FY15|Forecast|Working|000|P_000"|Plan1 1310|5751347.75|5702141.46|5709563.28|5716113.26|5705995.47|5701355.24|2929495.285|3069860.55|29861952.18|26789869.71|29056293.78|36672041.505|"FY15|Forecast|Working|000|P_000"|Plan1 1360|-1150269.42|-1160110.42|-1161594.96|-1162904.94|-1164928.94|-1165856.94|-1312331.731425|-1465824.7743|-1615134.5352|-1749083.883675|-1894365.352575|-2077725.5601|"FY15|Forecast|Working|000|P_000"|Plan1 1710|2314782.58|2314782.58|2314782.58|2314782.58|2314782.58|2314782.58|#missing|#missing|#missing|#missing|#missing|#missing|"FY15|Forecast|Working|000|P_000"|Plan1 |
From the first row we see that in each line of the CSV:
First value corresponds to the Account, meaning that each row of data corresponds to the same account
Next 12 values correspond to the Period, meaning that each column of data corresponds to the same period
Next value corresponds to “Year | Scenario | Version | Entity | Product“, meaning that it defines the rest of dimensionality for this row of data
Therefore, in order to extract the data from Oracle EPM Cloud, the file structure is defined by choosing from all mapped dimensions which ones are for rows, for columns, and the rest of the point of view.
Selections
For each dimension, a relevant subset data can be specified by using Oracle EPM’s Member Selection string. Typical values would be:
Getting data for all base members, e.g., ILvl0Descendants(Account) for Account dimension
Getting data only for one specific member, e.g, Working for Version dimension
Getting data for a few specific members, e.g, Europe, US, Asia for Version dimension
Some resources for a deeper dive on member selection: member API docs, export data API docs
Scheduling
The goal of this page is the define which the moments when metadata and data will be extracted in which moments.
Schedule can be on a daily or weekly basis, and the extracted data set is defined per combination Period, Year, and Scenario (e.g. Q1 2020 Actual)
Logs
The goal of this page is to help in troubleshooting problems when running the adapter. A few things know:
Firstly only recent log entries are loaded, as the user scrolls down, more entries are loaded.
Filtering log entries is possible to narrow down of the amount of long entries
Log entries are not updated live, but only on when the page is loaded or filtering is applied.
FAQ
The credentials don’t seem to work, even though I can log in to Oracle Cloud in my browser?
Check API URL, it should contain only protocol and domain (e.g., https://something.else.oraclecloud.com/) and no subdirectory (so not https://something.else.oraclecloud.com/HyperionPlanning/)
Check if there is a domain when logging in to Oracle Cloud. If so, then API URL and username should contain the domain.
Examples where a455368 is the domain within Oracle Cloud - API URL: https://something-a455368.else.oraclecloud.com/, username a455368.john.doe@company.com
The web application shows a message that the configuration database needs migration. How do I resolve this issue?
Login to the VM on which the Oracle EPM Adapter is installed and open a command prompt in the Extraction folder in Program Files (default: C:\Program Files\CXO Solutions\CXO-Cockpit\Oracle Cloud EPM Adapter\Extraction)
Run the command line tool with migrate-database command: CXO.Adapter.OracleCloudEpm.Cli.exe migrate-database
The database should now be migrated and after reloading the web application should no longer ask to migrate the configuration database
The export job in oracle EPM failed. How to find the problem
Check logs in CXO Oracle EPM Adapter
Check if job was executed successfully in Oracle EPM Adapter. You can see it in the jobs page.
