Using Essbase UDAs in OBIEE11g

Essbase UDA’s (User Defined Attributes) can now be imported into OBIEE and used to filter data in reports. Here I’ll show an example of how to do this and what can be done with it.

Here we have an Essbase cube. The Sales Channel dimension has a geographic hierarchy, but in this case the company has a sales area structure which is not based on geography, so we want to be able to filter reports based on sales area.  In the Essbase outline we have the sales areas (A to E) defined as UDAs against each country:

The Essbase cube definition is imported in the OBIEE repository via the Administration tool using the usual File -> Import metadata option:

When selecting the cube to Import, make sure the ‘Import UDAs’ option is ticked (is usually is by default)

Now, although the Import UDAs option was ticked, the UDAs do not appear automatically in the physical layer. Expanding the data source down the Sales Channel hierarchy does not reveal them:

To bring them into the physical layer, right click on Sales Channel (the dimension which have the UDAs) and select  ‘Create columns for UDA’ -> ‘All UDAs’:

You don’t have to bring in all UDAs if they are not needed – you may have other UDAs in the Essbase cube that you don’t need for OBIEE reporting. Instead you can repeatedly select ‘create columns for UDA’ for each required UDA.

In this example I’ve selected All UDAs. The Sales Channel hierarchy now includes columns for each individual UDA.

An important point on how UDAs are handled by OBIEE is that they are each individual columns against the dimension. OBIEE does not associate them with each other. In this example there is no concept of ‘Sales Areas’  - as far as OBIEE is concerned there are five columns against which you can filter the dimension. You can therefore bring in as many UDAs as you need irrespective of whether they are logically associated or not – you could for example have Local Currency, Area Manager or any other type of UDA and they would all appear as a long list of additional columns against the dimension.

 

Once the physical layer has been arranged, copy the entire cube into the business model and presentation layers as usual. No further changes are necessary, although one thing to be aware of is that the UDAs will be placed against all levels of the dimension, not just those to which the UDA’s have been assigned.  So you end up with the presentation layer looking like this:

This is understandable as OBIEE doesn’t know that in this case the UDAs only apply to a specific level ( in this case Gen 4), so you can manually tidy things up a bit by removing the UDAs from the levels that aren’t required. Usually you’d rename the levels as well to make them meaningful to the users:

Finally save the RPD and check in the changes.

In OBIEE the UDA fields now appear under the Sales Channel dimension when creating a new Analysis:

And can be selected for filtering the report. Before we do that, lets look at what these columns are. You can select them for inclusion in the report. Choosing all of them along with Country and the main data measure:

Displays the following:

So you can see that the UDA fields simply contain a 1 or 0 value, indicating whether that dimension value has each UDA in Essbase.  Refering back to the outline screenshot above UK, France and Germany had UDA Area A, so they have a 1 in the "Country – Area A" column, the other countries have a 0. The pattern is repeated across the countries that have each of the 5 Area UDAs. If any country had been assigned more than one Area UDA in Essbase then it would have a 1 in all appropriate columns. Similarly if a country didn't have any of the selected UDAs it would have 0 in all columns.

Filtering the report then becomes the simple matter of adding a filter to the report on the required column where the value is 1 or 0 depending on whether you wish to include or exclude countries with that UDA. The downside is that you do need to add a filter for each UDA value, you can’t simply say “where UDA value = Area A or Area B”.

You therefore add a UDA column as a filter in the same way as any other column:

So the combined filters of:

Have this effect on the above report:

Only those countries with a 1 for Area A or Area D are included. Note that the filter needs to have the OR operator – the default AND operator would produce no records in this case as there are no countries within Area A and Area D. 

Of course in reality you wouldn’t include the UDA columns, so the report would appear  as:

The dimension which the UDA columns are associated with does not need to appear in the report. The following pivot table has no filters and no reference to the Sales Channel dimension:

Adding the Area A = 1 filter does limit the data in the report:

So, in conclusion, importing and using UDAs from Essbase is quite straight forward , however their use is pretty much limited to filtering on the data, you can’t treat a set of UDAs as a dimension. 

It is however quite a powerful feature, especially when you have many sets of UDAs against multiple dimensions in your Essbase cube – it greatly increases the reporting power on that data within OBIEE.

Next time I’ll show how with a bit of advanced filtering a dashboard prompt of UDA values can be created to allow users to filter on the fly.