Analyzing text and dates in Essbase by Bernhard Kinkel
By Georgiana Codrescu on May 18, 2010
Everybody working with Essbase as an administrator will know that the database model is designed to operate with numbers and to execute mathematical operations up to a very high and complex level. In other words: Essbase calculates and that's what it does really well. Based on this, the analysis of Essbase data means creating reports on numbers at aggregated or detailed levels and their interrelations. By the way, that's also where we find the origin of our Extended SpreadSheet DataBASE. But starting with version 11 there is a new option available that enables you to show textual information in the data fields of a report, where before it was only possible to show numbers: the so called "Typed Measures". In this article I'd like to introduce you to this new feature and show you how and where to use it. At the end of this article, I present a nice enhancement for working with "text data".
But first, here are two examples to give you an idea of the potential usages:
- assigning one package type to each individual product sold
- assigning a textual satisfaction measurement to sales staff in a company
But how can Essbase now operate with alphanumerical information in the data fields, when there only used to be numbers? Well, Essbase hasn't really changed: it still stores numbers in the data cells in the database (which anyway is very useful as you will see later). But the new feature enables you to create a Text List in Essbase to store self-defined text labels and display them instead of the numbers in the background.
First of all you need to activate a new option on the Outline Properties tab in the Outline Editor setting "Typed Measures enabled" to "true".
Next you need to create the Text List in the left panel on the new Text List Manager tab as shown in the next diagram:
After this you can start to define the ID-Name mapping on the right panel of the Text List Manager by adding lines and filling them with IDs and labels.
As a final action you save the list.
Tip: The ID numbers don't have to be consecutive. If there is a chance, that you will be adding more labels at a later time, which may need to fit between already existing IDs, it's a good idea to leave "spaces" in the numbering, e.g. 1, 6, 11, ... In your reports, only the labels are displayed, regardless of the ID that is used in the background.
Now you need to create a member in the outline to which the Text List will be attached. Usually this will be an Accounts dimension member. After creating the member, it will be assigned a Type "Text" in the Member Properties. In the subsequent step you select the Text List you have just created in the preceding step from the "Select Text List" drop down menu.
Tip: For new members, after making them a Text or Date type member, the Never Consolidate operator (^) is automatically applied.
After finally saving the outline and the restructuring of the database, the new member is ready for use. You have two ways then to populate data cells: data can be loaded to the member or entered manually.
In the first case, you can load any number, also those with decimal places, using the common load procedures. But this wouldn't really make sense, because in your reports only the ones that exactly match the defined IDs will display the label - any other or missing number will be shown as "Out Of Range" or "Missing" or any other label that you have defined for these values. In other words: you must make sure, that decimals are rounded or truncated and that numbers fit into the range of IDs.
Regarding manual data entry for text members, there will be no need to do this, as data entry can be done only by selecting from the displayed pull-down menu with the list of predefined items as shown in the first diagram of this article. So no "wrong" data/text can be entered. Write access is required for this type of data entry.
In both cases, subsequent data analysis then only shows the labels for the loaded/entered data of your text list member.
What about creating a date type member instead of a text type member? Let's have a look at some of the slight differences in the process:
The first steps are almost the same, enabling the outline for Typed Measures and creating a new member on which data will be stored. The creation of a Text List is not necessary for date type members and thus can be omitted.
The next actions are now different: tagging the member as "Date" type under Member Properties (without a Text List assignment!) and making a setting for the date format. This final setting is not done under Member Properties but in the Outline Properties tab, as this applies to all date type members throughout the entire database.
Again after finally saving the outline and restructuring the database, the new member is ready for use.
You might say at this stage, that this is nothing else or different, than tagging members with an attribute, and somehow you are right. But there are definite differences:
- Firstly there is now the possibility to assign different tags (or "attributes") to a single member across combinations with other existing dimensions, e.g. across different time periods or regions.
- Secondly we now have the advantage that users can make the assignments themselves (assuming they have write access to these cells), which would not be possible for UDAs, Shared Members or Attribute Dimensions, where the administrator has to make the assignments in the outline.
- And thirdly we should keep in mind that, unlike "real" attributes in Essbase such as UDAs, Shared Members or Attribute Dimensions, a stored input member is needed in any case to store the numbers for our Typed Measures. As this probably will be a member of the Accounts dimension (which is usually a dense dimension) we will realize an increase of the overall size of our database.
But there is also another aspect of this new feature, which leverages the fact that the data cells in the database still store numbers and not text:
The numbers can (still) be used for any type of calculations!
So let's assume, that the Sales Risk of certain products, which is defined as Low (1), Medium (2) or High (3) and which we are capturing in our example on the Level0 members (e.g. by input of this information through our users using the pull-down menus), should be calculated as a Sales Risk Average on Level1. We can achieve this by creating an average calculation for a Level1 parent across the Sales Risks of its children, and this could be done with an outline formula as shown in the diagram below. The important point here is that we will only be able to see the correct labels displayed for the (calculated) Sales Risk Average at parent level, if the calculation result matches exactly with the defined IDs (1, 2 or 3). Thus we need to round the results to an integer value without decimal places; this is reflected in the formula.
You can find general information about Oracle University's offerings
for the Essbase curriculum and for other Oracle-Hyperion products
Just navigate to your own country/region at the top right hand corner of this page.
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 Instructor. 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.