Build Dimensions and Load Data Using a Rule File

Using a rule, you can build a dimension and load data from a text or other flat file.

Before you begin, you will need the following resources.

• Access to an Essbase instance.

• If you're not using a flat file as the source of data, you will need a connection and Datasource that have been set up in Essbase at the application level.

• Dimension metadata file (sample exercise file: dim-market.txt) downloaded to your computer.

• Data file (sample exercise file: data-basic.txt ) downloaded to your computer.

Using the listed resources, you can now perform the tasks of building dimensions and loading data using a rule.

Build Dimensions Using a Rule File

You can edit and map dimensions to an Essbase outline using a rule, rather than manually building empty dimensions in the Essbase Outline editor. In this section, we address and illustrate building dimensions from a flat file, using a rule.

When you build using a rule, you define the hierarchical structure of dimensions and member metadata. You can create one or more dimensions using a single rule file, or use one rule file per dimension.

You can build a dimension to add or modify dimensions, but you can’t use it to delete an existing dimension.

Here, we illustrate an example of building dimensions, from a flat file, using rules. The process of loading data using SQL, or by streaming, is described in other topics.

1. Open the downloaded dimension metadata file, dim-market.txt, in a formatted text editor. Notice that the file doesn't have a header row and that the file delimiter is a comma.
2. Sign into the Essbase web interface.
3. On the home page, expand the Sample application, and select the Basic cube.
4. Now you create the rule file.
   1. From the Actions menu to the right of the cube, launch the inspector.
   2. Click Scripts, and then Rules. The Rules editor is displayed, showing the currently defined rules.
   3. Click Create, and select Dimension Build (Indexed Based) to define the build dimension rule. An index-based build dimension rule removes dependency of fields to each other and allows the fields to appear in any order.
   4. In the New Rule dialog box, enter Dim_market1 as the name of the rule file.
   5. Under Preview Data, select File for the flat file input option.
   6. Click the browse icon and locate the file dim-market.txt that you downloaded, and click Open to select it.
   7. As you saw earlier, the first row of the flat file doesn’t contain header values. Deselect the Header Row check box if it is selected.
   8. Specify the Delimiter value as Comma, based on the file format.
   9. Click Proceed.
      You can now preview the dimension structure in the Rules editor, with the columns displayed based on the input flat file.
   The top-right toolbar in the Rules editor shows the Global options for a rule. You an change the properties or data source here and view the results. The left toolbar of the Rules editor shows the Field options for the rule.
5. On the Rules editor page, you can now set up and edit the rule.
   1. On the Preview page for the new rule, in the first field (column), click Dimension, and select Market as the dimension name. The Market dimension is now assigned to all fields.
   2. Under Market, in the first field, it, click Type, and select the dimension type, Parent.
      The source file for this rule is in parent-child format. If you had a generation-based source file, you could set the first field to Generation. In that case, the Generation Number is set to 2, as by default, the Generation 1 is the dimension itself.
   3. Set up the other fields:
      Set Field 2 Type to Child.
      Set Field 3 Type to Property, and third row Parent/Child box to Child.
      For Field 4 and 5, set Type to UDA, and third row Parent/Child boxes to Child.
      For Field 6-9, set Type to Alias, third row Alias boxes to ChineseNames, JapaneseNames, RussianNames, and GermanNames respectively; and fourth row boxes to Child.
      Set Field 10 Type to Attribute Member, third row box to Population, and fourth row box to Child.
      The Dimension field is most often set to Generation, Parent or Child. If the Dimension name you want isn’t in the menu, click Dimensions (on the Global toolbar), add the dimension name, and click Add and OK.
   4. Now check the field properties for a field. Select the last field column, Population. On the Field options toolbar, open the Properties tab and verify that the Case option is set to No Operation > This means that uppercase and lowercase text aren’t handled differently here than they were in the source text file.
   5. In the Global toolbar, click the Source tab, if you want to change the data source file. On the File Properties tab, verify that the Delimiter is set to Comma.
   6. When you have finished defining the rule, click Verify in the Global toolbar, to validate the rule syntax.
   7. Click Save and Close.
   8. Click Refresh. See that your created rule is now listed in the Rules pane of the Scripts tab. You can edit your rule by clicking on the rule name and then clicking Proceed.
      From the Actions menu for a listed rule, you can optionally copy, rename, copy or export the build (into a json file to be used for troubleshooting purposes. Click Close to return to the home page.
6. Next, you create and run a job to build the dimension using the rule.
   1. On the home page, select, Jobs, and then New Job.
   2. Select Build Dimension.
   3. In the Build Dimension dialog box, from the Application list, select the Sample application.
   4. In the Database list, select the Basic cube. It might take a few moments to load.
   5. In the Script list, select the build dimension rule that you created, Dim_market1.rul.
   6. For Load Type, select File.
   7. In the Data File list, select the Dim_Market as the data dimension data file. This file is located in the Sample, Basic folder.
   8. From the Restructure Options list, select the Preserve Input Dataoption for the data you want to preserve.

      To disconnect other users who are connected to the Sample, Basic cube, so that you can immediately build the dimension, you could select Force to Build Dimension.

      For leaf level data, only level-0 values are preserved. Use this option if all data required for the calculation resides in level-0 members. For input data, only blocks that contain data being loaded are preserved. Neither option applies to aggregate storage databases.

   9. Click OK. The build dimension job is executed.
   10. On the Jobs page, click Refresh to monitor the job status.
   11. When the job completes, click the Actions menu for the executed job, and select Job Details to verify the status of your build job.
   12. On the Applications home page, to the right of the Basic cube in the Sample application, open Actions, and then Outline to verify the dimension hierarchy. In Actions, Database, Inspect, you can also view the created generation names under the dimension tab. When done, exit the view.
   You have now completed building a dimension using a rule.

Load Data Using a Rule File

You can use rules to extract, transform and load data values to an Essbase cube. The source data values can contain the following:

• Data values

• Member names, aliases and formulas

• Generation and level names

• Data storage properties

• Attributes and user—defined attributes

When you build an Essbase cube, data files and load data rule files are created in the cube directory. You can also use data and rules from a supported on-premises version of Essbase.

Both pivot data and row set flat file data format are supported.

When you load data, SUM, MIN, MAX, AVG, and COUNT operations are supported in data columns across rows. This supports big-data use cases in which Essbase cubes are created with upper-level members. You can drill through, from Essbase, to view the data at a more granular level.

Here, we illustrate an example of loading data from a flat file, using rules. The process of loading data using SQL, or by streaming, is described in other topics.

1. Open the downloaded data file, data-basic.txt, in a formatted text editor. Notice that there's no header row and that the file delimiter is a comma.
2. Sign in to the Essbase web interface.
3. On the home page, expand the Sample application, and select the Basic cube.
4. Now create the load rule.
   1. From the Actions menu to the right of the Basic cube, launch the inspector.
   2. Select Scripts tab, and then Rules. The Rules editor is displayed, showing currently defined rules.
   3. Click Create, and select Data Load to define the load data rule.
   4. In the New Rule dialog box, enter Data_basic1 as the name of the rule.
   5. Enter Measures as the data dimension.
   6. Under Preview Data, select File for flat file input.
   7. Click the browse icon to locate the file data-basic.txt that you downloaded, and click Open to select it.
   8. As you saw earlier, the first row of the flat file doesn't contain header values. Deselect the Header Row check box if it is selected. When the header row is present, the columns are mapped automatically.
   9. Select Comma as the Delimiter value, based on the file format.
   10. Click Proceed.
       You can now see the preview of the data in the Rules editor, based on the input flat file.
   The Global options toolbar, on the top right of the Rules editor allows you to modify file properties or the data source and to see the results in the Rules editor. The Field options toolbar on the left side of the Rules editor allows you map fields in the rule.
   Because there were no headers in the input file, you need to map each column to the appropriate dimensions and members.
5. In the Rules editor, you can now set up the rule fields.
   1. Click Create drop-down menu, and start setting the field names.
      Set Field (column) 1 to Product.
      Set Field 2 to Market.
      Set Field 3 to Year.
      Set Field 4 to Scenario.
      Set Field 5 to Sales.
      Set Field 6 to COGS.
      Set Field 7 to Marketing.
      Set Field 8 to Payroll.
      Set Field 9 to Misc.
      Set Field 10 to Opening Inventory.
      Set Field 11 to Additions.
      All dimensions must be represented in the load data rule before any data can be loaded.
   2. When you are finished defining the rule, with global and field options, click Verify on the Global toolbar to validate the syntax and click Close.
   3. After syntax is validated, click Save and Close.
   4. Click Refresh. See that your created rule is now listed in the Rules pane of the Scripts tab. You can edit your rule by clicking the rule name and then clicking Proceed.
   5. Click Close to return to the Applications home page.
   Next, create a job to load the data using the rule.
6. On the home page, select Jobs, and then New Job.
   1. Select Load Data.
   2. In the Load Data dialog box, from the Application menu, select the Sample application.
   3. In the Database list, select the Basic cube.
   4. In the Script list, select the load data rule that you created, Data_market1.rul.
   5. For Load Type, select File.
   6. Select the file Data_Basic1 from the Data File list. This file is located in the Sample > Basic folder.
   7. Optional: select the Abort on error check box if you want the load to stop if an error occurs.
   8. Click OK. The load data job is executed.
   9. On the Jobs page, click Refresh to monitor the job status.
7. After the job is completed, verify that the input records were processed and loaded.
   1. On the Applications home page, click Actions to the right of the Basic cube in the Sample application.
   2. Select Job Details to check the load data job details.
   3. Click Close when done.
   4. On the Applications home page again, open the Actions inspector for the Sample cube.
   5. Select Statistics to view the resulting statistics for the Basic cube.
   You have now completed loading data using a rule.