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 hereSave 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

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 f…

OAC - Scripted Backups on Autonomous

So, you’ve got Oracle Autonomous Analytics Cloud and you want to schedule a backup of your BI and Essbase applications.

Essbase has the nice esscs/esscli command line REST API wrapper to which will perform an LCM backup for you and you’ve got the MaxL client which you can use to perform data exports. I’ll show an example of this further down this blog

However, at the time of writing this blog there aren’t any documented REST API commands to perform an automated BI BAR extract (these are in the pipeline). This means there isn’t an obvious way to schedule your own non-Oracle backup.

BI Admin Tool and Catalog Manager to the Rescue! Fortunately, you can use the command line to launch BI Admin Tool and Catalog Manager commands. There’s not much documentation so I’ve blogged how you use these tools to connect to your Oracle Analytics Cloud instance.

OAC RPD Backup Here we will download and backup our RPD using the datamodel.cmd tool which comes with BI Client. There’s some documentation on…

OAC - Bulk Importing Users & Groups via IDCS REST API

In my previous post (here) we saw how it's possible to bulk import users using a CSV file. Here we look at how we can automate creating/importing users using the IDCS REST API.

Note: there is a tool called the Identity Bridge Agent which you can install to sync your MSAD users with IDCS with no coding required. However, you will need to subscribe to a paid version of IDCS to use the Identity Bridge Agent. There are 3 versions of IDCS:

Foundation: Free with your OACS subscriptionBasic: Not included with OACS. You need to subscribe to this if you want to use the Bridge Agent. It also includes additional authentication features above FoundationStandard: Same as Basic with even more featuresGo here for information on IDCS versionsGo here for information on the IDCS Bridge Agent and here for a lovely tutorial on how you install it

Okay, back to the IDCS REST API. Here are the links to the Oracle documentation I used. Kudos again to the documentation team!
Make your first REST API call

OAC - Bulk Importing Users & Groups via the IDCS UI

A key part of Cloud SaaS is integrating with your existing systems. This blog will show you how to bulk import users manually via the IDCS UI. The second part of the blog (here) will show you how to do this in an automated way using CURL and the IDCS REST API.

IDCS User Federation All user/group security and roles for the Oracle Analytics Cloud are managed within the Oracle Identity Cloud Service. IDCS allows you to add users natively or use federation to authenticate your users with an external identity provider. In my case we used Microsoft Active Directory Federation Server (ADFS) as our authentication provider.

The setup is relatively simple and it worked first time! It also gave us true Single Sign-On SSO (the users just open their browser to access BI without having to login, user security is still maintained). This tutorial from Oracle is very good and all we needed to configure the security integration: Oracle Identity Cloud Service: Integrating with Microsoft Active Directory…

OAC - How to Create Oracle Autonomous Essbase and OBI Analytics Cloud Instances?

I've recently been implementing Autonomous Oracle Analytics Cloud for a customer. The customer had Essbase and OBIEE on-prem and we have migrated everything to the Oracle Autonomous Cloud.

It's been an interesting process and whilst there have been some small niggles the OAC offering is a very simple to use and powerful platform.

In this OAC blog (the first of several if I get the time!) I will show how easy it is to spin up new OAC instances. We shall create an Essbase instance and a Business Intelligence Enterprise instance, each running on one OCPU.

With the Autonomous Cloud, Oracle have made this process super simple!

There are free trials available, so I urge you all to give it a go and test drive the new features in Essbase 12c and Data Visualization. When you receive your login, you will get a URL which will direct you to your Oracle Services:


This will give you a huge list of …

PBCS: Financial Report Bursting to the File System

Hello blog, it's been a while. I got back from Kscope18 - Orlando last week and as usual it was a brilliant conference. My last blog entry (more than a year ago, tut-tut) was about OACS and some of the limitations. I've come back from Kscope with a completely different point of view. It's extremely good value (they're practically giving it away if you're a maintenance-paying on-prem Essbase/OBIEE customer) and the features and functionality ARE ABSOLUTELY AWESOME! I've already presented it to some of our customers and they love it. Anyway, back to the topic of this short blog entry...
I know it's old skool, but many users in the EPM on-prem world use the Financial Reporting bursting feature to burst out 100s of reports to the file system and automatically move them elsewhere for consumption. I didn't think that this was possible in PBCS but I was wrong!

If you select 'Export to external directory' in your batch definition, then the all the fold…