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

Generation References in Dimension Builds

Essbase dimension members occupying a common branch are in the same generation. Generation 1 is the dimension name, and each lower level increments the generation number. When building dimensions from a top-down source, use the Generation build method. If source data contains nulls, use the Process generation nulls build method.

Top-down sources of data are organized left to right from the highest level to the lowest level. Each record begins with the most general information and progresses to the most specific information. The name of the new member is at the end of the record. When using a top-down source of data, use the generation references build method. In the rules file, specify the generation number and the field type of each field of the source of data.

Essbase numbers members within a dimension according to the hierarchical position of the member within the dimension. The numbers are called generation references. A dimension is always generation 1. All members at the same branch in a dimension are called a generation. Generations are numbered top-down according to their position relative to the dimension; that is, relative to dimension 1.

For example, as illustrated below, the Product dimension is generation 1. Product has a 100 member, which is generation 2. 100 has members, such as 100-10, which are generation 3. To use the generation references build method, specify the generation reference number in the rules file.

Figure 14-1 Generations


This image illustrates generation reference numbers, as described in the text preceding the image.

Assume you have a cube with a Product dimension. Dimensions are generation 1, so Product is generation 1. Assume you want to use the following top-down, tab-delimited data file to build the Product dimension:

500	500-10	500-10-10
500	500-10	500-10-20
500	500-20	500-20-12
500	500-20	500-20-15
500	500-20	500-20-20

Typically, you would run a dimension build using the Generation build method when the lowest-level members are sequenced right to left as shown above.

You can use the following rule to build the dimensions using generation references. The rule specifies the generation number for each field in the source of data. You must also select the Generation 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).

Rules file for generation build. Field 1: Product, Generation 2 named Category. Field 2: Product, Generation 3 named Product SKU. Field 3: Product, Generation 4 named Promotions

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

Figure 14-2 Generation References


This image shows the new members that are created during the generation 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: Process generation null, or Process level null.


Null processing build methods in dimension properties for a new rule

If you use the Process generation null build method, it tells Essbase to expect some null values while processing generation 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 is generation 1. An "All Channels" hierarchy is generation 2. Generation 3 includes "Indirect" and "Direct" members. Indirect has channel IDs as children at generation 4. Direct has location members "Outlet," "Mall," and "Kiosk" at generation 4, followed by channel IDs at generation 5. The indirect channel IDs are at generation 4, while the direct channel IDs are at generation 5, making this an unbalanced/ragged hierarchy.

The data source is top down, meaning that the higher levels (close to the top dimension member) appear first in each record. Therefore, it makes sense to use a generation 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.

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

You can create a rule to build the dimensions and avoid errors relating to nulls, using the Process generation 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 genchannel.txt, and upload it to the Sample Basic cube directory. 

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

  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 scripts 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, genchannel.

    2. For Source Type select File, click Catalog and navigate to genchannel.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.
      Image of the dimension build rule editor for the new rule "genchannel," 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 generation null.


    Image of the Edit Dimensions window in the dimension build rule editor, with Process generation null selected.

    Click OK.

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


    Image of field 1 in the dimension build rule editor with the Channel dimension selected.

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

  12. Increment the Generation to 2, as the All Channels hierarchy member is at at level 2. The generation 1 member is always the dimension name; in this case, Channel.

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


    This image shows a rule creating a Channel dimension with a ragged hierarchy by using the Process generation nulls build method. Field 1 is defined as Channel, Generation, 2, and contains All Channels. Field 2 is defined as Channel, Generation, 3, and contains Indirect for three records and Direct for three records. Field 3 is defined as Channel, Generation, 4, and contains three null fields and three location fields (Outlet, Mall, and Kiosk). Field 4 is defined as Channel, Generation, 5, and contains channel IDs.

  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 Generation-based Dimension Builds

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

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

    Example

    The dimension build promotes the channel ID to Generation 4 instead of Generation 5, because Generation 4 contains a null.


    Field 1 is Channel, Generation 2 containing All Channels. Field 2 is Channel, Generation 3 containing Indirect. Field 3 is Channel, Generation 4 containing a null. Field 4 is Channel, Generation 5 containing a channel ID.

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

    Example

    The dimension build ignores the alias in field 4, because Generation 4 contains a null, and promotes the channel ID to Generation 4 instead of Generation 5.


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

  • Null secondary field: If the 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 Generation build method).

    Example

    The dimension build ignores the alias in Field 4, because that field contains a null. The member Outlet is added to the outline without any alias.


    Field 1 is Channel, Generation 2 containing All Channels. Field 2 is Channel, Generation 3 containing Direct. Field 3 is Channel, Generation 4 containing Outlet. Field 4 is Channel, Alias, containing a null. Field 5 is Channel, Generation 5 containing a channel ID.