Have you ever wanted to visualise your forecast using state
of the art tools?
Oracle’s PBCS is a fantastic tool for processing Forecasting
and Budgeting data, but despite Oracle’s best efforts, the dashboarding has
always fallen short of the competition. Regardless of how great their
dashboarding is, 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
PBCS with Power BI, as well as the pros and cons of several different
approaches. First, lets 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
The first step is extracting data from PBCS. If you’re
on-premise, you may as well stop reading now, because you can simply use Power
BI’s SQL database connector to sync up with your SQL data store in the Essbase
back-end and bypass any integration process. However, if you’re a cloud user it’s
not quite so simple. As my colleague Gui is fond of saying: “there are many ways to skin a cloud”
Vanilla PBCS 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
FDMEE 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
Financial Report using Export to Excel
Pros - Can efficiently export aliases and attributes
but not attribute aliases
Extremely
configurable, with header row easily defined for more dynamic load files
Cons- Limited size based on max cell count limit
in forms
Cannot
be automatically updated using EPMAutomate or batch bursting.
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’ve preferred the financial report where attributes
are required, and the DATAEXPORT command where they are not.
If you choose to use a financial report, you should save
an Excel workbook on a server and use Smart View to open the financial report
you’re using as the basis of your Power BI data set. Then, open Power BI Pro and
import the workbook as your data source, taking advantage of the great ETL
(Extract, Transform Load) tools in Power BI Pro to transform your data.
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.
When you run this business rule, you will be able to see
the file in Overview -> Inbox/Outbox
Explorer
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:
