Cube Processor

Elixir Data Designer provides a Cube component which allows you to generate data results of the your choice by defining multi-level dimensions and multiple measures using the pre-defined functions such as sum, average, count, max, and min for example.

The Cube processor is selected from the menu bar of the Designer window and then placed in the designer window workspace.

The properties of the Cube processor are as follows:.

Properties

On opening the properties of a Cube processor, the dialog appears as shown in Figure 4.47, “Cube Wizard”.

Figure 4.47. Cube Wizard

Cube Wizard

The Cube Hierarchies screen contains the Name and Elements column where the names of each hierarchy and the corresponding hierarchy elements will be listed.

There are three buttons on the screen namely Add, Edit and Remove that can be used to add, edit or delete a hierarchy.

Click Add to display the dialog, as shown in Figure 4.48, “Add Hierarchy”, where the hierarchy elements can be added and a name can be assigned to the hierarchy. A hierarchy indicates a strict relationship between the hierarchy elements - each child can only belong to a single parent. For example, Country/State/City is a valid hierarchy because a City cannot be in more than one State, and a State cannot belong to more than one Country.

Year/Month is not a valid hierarchy, because "January" can occur in many years.

Figure 4.48. Add Hierarchy

Add Hierarchy

Click Next, to display the Cube Wizard as shown in Figure 4.49, “Cube Axes Screen”. In this screen, you can add the Column dimensions and Row dimensions. The elements that are added in the Column dimension windows will be displayed as the column fields in the output table. The elements that are added in the Row dimensions window will be displayed as the row fields in the output table. The hierarchy column added in the first screen can be made use of in this screen as a row or column field. Use of a hierarchy is more efficient, as the system relies on the strict definition of each child only having one parent. However an incorrect use of hierarchy, as illustrated by Year/Month above will usually yield incorrect results.

Figure 4.49. Cube Axes Screen

Cube Axes Screen

Click Next to display the Cube Wizard as shown in Figure 4.50, “Cube Measures Screen”. There are two columns namely Name and Function. In this screen, the Pre-defined functions and the name of the fields using these functions will be listed. There are three buttons namely Add, Edit and Remove to add, edit or remove the cube measure columns.

Figure 4.50. Cube Measures Screen

Cube Measures Screen

Click Add. The dialog box pops up as shown in Figure 4.51, “Add Measure Dialog”. In this dialog box, the Field name, Function and Pattern can be selected. Each measure applies a function to a particular field, for example Average(Salary). The name of the measure is given by the values of the dimensions that form the output column. For example, Male/Average(Salary) and Female/Average(Salary) would be the column names for a Cube with a Gender column dimension. If there are two dimensions, e.g. Country/Gender, then you would get column names like Singapore/Male/Average(Salary).

Figure 4.51. Add Measure Dialog

Add Measure Dialog

Sometimes you might want to choose your own column names. This is where the Pattern field is useful. By default, if you leave the Pattern field blank, you will get column names as described above. If you enter a Pattern description, it will be used to generate the column names. There are a number of substitution parameters available. Assuming the default column name is US/Oregon/Married/Count(employeeid), here are some samples of the available substitutions:

Note

It is important that any pattern you define should result in a unique column name for each column in the cube. This usually means that you should include the elements of the hierarchy and the measure somewhere in your pattern. Failure to ensure unique column names may prevent subsequent processor steps from accessing data from those fields with duplicate names.

The next wizard page provides a few options to tune the cube process and output:

Click Next in the Cube Wizard to display the Infer Schema screen. Click Infer Schema to infer the schema. You should infer the schema after any changes to the dimensions or measures, including changing the measure patterns, since all these affect the column names.

Note

  • In order to display Row Totals After Details and Column Totals After Details to display when generating the Cube, Show Totals must be selected, as shown in Figure 4.56, “Cube Axes - Edit Level”. As for Show Row Grand Totals and Show Column Grand Totals, it is not necessary to check the option Show Totals.

    Figure 4.56. Cube Axes - Edit Level

    Cube Axes - Edit Level
  • In order to display Row Totals After Details, Column Totals After Details, Show Row Grand Totals and Show Column Grand Totals when generating the Cube, corresponding check boxes must be selected and titles must be entered, as shown in Figure 4.57, “Cube Options”

    Figure 4.57. Cube Options

    Cube Options

Working with the Cube Processor

The averages of frozen and store for the stores in the different states of Mexico have to be compared.

  1. Add the JDBC data source Stores.

  2. Add a Composite DataSource named Cube and drag the Stores data source over the diagram.

  3. Select a filter and place it in the designer. Next add a cube and connect the Stores to the filter, then to the cube and finally to Result. The diagram appears as shown in Figure 4.58, “Sample Cube Flow”.

    Figure 4.58. Sample Cube Flow

    Sample Cube Flow
  4. Open the filter properties and configure it to keep Country equals Mexico.

  5. In the Cube Properties, go to the Cube Axes page, select Country as the cube column and State as the row. On the Cube Measures page, add a measure to Average the field frozen and add another measure to Average the store field.

  6. Click Next and infer the schema. The screen appears as shown in Figure 4.59, “Infer Schema Screen”. Click Finish.

    Figure 4.59. Infer Schema Screen

    Infer Schema Screen
  7. From the Cube popup menu, choose View Cube. The output appears as shown in Figure 4.60, “Cube Result”. The averages of frozen and store for different states in Mexico are displayed.

    Figure 4.60. Cube Result

    Cube Result