ExoInsight enables connectivity from Google Sheets to your on-premises Essbase, OAC Essbase, or OCI-hosted Essbase instance. The ExoInsight Google Sheet Add-in provides both standard ad-hoc functionality as well as the capability to pull back data in ExoInsight (relational) format directly into your Google Sheet.
- ExoInsight must be installed, configured, and enabled at your organization (on-premises Essbase and OCI-hosted Essbase only).
- 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.
- The OAC Essbase Add-in has been whitelisted for your organization's domain and you can open it from the Add-ons menu in your Google Sheet toolbar:
- The steps outlined in the ExoInsight Google Sheet Add-in Setup have been completed.
If all the above prerequisites have been met, read on to get started.
Connecting to Essbase
Open the Google Sheets Add-in by opening a Google Sheet, clicking the Add-ons menu, and selecting OAC Essbase Add-in. The add-in sidebar will display on the right-hand side of the screen:
Essbase instance type: Select OAC to connect to OAC Essbase, on On-Premise for on-premise Essbase or OCI-hosted Essbase.
ExoInsight URL: The ExoInsight URL that has been opened to Google Sheets needs to be entered in the following format:
ExoInsight environment: The name of the ExoInsight environment you are connecting to.
Username and Password: The Essbase username and password.
Upon successfully logging in, the Cubes tab will be displayed and allow you to select the Essbase application and database that you have access to. Select your application and database and click the Set Active button.
Using the Add-in
Upon selecting your application and database, the Operations tab will be displayed and will show the primary ad-hoc operations. The connected cube is shown at the top of the screen.
To pull back a default retrieve, click the Retrieve button:
The structure of the cube you are retrieving from will determine the dimensions that pull back on a default retrieve. The ad-hoc functionality can now be used. If you are familiar with SmartView or the classic Essbase Excel add-in, the functionality is very similar.
To utilize the ad-hoc functions, highlight a member in the sheet and click the appropriate button:
Zoom In: The member(s) underneath the member will be displayed, if they exist. This is also known as drill-down.
Zoom Out: The members will collapse to the parent member. This is also known as drill-up.
Keep Only: Only the highlighted member and other rows that contain the highlighted member will remain.
Remove Only: Only the highlighted member and other rows that contain the highlighted member will be removed.
Pivot: Switch a dimension from Rows to Columns, or vice versa.
Pivot to POV: Move a dimension to the POV (Page) dimensions. Only one member can be in the POV per dimension at a time.
By default, member names will be pulled back into the sheet. To switch to aliases, select the appropriate Alias Table from the drop-down and click the Retrieve button:
Besides using the buttons to manipulate your ad-hoc, you can also manually enter member names, add rows and columns, shift dimensions from one section of the ad-hoc to another, and any other manual manipulation as long as the resultant ad-hoc conforms to the Essbase retrieval rules.
Multiple Retrieves Per Sheet
The Google Sheets Add-in for Essbase allows multiple retrievals per sheet. Each retrieval has an assigned area that dynamically updates based on zooming in, zooming out, pivoting, etc. The first time you refresh your retrieve per session, the entire retrieval must be highlighted. Subsequent retrievals need to have a cell highlighted in the retrieval you want to refresh.
To create another retrieval on the same sheet, simply select a cell outside of the first retrieval range and click the Retrieve button. A new default retrieve will be created that you can then manipulate with the ad-hoc functions. The retrieval that has a cell highlighted will be the one that is updated upon clicking the Retrieve button.
Click on the Preferences tab to change the behavior of your ad-hoc retrieves:
Member Display: When Member Name and Alias is checked, the clicking the Retrieve button will return both member names and aliases for every row dimension:
Missing Text: Determines what is displayed when intersections are requested that have no data.
No Access Text: Determines what is displayed when intersections are requested that the user does not have access to based on their Essbase security filters.
ZoomIn: Determines the behavior of clicking the Zoom In button during ad-hoc retrievals.
ZoomIn Ancestors: Determines the positioning of the parent member when clicking the Zoom In button during ad-hoc retrievals.
Indent: Determines the display of indentation during ad-hoc retrievals.
Suppress Missing: When Rows is checked, rows that have all missing values will not be displayed. When Columns is checked, columns that have all missing values will not be displayed.
Retain Formulas: When checked, formulas will be retained in the ad-hoc retrieval. Please note that some ad-hoc behaviors such as Zoom In and Zoom Out can impact formula validity.
By clicking the Outline button, a dialog box with the full outline of the connected cube will be displayed.
You can navigate up and down the hierarchy by clicking members with a plus (+) sign to show the children beneath.
Select the members that you would like to see in your retrieval and then click the Write selected to sheet button. The selected members will then be added to your Google Sheet starting at the highlighted cell.
You can submit data back to the Essbase cube by clicking the Submit Data button. The user you are logged in as must have the appropriate rights to submit data back to the cube. The normal rules for loading Essbase apply. For a detailed description of these rules, please refer to the Oracle Essbase Administrators Guide for your version of Essbase.
You can run calculations by selecting the appropriate calculation script from the drop-down and clicking the Run Calc button. The user you are logged in must have the appropriate rights to run a calculation.
Using the POV String and MDX Query Box
The ExoInsight Google Sheet Add-in gives you additional functionality of pulling back any data in your Essbase cubes in relational (tabular) format. The add-in allows you to specify an MDX query or make use of ExoInsight's proprietary POV String syntax. Please see the POV String Documentation - Oracle Essbase Module document for detailed information about the POV String.
To open the POV String and MDX Query box, click the MDX or POV button. A drop-down list of any Saved POV String queries as well as a query box will be displayed.
Select a Saved POV, or enter a valid POV String or MDX statement in the query box, then click either the Run MDX or Run POV button. The data will then be returned to the sheet in relational (tabular) format: