This guide details how to use the ExoInsight for Snowflake - Oracle Cloud EPM native application. This article assumes the steps provided in the Oracle Cloud EPM - Installation and Setup Guide have already been performed and you have been granted access to the application by your Snowflake administrator.
If you are new to ExoInsight it may be helpful to refer to the POV String syntax documentation located at the following article:
Building Queries
To get started, select the Query Builder section on the left-hand pane:
You will see the Query Builder page appear on the right. The user you're currently logged in with will appear at the top.
The Query Builder page is a UI designed to make it easy to build the query to pull the desired data from your Oracle Cloud EPM application. The query will be built in ExoInsight POV String syntax, which is a familiar and intuitive way to pull back data slices from your applications. Details about the POV String syntax can be found here:
The first step is to select the Environment, Application, and Plan Type that you want to query from the available drop-downs:
The dimensions associated with the selected Application/Plan Type will appear below:
To build your query, simply expand the first dimension (EPBCS_Plan1_Account in the example above), and navigate in the hierarchy to the member for which you would like to pull data. Because Oracle Cloud EPM applications are hierarchical in nature, you can either pull data for specific members, or you can use set operators to pull data for multiple members at once.
For example, given the Account hierarchy below, if a user wanted to pull data just for account A_95001 (Net Income Current Year), then they could select "Member". However, if they wanted to pull data for account A_95001 as well as all members below it in the hierarchy, all the way to the bottom level members, then "idescendants" would be selected:
Set Operator Descriptions
- children: pull data for the members directly below the current member in the outline.
- descendants: pull data for all members below the current member in the outline, all the way down to the lowest-level members, exclusive of the current member.
- idescendants: pull data for all members below the current member in the outline, all the way down to the lowest-level members, inclusive of the current member.
- ILvl0Descendants: pull data for only the lowest-level members in the hierarchy below the current member.
- siblings: pull data for all members on the same level as the current member and that have the same parent.
- Member: pull data for only the current member.
As you make the selections to determine which slice of data to pull back from your Oracle Cloud EPM application, ExoInsight for Snowflake will automatically build both the query (called the POV String) as well as the Snowflake stored procedure that you can run to pull the data.
If you scroll down below the dimensions, you can see the stored procedure and POV String beginning to be built. For example, after selecting "idescendants" on Account A_95001, the Account section of the query is added to the stored procedure call:
Continue through the remaining dimensions and select the member(s) or set operators to build the query. The auto-generated stored procedure call can now be used in SnowSight or anywhere else to pull data back from Oracle Cloud EPM.
A complete list of available stored procedures, their parameters, usage, and behavior can be found in the Oracle Cloud EPM - Stored Procedures guide.
Saved Queries
Saved Queries provides a convenient way to manage, update, and utilize commonly-used queries within the ExoInsight for Snowflake application. Multiple processes can reference a centrally-managed query that's easily updatable, instead of having to modify existing processes whenever a query changes.
To access the Saved Queries section, click on Saved Queries on the left-hand pane:
The following screen will appear, allowing you to enter, update, or delete a saved query. To add a new query, simply click the "+" icon at the top-right of the grid, or click the "+" icon in a new row in the grid:
Enter the relevant information. ExoInsight is case-sensitive, so enter the Environment, Application, and Plan Type exactly as you have elsewhere in the application.
Use a descriptive name for the Query Name, keeping in mind that calling the query is case-sensitive as well.
For the Query Value, enter any valid ExoInsight for Snowflake query. Make sure to remove any newline characters before adding.
Once complete, click the Save Changes button to add or update the saved query.
To delete a saved query, click the checkbox next to the row you would like to delete, then click the Delete button on the pop-up toolbar on the upper-right side of the grid:
Using saved queries in ExoInsight for Snowflake is easy and convenient. In the POV_STRING parameter of the EPM.RETURN_DATA stored procedure, simply use the syntax of savedpov=Saved_Query_Name instead of the hard-coded query. For example, to utilize the saved query we added above, the stored procedure call will be as follows:
call EPM.RETURN_DATA(
'PBCS_CBSW',
'EPBCS',
'Plan1',
'savedpov=NET_INCOME_ACTUALS',
null, --USERNAME
null, --PASSWORD
True, --SUPPRESS MISSING
null, --LOOPS
null --TABLE NAME
)
Using Loops
Loops are one of the most powerful features of ExoInsight for Snowflake. Looping allows you to pull much more data than you would normally be able to with a single query. For more information about using Loops in your queries, please see the Loops section of the following article: