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:

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

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

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:

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:

Then you’ll get a successful message:

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:

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):

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

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:

Choose to create a new report:

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

Then select the new Essbase Cloud data source created earlier:

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:

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

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

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:

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

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:

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

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:

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):

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:

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

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

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):

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

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

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

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:

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

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.