One of the most popular features of ExoInsight is the ability to easily extract Essbase data and hierarchies directly into relational tables. Having Essbase data and hierarchies available in relational tables allows report writers to access the data from their favorite visualization tools and allows easy integration with any other system or process. Now that ExoInsight allows integration to just about any modern database platform, working with your Essbase data and hierarchies has never been easier.
Let’s look at how easy ExoInsight makes it to extract your Essbase data and hierarchies to a relational database. First, you’ll add a connection to your database in ExoInsight:
ExoInsight supports most modern relational database platforms. Some of the most popular are:
- SQL Server
- Azure SQL Database
- Amazon Redshift
- Google BigQuery
- Many others…
Once you’ve added the database connection information, click the “Validate Connection to Selected Environment” button to validate the connection information:
Next you’ll make a call to the ExoInsight REST API to push the data out to your relational database destination. You may use any method/application/process to make the REST call; for the purposes of this example we’ll create a simple PowerShell script:
The parameters in the red box should be filled out to determine which Essbase cube to connect to, the POV String for the data slice to extract, the table name where the data will land, and which dimensions to extract (all, none, or a list of specific dimensions). The highlighted SqlEnv variable is the connection you set up in ExoInsight in the previous step. In this case, we’re going to extract our data and all dimensions to our Amazon Redshift database.
Once the script is set up you can run it with whatever method you choose. Here we’ll run it through he normal Windows command prompt (this could be scheduled, called from Alteryx, or any other method for kicking off the script you want):
Once the script completes, relational tables will be automatically created for the data you specified with the POV String as well as the dimensions you specified:
Easy as that! Simply set up the connection, create the script, then run it and you’ve got a process that extracts Essbase data and hierarchies to your relational database of choice without the need to set up or maintain any Essbase calc scripts, report scripts, or any other Essbase object. In fact, ExoInsight is completely stand-alone and does not require any changes, updates, or other modifications to your existing Essbase environment!
You can now schedule the script to run whenever makes sense, or run it manually. Each time the script runs it will clear the tables (if they exist) and recreate them with fresh data from Essbase or MSAS. Create a single script to pull in data from multiple Essbase or MSAS cubes into your relational database, set up a script to stage dimensions where the rest of the organization can access them, etc. The possibilities for your organization to work with your Essbase or MSAS data are now literally endless.
ExoInsight enables all users to get their Essbase and MSAS data into a usable format for any use-case imaginable. Contact us today so we can show you how ExoInsight can end your Essbase and MSAS data access frustrations!