EPRCS Series: Connecting MR to Essbase Cloud

One of the cool tricks of EPRCS is its ability to connect to Essbase Cloud. It can handle this through multiple tools under its umbrella: Narrative Reporting and Management Reporting. Management Reporting (“MR” for short) is a static reporting technology within EPRCS that is much like Hyperion Financial Reporting (a.k.a. “FR”). It’s built on a better platform than FR and has started transcending FR in features already. It’s expected that MR will reach parity with FR’s features by the end of this year.

MR connects to just about every Oracle EPM Cloud product. It’s also the only Oracle EPM Cloud static reporting technology that connects to Essbase Cloud. In this post I’ll explain how the Essbase Cloud connection must be set up and how MR looks and feels in general with Essbase Cloud as its source.

In full transparency, I’ve been connecting MR to Essbase Cloud for some time. However, the security part has kept me from blogging about it since it would take time to test. I finally took some time to test it all out this past weekend. This blog post is more of a treatise on how to configure security properly between MR and Essbase Cloud than anything else.

Before we get started, note a couple of things about how the Essbase Cloud connection is configured:

  • The Essbase Cloud connections are set up in EPRCS by cube. Therefore, if your Essbase Cloud application has 3 databases, you need 3 separate MR Data Source connections to have full connectivity to that entire application.
  • The Essbase Cloud Administrator is responsible for defining and maintaining the Essbase Cloud Data Source connections within EPRCS. Essbase Cloud Administrator credentials will be required for each MR Data Source connection. Therefore, it is recommended that the MR Administrator and Essbase Cloud Administrator work in conjunction to set up each Data Source connection.
  • Security notes for EPRCS:
    • MR Administrator: should be set up with EPRCS Reports Administrator or EPRCS Service Administrator rights to help create Data Source connections.
    • MR Developer: should be set up with a minimum of Reports Administrator rights to create and maintain MR reports.
    • MR User: should be set up with a minimum of User rights and then must be granted at least view access to the appropriate MR reports.
  • Pass through security notes for Essbase Cloud:
    • MR Administrator: a matching user ID and password must be added to the Essbase Cloud instance with a minimum of Service Administrator rights.
    • MR Developer: a matching user ID and password must be added to the Essbase Cloud instance with a minimum of Database Manager rights on the connection cube (currently). I felt it was a big ask to have an MR Developer be granted Database Manager rights to an Essbase Cloud cube, so I asked for an enhancement. Apparently, there was an existing bug that will be fixed in the April 2018 release (#safeharbor). When the enhancement is out, an MR Developer will only need a minimum of read/view rights on the cube in question on the Essbase Cloud instance.
    • MR User: a matching user ID and password must be added to the Essbase Cloud instance with a minimum of Database Access with read access rights to the appropriate intersections of data (which I accomplished through Essbase security filters). <– This is how it will work. The enhancement listed above for the MR Developer, once applied, will correct the MR User pass through security as well.
  • When the EPRCS user password expires for any of the above roles, that user should then go in and update the corresponding Essbase Cloud instance password for the same user to keep the passwords in sync.

Within EPRCS, connections to other Cloud products are set up in the Library through the Data Sources folder:

Library - Data Sources

To create a new Data Source, press the + button and select “Data Source”:

Library - New Data Source Connection

Select the “Oracle Essbase Cloud” option from the drop-down (and notice the other options available for future reference):

New Data Source - EssCS drop down

Then fill in all of the details. I’m going to choose to connect to the super generic, out of the box Sample.Basic Essbase Cloud cube:

Essbase Cloud Data Source Connection

Some notes about the required connection details:

  • Data Source Name: Since EPRCS can connect to multiple tools, I’d suggest a prefix that groups the data sources based on some logic. I’m choosing source technology in the screen shot above.
  • Server Name: This needs to be the public Essbase Cloud IP address. No other information (like port or a prefix of http://) is required at this time. So it might look something like “125.11.11.111” (<– completely fictitious for blog post’s sake – don’t try to hack into this IP address).
  • Administrator User ID: a Service Administrator on the Essbase Cloud instance
  • Administrator Password: Essbase Cloud Service Administrator’s password
  • Application Name: Essbase Cloud application name
  • Cube Name: Essbase Cloud cube name

For first time setups, you should see the following warning message. Click “Yes” to continue:

Connection Warning

Then you’ll get a successful message:

Connection Succeeded

Once the connection is set up, you can use your Essbase Cloud cube as a data source in MR.

Before we cover that part, let’s go through some common security error messages. Security is a bit challenging to understand at first, since the EPRCS side is part of the authentication, in addition to the Essbase Cloud Administrator.

If you entered either the wrong username or password for the Essbase Cloud Administrator, you might see this error message:

Security Error - wrong user or password

If the Essbase Cloud Administrator you’ve entered does not have Service Administrator rights on the Essbase Cloud instance, you might see this error message (the blurred out part is the EPRCS Administrator username):

Security Error - Essbase Security Issue

If the EPRCS Administrator does not have Service Administrator rights on the Essbase Cloud instance, you might see this error message:

Security Error - EPRCS Security issue

Now that the basic connection configuration has been explained, lets continue on with setting up an MR report using Essbase Cloud as the data source. I’ll cover just a few essentials to see how an Essbase data source looks and feels. Navigate to the Reports (MR) area:

Navigating to MR

Choose to create a new report:

Creating a New MR

Click to “Setup Grid” in the grid area of the report to choose Essbase Cloud as the data source:

MR-Setup Grid

Then select the new Essbase Cloud data source created earlier:

MR-Essbase Cloud Connection

This might take up to 30 seconds to connect. Once Essbase Cloud connects, you’ll see the dimensions laid out in a default format in the report:

MR-Essbase Cloud Dims

Select the “Show Dimension Layout” icon to reorder the dimensions in the report:

MR-Show Dimension Layout

From this screen, the dimensions can be dragged and dropped across the POV, Columns, and Rows:

MR-Change Dim Layout

I’ve set the dimensions up in a test layout and gone back to edit mode for the grid (by deselecting the “Show Dimension Layout” icon. Next, I will take a look at relationship functions. First, I’ll select members for the Year dimension:

MR-Change Year Dim Members

Within the Member Selector, I can select relationship functions to make my member list more dynamic:

MR-Year Member Selector

In Sample.Basic, Year expands to Quarters and then Months so I’ll remove the default “Year” member selection and choose Bottom inclusive to include only the Months and the Year total:

MR-Member Functions

I’ll also choose my default members for the other dimensions and apply some light formatting. Now my report result looks like this:

MR-First Draft Report.jpg

My report needs a title. How does that work in MR with Essbase Cloud? After adding a text object to the header section of my report, I look at my options for text functions:

MR-Text Functions

There are quite a few more than captured in my screen shot above, but I think a few short of what’s available in FR. I’ll use the usual ones to create a dynamic report header. My report now looks like this (with the dynamic text supported by text functions in red font in the header below):

MR-Second Draft Report

So how about some Essbase-specific features in MR? For instance, can you use Essbase Cloud substitution variables? What about member aliases? Absolutely!

Here, I am showing a few substitution variables from the Essbase Cloud instance:

Essbase Cloud Subvars

I can access these from MR in my report development. Here is how you navigate to them from a POV dimension:

MR-Essbase Subvars

And here are the neat function options you can use with them:

MR-Subvar options

Aliases, I noticed, are selected by default over member names. But you can select member names, aliases, or both in the various report properties (much like FR):

MR-Member Labels

You can also distinguish between the multiple alias tables available from within Essbase Cloud:

MR-Alias Tables

You can suppress data in columns, rows, and the entire grid, too. Here is a screen shot from the column properties:

MR-Data Suppression

You can also format according to Essbase Cloud member generations and levels. Here is the auto-indent by level feature in the row properties:

MR-Row Property Indent

And within Conditional Properties, you can use generations and levels again to format based on the specific conditions you set. Here is where conditional properties are set up for columns:

MR-Column Cond Suppression

Here is an example of a conditional format that I’ve set up to bold total columns:

MR-Conditional Formatting

Honestly, working with Essbase Cloud as a data source in MR feels like working with just about every other data source in MR. The EPRCS environment is purpose-built to provide one development experience for every supported data source.

The Oracle EPRCS Product Management and Development teams have come a long way in securing near parity with FR. There is already a migration path for FR to MR. Once the security enhancements and FR functionality parity are fully in place, MR will be a fantastic tool for static reporting in Essbase Cloud. I am excited about what’s available in MR today and where they plan to go.

One thought on “EPRCS Series: Connecting MR to Essbase Cloud

Let it out here

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s