Loading EDMCS Hierarchies into Relational table – Hierarchy Flattening

 Following are the high level steps to be performed in order to load EDMCS hierarchy into relation tables.

1. Create Viewpoint for EDMCS Dimension Hierarchy
2. Download EDMCS Dimension Hierarchy into local computer or object storage
3. Load above downloaded file into table and then flatten hierarchy

Create Viewpoint for EDMCS Dimension Hierarchy

1.      Create Dimension Viewpoint Hierarchy extract

1.Login EDMCS and Navigate to Viewpoint

Company Hierarchy

     

2.Create Viewpoint Extract for this company dimension hierarchy

 Click on Inspect

3. Click on Dimension Company

4. Navigate to Extracts and create new

1.      5.Add required columns needed in extract

Download EDMCS Dimension Hierarchy into local computer or object storage

Here we will explore 2 options to extract data from EDMCS to local filesystem.

Option 1: Download using CURL commands

• .      Use curl commands to download extracts into local file storage.

Create comp_request_body.json with following content.

{
    "applicationName": "FinCloud GL",
    "dimensionName": "Company",
    "fileName": "Company.csv",
    "extractName" : "Company Alt Hier"
}

• .      Submit REST API call to download viewpoint extract

curl --user <IdentityDomain>.<username>:<password> -X POST -H
"Content-Type: application/json" -d @"C:\Users\XYZ\Documents\REST\comp_request_body.json"
https://<edmcshostname>/epm/rest/v1/dimensions/byName/extract

output:

 {"links":[{"rel":"results","href":"https://<edmcshostname>:443/epm/rest/v1/jobRuns/83930088-abd2-42cd-9f70-a4fe11792744"}]}

• .      Verify job status

curl --user <IdentityDomain>.<username>:<password> -X GET https://<edmcshostname>:443/epm/rest/v1/jobRuns/83930088-abd2-42cd-9f70-a4fe11792744/result

Output:

{"id":"83930088-abd2-42cd-9f70-a4fe11792744","description":"Extracting viewpoint Company_Region | V1 (60537370-25ff-4843-af5e-403c595bf21b).","origin":"EXTRACT_VIEWPOINT","status":"COMPLETED","result":{"success":true,"links":[{"rel":"results","href":"https://<edmcshostname>:443/epm/rest/v1/files/staging/Company.csv"}],"messages":"37 records extracted.\n"},"created":"2021-04-23T11:01:57.858Z","createdBy":"c31a0ecb-093e-437d-8c3b-8ab958d07071","lastModified":"2021-04-23T11:01:57.891Z","lastModifiedBy":"c31a0ecb-093e-437d-8c3b-8ab958d07071","links":[{"rel":"self","href":"https://<edmcshostname>:443/epm/rest/v1/jobRuns/83930088-abd2-42cd-9f70-a4fe11792744/result"},{"rel":"jobRun","href":"https://<edmcshostname>:443/epm/rest/v1/jobRuns/83930088-abd2-42cd-9f70-a4fe11792744"}]}

• .     Download file into local machine

curl --user <IdentityDomain>.<username>:<password> -X GET
https://<edmcshostname>:443/epm/rest/v1/files/staging/Company.csv
>> "C:\Users\XYZ\Documents\REST\CompanyHier.csv"

Option 2: Download using EPM Automate commands

Make sure EPM Automate tool is installed in the working computer.

• .      Login EDMCS

call epmautomate login %EDMUSER% %EDMPWDFILE%
%EDMSVR%

• .      Extract viewpoint dimension hierarchy

Syntax:
epmautomate extractDimension APPLICATION
DIMENSION EXTRACT_PROFILE FILE_NAME [connection=NAME]
Example:
C:\Oracle\EPM Automate\bin>epmautomate
extractDimension "FinCloud GL" Company "Company Alt Hier"
CompanyDimHier.csv

Output:

Processing...

Retrieving status...

extractDimension completed successfully

 Details: Extracting viewpoint Company_Region | V1 (60537370-25ff-4843-af5e-403c595bf21b).

• .      Download file to local computer

epmautomate downloadFile "[FILE_PATH]/FILE_NAME "

Output:

Processing...

100% completed

downloadFile completed successfully

Note: The file will be downloaded into the folder from which you run EPM Automate.

C:\Oracle\EPM Automate\bin

Load above downloaded file into relational table and then flatten hierarchy

• .      Load this file into local Oracle table 

CREATE TABLE HR.COMPANY_HIER
(
  NAME          VARCHAR2(30 BYTE),
  PARENT           VARCHAR2(30 BYTE),
  LVL        NUMBER,
  ENABLED          NUMBER
);

.      

• Build hierarchy flattening query

with
h as (
    select sys_connect_by_path(NAME,'/') ||'/' hier_path,
          NAME
    from HR.COMPANY_HIER
    start with parent = 'Alt Company'
    connect by prior NAME = parent
)
select h1.hier_path,
substr(h1.hier_path,2,instr(h1.hier_path,'/',1,2)-2) node,
substr(h1.hier_path,instr(h1.hier_path,'/',1,2)+1,instr(h1.hier_path,'/',1,3)-instr(h1.hier_path,'/',1,2)-1) lvl1,
substr(h1.hier_path,instr(h1.hier_path,'/',1,3)+1,instr(h1.hier_path,'/',1,4)-instr(h1.hier_path,'/',1,3)-1) lvl2,
substr(h1.hier_path,instr(h1.hier_path,'/',1,4)+1,instr(h1.hier_path,'/',1,5)-instr(h1.hier_path,'/',1,4)-1) lvl3,
substr(h1.hier_path,instr(h1.hier_path,'/',1,5)+1,instr(h1.hier_path,'/',1,6)-instr(h1.hier_path,'/',1,5)-1) lvl4,
substr(h1.hier_path,instr(h1.hier_path,'/',1,6)+1,instr(h1.hier_path,'/',1,7)-instr(h1.hier_path,'/',1,6)-1) lvl5,
substr(h1.hier_path,instr(h1.hier_path,'/',1,7)+1,instr(h1.hier_path,'/',1,8)-instr(h1.hier_path,'/',1,7)-1) lvl6,
substr(h1.hier_path,instr(h1.hier_path,'/',1,8)+1,instr(h1.hier_path,'/',1,9)-instr(h1.hier_path,'/',1,8)-1) lvl7,
substr(h1.hier_path,instr(h1.hier_path,'/',1,9)+1,instr(h1.hier_path,'/',1,10)-instr(h1.hier_path,'/',1,9)-1) lvl8,
substr(h1.hier_path,instr(h1.hier_path,'/',1,10)+1,instr(h1.hier_path,'/',1,11)-instr(h1.hier_path,'/',1,10)-1) lvl9,
substr(h1.hier_path,instr(h1.hier_path,'/',1,11)+1,instr(h1.hier_path,'/',1,12)-instr(h1.hier_path,'/',1,11)-1) lvl10
from h h1 ;

• .      Insert into final relational flatten table

--Create final table
create table hr.w_company_dh
( HIER_PATH varchar2(80),
NODE varchar2(80),
LVL1 varchar2(80),
LVL2 varchar2(80),
LVL3 varchar2(80),
LVL4 varchar2(80),
LVL5 varchar2(80),
LVL6 varchar2(80),
LVL7 varchar2(80),
LVL8 varchar2(80),
LVL9 varchar2(80),
LVL10 varchar2(80)
);

-- Insert flatten data into final table
INSERT into hr.w_company_dh
with
h as (
    select sys_connect_by_path(NAME,'/') ||'/' hier_path,
          NAME
    from HR.COMPANY_HIER
    start with parent = 'Alt Company'
    connect by prior NAME = parent
)
select h1.hier_path,
substr(h1.hier_path,2,instr(h1.hier_path,'/',1,2)-2) node,
substr(h1.hier_path,instr(h1.hier_path,'/',1,2)+1,instr(h1.hier_path,'/',1,3)-instr(h1.hier_path,'/',1,2)-1) lvl1,
substr(h1.hier_path,instr(h1.hier_path,'/',1,3)+1,instr(h1.hier_path,'/',1,4)-instr(h1.hier_path,'/',1,3)-1) lvl2,
substr(h1.hier_path,instr(h1.hier_path,'/',1,4)+1,instr(h1.hier_path,'/',1,5)-instr(h1.hier_path,'/',1,4)-1) lvl3,
substr(h1.hier_path,instr(h1.hier_path,'/',1,5)+1,instr(h1.hier_path,'/',1,6)-instr(h1.hier_path,'/',1,5)-1) lvl4,
substr(h1.hier_path,instr(h1.hier_path,'/',1,6)+1,instr(h1.hier_path,'/',1,7)-instr(h1.hier_path,'/',1,6)-1) lvl5,
substr(h1.hier_path,instr(h1.hier_path,'/',1,7)+1,instr(h1.hier_path,'/',1,8)-instr(h1.hier_path,'/',1,7)-1) lvl6,
substr(h1.hier_path,instr(h1.hier_path,'/',1,8)+1,instr(h1.hier_path,'/',1,9)-instr(h1.hier_path,'/',1,8)-1) lvl7,
substr(h1.hier_path,instr(h1.hier_path,'/',1,9)+1,instr(h1.hier_path,'/',1,10)-instr(h1.hier_path,'/',1,9)-1) lvl8,
substr(h1.hier_path,instr(h1.hier_path,'/',1,10)+1,instr(h1.hier_path,'/',1,11)-instr(h1.hier_path,'/',1,10)-1) lvl9,
substr(h1.hier_path,instr(h1.hier_path,'/',1,11)+1,instr(h1.hier_path,'/',1,12)-instr(h1.hier_path,'/',1,11)-1) lvl10
from h h1;

commit;