Have you ever had a need to group hierarchy roll-ups as based on characteristic, for example utilizing a tag called “Green” to capture all green-colored “Item” dimension members? How about the prospect of reporting data using such characteristics in Oracle Hyperion Planning, Essbase, Oracle Planning and Budgeting and Cloud Service (PBCS) or Enterprise Planning and Budgeting Cloud Service (EPBCS)?
Great news! Both business cases can be satisfied with little effort through the use of attribute dimensions. In this blog post, we’ll discuss the topic of attribute dimensions (hierarchies commonly referred to as “attributes” for short) in detail within the context of PBCS. Sub-topics include how to create and manage attributes, how to integrate them into forms and reports, and how to leverage them in calculations.
What are Attributes?
In Oracle Hyperion Planning, Essbase and Oracle EPBCS/PBCS, attributes are essentially descriptions of the data. These dimensions supplement sparse dimension hierarchies. Oracle sometimes uses the example where the sparse dimension of interest is “Product”. This dimension might have color associations for its members such Green, Yellow, Blue, etc. In this example, “Color” would be the attribute dimension and the associations “Green”, “Yellow”, “Blue”, etc. would be attribute values (members).
Let’s consider another example – a primary dimension of interest called “Item” that is associated with attribute dimension “Shape”. “Shape” might have values “Circular”, “Square”, “Rectangular”, etc.
Attributes are commonly used to group data points through alternative means as opposed to utilizing the existing dimensional roll-ups. They are also used for reporting purposes. For instance, consider the previous case – we could choose to build an ad-hoc grid for only items with the “circular” attribute association and visualize P&L information for that group alone.
Watch the demonstration!
Read the full blog post!
Creating Attribute Dimensions and Adding Member Values in The Web Interface
Consider our PBCS application called “PLNtstc1” (pronounced “PLANtastic1”). Through the Web Interface, we can visualize our required “Entity” dimension:
There is also an attribute dimension associated with this dimension called “Division”:
As you can see, “Division” has the “Text” Data Type, but there are a few other data types available as well. Let’s consider key constraints for attributes:
Per Oracle requirements, Level 0 attribute dimension members are restricted to certain data types:
Attribute dimensions can only be associated with sparse dimensions – for example:
- Product (an example Custom dimension)
- Item (an example Custom dimension)
- Project (an example Custom dimension)
Another notion to keep in mind is that you are required to associate attribute dimension values with members at the same level within the associated dimension
- For example, if assigning attribute values for Level-0 “Product” dimension members, you can’t one day choose to assign at the level above that unless you modify the existing associations
With those points in mind, let’s walk through how we would setup an attribute dimension hierarchy within the application.
1) Consider the sparse dimension “Department”. Highlight the dimension name and click the tool icon as in the screen below to access the associated attribute dimensions:
2) Here we can review our associated attribute dimension – “Territory”:
3) From this screen, we can add, edit, or delete attribute dimensions (left -hand side) and/or attribute member values (right-hand side) as needed. We can “Save” once done.
4) Next, go back to the sparse dimension (“Department”) and add attribute associations (values) to members as needed. This process is alternatively referred to as “assigning” attributes to members. Click a member of interest (for example, “FP&A”) and click the pencil at the top to “edit” the member:
Next, click the “Attribute Values” tab. “Add” or “Remove” values as needed and click “Save”:
We can repeat the steps for the “Treasury” member and can confirm we have assigned attribute values as we would expect. We might also want to refresh the cube for good measure.
Loading Attribute Hierarchies Through a PBCS Job
Here, we will take a look at how to utilize PBCS jobs for managing attribute hierarchies within our example application. Begin by accessing the PBCS Simplified UI:
Next, access the “Application” button and click “Overview”:
Click the “Dimensions” tab:
At this point, if you haven’t worked with importing metadata before through job functionality, it could be good to “export” the CSV file format for the hierarchy that was setup through the web interface. We can then modify that hierarchy and import into PBCS.
To do this, click “Export” and then “Create”:
Select the dimension of interest (in our case “Territory”) and click “Export”:
Open the downloaded zip file and review the hierarchy in Excel. Add members as needed/modify as needed (as in example below). Save the CSV file and use the “Import” button in the Simplified UI to bring the latest file’s metadata into the application:
*Note that this process can be automated using the EPM Automate utility
Managing Attributes Through Smart View
In cases where attribute dimensions might have many members, it might be best to use Smart View for management purposes.
In this case, simply login to Smart View and connect to the application. Then, drill in on “Attribute” dimensions within the Panel:
Double-click on the attribute of interest to open the dimension (in our case, “Territory”)
Zoom-in, modify/add members as needed, and submit the updated metadata to the cube.
Using Attributes in Data Forms
Now that we have reviewed the methods for managing attributes, let’s discuss how we would use them in data forms. Consider this example where we use attributes in data forms to filter and isolate specific data sets.
Let’s assume we are inputting working budget data into our database for FP&A and Treasury – two departments we discussed earlier that are associated with the “Southern” territory. Let’s also assume these two departments are within our Entity “Uruguay”.
We begin by inputting data into a data form like we normally would, for example:
In the standard hierarchy for “Department”, “FP&A” and “Treasury” both roll up to “Finance_Organization”.
But what if we wanted to see the “Southern” territories roll-up?
In this case we could design a form where we filter for specific territories (like “Southern”). Access the form “Layout” and move the attribute dimension to the axis of interest (in this case, “Territory”). We can also allow for selection of descendants in the Page axis:
*Note that here we are using the same form as “Operating Expenses_v4” where we input our data, except now we moved the “Territory” attribute dimension to the “Page” axis and essentially saved the form as a new form that is “Operating Expenses_V4_withAttributes”.
Once completed, we can view the form for “All_Departments” but filter for the attribute grouping that is “Southern”. Here, you can see the totals for “Southern” departments as we would expect:
You can see this functionality would be very useful if we had multiple departments in multiple territories.
Using Attributes in Smart View Reporting
We can also utilize attributes in Smart View reporting. Let’s demonstrate this by continuing our Department/Territory example.
Begin by accessing Smart View and connecting to the database, and then open an ad-hoc grid. To save time, let’s type member names using the free-form reporting method where possible instead of using member selection:
In this case, notice that we keep the base dimension (what we’ve been referring to as the sparse dimension – “Department”) untouched. You can see in the image above that we left it at the parent level. We do this because there is no need to drill-in here – instead we will be integrating the attribute associated with that dimension for use in filtering. Again, we can simply type in after inserting a column:
And from here, we can double-click/zoom-in, or use member selection or simply type in our “Territory” attribute dimension value that is “Southern” to see our data as we would expect:
Using Attributes in Financial Reporting
With the July ’16 release of PBCS, we can use attributes to filter data in Financial Reporting as well.
Consider our Department/Territory example using FR Web Studio.
Begin by creating a new report and inserting a new grid. Connect to the database and you will see the “Dimension Layout” page as you would expect:
In a manner similar to data forms – move the attribute dimension to an axis of interest and customize as desired:
You can type in member names or use member selection. Observe that we are keeping the “Territory” dimension in the POV so that the user can select the attribute value as desired to be used as a filter.
After we save the updated report, we can visualize it in Workspace. Again, we can keep the “Department” dimension untouched and filter for “Southern”. We will see the filtered data as we would expect:
Using Attributes in Business Rules
In addition to using attributes in forms and reports, we can also use the function “@ATTRIBUTE” to run calcs against base dimension members that are tagged/associated with the specific attribute.
For example, consider the Business Rule below in the context of our example:
We are essentially aggregating data for the “Southern” territories that also exists in the “South_America” Entity roll-up (which includes “Uruguay”).
After running this rule, we can see data for “South America” as we would expect considering the “Southern” territory grouping:
In conclusion, attributes are very useful for filtering data through a number of different tools. Since the July ‘16 release, attribute reporting is possible through PBCS. Attribute dimensions and associations can be managed in similar methods that are applicable to managing base dimension metadata (e.g., through web interface, Smart View etc.).
We hope you learned a lot from this post and will consider attributes in the future!
Blog post by Noah Neal of Key Performance Ideas.