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 15-3 Generation and Level Numbers

To build the outline, you can use 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 rule file 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).

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

Figure 15-4 Levels

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.

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.

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, expand the application (Sample).

4. From the Actions menu, to the right of the cube name (Basic), launch the inspector.

   
   
   
   
   

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

   
   
   
   
   

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

   
   
   
   
   

7. 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.
      
      

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

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

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

10. Change Build Method to Process level null.

    
    
    
    
    

    Click OK.

11. Click the Dimension selector in Field 1, and select Channel.
    
    

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

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

14. 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.

    
    
    
    
    

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

16. Run the dimension build job.

17. 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.

  
  
  
  
  

• 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 ignores the alias in Field 3, because Field 2 contains a null, and places Indirect in Level 1, and All Channels in Level 2.

  
  
  
  
  

• 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 ignores the first alias in Field 3, because that field contains a null. The member Outlet is added to the outline without any alias.