Installation
...
Prerequisites
The following instructions are applied to both PBCS and FCCS. Instructions which are specific to FCCS only have been mentioned.
CXO version 21.1.3 (or higher)
Install CXO Identity Provider
Install Oracle EPM Cloud Adapter
Install pre-requisitesprovider
.NET
56.0
Hosting Bundle:
https://dotnet.microsoft.com/download/dotnet/5.0 Run installer
| Info |
|---|
Important: Restart server after installing .NET core hosting bundle |
...
Setting up
Install Oracle EPM Cloud Adapter.
Open CXO Configurator>Maintenance and click Register Services and Websites.
...
Create IIS website for Oracle Epm (Application Pool should have "No Managed Code")
...
Select Register Oracle EPM Adapter Api to add service account details.
Create Client in the CXO Identity Provider.
Option available only for version 21.2.0 or higher.
Identity Provider
...
Configuration
...
Create new Generic Client.
...
Check “Allow Offline Access” checkbox
Select Allow Offline Access option.
Add the following scopes:
User profile
User groups
Add Redirect
...
URL(
...
s) in the following format:
{OraclEPMAdapterUrl}/signin-oidcAdd Post Logout Redirect
...
URL(s) in the following format:
{OraclEPMAdapterUrl}/signin-oidc-auth
| Info |
|---|
...
RedirectUri’s are case sensitive so it’s good practice to use lowercase in all URLs. |
...
CXO
...
Configuration and
...
Source Creation
...
To
...
use OracleEPMAdapter you need a license key with: Oracle EPM Planning or Oracle EPM Financial Consolidation and Close.
Create a new source system in the SSM for the correct source type: Oracle EPM Planning / Oracle EPM Financial Consolidation and Close. This should create:
Fact database
SSAS cube
...
Open command line as Administrator and run configuration database creation command
...
, for example:
.\CXO.Adapter.OracleCloudEpm.Cli.exe create-configuration-db server-name:cxo-dev-sql02 database-name:oracleEpmConfigTest authentication-type:Proprietary username:cxo password:somePassword
Parameters:
o server-name:
...
Name of the server on which the database should be created.
o database-name:
...
Name of the database to be created.
o authentication-type: windows / proprietary
o username: SQL
...
username (if authentication-type == proprietary).
o password: SQL user password (if authentication-type == proprietary).
...
Refer CXO.Adapter.OracleCloudEpm.Cli.exe help for detailed description of the command.
Run set-authentication-settings command, for example:
...
.\CXO.Adapter.OracleCloudEpm.Cli.exe set-authentication-settings idp-authority: https://cxoUrl/idp idp-client-id:clientId idp-client-secret:secret idp-client-allowed-user-group:ADM
...
Refer CXO.Adapter.OracleCloudEpm.Cli.exe help for detailed description of the command.
Run create-source-in-adapter command
...
for example:
.\CXO.Adapter.OracleCloudEpm.Cli.exe create-source-in-adapter source-type:pbcs source-name:source factdb-name:factdb-name factdb-server:factdb-server factdb-authentication-type:1 ssas-server-name:ssas-server-name ssas-database-name:ssas-database-name ssas-cube-name:ssas-cube-name
Important: The name of the source should be the same as name of the source in CXO
...
Refer CXO.Adapter.OracleCloudEpm.Cli.exe help for detailed description of the command.
Restart IIS Application Pool for Oracle EPM Adapter
...
Oracle EPM system configuration
...
.
Note: UserGroup specified in “idp-client-allowed-user-group” should be created in CXO. Only users from that group are allowed to open CXO Oracle Cloud Adapter.
Important: The name of the source should be the same as name of the source in CXO. Note that two sources should not share a fact database - we do some rudimental checks, but do not fully guarantee if configuration UI prevents creating two sources with the same fact DB.
Oracle EPM System Configuration
Make sure you have an account to connect from the adapter to the Oracle system (A user with rights to create export job is required - admin user)
Configure metadata export job
Configure data export job
See sections below for more details
Configure metadata export job
Steps to perform:
...
Click Overview
...
.
...
2. Go to Under Dimensions tab, click Export button on the right.
...
3. On the export metadata screen, you will see the existing CXO job, might be others as welljobs.
a. create new job. See two screenshots below
...
Click Create.
...
b. Select Location to Outbox and select all dimensions which should be extracted.
...
The export job name created in Oracle Cloud and export job file delimiter has to must be later on used in Oracle EPM Adapter in Metadata General Settings.
...
Configure data export job
...
Click Overview
...
.
Choose Export Data
...
.
Click Create
...
.
Specify the following values:
Location: Outbox
Pick right cube
Choose a delimiter, preferably set it to
...
Other and use “|”
Smart Lists are not used currently
Dynamic Members: Exclude
Decimals:
...
None (or discuss with the customer)
Slice definition:
...
Take Account on rows, Period on columns, set default slice values for all dimensions
...
including POV.
Note:
...
This configuration will not be used in the actual data extraction, as it will be input to the job API call.
...
The export job name created in Oracle Cloud and export job file delimiter has to must be later on used in Oracle EPM Adapter in Metadata General Settings.
...
Oracle EPM
...
Adapter Configuration
Configure connection.
Cube
...
Use host url for Api Url
...
without any path for example: https://epm a000000.epm.em2.oraclecloud.com.
Application name is the same you see in
...
Oracle.
For credentials use username combining domain + “.” + username
Example: for picture below username would be
...
a000000.xxxxxxxxxxxxx
...
Configure dimension mappings. Most standard dimensions would be mapped already – you need to map the custom ones. Number of custom Oracle Dimension doesn’t matter and are only used
...
by CXO. Fill in the alias corresponding to custom dimension name.
Example:
...
...
Configure metadata.
Please follow instructions for FCCS here and then proceed.Go to Overview
...
> Dimensions
...
>Extract.
...
...
Configure Data.
...
Should be filled in consultation with the customer.
- Multiple members are supported and should be separated by comma
...
- Do not use quotes around members (even if they contain spaces)
...
- Do not use “@” before selector methods (like
@
...
ILvl0Descendants(YearTotal))
Fill in Account dimension on Row and Period on Column.
Select all other dimensions in any order and fill in desired selectors.
The Order of POV dimensions is important –
...
to parse data extraction file using it. To figure it out fill in at first in a random order, finish configuration and return to the next step after you have successfully run an extraction. (
...
You will see in the logs that data extraction
...
has finished successfully but there
...
are warnings of zero records being mapped).
Download the export data file for your last extraction (it should have at least one record).
...
Add
...
.
...
zip extension to file if necessary.
Open a file. Identify for any record
...
“Point-of-
...
View” member (it would be few members enclosed in quotes). Identify to which dimension each members belongs. Reorder your
...
POV dimensions in adapter in the same order.
...
...
Configure schedule.
Create a new schedule
...
. Name and schedule
...
are up to the customer (or
...
CXO) and not connected to
...
Oracle configuration.
...
- Initially its best to have “Extract Metadata” enabled.
Create a data slice.
- Multiple members separated by commas are supported.
...
- Do not use quotes.
Selector functions are not supported yet (e.g. Children(YearTotal)) there is no warning for this so make sure there are not included in Data Slices. Use multiple members to replace such functions.
Example:
...
Schedule the time when metadata and data should be extracted from under the Scheduling tab. A schedule can be on a daily or a weekly basis, and the extracted data set is defined with a combination of Period, Year, and Scenario (for example Q1 2020 Actual).
Select the Full option to load the entire metadata. If there are smaller changes like a new dimension structure or if you need to perform only an incremental extraction, the option Incremental can be selected to get faster results. When this option is selected, although the entire metadata is loaded, only the delta/changes get added to the cubes list.
Click Run now to extract data and metadata.
...
You can see the progress of the extraction
...
under the
...
After successful extraction Process cube from SQL Server Management Studio (this is temporary solution. This process will be automated in the future)
Logging
The log level can be set in appsettings.json in "LogLevel" property (Both Web project and CLI project have own appsettings.json file)
Location of the log files:
CLI command {CXO-Installation-Dir}\Oracle Cloud EPM Adapter\Extraction\logs
Web application {CXO-Installation-Dir}\Oracle Cloud EPM Adapter\Web API\logs
Log files are split into two file types
nlog-all -contains cxo defined logs and logs generated by other Microsoft libraries. Usually it is more verbose
nlog-own-detailed -contains logs defined by cxo with more detailed information then info in “nlog-all“ and additionally filters logs created by Microsoft libraries.
Logging to the console in CLI tool
...
Logs tab.
Note:
Selector functions are not supported yet (for example, Children (YearTotal)).
2. Make sure these are not included in Data Slices.
- Use multiple members to replace such functions.