Business Intelligence – Oracle

Hyperion Essbase 11.1.1 – Handling Textual Facts – Text List Manager and Text Measures

Posted by Venkatakrishnan J on December 10, 2008

One of the new features which was introduced in the 11.1(or 9.5) version of Essbase is the ability to handle textual measures. This is a very intriguing feature since this ties back directly to BI EE and Essbase Connectivity. We shall look how these 2 are connected later. But for now, lets take a look at how Essbase handles these Text Measures. We shall start with a new Application and Database called TestApp->TestDb. By default Text measures are not enabled. In order to Enable them, one would have to change it in the outline property.

               

Once this is done, lets copy the same outline members from Demo->Basic to TestApp->TestDb.

               

Now our requirement is to have a Text Measure called TrueOrFalse. Basically, this measure tracks whether a specific product is available at a specific Market at a specific point in Time. Since we also have a Scenario Dimension (which does not make sense for this metric) we shall use Actual for the metric tracking. So, a value of “True” states that a product is available in a specific Market at a specified point in Time. And a False value states otherwise. In order to load this textual measure, we need to go through a specific set of steps. Currently, an arbitrary entry of Text inside an Essbase Cell is not allowed. The text measure values that can be stored would have to be part of a list called as Text Lists. Only those values (consider them as Text Lookups) that are stored in the Text Lists can be stored inside Essbase. SoThe first step is to create a Text List. Since an outline can have more than one Textual measure, we can have more than one Text List. This list is created from the Textual Manager Tab in the outline. Since we have only one textual measure, lets create one Text List called TrueOrFalse. By Default every text list will have #Missing and #OutofRange. This is to enable NULL cells and Out of Range cells. These values cannot be removed.

               

In our case, we would need 2 Text values called as True and False. Each of them would need an unique ID (which would have to be numeric). So, the way this works is, whenever the Text Value (say True) is encountered in the data source, Essbase would lookup in the Text List and get the corresponding ID stored. Then this ID would only be stored inside the Essbase cell. So, effectively only numeric values get stored inside Essbase. But during data load and retrieval these Text Lists would be used to retrieve them. In order to test this, lets create a simple data source table as shown below

create table TextFactLoad(Year Varchar2(100),Market Varchar2(100), Product Varchar2(100), Scenario Varchar2(100),Accounts Varchar2(100), FACTTEXT Varchar2(100);
insert into TextFactLoad values ('Jan','Boston','Stereo','Actual','TrueOrFalse','True');
insert into TextFactLoad values ('Jan','Boston','Stereo','Actual','TrueOrFalse','False');
Commit;

Now, the next step is to create a rule file which will load the above data into the Essbase Cube.

               

Once the rule file is created lets try to load the data into Essbase.

               

Now, if we take a look in Excel-Addin, we would see the Textual Measure value

               

But if we take a look from Visual Explorer (VE uses MDX to fire queries back to Essbase), we would see that the ID’s would actually be displayed instead of the Measure Values (True and False).

               

Now, lets pick the actual MDX query fired by VE and fire that directly within Essbase.

WITH
SET [Tableau Set 0] AS
'Except(
Descendants([Market].[East], [Gen3,Market], SELF),
{[East].[New_York]})'
SELECT
{[Accounts].[TrueOrFalse]} DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON COLUMNS,
NON EMPTY CROSSJOIN(
[Tableau Set 0],
CROSSJOIN(
{([Audio].[Stereo], [Qtr1].[Jan])},
[Scenario].Generations(2).Members)) DIMENSION PROPERTIES [MEMNOR],[ANCESTOR_NAMES] ON ROWS
FROM [TextApp].[TextDb]

               

This is quite strange. We are getting the ID for the first one but for the second one we are getting the measure value. I believe this is expected since we are supposed to handle all the Textual measures in MDX using CellValue function. If that is not used, then the results can be quite different. I would blog about the significance of these new MDX functions in a future blog entry.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: