This guide details the process of installing and configuring the ExoInsight for Snowflake application for use in your Snowflake environment. This guide is intended for Snowflake users with the ACCOUNTADMIN role assigned, and must either have an Oracle Cloud Account with application admin privileges or be logged into Snowflake with a username that has application admin privileges.
(Note: if you are looking for the Oracle Fusion Cloud ERP installation guide, please follow this link: Oracle Fusion Cloud ERP - Installation and Setup Guide).
Installation
First, install ExoInsight for Snowflake - Oracle Cloud EPM into your Snowflake environment from Snowflake Marketplace. You must have the role ACCOUNTADMIN to install ExoInsight for Snowflake.
Open the ExoInsight for Snowflake application. The left-hand pane contains navigation to the different sections of the application:
Home
(Covered in this article) The Home section allows you to see the existing Environments that have been set up, add or remove Environments, verify the validity of the network rules, secret, and external access integration, and make changes to the setup.
Tables Setup
(Covered in this article) This section enables the creation of the dimensional tables within the application that are used for query building.
Query Builder
(Covered in the Oracle Cloud EPM - User Guide article) This section presents users with a UI to create their queries to pull data from the Oracle Cloud EPM applications.
Saved Queries
(Covered in the Oracle Cloud EPM - User Guide article) This section allows users to create saved queries that can be centrally managed and referenced by name.
Documentation
This section provides documentation on common setup and usage tasks.
Query Log
(Covered in the Oracle Cloud EPM - User Guide article) The query log provides detailed audit information of every query that runs through ExoInsight for Snowflake.
Generate Certificate
(Covered in this article). This section allows the admin to generate the Snowflake secret that ExoInsight for Snowflake uses for connectivity to the Oracle Cloud sources.
Setup
The Home screen provides step-by-step instructions on how to set up your ExoInsight for Snowflake application. Setup consists of four primary activities:
- Adding your ExoInsight Environments. ExoInsight Environments refer to a named connection to a specific Oracle Cloud EPM resource. For example, you will set up one environment for each Oracle "pod" you'd like to integrate with your Snowflake instance.
- Configure Snowflake secret. Snowflake secrets securely store the Oracle credentials and/or Oracle confidential application information used to connect to your Oracle resources.
- Setup network rule and external access integration. Network rules control what external Oracle resources ExoInsight for Snowflake can communicate with, and external access integrations bind the network rule and Snowflake secrets together to enable communication with external Oracle sources.
- Set up the Oracle Cloud EPM Query Builder
Step 1: Adding your ExoInsight Environments
Upon accessing the Home page for the first time, you will be presented with the following screen:
There are two URL types:
- EPM: Select this to add a connection to EPBCS, PBCS, FCCS, PCMCS, Tax Reporting, or Freeform Planning
- IDCS: Select this to add a connection to Oracle IDCS, where the confidential application will be set up for communication using JWT Assertion.
Select EPM as the URL type, then enter a unique name for the environment. This name is how you will refer to this connection when pulling data with ExoInsight for Snowflake.
Next, enter your Oracle Cloud EPM URL. Your URL will look similar to the one in the screenshot below. Note: The URL needed is the URL after you have logged into your Oracle Cloud EPM application, not the login URL. Also, it is not necessary to enter the "https://" or anything past the "oraclecloud.com" ("/HyperionPlanning" in the example below), but it is fine to enter the whole URL as well.
Finally, click the Save button to save the ExoInsight Environment.
Once the save completes, you will see your entry in the ExoInsight Environments section, and ExoInsight for Snowflake application will notify you that Oracle Cloud EPM credentials (Snowflake secret) have not yet been set up:
Move on to the next step to set up the Snowflake secret. Note: If your organization will use Oracle IDCS to authorize access to Oracle, you will come back and set up the IDCS Environment after the following steps are completed.
Step 2: Setting up the Snowflake Secret
ExoInsight for Snowflake will present the UI to generate the Snowflake secret. The first step is to decide how your organization will enable authorization to Oracle resources with ExoInsight for Snowflake. There are two Authentication options with the Snowflake secret:
- Use an Oracle Cloud Account username/password. This username and password will be stored in the Snowflake secret and, once created, will not be able to be viewed by anyone, even the user who creates the secret. Please see the Security and Privacy Overview article for more information on how Snowflake secrets work. You will have the option of specifying individual Snowflake users who can utilize the Snowflake secret (and therefore connect to Oracle via the username/password supplied in the secret).
- Use the Snowflake username of the currently logged in user to connect to Oracle. This process is done via JWT Assertion and is controlled by a Confidential Application set up in Oracle IDCS. The logged in Snowflake username is sent to Oracle IDCS via JWT, and Oracle handles Authorization based on the user. This method requires the creation of the Confidential Application in Oracle IDCS prior to usage.
Please note that only one Oracle Cloud Account username/password can be stored in the Snowflake secret. If you have multiple Cloud EPM environments set up and want to use an Oracle Cloud Account for connectivity, you have the option of sending the username/password directly to the stored procedures as as parameter, as opposed to using the Snowflake secret.
Also note that you can manually send in an Oracle Cloud Account username and password for all ExoInsight for Snowflake stored procedures, bypassing the Snowflake secret. This is often done to test connectivity and for ease of use during the development phase. Snowflake secrets are recommended for production use, however.
Snowflake Secret Setup: Native Oracle Cloud Account Username and Password
Navigate to the Generate Certificate section on the left-hand pane. Note that if this is your first time setting up ExoInsight for Snowflake, the Generate Certificate setup screen will be displayed in the Home section. The process is identical if you choose to use the Home section setup or navigate to the Generate Certificate screen to follow the below steps.
Enter the Oracle Native Account username and password, as well as a comma-delimited list of Snowflake users that are allowed to utilize the native username/password embedded in the secret.
If your organization will not be using an Oracle Native Account for connectivity, this section can be left blank:
If the Oracle Cloud Account username and password will be the only method you connect to Oracle Cloud EPM, your Snowflake secret information will look similar to below (the idcs section is not populated). In this case, you can skip directly to the Snowflake Secret Setup: Configuring the Snowflake Secret section.
Snowflake Secret Setup: Oracle IDCS Confidential Application Authorization
If Oracle IDCS will be used for Authorization, add the IDCS environment by scrolling down to the bottom of the Home section and adding a new IDCS environment:
Next, navigate to the Generate Certificate section on the left-hand pane.
Click the Generate certificates button to generate the certificate and private key. Click the Download Certificate button to download the certificate you will need to add to the Confidential Application in IDCS.
The next section is where you enter the information about the confidential application you have set up in Oracle IDCS. Select the IDCS environment form the drop-down, then add the IDCS Client ID, Client Secret, and select an alias for the certificate (such as ExoInsight_SF).
The certificate alias is important because you must use the exact same alias when adding the certificate to the Confidential Application in Oracle IDCS.
After filling out all the necessary information, the Snowflake secret information is provided below. Copy this text.
Snowflake Secret Setup: Configuring the Snowflake Secret
Navigate back to the Home section on the left-hand pane. Click the "Set up credentials to connect to Oracle Cloud EPM" button. Paste the text you copied in the previous step in the "Secret value" box (yes, the box is very small for the amount of text you will be pasting). If desired, click the Advanced options section and change the name of the secret to something meaningful for your organization.
Click Configure to create the Snowflake secret.
Step 3: Setting up the Network Rule and External Access Integration
In the Home section, simply click the "Set up access to Oracle Cloud EPM" button to create both the Snowflake network rule as well as the Snowflake external access integration. The network rule will be created with all of the URLs that have been set up in the ExoInsight environments section:
You will see the URLs listed in the "Allowed endpoints" section of the network rule:
Click Connect to set up connectivity between your Snowflake instance and Oracle Cloud.
Once the above steps have been completed, click the "Verify Setup Status" in the Home section to ensure everything has been created properly:
Step 4: Setting up Oracle Cloud EPM Query Builder
Navigate to the Tables Setup section on the left-hand pane. This screen creates the APPINFO table, which contains the Applications, Plan Types, and Dimensions per ExoInsight Environment, and refreshes the dimension tables which enable the Query Builder screen.
The first step is to populate the APPINFO table with the Applications, Plan Types, and Dimensions from your Oracle Cloud EPM environment. In the "Create APPINFO table from Oracle Data Management" section, add the Oracle Cloud EPM application name, Oracle username, and Oracle password.
Once the information is entered, click the Create APPINFO table button. The APPINFO table will be populated and displayed with a list of all the Plan Types, and Dimensions available in the Application entered.
Note: the Oracle user must have admin rights on the Oracle Cloud EPM application to pull over all necessary information. If the user you are using does not have the appropriate rights, you'll see the following error message and need to manually enter the information. See Manually Populating APPINFO Table.
Manually Populating APPINFO Table
If you receive an error after clicking the Create APPINFO table button, this likely means the user you are attempting to run the process with does not have sufficient privileges to Oracle Data Management. When this happens, you can either use an Oracle user with higher privileges, or manually populate the APPINFO table.
The APPINFO table will initially be blank:
Click in the ENV_NAME field and enter the ExoInsight Environment name. Please note that the environment name must be entered exactly as it was created in the Home section. Then populate the APPLICATION, PLANTYPE, DIMENSION, and DEFAULT_MEMBER fields. The DEFAULT_MEMBER field needs to be populated with a level-0 member from the dimension that everyone who will be running queries has access to.
Repeat the process for all the remaining dimensions in the APPLICATION/PLANTYPE. If necessary, repeat the process for any other PLANTYPES associated with the APPLICATION that you would like to query through ExoInsight for Snowflake. Once finished, click the Save changes button:
After the APPINFO table has been populated, select the Plan Type(s) that you would like to refresh to ExoInsight for Snowflake. This step materializes the dimension tables, which can then be queried by your processes.
A call to the WRITE_DIMENSION_TABLE stored procedure is created for you automatically. This stored procedure can be called at any time to refresh dimensionality in ExoInsight for Snowflake.
Note: The example below is passing in an Oracle Cloud Account username and password, but the Snowflake secret method can also be used.
Once this step is complete, your ExoInsight for Snowflake application is now ready for use. Please refer to the Oracle Cloud EPM - User Guide article for more information on how to build queries and pull data and hierarchies from your Oracle Cloud EPM applications into Snowflake.