Building Views with Oracle Essbase

When Tableau is connected to an Oracle Essbase data source, there are three important features that you should know about:

Generations and Levels

In Tableau, you can work with either the generations or the levels of a dimension. The generations of a dimension are all members that are an equal distance from the root of the dimension. The levels are all members that are an equal distance from the leaves of the dimension. For balanced dimensions, you'll typically want to work with generations. However, if your dimension is ragged, then it may make more sense to navigate using levels.
By default, the generations of each dimension are listed in the Data window. When you drag a dimension to a shelf, all generations that are ancestors of the selected generation (all generations that are above it in the hierarchy) are automatically included in the placement.
If you would rather navigate using the levels of a dimension, right-click the name of the dimension and then select Hierarchy > Levels.
If you are using the same dimension in multiple worksheets, you can use levels in one worksheet and generations in another worksheet simultaneously. Furthermore, you can mix generations and levels from different dimensions in the same worksheet.

Shared Members

Shared members are dimension members that appear in more than one place in a hierarchy. For example, Diet Coke might be part of the product generation. But it might be shared by both the diet colas branch and the colas branch of the product hierarchy above it. In the database, however, the data about Diet Coke is stored just once.
By default, Tableau includes shared members in all generations (or levels) of a dimension. This means that a shared member might appear multiple times in a table. If you choose to exclude shared members, they will appear only once in a table. By default, shared members are included for all dimensions. Exclude shared members for a given dimension hierarchy by right-clicking the dimension name in the Data window and selecting Include Shared Members from the menu.

The figure below shows part of a data view where shared members are included (left) and excluded (right). Notice that diet drinks are shared members.
Includes Shared Members
The diet drinks are listed in both the Diet Drinks hierarchy and their respective drink hierarchies.
Does Not Include Shared Members
The diet drinks are only listed once, in their respective drink hierarchies.

Setting the Default Member

All multidimensional data sources have default members that are set when the data source is first built. If you find that you are creating filters all the time to look at the same specific data, you may find it useful to change the default member. For example, if you are the regional manager for the Western region in a company and you only want to look at your region’s numbers, you can set the default member to the Western region.
You can change the default member in Tableau by right-clicking a dimension hierarchy and selecting Set Default Member.

In the subsequent dialog box, select from the following options:
  • Default member defined on cube – uses the default member that was defined when the cube was built. This is the default setting in Tableau.
  • (All) member for the hierarchy – uses the ALL member for the selected hierarchy as the default member.
  • Selected member – uses the member that you select in the bottom half of the dialog box as the default member.
The default member determines how you view the cube and so is much more powerful than applying filters. All fields will be calculated based on the default member you select. In addition, these default member settings are saved with the connection.

1 comment: