Analyzing Varying Attributes in Essbase by Bernhard Kinkel

I want to present you with a new 11.1.x version feature for extending analysis using Attribute Dimensions.

Using attributes somehow implies that the attributes assigned to existing members in a database never change or at least have a high persistence, i.e. characteristics are attributed to a single product, which do not change under normal conditions. So the limitation of being able to associate only one unique value of an attribute to a given member generally works fine (i.e. in a database's product dimension, drinks are either caffeinated or not, but not both at the same time).

But there are also cases, where an association applies at a certain moment, but is subject to change over time. This might be a customer's importance that could change from low to medium or even high, or a product manager being responsible for dedicated product lines, but due to organizational changes responsibilities are aligned. On the one hand, such changes in the associated attributes in the database outline are easily made at any time and can be applied with a few clicks. But on the other hand the impact it causes on data analysis and consistency has to be considered carefully, because attributing e.g. a different product manager to a product line, effective from today onwards, will not keep tracking data from the past associated with a former product manager separated from the new data under the new manager. Changing the association will instead cause all data throughout the entire database to be dedicated to the current product manager, and nothing to the former manager - not even data from before the change. This was and is still the common behaviour of using attributes in Essbase.

But now an enhancement to Attribute Dimensions starting from version 11.1.x gives you more flexibility to handle these cases, without the necessity of creating new stored members or even a new dimension, and thus affecting the database size in a negative way. This new option is called Varying Attributes.

By defining one or more dimensions as so called independent dimensions it is possible to assign different attribute specifications along the base members of these dimensions, i.e. for a dimension Time Periods with months on level0, you can assign varying product managers per month as required according to the following example:

screenshot 1

As a first step, in order to create varying attributes (assuming, that the other standard dimensions, like Product and Time, already exist), a common Attribute Dimension with its members needs to be created in the outline.

Tip: You can also use any already existing attribute dimension and add the functionality for varying attributes to it. But be aware, that all existing member associations will be discarded and will have to be created again as described subsequently.

Then within the outline's Properties tab the option "Varying attributes enabled" needs to be set to "true" in order to activate the database for using varying attributes.

Tip: This setting can be reverted at any time, but will cause a loss off all formerly made assignments.

screenshot 2

The next step is associating the attribute dimension with an independent dimension, and - if not already done - with its base dimension, which is the standard dimension for which the attribute should be used and where the numbers come from. These definitions are made on the Attributes tab of the member properties, the steps are shown in the following figure. Just in case, the attribute dimension has not yet been assigned to its base dimension, after step 3 the attribute dimension is first shown in the "Other attribute dimensions" box. From here select the dimension and click the Assign button in the middle of the window - step 5 could also already be incorporated here. Finally click OK.

Tip: If the "Independent dimensions" selection box on the lower right corner of the Member Properties window is not immediately visible, just hover for a short time with the mouse cursor over this area, the box will then show up.

screenshot 3

When selecting independent dimensions, you can treat each one as continuous or discrete. Continuous independent dimensions, such as Year or Periods, are characterized by members that are listed in a specific, meaningful order in the outline. For example, because a product manager's responsibility for distinct product lines can vary over time, you can create a manager attribute that varies over continuous ranges in the time dimension (i.e. Jan to Jun), like shown in the first figure of this article.

Discrete independent dimensions, such as Scenarios or Regions, are characterized by members that might appear in any order in the outline. For example, if product packaging varies regionally, you can create a packaging attribute that varies across discrete regions.

To change the Continuous/Discrete setting, double click on the underlined item. If you have more than one independent dimension assigned, discrete dimensions must appear first in the list. Reordering the dimensions could be done using the up/down arrows on the right side of this box.

Now the assignment of varying attributes to base dimension members can be done. The example shows how to assign a product manager to a certain product for the period June to December: After accessing the Member Properties of the base member, go to the Associations tab and select the respective manager by highlighting it. Next, if you click on the Add Range button below, another selection box opens, providing you with the months as base periods of the Year dimension, where the left panel represents the starting period and the right one the ending period. Your selection will be shown accordingly on the bottom of this window. Then click on Add Range - the windows disappears. Finally in the still open Member Properties window, click the Assign button in the middle of the window. Now click OK or on Next, if you want to make further assignments.

screenshot 4

After creating all associations between the base members and the attributes, you need to save your outline. Subsequent restructuring should be rather fast, as these changes only require an outline restructure, not a data restructure. No new, additional cells in the database are created, no additional data has to be loaded and no calculations have to be run - an analysis could be done right away showing the following details:

screenshot 5

As an additional feature you have the ability to show a kind of "What-if" analysis in your report: using Smart View in Excel, under the Hyperion menu there is a new option called "Data Perspective". Here you can select an individual base member of the independent dimension (in our example a month) in order to show data in a way as if all assignments were made only with the product manager from that respective period. Available standard options are Reality (activated by default), First, Last or Custom. While First and Last directly select the first or last available member according to the outline, the Custom options lets us choose any of the level0 members of the independent dimension: referring to data from the report in the last figure selecting Feb for example and then refreshing the report would show all data for Lightbolt and Thunderball under Sergiy; selecting Nov instead would show all Lightbolt data under Pete and all Thunderball data under Sergiy after refreshing the sheet.

As data for attribute dimensions is dynamically calculated, a change of data perspective can be done quite easily at run-time, but keep on mind, that it also requires data to be recalculated at run-time.

Tip: Using First or Last refers to all members of an independent dimension in the outline, even if not used for any assignment. Thus having other level0 members before or after the assigned or used range of members might cause unexpected results to be displayed in the report when selecting First or Last. Choosing the Custom option instead and then selecting a concrete member from the drop-down list is the clearer option in this case.

In order to complete my explanations around varying attributes, the following screenshots show an example with two independent dimensions, one continuous and one discrete.

The assignments should be as follows:

screenshot 6

Accordingly, the definitions for Teresa on the associations tab in the member properties window are:

screenshot 7

And finally the report would look like the figure below, where "---" replaces invalid combinations (#Invalid) and "-" indicates no or missing data (#Missing).

screenshot 8

You can find general information about offerings for the Essbase curriculum and other Oracle-Hyperion products here (please make sure to have the corresponding country/regional setting made on the top of this page) or in the OU Learning paths section, where Essbase and Hyperion products can be found under the Fusion Middleware heading (again, please check the right country/regional setting).

Bernhard Kinkel

Bernhard Kinkel started working for Hyperion Solutions as a Presales Consultant and Consultant in 1998 and moved to Hyperion Education Services in 1999. He joined Oracle University in 2007 where he is a Principal Education Consultant. Based on these many years of working with Hyperion products he has detailed product knowledge across several versions. He delivers both classroom and live virtual courses. His areas of expertise are Oracle/Hyperion Essbase, Oracle Hyperion Planning and Hyperion Web Analysis.


Post a Comment:
Comments are closed for this entry.

Expert trainers from Oracle University share tips and tricks and answer questions that come up in a classroom.


« July 2016