This blog entry focuses on configuring Oracle’s Planning and Budgeting Cloud Service (PBCS) in a unique way to report on various member attributes. At this point, you might be thinking, “Hang on, can’t we report on attributes in Oracle Hyperion Planning already, by simply assigning attributes to a base dimension?” Traditionally, the answer is yes.
Why to Use Smart Lists for Attribute Reporting in PBCS
So why would I want to use Smart Lists to accomplish this? There are three reasons:
- Attributes Unavailable with Planning Connection in Current Version of PBCS
Typically, attribute reporting is accomplished through reporting tools via an Essbase database connection. However, in PBCS, there is no Essbase connection; only a Planning connection. While Oracle is scheduled to provide attribute dimensions through the Planning connection in the near future, this functionality is not currently offered.
- Flexible and Variable Attribute Assignments
Using Smart Lists to represent attributes allows users to assign the attribute on the fly, without having an administrator go in to change the metadata. In addition, using Smart Lists permits the user to change the assignment based on other dimensions (years, periods, etc.). This allows for an entity to be assigned a certain attribute for FY15, and then a different attribute for FY16, while leaving the assignment for FY15 with the historical reference.
- Reporting Performance
Reporting on an attribute does not typically yield performance issues. However, in situations where there are several attributes, and reports are required in which multiple attributes are provided in the report rows, the traditional block storage outline (BSO) attribute model may have performance limitations.
What are Attributes?
In Essbase, attributes describe features or characteristics of a dimension. This is done by tagging a base dimension with an attribute dimension.
This allows for more detailed reporting on your application. What this means is that after you’re done with your planning, when you choose to extract and report on data, you can pull out the various characteristics or attributes of particular data sets.
The way this is achieved is by creating attribute dimensions and using these to tag a base dimension. For example, you could have a cost center such as “New York” and you want to describe more features about your New York sales region such as number of sales people or locations in New York. To do this, you would create two attributes called “Sales Managers” and “Location.” You would then tag “Cost Center New York” with all the sales managers for that region along with all products sold in that region.
It would look like this:
In your Oracle Hyperion Planning application, you would only be planning or budgeting by New York, in this example. But at the reporting end, you could now request further attributes of your cost center.
You can see why attributes are such a unique and important feature of Hyperion Planning and Essbase.
Select advantages of using attributes are:
- These do not take up blocks or “space” in your database
- They can be customized to be numbers, text, alphanumeric, etc.
- They can be used in calculations by using comparisons such as “>” than, “=” to etc.
- They can used to drill down to details of the base dimension in Oracle Smart View for Office for reporting
What are Smart Lists?
The Oracle Hyperion Planning Administrator Guide for Version 18.104.22.168 explains, “Smart Lists are custom drop-down lists that users access from data form cells in Oracle Hyperion Planning applications. When clicking into data form cells, users select items from drop-down lists instead of entering data. Users cannot type in cells that contain Smart Lists. Smart Lists display in cells as down arrows that expand when users click into the cells.”
Basically Smart Lists are similar to Excel lists and can be customized to provide a drop-down feature in a data entry web form that allows users to pick a value.
It would look something like this:
The screen capture above is from a real client instance and as you can see, there are six Smart Lists or drop-downs available for a user to pick values.
Generally in the past, we’ve used Smart Lists to assign items like a budget method (where in, based on a plan method, a particular calculation is executed) or simply to assign a characteristic of a base dimension much like an attribute.
A very good real world example is in salary expense budgeting where an employee can be assigned one of four different benefit rates. These rates can be in a Smart List drop down that the user picks for each employee. We would then have a simple business rule that looks at each benefit rate Smart List value and performs a calculation based on the value picked.
Moving on, let’s now discuss real world uses of attributes and Smart Lists.
In one of my most recent PBCS implementations, my customer’s requirements were simple. They had a small planning application that allowed them to create a four year long range plan (LRP) and a one year forecast.
They have a “Cost Center” dimension that offers six additional reporting characteristics which are not required for them to perform their long range plan, but rather needed for additional reporting. These additional characteristics include:
- Scholarship Types
- Officer Codes
Now, having performed multiple Essbase and Planning implementations, this would sound to me like a classic example where “attributes” should be applied.
Normally, the way this would be designed is:
- Keep the application small and robust with “Cost Center” as the base dimension
- Create six attribute dimensions for additional reporting
- Tag various Level Zero Cost Centers with the relevant attributes
- Report on these attributes using Smart View and Oracle Hyperion Financial Reporting
What’s Easier for The Planner?
What we learned above: Attributes can be used for reporting when connected to an Essbase database.
However, only admins can change or modify the list of attributes assigned to a base dimension. User cannot do this. As a result of this, there is no instant or fast way for a planner to see how changing an attribute will impact the business.
How Smart Lists Give Power to the Planner
As I mentioned above, our customer had specific requirements around attributes, and needed to report by six additional attributes of their cost center.
Waiting for an admin to constantly make these attribute changes and assignments meant there would be significant delays and the overall budget process would be impacted.
In order to allow the planner to be able to pick from his or her choice of members from a drop-down allowed the planners to look at multiple variations to their budgets. It also allowed planners to try out various combinations without resorting to admin assistance.
In order to accomplish this, we created multiple Smart Lists that were all tagged to accounts and displayed in a data entry form.
To summarize in three steps, we:
- Used Planning Smart Lists to capture each attribute for all of the cost centers (this was prepopulated via a load)
- Smart List values were ported to RPT (ASO environment) as actual dimensions; allowing for full reporting
- This is the primary reason for existence of a “RPT” plan type (database)
In Part Two of this blog we will delve further into this solution…stay tuned!
Author: Mohan Chanila, Performance Architects