One of the most popular features of ExoInsight is the ability to easily extract Essbase and PBCS data and hierarchies directly into relational tables. Having Essbase or PBCS 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 and PBCS 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
- Oracle
- Amazon Redshift
- Snowflake
- Google BigQuery
- PostgreSQL
- MySQL
- 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 you can choose to clear the tables (if they exist) and recreate them with fresh data from Essbase or PBCS, or append to the tables that are already there. You can add SQL both before and after the process runs to do targeted clears, kick off a stored procedure to do additional processing after the data is loaded, etc. You can create a single script to pull in data from multiple Essbase or PBCS cubes into your relational database and set up a separate script to stage dimensions where the rest of the organization can access them. The possibilities for your organization to work with your Essbase or PBCS data are now literally endless.
ExoInsight enables all users to get their Essbase and PBCS 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 PBCS data access frustrations!
