OAC - Essbase Streaming Dataload from SQL Server Database

Here's a short blog on streaming to the OAC Essbase cloud from a MS SQL Server source. The team at essbaselabs have written a nice blog on streaming Essbase data loads and the Oracle docs are here.

• Download your MS SQL driver from here
• Save it to your client machine and add the path to the jar file to your EXTERNAL_CLASSPATH in the esscs.bat file:

• Using essCLI create the local connection to your on-prem data source:

esscs createLocalConnection -N MSSQL_EPMDB -cs jdbc:sqlserver://epmdb:1433 -u epmsql -D com.microsoft.sqlserver.jdbc.SQLServerDriver

• Run the dataload command. Here I'm passing the SQL query into the command but you can also store the query in your Essbase rules file:

esscs dataload -application Sample -db Basic -rule SQLData.rul -stream -connection MSSQL_EPMDB -query "Select * from Sales"

Error handling is good and logged in the essbase-cli.log file:

I then correct my SQL statement and the query is successful:

For rules related errors a familiar dataload.err file is created in your working directory:

Correct the rules file and the load is successful:

The other nice feature is that essCLI will compress the data whilst it flies over the interwebs to the Essbase cloud. This means that it reduces any latency which could be associated with loading data from an on-prem source to the cloud.

So there we have it, a very simple way of loading data from your on-prem SQL source to your Essbase cloud target and no messing about with networks and firewalls etc.