ExoInsight enables you to connect KNIME to Oracle Essbase to utilize the data and hierarchies from these cubes in your workflows. This guide will walk you through setting up and working with ExoInsight to enable integration between Oracle Essbase and KNIME.
Prerequisites
- ExoInsight must be installed, configured, and enabled at your organization.
- You must know the name of the ExoInsight Essbase environment(s) that you would like to connect to. If you do not know the name of the ExoInsight Essbase environment name, please contact your ExoInsight admin.
- You have downloaded and added the ExoInsight KNIME Plug-In for Essbase (ExoInsight for Essbase) to your KNIME instance, and you can see it in the Node Repository by searching for Essbase or ExoInsight. If you do not have the plug-in installed, please contact your ExoInsight admin.
- (Optional) If you would like to load data to Essbase from KNIME, you have downloaded and added the ExoInsight KNIME Plug-In for Essbase Loads (ExoInsight Essbase Load) to your KNIME instance, and you can see it in the Node Repository by searching for Essbase or ExoInsight. (Note: you will have to have the proper rights to load data back to Essbase). If you do not have the plug-in installed, please contact your ExoInsight admin.
If all the above prerequisites have been met, read on to get started.
Connecting to Essbase
You will find the ExoInsight KNIME Plug-in for Essbase in the Node Repository by searching for ExoInsight or Essbase. The node is called ExoInsight for Essbase. Note: Instructions appear later in the article for using the ExoInsight Essbase load node to load data back to your Essbase cubes from KNIME.
Once you pull it to the workflow canvas, right click and select Execute and Open Views:
The following screen will be displayed after a brief delay:
ExoInsight URL
The URL that references the ExoInsight instance at your organization. The format is
http(s)://ServerName:PortNumber/ExoInsight/service
Your ExoInsight admin will provide you the correct ExoInsight URL to use. Please note that /ExoInsight/service is case-sensitive.
ExoInsight Essbase Environment
This is a name given to the connection to your Essbase environment. This is set up in ExoInsight and will be provided to you by your ExoInsight admin. Please note that the environment name is case-sensitive and will not work properly if not inputted exactly as it’s set up in ExoInsight. Upon entering the ExoInsight URL parameter, you can click the Get List of ExoInsight environments button to populate the drop-down box with available environments.
Essbase Username and Password
This is the same username and password that you normally use to log into Essbase, whether it’s in SmartView, Workspace, or some other tool. ExoInsight will respect the security filters that your username has been given in Essbase, so you’ll be able to see the same applications, cubes, and data as you normally do.
Selecting Your Cube
Once you have successfully logged in, you will then be presented with a screen to select the Essbase application and cube you would like to work with. First select the application from the first drop-down (1), then select the associated cube (2). Once the cube is selected, the outline associated with that cube will appear in the left-hand pane (3):
Querying for Data
There are three ways to query for data with ExoInsight: POV String, MDX, and Essbase Report Scripts. This quick start guide will focus on the POV String method.
The outline that displays in the ExoInsight Alteryx Plug-in for Essbase is a visual representation of the structure of the Essbase cube. To begin pulling data back, you simply need to select the members of the outline that you want to return data for. As you select members, the query will be built dynamically in the query box:
You can also right-click on a member and utilize set operators such as Children, Descendants, Leaves, Members, and Siblings (for a complete discussion of the POV String syntax, please refer to the POV String Documentation - Oracle Essbase Module document):
Once you have your query built, click the Run Query button to see the results:
Once you are satisfied with the results, click the Close & Apply button to execute the query in KNIME:
Upon successful completion, the ExoInsight for Essbase node will be flagged as green. Right click on the node and select View output table to see the results that are available for output:
Querying for Dimension Metadata
ExoInsight makes it just as easy to pull dimension information from your Essbase cubes as it does data. To pull dimension information, right click on the dimension name and select Get Dimension Extract. The dimension metadata will be displayed as the result set:
Click the Close & Apply button and the dimension metadata will be available in the ExoInsight for Essbase node in your KNIME workflow:
Passing in Parameters
Instead of manually entering your ExoInsight URL, ExoInsight Essbase environment, username and password, etc, you can instead dynamically pass in these parameters as part of your workflow.
There are many ways to pass in parameters. One way is to use the Table Creator node to consolidate all the values into a single row, then utilize the Table Row to Variable node to create variables to pass into the ExoInsight for Essbase node. The flow can be seen below:
If parameters are passed in, they all must be present. The required parameters are as follows:
url: The ExoInsight URL
env: The ExoInsight Essbase environment (case-sensitive)
user: The Essbase user to run the query
password: The password of the Essbase user that will run the query
application: The Essbase Application name
cube: The Essbase Cube name
query: Either a) A valid POV String, b) A valid MDX statement, c) a valid Essbase Report Script syntax, d) Dimension name
loops: A valid POV String or MDX string that the base query will be looped over. Note: Starting in ExoInsight version 8.3.3, the loops functionality can be embedded directly into the POV String syntax. See the POV String Documentation - Oracle Essbase Module article for more information.
A complete Table Creator node with all the requisite parameters is below:
Parameters that are passed in will override any parameter values manually entered into the ExoInsight for Essbase node. It is recommended that you keep the default values of the ExoInsight for Essbase node and do not manually log in since all these parameter values will be passed in instead.
Casabase Software