1. Database Administrator's Guide for Oracle Essbase
  2. Dimension Build Examples
  3. Level References in Dimension Builds

Level References in Dimension Builds

Levels are defined from a bottom-up hierarchy, where leaf members are level 0, and each step closer to the dimension name increments the level by 1. When building Essbase dimensions from a bottom-up source, use the Level build method. If source data contains nulls, use the Process level nulls build method.

In a bottom-up source of data, each record defines a single member of a dimension. The definition begins with the most specific information about the member and provides progressively more general information. A typical record specifies the name of the new member, then the name of its parent, then its parent’s parent, and so forth.

For example, in the outline illustrated below, the lowest-level members are at the bottoms of the branches of the Product dimension.

Figure 14-3 Generation and Level Numbers


This image shows the generation number and level number for each member.

To build the outline using a dimension build job, you can input the following bottom-up source of data:

100-10-12  100-10  100
100-20-12  100-20  100

Typically, you would run a dimension build using the Level build method when the lowest-level members are sequenced left to right. Level 0 (leaf level) members are in the first field, level 1 members are in the second field, and so on. This organization is the opposite of how data is presented for generation references (top-down).

Assume you have a cube with a Product dimension, and you want to use the following bottom-up, tab-delimited data file to build more product members to the Product dimension.

600-10-11	600-10	600
600-20-10	600-20	600
600-20-18	600-20	600

The source of data is "bottom-up" in that the first column of the source of data contains new leaf level members (600-10-11, 600-20-10, and 600-20-18). The second column contains the parents of the new members (600-10 and 600-20), and the third column contains parents of the parents (600).

The dimension build load rule uses the level reference build method to add the members to the Product dimension. The rule specifies the level number and the field type for each field of the source of data. You must also select the Level build method in the dimension properties of the rule, unless your source data contains nulls (for that use case, see Dealing with Empty Fields, below).

Dimension build rule for a level build

Essbase builds the following hierarchy from the source of data and rule:

Figure 14-4 Levels


This image shows the new members that are created during the level build.

Dealing with Empty Fields

When you build dimensions from generation or level references in the source data, you can choose to process null values. Null processing specifies what actions Essbase should take when it encounters empty fields in the source of data.

Note:

Null processing options are available only for regular dimension build rules (not index-based).

If null processing is not enabled, Essbase rejects all records with null values and writes an error to the error log.

To enable null processing, select one of the null processing build methods in the dimension properties when you create the new dimension build rule.


Null processing build methods in dimension properties for a new rule

If you use the Process level null build method, it tells Essbase to expect some null values while processing level references in the source data, which helps prevent errors.

Example

For this dimension build example, assume you want to add a ragged/asymmetric hierarchy like the following into a new Channel dimension on Sample Basic.


Channel dimension contains four levels depth of members. Channel IDs are at level 0. The indirect channel IDs have three levels of members above them, and the direct channel IDs have four levels of members above them, making this an unbalanced/ragged hierarchy.

The data source is bottom up, meaning that the lowest levels appear first in each record. Therefore, it makes sense to use a level references build method. However, null handling instructions will be required. The first three source data records contain null fields, as there is no location data for the indirect channels.

Channel-04,,Indirect,"All Channels"
Channel-07,,Indirect,"All Channels"
Channel-24,,Indirect,"All Channels"
Channel-21,Outlet,Direct,"All Channels"
Channel-29,Mall,Direct,"All Channels"
Channel-31,Kiosk,Direct,"All Channels"

You can create a rule to build the dimensions and avoid errors relating to nulls, using the Process level null build method.

To create a rule for this example,

  1. Import/build the Sample Basic cube, using the application workbook available from the gallery in the file catalog on the Essbase Server.

  2. Create a comma-delimited data file like the following, name it levchannel.txt, and upload it to the Sample Basic cube directory. 

    Channel-04,,Indirect,"All Channels"
Channel-07,,Indirect,"All Channels"
Channel-24,,Indirect,"All Channels"
Channel-21,Outlet,Direct,"All Channels"
Channel-29,Mall,Direct,"All Channels"
Channel-31,Kiosk,Direct,"All Channels"

  3. On the Applications page, open the application and then open the database (cube).

  4. Select the Scripts tab, and then click Rules.


    Scripts tab is selected and Rules tile is highlighted, in Essbase web interface.

  5. Click Create and choose Dimension Build (Regular).


    In the database dialog, create button is pressed and option Dimension Build (Regular) is selected

  6. In the New Rule dialog,

    1. Enter a rule name; for example, levchannel.

    2. For Source Type select File, click Catalog and navigate to levchannel.txt.

      When you click Catalog, the file is expected to be located in the cube directory on the Essbase Server by default, so you must have already uploaded it using the Files section of the Essbase web interface. If the file is on your client machine, click File Browser to locate the file.

    3. Leave the Header Record Number and Dimension Build Record Number fields as 0. Header records are not useful for dimension builds.

    4. Click Proceed.

    5. The dimension build rule opens with undefined fields, and preview data (from the text file) populating the grid below the fields.
      Dimension build rule editor for the new rule "levchannel," with undefined fields 1 to 4 as metadata columns waiting to be filled. Under each field are the imported data values from the source data file.

  7. As this is a new rule, there are no dimensions associated yet.

    1. Click Dimensions.
      Dimensions button in rule editor

    2. Type the new dimension name Channel, and click Add.

  8. Now you need to define the build method and check other dimension build operational instructions. Click the Channel link to edit the dimension properties.

  9. Change Build Method to Process level null.


    Edit Dimensions window in the dimension build rule editor, with Process level null selected.

    Click OK.

  10. Click the Dimension selector in Field 1, and select Channel.
    Field 1 in the dimension build rule editor, populated with data, and with the Channel dimension selected.

  11. Change the Type selector of Field 1 to Level.

  12. Increment the Level to 0, as the channel IDs are at level 0.

  13. In Fields 2 - 4, Channel should now be selected as the dimension. Change the Type selectors to Level, and mark the level numbers as 1 for Field 2, 2 for Field 3, and 3 for Field 4.


    Dimension build rule to create a Channel dimension with a ragged hierarchy by using the Process level nulls build method. Field 1 is defined as Channel, Level, 0, and contains channel IDs. Field 2 is defined as Channel, Level, 1, and contains three null fields and three location fields (Outlet, Mall, and Kiosk). Field 3 is defined as Channel, Level, 2, and Indirect for three records and Direct for three records. Field 4 is defined as Channel, Level, 3, and contains All Channels.

  14. Verify the rule, then save and close. If there are any errors, see Requirements for Valid Dimension Build Rule Files.

  15. Run the dimension build job.

  16. View the outline to confirm that the Channel dimension and the expected hierarchy of members were added.

Null Processing Flow for Level-based Dimension Builds

If null processing is enabled, Essbase processes level nulls in the following ways:

  • Null level field: If a null occurs where Essbase expects a Level field, Essbase promotes the next Level field to replace the missing field.

    Example

    The dimension build places Indirect in Level 1 instead of Level 2, because Field 2 for Level 1 contains a null.


    Field 1 is Channel, Level 0 containing a channel ID in the first row. Field 2 is Channel, Level 1 containing a null in the first row. Field 3 is Channel, Level 2, containing Indirect in the first row. Field 4 is Channel, Level 3, containing All Channels.

  • Null field before a secondary field: If a null occurs directly before a secondary field, Essbase ignores the secondary field. (Secondary field options are alias, property, formula, duplicate level, duplicate level alias, currency name, currency category, attribute parent, UDA, and a name of an attribute dimension.)

    Example

    The dimension build will ignore the alias in Field 3, because Field 2 contains a null. Essbase will place Indirect in Level 1, and All Channels in Level 2.


    Field 1 is Channel, Level 0 containing a channel ID. Field 2 is Channel, Level 1 containing a null. Field 3 is Channel, Alias, containing Catalog. Field 4 is Channel, Level 2, containing Indirect. Field 5 is Channel, Level 3 containing All Channels.

  • Null secondary field: If a null occurs where Essbase expects a secondary field, Essbase ignores the secondary null field and continues loading (this is the same behavior as for the Level build method).

    Example

    The dimension build will ignore the first alias in Field 3, because that field contains a null. The member Outlet will be added to the outline without any alias.


    Field 1 is Channel, Level 0 containing channel IDs. Field 2 is Channel, Level 1 containing Outlet in the first row, and Mall and Kiosk in the next two rows. Field 3 is Channel, Alias, containing a null in the first row, and some aliases in the next two rows. Field 4 is Channel, Level 2, containing Direct in all rows. Field 5 is Channel, Level 3 containing All Channels in all rows.