ExoInsight enables you to connect Alteryx 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 Alteryx.
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 Alteryx Plug-In for Essbase (ExoInsight Essbase Output) to your Alteryx Designer instance, and you can see it in the Connectors menu in the Alteryx toolbar. 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 Alteryx, you have downloaded and added the ExoInsight Alteryx Plug-In for Essbase Loads (ExoInsight Essbase Load) to your Alteryx Designer instance, and you can see it in the Connectors menu in the Alteryx toolbar. (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 Alteryx Plug-in for Essbase under your Connectors menu. Once you pull it to the workflow canvas, it will ask for the following parameters:
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.
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 space below (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, run the workflow to see the results in Alteryx:
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, enter the dimension name in the query box exactly as it appears in the outline the run the workflow:
Starting in ExoInsight version 8.4.3, generation alias columns are available. This means that generation columns will be created for each generation and include the member name and one column for each alias table, as seen below:
To pull back generation alias columns with the dimension metadata, add the following syntax to the dimension name:
DimensionName{Gen_Aliases}
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. If you choose to pass in parameters, they all must be present in the Text Input node. 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. Note: The fields passed into the Text Input Alteryx node are in plain text, including the password. If publishing the workflow to Alteryx Server/Gallery, or anywhere else the workflow can be viewed, it is highly recommended to lock the workflow prior to doing so. Alteryx documentation on locking the workflow can be found here: Lock Your Workflow.
application: The Essbase Application name
cube: The Essbase Cube name
entry: Either a) A valid POV String, b) A valid MDX statement, c) a valid Essbase Report Script syntax, d) Dimension name
Parameters that are passed in will override any parameter values manually entered into the ExoInsight Alteryx Plug-in. It is recommended that you keep the default values of the ExoInsight Essbase Output plug-in and do not manually log in since all these parameter values will be passed in instead.
Loading Data to Essbase
ExoInsight enables your Alteryx workflows to load data back into your Essbase cubes. An Essbase user with the appropriate rights to load data to Essbase is required to use this functionality.
To load data to Essbase from Alteryx, download the and install the ExoInsight Essbase Load plug-in (contact your ExoInsight admin if you do not have this plug-in available in your Connectors menu). Drag the plug-in to your Alteryx workflow to see the options:
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.
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. For loading data to Essbase, you must use a user with the appropriate Essbase rights to do so.
Select the Essbase application and cube that data will be loaded to.
Select the appropriate options.
Dry Run: No data will be loaded to Essbase. Select to test the workflow.
Stop Load if Errors Encountered: The load will stop when an error is encountered and no additional records will be loaded.
Calculation Scripts: The ExoInsight Essbase Load plug-in allows you to specify an Essbase calculation script to run before the load process starts and after the load process completes. These are optional.
The input data into the plug-in that is to be loaded to Essbase must be in the specific format of one column per non-attribute dimension and a single column called Amt that contains the data to be loaded. The ExoInsight Essbase Output macro returns data from Essbase in the same format.
The normal rules for loading Essbase apply. The data will be loaded in replace mode. For a detailed description of these rules, please refer to the Oracle Essbase Administrators Guide for your version of Essbase.
General Tips and Best Practices
- Start with small, high-level queries. Essbase works best with targeted, focused queries. Think about the data required for your particular use-case and do not attempt to pull more data than you need. Most reporting and analytic requirements can be done with less data than initially assumed.
- If large data volumes are required, please refer to the discussion on looping in the POV String Documentation - Oracle Essbase Module.
- Add aliases by selecting the alias table you’d like to include in the data result set:
- Consider making your queries dynamic by including Essbase substitution variables. The substitution variables available for the cube can be viewed by expanding the Substitution Variables section of the outline:
- For a detailed description of the POV String syntax, please refer to the POV String Documentation - Oracle Essbase Module guide.
- If publishing the workflow to Alteryx Server/Gallery, or anywhere else the workflow can be viewed, it is highly recommended to lock the workflow prior to doing so. Alteryx documentation on locking the workflow can be found here: Lock Your Workflow.
- For additional help, please contact the ExoInsight support contact for your organization.