1. Database Administrator's Guide for Oracle Essbase
  2. Dimension Build Examples
  3. Build Shared Members
  4. Build Shared Members at the Same Generation

Build Shared Members at the Same Generation

Sharing members at the same generation in the Essbase outline is the simplest way to share members. To build shared members at the same generation, use the appropriate dimension build method to identify generation, level, or parent-child references as you design the rule.

Members that are shared at the same generation roll up into the same branch. In the example below, based on Sample Basic, the highlighted product member 100-20 (Diet Cola) is at Generation 2, and is shared by another parent member, Diet. Both parents (100 and Diet) roll up into the same branch (the Product dimension).

In the image below, all the highlighted product members beneath 100-400 are stored prototype members, and all the members under Diet are unstored shared members.

Figure 14-12 Sample Outline: Members Shared at the Same Generation


Members shared at the same generation within the Product dimension. All the members described in the following text as rolling up are at generation 2. Member 100-20 rolls up to member 100 and to member Diet. Member 200-20 rolls up to member 200 and to member Diet. Members 300-20 and 300-30 roll up to member 300 and to member Diet. Member 400-20 rolls up to member 400 and to member Diet. Member Diet has five shared members: 100-20, 200-20, 300-20, 300-30, and 400-20.

To share members at the same generation in a dimension build, choose a build method using generation, level, or parent-child references. For examples of each build method, see:

Use Generation References to Create Same Generation Shared Members

When you design a rule to build shared members at the same generation in the Essbase outline using the Generation build method, place a duplicate generation after the prototype member parent in the dimension build rule.

To create shared member parents at the same generation by using the generation references build method, define the field type for the parent of the shared members as Duplicate Generation. A duplicate generation, sometimes known as an alternate roll-up, is a generation with shared members for children. Place it after the prototype members' parent (also known as the primary roll-up), as shown in the example.

Note:

"Roll ups" are the members to which the prototype and shared members consolidate. In the example below, the shared members are in Field 3, the primary roll-ups are in Field 1, and the secondary roll-ups are in Field 2.


Dimension build rule creating shared member parents at the same generation by using the generation references build method. Field 1 is defined as Product, Generation, 2, and contains fields 100, 200, 300, and 400. Field 2 is defined as Product, Duplicate Generation, and contains fields Diet, Diet, Diet, and Diet. Field 3 is defined as Product, Generation, 3, and contains fields 100-20, 200-20, 300-20, and 400-20.

To recreate 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 tab-delimited data file like the following, name it shgenref.txt, and upload it to the Sample Basic cube directory. 

    100	Diet	100-20
200	Diet	200-20
300	Diet	300-20
400	Diet	400-20

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

    2. For Source Type select File, click Catalog and navigate to shgenref.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. Change the Delimiter value from Comma to Tab.

    5. Click Proceed.

    6. The dimension build rule opens with undefined fields, and preview data (from the text file) populating the grid below the fields.


      Unfinished new rule named shgenref. Field metadata for fields 1-3 is blank in the field selectors. Records are populated below the field selectors, matching the data in the .txt file from step 2.

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

    1. Click Dimensions.
      Dimensions button in rule editor

    2. Select or type the dimension name Product, and click Add.

  8. Now you need to define the build method and check other dimension build operational instructions. Click Product to edit the properties.
    Clickable Product dimension name in the Edit Dimensions dialog.

  9. Ensure that Build Method is Generation, and click OK.


    Dimension build properties for Product. All options are left default, and Build Method is Generation.

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


    Product dimension selectable from Dimension box in Field 1

  11. Change the Type selector of Field 1 to Generation, and increment the generation number below it to 2.

  12. For Field 2, change the Type selector to Duplicate Generation.

  13. For Field 3, change the Type selector to Generation, and increment the generation number below it to 3.

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


    Dimension build rule creating shared member parents at the same generation by using the generation references build method. Field 1 is defined as Product, Generation, 2, and contains fields 100, 200, 300, and 400. Field 2 is defined as Product, Duplicate Generation, and contains fields Diet, Diet, Diet, and Diet. Field 3 is defined as Product, Generation, 3, and contains fields 100-20, 200-20, 300-20, and 400-20.

  15. Run the dimension build job.

  16. View the outline, and inspect the Product member Diet to confirm that it has five shared members now (the dimension build added two more shared members, 300-20 and 400-20, to the pre-existing three).


    Outline viewer in Essbase web interface. Product dimension is expanded to show parent member Diet with shared members beneath it: 100-20, 200-20, 300-30, 300-20, and 400-20.

Use Level References to Create Same Generation Shared Members

When you design a rule to build shared members at the same generation in the Essbase outline using the Level build method, the prototype and shared members' parents must be in the same record in the dimension build rule.

To create shared members of the same generation by using the level references build method, first ensure that the primary and any secondary roll-ups are specified in one record. You can specify as many secondary roll-ups as you want, as long as they are all in one record.

Note:

"Roll ups" are the members to which the prototype and shared members consolidate. In the example below, the shared members are in Field 1, the primary roll-ups are in Field 2, and the secondary roll-ups are in Field 3.

Define the field type for the shared member as Level, then enter the level number below it. Set the level number of the secondary roll-up to the same number as as the primary roll-up. While processing the data source, Essbase creates a parent at the specified level and inserts the shared members under it.


Dimension build rule to create shared members using the level build method. Field 1 is defined as Product, Level, 0, and contains data fields 100-20, 200-20, 300-20, and 400-20. Field 2 is defined as Product, Level, 1, and contains data fields 100, 200, 300, and 400. Field 3 is defined as Product, Level, 1, and contains data fields Diet, Diet, Diet, and Diet.

To recreate 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 tab-delimited data file like the following, name it shlev.txt, and upload it to the Sample Basic cube directory. 

    100-20	100	Diet
200-20	200	Diet
300-20	300	Diet
400-20	400	Diet

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

    2. For Source Type select File, click Catalog and navigate to shlev.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. Change the Delimiter value from Comma to Tab.

    5. Click Proceed.

    6. The dimension build rule opens with undefined fields, and preview data (from the text file) populating the grid below the fields.


      Unfinished new rule named shlev. Field metadata for fields 1-3 is blank in the field selectors. Records are populated below the field selectors, matching the data in the .txt file from step 2.

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

    1. Click Dimensions.
      Dimensions button in rule editor

    2. Select or type the dimension name Product, and click Add.

  8. Now you need to define the build method and check other dimension build operational instructions. Click Product to edit the properties.
    Clickable Product dimension name in the Edit Dimensions dialog.

  9. Change the Build Method to Level, and click OK.


    Dimension build properties for Product. All options are left default, and Build Method is Level.

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


    Product dimension selectable from Dimension box in Field 1

  11. Change the Type selector of Field 1 to Level, and specify the level number below it as 0.

  12. For Fields 2 and 3, change the Type selector to Level, and specify the level numbers below them as 1.

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


    Dimension build rule creating shared member parents at the same generation by using the level references build method. Field 1 is defined as Product, Level, 0, and contains fields 100-20, 200-20, 300-20, and 400-20. Field 2 is defined as Product, Level, 1, and contains fields 100, 200, 300, and 400. Field 3 is defined as Product, Level, 1, and contains fields Diet, Diet, Diet, and Diet.

  14. Run the dimension build job.

  15. View the outline, and inspect the Product member Diet to confirm that it has five shared members now (the dimension build added two more shared members, 300-20 and 400-20, to the pre-existing three).


    Outline viewer in Essbase web interface. Product dimension is expanded to show parent member Diet with shared members beneath it: 100-20, 200-20, 300-30, 300-20, and 400-20.

Use Parent-Child References to Create Same Generation Shared Members

When you design a dimension build rule to build shared members at the same generation in the Essbase outline using the Parent-Child build method, define the Parent and Child field types and ensure that sharing is enabled in the dimension properties.

When sharing is enabled, Essbase automatically creates duplicate members under a new parent as shared members.

Figure 14-13 Sample Rule: Members Shared at the Same Generation Using Parent-Child References


Dimension build rule for creating shared members of the same generation using the parent-child build method. Field 1 is defined as Product, Parent, and contains fields 100, 200, 300, 400, Diet, Diet, Diet, and Diet. Field 2 is defined as Product, Child, and contains fields 100-20, 200-20, 300-20, 400-20 (repeated again in the same order to build shared children of Diet).

To recreate 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 tab-delimited data file like the following, name it shparentchild.txt, and upload it to the Sample Basic cube directory. 

    100	100-20
200	200-20
300	300-20
400	400-20
Diet	100-20
Diet	200-20
Diet	300-20
Diet	400-20

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

    2. For Source Type select File, click Catalog and navigate to shparentchild.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. Change the Delimiter value from Comma to Tab.

    5. Click Proceed.

    6. The rule opens with undefined fields, and preview data (from the text file) populating the grid below the fields.


      Unfinished new rule named shparentchild. Field metadata for fields 1 and 2 is blank in the field selectors. Records are populated below the field selectors, matching the data in the .txt file from step 2.

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

    1. Click Dimensions.
      Dimensions button in rule editor

    2. Select or type the dimension name Product, and click Add.

  8. Now you need to define the build method and check other dimension build operational instructions. Click Product to edit the properties.
    Clickable Product dimension name in the Edit Dimensions dialog.

  9. Change the Build Method to Parent Child, ensure that Share is enabled, and click OK.


    Dimension build properties for Product. Share check box is checked, and Build Method is Parent Child.

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


    Product dimension selectable from Dimension box in Field 1

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

  12. For Field 2, change the Type selector to Child.


    Rule for creating shared members of the same generation using the parent-child build method. Field 1 is defined as Product, Parent, and contains fields 100, 200, 300, 400, Diet, Diet, Diet, and Diet. Field 2 is defined as Product, Child, and contains fields 100-20, 200-20, 300-20, 400-20 (repeated again in the same order to build shared children of Diet).

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

  14. Run the dimension build job.

  15. View the outline, and inspect the Product member Diet to confirm that it has five shared members now (the dimension build added two more shared members, 300-20 and 400-20, to the pre-existing three).


    Outline viewer in Essbase web interface. Product dimension is expanded to show parent member Diet with shared members beneath it: 100-20, 200-20, 300-30, 300-20, and 400-20.