Oracle by Example brandingBuild Dimensions Using Rules

section 0Before You Begin

This 15-minute tutorial shows you how to build dimensions using a rule.

Background

You can add, edit, and map dimensions to an Essbase outline using a rule, rather than manually building empty dimensions in the Essbase Outline editor. When you build using a rule, you define the hierarchical structure of dimensions and member metadata.

In this tutorial, you build dimensions from a flat file using a rule. You also set up options for the rule and preview the results.

After defining a rule, you run a job to build the dimension using the rule.

What Do You Need?

section 1Import the Database without Data

Import the worksheet and create Sample5 application, and Basic database, which is emtpy of data.

  1. Sign into the Oracle Analytics Cloud – Essbase web interface.
  2. On the Applications home page, click Import.
  3. Click File Browser, select the downloaded file sample_basic.xlsx, and click OK.
  4. In the Import dialog box, modify Application Name to Sample5.
  5. Accept the default database name, Basic.
  6. Expand Advanced Options, and deselect the check boxes, Load Data and Execute Scripts, and click OK.
  7. On the home page, expand the Sample5 application, and select the Basic database.
  8. On the row for the Basic database, click Actions Actions, and click Inspect.
  9. Select Statistics tab. On the General page, under Storage, notice that the value for Number of Existing Blocks is 0, indicating that the Basic database contains no data.
  10. Click Close.

section 2Create the Rule File

Create a build dimension rule that is index-based, which removes the dependency of fields to each other and allows the fields to appear in any order.

  1. Open the downloaded dim_market_data1.txt file in a formatted text editor. Notice that the file doesn't have a header row and that the file delimiter is a comma.
  2. On the home page, expand the Sample5 application, and select the Basic database.
  3. On the row for the Basic database, click Actions Actions, and click Inspect.
  4. Click Files, and Upload Files. Select the two files that you previously downloaded, click Open, to upload the files to the Catalog, and then click Close.
  5. Select Scripts tab, and then Rules, to see the defined rules in the Rules editor.
    Create a rule file
    Description of the illustration create-rule-file.png
  6. Click Create, and select Dimension Build (Indexed Based) to create the build dimension rule.
  7. In the New Rule dialog box, enter Dim_market1, as the name of the rule file.
  8. For Preview Data, select File, for the flat file input option.
  9. Click the browse icon to the right of File Select File (optional).
  10. In the Preview Data dialog box, click Catalog, select the file you uploaded, dim_market_data1.txt, and click Select.
  11. Deselect the Header Row check box, if selected, because the first row of the flat file doesn't contain header values.
  12. In the Delimiter drop-down list, ensure that Comma is selected.
  13. Click Proceed, to view the dimension rule in the Rules editor, with the columns populated based on the input flat file.

section 3Select Rule Options and View in Preview

On the Rules editor page, you can now set up and edit the rule.

Using the Global options toolbar, you can view and change the properties and data source for the rule. Using the Field options toolbar, you specify field mapping for the rule.

Options toolbars
Description of the illustration options-toolbars1.png
  1. In the first field (column), click Dimension, and select Market, as the dimension name for all of the rule fields.
    Dim field market
    Description of the illustration dim-field-market.png
  2. Under Market, in the first field, in the Type list, select Parent, to reflect a rule for parent-child source file format.
  3. Set the other fields (columns) as follows:
    • Field 2, set Type to Child
    • Field 3, set Type to Property, and third row Parent/Child box to Child
    • Fields 4 and 5, set Type to UDA, and third row Parent/Child boxes to Child
    • Field 6, set Type to Alias, third row Alias box to ChineseNames, and fourth row box to Child
    • Field 7, set Type to Alias, third row Alias box to JapaneseNames, and fourth row box to Child
    • Field 8, set Type to Alias, third row Alias box to RussianNames, and fourth row box to Child
    • Field 9, set Type to Alias, third row Alias box to GermanNames, and fourth row box to Child
    • Field 10, set Type to Attribute Member, third row Attribute Dimension box to Population, and fourth row box to Child
    Build dimension rule
    Description of the illustration build-dim-market.png
  4. Click the gray area in column Field 4, under the mapping of Market, UDA, and Child, to highlight the column. Notice that the data values in that column contain a space, for example: Major Market.
  5. On the Field options toolbar, open the Properties tab, select the Convert Spaces to Underscore check box, and click OK. Notice that the data values in Field 4 now contain an underscore.
  6. In the Global options toolbar, open the Source tab to view the options and properties for the file associated with the rule. Open the File Properties tab, verify that the Delimiter is set to Comma, and click OK.
  7. Click Verify in the Global options toolbar to validate the rule syntax, and click Close.
  8. On the Rules editor page, click Save and Close.
  9. On the Scripts tab, click Refresh, and verify that your created rule Dim_market1 appears in the Rules list.
  10. Click the rule, Dim_market1, to edit it. Accept the defaults, click Proceed.
  11. In the Global options toolbar, open the Dimensions tab, click the Market dimension, and the Advanced tab.
  12. Select the check boxes for Allow Property Changes and for Allow UDA Changes. Ensure that all four Allow xxx Changes check boxes are selected. Click OK.
  13. Click Save and Close, and then Close, to return to the Applications home page.

 

section 4Run the Build Dimension Job

Next, create a job to build the dimension using the rule.

  1. On the home page, select Jobs Jobs.
  2. In the New Job drop-down menu, select Build Dimension.
  3. In the Build Dimension dialog box, from the Application list, select the Sample5 application.
  4. In the Database list, select the Basic database. It might take a few moments to load.
  5. In the Script list, select the build dimension rule file, Dim_market1.
  6. For Load Type, select File.
  7. In the Data File list, select dim_market_data1 as the build dimension data file.
  8. In the Restructure Options list, select the Preserve Input Data option for the data to preserve, and click OK.
  9. On the Jobs page, click Refresh Refresh to monitor the job status.
  10. When the job ends, click Actions Actions menu for the executed job.
  11. Select Job Details to verify the status of your build data job completion. Notice the number of records processed. Click Close.

section 5View the Build Dimension Results

  1. On the Applications home page, expand the Sample5 application.
  2. On the row for the Basic database, click Actions Actions.
  3. Select Outline. View the build dimension in the Outline editor, based on the executed rule.
  4. On the Outline editor, expand Market, East, and click New York.
  5. In the right pane, scroll down and expand User-defined Attributes. Notice that Major_Market appears with an underscore, as you specified in the field properties of the rule. Close the browser tab.

more informationWant to Learn More?