Skip to main content

Oracle Cloud EPM - Stored Procedures

Updated

ExoInsight for Snowflake - Oracle Cloud EPM makes the following stored procedures available for accessing and loading your Oracle Cloud EPM data from Snowflake.

The following stored procedures are available:

 

RETURN_DATA

The RETURN_DATA stored procedure is the main stored procedure when working with ExoInsight for Snowflake. It will return the data from your Oracle Cloud EPM application as defined by your ExoInsight POV String query and either return it to the Snowflake worksheet or calling program or create a materialized table in the SHARED_DATA schema, depending on your parameter selections. The data returned will always be in tabular/column format, perfect for any reporting or analysis use-case. You can pull aggregated data and dynamically calculated data as well.

RETURN_DATA Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. PLAN_TYPE - the Cloud EPM plan type/cube. e.g. Plan1
  4. POV_STRING - A simple way to specify which data to pull. The Query Builder provides a graphical means to build the POV String while making member selections in the dimensions. A Saved Query can also be used.
  5. USERNAME (optional) - native Oracle username valid for Cloud EPM
  6. PASSWORD (optional) - native Oracle password valid for above username
  7. SUPPRESS_MISSING (BOOLEAN DEFAULT TRUE) - Do NOT display rows with no data. Improves performance by returning less data.
  8. LOOPS (optional) - Sometimes a single query/POV tries to pull back too much data from EPM. Specify dimensions/members for which the base query/POV will be run once for each loop. The results will be combined and returned.
  9. OUTPUT_TABLE (optional) - Leave empty (null) to return the results or enter a table to which the results should be written. Tables will be written to the SHARED_DATA schema under EXOINSIGHT_EPM.

Sample call to the RETURN_DATA procedure from a SQL worksheet to return results directly.

  • SUPPRESS_MISSING is not specified so defaults to TRUE.
  • LOOPS is not specified.
  • OUTPUT_TABLE is not specified, so data is returned directly.
  • Username and password are passed in, so this overrides the Snowflake secret.
set username = 'valid Oracle EPM username';
set password = 'password1234';
call EXOINSIGHT_EPM.EPM.RETURN_DATA('EPM_env_name','EPBCS','Plan1','{"Account":["idescendants(A_95001)"],"Cost Center":["CC_T"],"Currency":["Input Currencies"],"Entity":["C_T"],"Intercompany":["IC_All_Corp_Comp_V1"],"LoB":["L_T"],"Period":["ILvl0Descendants(YearTotal)"],"Product":["Total Product"],"Scenario":["OEP_Actual"],"Version":["OEP_Working"],"Years":["FY21","FY22"]}',$username, $password);

 

Sample call to the RETURN_DATA procedure from a SQL worksheet to write results to a table:

call EXOINSIGHT_EPM.EPM.RETURN_DATA('EPM_env_name','EPBCS','Plan1','{"Account":["idescendants(A_95001)"],"Cost Center":["CC_T"],"Currency":["Input Currencies"],"Entity":["C_T"],"Intercompany":["IC_All_Corp_Comp_V1"],"LoB":["L_T"],"Period":["ILvl0Descendants(YearTotal)"],"Product":["Total Product"],"Scenario":["OEP_Actual"],"Version":["OEP_Working"],"Years":["FY21","FY22"]}',$username, $password, TRUE, NULL, 'MY_TABLE');

 

RETURN_DIMENSION

The RETURN_DIMENSION stored procedure returns the dimension metadata, including the hierarchy in parent/child and generation format, member aliases, and various other metadata.

RETURN_DIMENSION Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. DIMENSION_NAME - name of the dimension to return. e.g. Entity
  4. USERNAME (optional) - native Oracle username valid for Cloud EPM
  5. PASSWORD (optional) - native Oracle password valid for above username

Sample call to the RETURN_DIMENSION procedure from a SQL worksheet to return results directly.

  • USERNAME and PASSWORD are not specified, so the ones in the "native" section of EXOINSIGHT_EPM_CREDENTIALS will be used.
call EPM.RETURN_DIMENSION('EPM_env_name', 'EPBCS', 'Entity');

 

RETURN_APPINFO

The RETURN_APPINFO stored procedure returns all the Oracle Cloud EPM servers, applications, plan types, and dimensions currently registered with this instance of ExoInsight for Snowflake.

RETURN_APPINFO Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. B_WRITE_TABLE (optional BOOLEAN DEFAULT FALSE) If TRUE, data will be written to 'SHARED_DATA.APPINFO'. If FALSE (default), data will be returned to the worksheet.
  4. B_APPEND_TO_TABLE (optional BOOLEAN DEFAULT FALSE) If TRUE, data will be appended to 'SHARED_DATA.APPINFO'. If FALSE (default), data will be overwritten in 'SHARED_DATA.APPINFO'.
  5. USERNAME (optional) - native Oracle username valid for Cloud EPM
  6. PASSWORD (optional) - native Oracle password valid for above username

Sample call to the RETURN_APPINFO procedure.

call EXOINSIGHT_EPM.EPM.RETURN_APPINFO('EPM_env_name', 'EPBCS', FALSE);

 

WRITE_DIMENSION_TABLES

WRITE_DIMENSION_TABLES writes the dimension tables for the specified APPLICATION/PLAN_TYPES to the EXOINSIGHT_EPM.SHARED_DATA schema - one table for each dimension, e.g. SHARED_DATA.EPBCS_ENTITY, SHARED_DATA.EPBCS_COST CENTER, etc.

WRITE_DIMENSION_TABLES Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. PLAN_TYPE (optional ARRAY DEFAULT NULL) - name(s) of the plan types for which to return dimensions. e.g. ['Plan1'] or ['Plan1','OEP_FS']
  4. USERNAME (optional) - native Oracle username valid for Cloud EPM
  5. PASSWORD (optional) - native Oracle password valid for above username

Sample call to the WRITE_DIMENSION_TABLES procedure specifying two PLAN_TYPES for which to write dimensions. USERNAME and PASSWORD are not specified, which means the Snowflake secret will be used to log into Oracle.

call EXOINSIGHT_EPM.EPM.WRITE_DIMENSION_TABLES('EPM_env_name', 'EPBCS', ['Plan1','OEP_FS']);

Sample call to the WRITE_DIMENSION_TABLES procedure NOT specifying PLAN_TYPES. This means that all dimensions for all PLAN_TYPES for the specified APPLICATION will be written to separate tables in the SHARED_DATA schema.

call EXOINSIGHT_EPM.EPM.WRITE_DIMENSION_TABLES('EPM_env_name','EPBCS');

 

LOAD_DATA

LOAD_DATA writes data back to Oracle Cloud EPM.

There are two possible sources for the data:

  1. A Snowflake table. The Snowflake table must fulfill the following conditions:
      • Must contain a column for each dimension in the EPM cube. The column name must be exactly the same as the dimension name in PBCS.
      • The last column must contain the numeric data amount, typically in a column named AMT.
      • The ExoInsight for Snowflake application must have access to the table. 
      •  
      • Hint: the simplest way to obtain a table that fulfills the above requirements is to start with a table obtained from running the EPM.RETURN_DATA stored procedure.
      •  
  2. An array of data in valid JSON string format. The conditions for the array are similar to those for the table:
      • The array must contain a header row with a "column" for each dimension. The column name must be exactly the same as the dimension name in PBCS.
      • The last "column" must contain the numeric data amount, typically in a "column" named AMT. Example: '[["ENTITY","ACCOUNT","PRODUCT","YEAR","PERIOD","AMT"],["CBSW","AR","EXOINSIGHT","2025","APR",500.00]]'

LOAD_DATA Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. PLAN_TYPE (optional ARRAY DEFAULT NULL) - name(s) of the plan types for which to return dimensions. e.g. ['Plan1'] or ['Plan1','OEP_FS']
  4. INPUT_DATA: name of Snowflake table or array of data. see data sources above
  5. INPUT_DATA_FROM_TABLE: True if INPUT_DATA is a Snowflake table. False if INPUT_DATA is a JSON array
  6. USERNAME (optional) - native Oracle username valid for Cloud EPM
  7. PASSWORD (optional) - native Oracle password valid for above username

 

Sample call to the LOAD_DATA stored procedure. This stored procedure loads data from the PBCS_LOAD table in the EPM schema.

call EPM.LOAD_DATA('EPM_env_name','EPBCS','Plan1','EPM.EPM.PBCS_LOAD',True,'my_epm_username','my_epm_password');

 

RUN_EXPORT

RUN_EXPORT exports data or metadata from Oracle Cloud EPM using a job.

RUN_EXPORT Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. PLAN_TYPE - the Cloud EPM plan type/cube. e.g. Plan1
  4. JOB_TYPE - determines data or metadata export. 2 valid options: 
      1. EXPORT_DATA_USING_RULE - for data export
      2. EXPORT_METADATA - for metadata export
  5. JOB_NAME - name of the Cloud EPM job to run. These jobs are either custom-created by users or 'canned', system jobs. e.g. ExportDim_Account
  6. SERVER_FILENAME - the job will write to this filename in the Cloud EPM Inbox/Outbox Explorer. e.g. Account.zip
      1. For EXPORT_METADATA this must ALWAYS be a zip file, as required by Cloud EPM. e.g. All_Dimensions.zip or Entity.zip
      2. For EXPORT_DATA_USING_RULE this must ALWAYS be a csv file. e.g. FY22_Income_Details.csv
  7. OUTPUT_TABLE (optional DEFAULT NULL) - table to which to write results. If not provided, results will be returned to the SQL worksheet only. The table will be written to SHARED_DATA schema.
      1. For EXPORT_DATA if the zip file contains more than one dimension, you will receive an error if OUTPUT_TABLE is not set. It is not possible to return multiple tables to one Snowflake SQL worksheet session at the same time.
      2. The OUTPUT_TABLE string you specify here be used as the prefix to the dimension name in the exported zip file. e.g. if OUTPUT_TABLE is 'april', then each dimension in the export will be written to a table starting with 'april_export_' - e.g. 'april_export_Account', 'april_export_Entity', etc.
  8. PARAMETERS (optional DEFAULT NULL) - any parameters required by the job in JOB_NAME not required or used by EXPORT_METADATA e.g. for EXPORT_DATA_USING_RULE - 'POV="@idescendants(A_95001)","Input Currencies","IC_All_Corp_Comp_V1",@idescendants("Q2"),"OEP_Working","OEP_Actual","FY21","CC_250",@Descendants("C_T"),@Descendants("L_T"),@Descendants("P_T")'
  9. USERNAME (optional) - native Oracle username valid for Cloud EPM
  10. PASSWORD (optional) - native Oracle password valid for above username

Sample call to the RUN_EXPORT procedure specifying an EXPORT_DATA_USING_RULE job. USERNAME and PASSWORD are not specified, so the Snowflake secret will be used to log into Oracle.

set env_name = 'PBCS_CBSW';
set application = 'EPBCS';
set planType = 'Plan1';
set jobType = 'EXPORT_DATA_USING_RULE';
set jobName = 'ExoInsightExport';
set serverFileName = 'corp_comp_fy21_q2.csv';
set output_table = NULL;
set parameters = 'POV="A_95001","Input Currencies","IC_All_Corp_Comp_V1",@idescendants("Q2"),"OEP_Working","OEP_Actual","FY21","CC_250",@Descendants("C_T"),@Descendants("L_T"),@Descendants("P_T")';

call EPM.RUN_EXPORT($env_name,$application,$planType,$jobType,$jobName,$serverFileName,$output_table,$parameters);

Sample call to the RUN_EXPORT procedure specifying an EXPORT_METADATA job. USERNAME and PASSWORD are not specified, so the Snowflake secret will be used to log into Oracle.

set env_name = 'PBCS_CBSW';
set application = 'EPBCS';
set planType = 'Plan1';
set jobType = 'EXPORT_METADATA';
set jobName = 'ExportAccount';
set serverFileName = 'Account.zip';

call EPM.RUN_EXPORT($env_name,$application,$planType,$jobType,$jobName,$serverFileName);

 

EXECUTE_JOB

EXECUTE_JOB runs an Oracle Cloud EPM job.

EXECUTE_JOB Parameters

  1. ENV_NAME - the name chosen for the given Cloud EPM server on the Home page during setup
  2. APPLICATION - the Cloud EPM application. e.g. Vision
  3. JOB_TYPE - can be any of the defined Job Types.
  4. JOB_NAME - name of the Cloud EPM job to run. These jobs are either custom-created by users or 'canned', system jobs. e.g. ExportDim_Account or 'Clear Actuals'
  5. PARAMETERS (optional DEFAULT NULL) - any parameters required by the job in JOB_NAME. For example, for a system 'Rules' 'Clear Actuals' job that expects a 'Years' parameter: 'Years="FY25"'
  6. B_WAIT_FOR_STATUS (optional BOOLEAN DEFAULT TRUE). Wait for the job to complete and return its status if TRUE. If FALSE, the job will be kicked off in Cloud EPM but the status upon completion will not be returned to Snowflake.
  7. USERNAME (optional) - native Oracle username valid for Cloud EPM
  8. PASSWORD (optional) - native Oracle password valid for above username

Sample call to the EXECUTE_JOB procedure specifying an EXPORT_DATA_USING_RULE job. USERNAME and PASSWORD are not specified, so the Snowflake secret will be used to log into Oracle.

set env_name = 'PBCS_CBSW';
set application = 'EPBCS';
set jobType = 'Rules';
set jobName = 'Clear Actuals';
set parameters = 'Years="FY25"';

call EPM.EXECUTE_JOB($env_name,$application,$planType,$jobType,$jobName,$serverFileName,$output_table,$parameters);

 

Related articles

Powered by Zendesk