Brovanture Power-Bi

Integrating Oracle Cloud EPM Planning with Power BI Pro

Mike Falconer
Brovanture Consultant

Have you ever wanted to visualise your forecast using state of the art tools?

Oracle Cloud EPM Planning is a fantastic tool for processing Forecasting and Budgeting data, but the dashboarding does not have the functionality of some of the widely available specific BI dashboarding solutions. Most execs want to see management information from all their systems together in a simple, user-friendly, central tool.

I’m going to walk you through the use case for integrating Oracle Cloud EPM with Power BI, as well as the pros and cons of several different approaches. First, let’s identify the main requirements:

  • Data should be extracted at a sensible level for dashboarding
  • Reporting attributes should be included if possible
  • Regular integrations, ideally automatically performed overnight, are essential

Oracle Cloud EPM has come on leaps and bounds in Cloud-to-Cloud connectors, but these are pretty limited to Oracle products. This means we need the tried-and-tested flat-file approach to connect to Power BI. This means involving a very basic EPM Automate host server alongside the Oracle Cloud EPM solution, which will hold the exported flat file that Power BI Pro will refresh from.

The first step is extracting data from Oracle Cloud EPM Planning. If you’re on-premise, you may as well stop reading now, because you can use Power BI’s SQL database connector to sync up with your SQL data store in the Oracle Essbase backend and bypass any integration process. However, if you’re a Oracle Cloud EPM user it’s not quite so simple. There are several ways to extract the data you need.

Vanilla Export Data

Pros –   Uses a wizard and works for ASO cubes

Cons –  Outclassed in every way by a DATAEXPORT command in a business rule unless ASO

Requires significantly more ETL by the Power BI tool.

DATAEXPORT command in Business Rule

Pros –   Extremely configurable, see the Oracle SET DATAEXPORT OPTIONS page for all options

Can be run using EPMAutomate and automatically downloaded from the cloud

File can be exported in columnar format that suits loading into Power BI

Cons – Can’t export attributes, and export file requires a dense dimension in the columns

Heading row is exported over two rows and requires some fiddly batch scripting to process

Can’t export aliases

Data Integration Export to Flat file

Pros –   Can use mapping tables to simulate exporting aliases

Can be run using EPMAutomate and automatically downloaded from the cloud

Cons–   File is exported in Essbase format rather than columnar format

Complex to set up and unwieldly to understand

Mapping tables add extra maintenance

Each of the options above has its place so take a read through each one and identify which set of pros and cons fit your set of requirements. Personally, I like the DATAEXPORT option as it’s quick and simple to set up, and you can use the ETL within Power BI Pro to handle most simple mappings.

I’ll show below how to set up a fully automated approach using the DATAEXPORT command, as it’s the most elegant solution for near-live dashboarding I’ve found. Firstly, select your subset of data that you’d like to export. You need a dense dimension to act as the columns and it must not be period. I’ve used accounts, and then specified a smaller subset of accounts to decrease the export size.

  • Level – Level0 is typically the best for integrating to Power BI as you won’t have your dimension hierarchies, you’ll be wanting to split data using your other dimensions
  • OverwriteFile – Must be ON to avoid needless admin
  • ColFormat – ON, this gives you a nice columnar format for loading
  • NonExistingBlocks – Up to you. If your subset is small enough, this can help ensure you get all dynamic data. If everything is stored, turn it off
  • ColHeader – The dimension on the columns. I’ve used account, must be dense
  • DynamicCalc – If you want dynamic calc members included, turn this on
  • DimHeader – ON, we need this to make the export more dynamic
  • Missing Character – Either blank or 0. This helps Power BI pick up the column as numeric
  • Filename – Put it in /u03/lcm which will allow you to export using EPMAutomate

Brovanture Power-Bi

 

When you run this business rule, you will be able to see the file in Overview -> Inbox/Outbox Explorer

Brovanture Power-Bi

 

Next, write yourself an EPMAutomate script to run the business rule, download the file, and process the two header rows into one header row. Or if that seems like too much work, use mine:

Brovanture Power-Bi

The above will process the downloaded file and change the header row as shown below:

Brovanture Power-Bi

Next, simply load up your Power BI Desktop app and select the data file you’ve created as your source data. You can now perform as much ETL as you’d like to the file, and that will be repeated on all future queries on that file. I highly recommend performing the following steps:

Merge Period and Year columns, with a space between them.

Change the data type of the column to Date and Power BI will turn it into one Time dimension.

So, to recap, at this point we have:

  • A business rule to export any data that we want from Cloud EPM Planning
  • A script that automatically runs the business rule, downloads the output and processes it into a Power BI format
  • A Power BI query which can be used to build dashboards

All that’s required is to schedule your EPMAutomate script to run on your server as regularly as you want, and to set up Power BI refreshes at a similar timeframe. To do this, you will need to publish your dataset to the Power BI online web application. Afterwards, select the dataset and set up scheduled refreshes.

You will need to download gateway software onto your server and log in to it using an account with a Power BI Pro licence to set it up. Microsoft walk you through it very simply in the Power BI dashboard.

Brovanture Power-Bi

 

Now you have near-live dashboarding from your forecasting and budgeting system, allowing you to analyse your data at any level you choose, and produce some fantastic dashboards!

Until next time.

Mike

For more useful Oracle Cloud EPM and NetSuite ERP blogs posted by my colleagues, see the Brovanture website HERE