ExoInsight enables you to connect Alteryx to Oracle HFM to utilize the data and hierarchies from these applications in your workflows. This guide will walk you through setting up and working with ExoInsight to enable integration between Oracle HFM and Alteryx.
Prerequisites
- ExoInsight must be installed, configured, and enabled at your organization.
- You must know the name of the ExoInsight HFM environment(s) that you would like to connect to. If you do not know the name of the ExoInsight HFM environment name, please contact your ExoInsight admin.
- You have downloaded and added the ExoInsight Alteryx Plug-In (ExoInsight 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 HFM from Alteryx, you have downloaded and added the ExoInsight Alteryx Plug-In for Loads (ExoInsight 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 HFM). 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 HFM
You will find the ExoInsight Alteryx Plug-in under your Connectors menu. Once you pull it to the workflow canvas, it will ask for the following parameters:
1. ExoInsight URL
The URL that references the ExoInsight instance at your organization. The format is
http(s)://ServerName:PortNumber/ExoInsight/
Your ExoInsight admin will provide you the correct ExoInsight URL to use. Please note that /ExoInsight is case-sensitive.
2. ExoInsight Data Source
Select HFM.
3. ExoInsight Environment Name
This is a name given to the connection to your HFM 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.
4. and 5. HFM Username and Password
This is the same username and password that you normally use to log into HFM, whether it’s in SmartView, Workspace, or some other tool. ExoInsight will respect the security filters that your username has been given in HFM, so you’ll be able to see the same applications, cubes, and data as you normally do.
6. Application
Enter the name of your HFM application. This name is case-sensitive and must match the name of your HFM application exactly.
7. Cube
Enter the same application name that was entered in #6.
8. Suppress Missing Data
Removes #Missing records from the data set. Note: Unchecking this box will result in a substantial increase in data volumes and query times.
9. Display Cell Status (HFM-only)
Check to include a column in the result set of the cell status of the particular intersection.
10. POV String or Dimension Name
Enter the POV String, Saved POV query name, or dimension name. See the POV String and Query Builder Documentation - Oracle HFM Module documentation for more information on building POV String queries.
11. Ludicrous Data Mode
By default the ExoInsight Alteryx Plug-in will allow 2GB of data to be pulled into the workflow. Check this box if more than 2GB of data needs to be pulled into the workflow with a single query. Note: Multiple queries can be run from a single workflow. Each query within a workflow has a 2GB limit.
Querying for Dimension Metadata
ExoInsight makes it just as easy to pull dimension information from your HFM applications 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:
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 HFM 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
source: Value should be HFM
env: The ExoInsight HFM environment (case-sensitive)
user: The HFM user to run the query
password: The password of the HFM user that will run the query. Note: The fields passed into the Text Input Alteryx node are in plain text, including the password. ExoInsight enables you to encrypt the password using the Password Encrypt utility on the ExoInsight homepage. This encrypted password can then be used in the Alteryx workflow.
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 HFM Application name
planType: The HFM Application name (same as application parameter)
suppressMissing: Suppresses missing (null) rows. Under most circumstances this should always be "true". Valid values are true and false.
cellstatus: Toggles whether to display the cell status of the row. Valid values are true and false.
ludicrousMode: If your query will pull back more than 2GB of data, set to true. Otherwise set to false.
entry: Either a) a valid POV String, b) a SavedPOV name, or c) a 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 Alteryx Plug-in and do not manually log in since all these parameter values will be passed in instead.
Loading Data to HFM
ExoInsight enables your Alteryx workflows to load data back into your HFM applications. An HFM user with the appropriate rights to load data to HFM is required to use this functionality.
To load data to HFM from Alteryx, download the and install the ExoInsight 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/
Your ExoInsight admin will provide you the correct ExoInsight URL to use. Please note that /ExoInsight/ is case-sensitive.
ExoInsight HFM Environment
This is a name given to the connection to your HFM 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.
HFM Username and Password
This is the same username and password that you normally use to log into HFM, whether it’s in SmartView, Workspace, or some other tool. ExoInsight will respect the security filters that your username has been given in HFM so you’ll be able to see the same applications and data as you normally do. For loading data to HFM, you must use a user with the appropriate HFM rights to do so.
Select the appropriate options. For a complete description of the consolidation tasks please consult your HFM documentation.
The input data into the plug-in that is to be loaded to HFM must be in the specific format of one column per dimension and a single column called Amt that contains the data to be loaded. The ExoInsight HFM Output macro returns data from HFM in the same format.
The normal rules for loading HFM apply. The data will be loaded in replace mode. For a detailed description of these rules, please refer to the Oracle HFM Administrators Guide for your version of HFM.
General Tips and Best Practices
- Start with small, high-level queries. HFM 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 and Query Builder Documentation - Oracle HFM Module.
- For a detailed description of the POV String syntax, please refer to the POV String and Query Builder Documentation - Oracle HFM 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.
Casabase Software