Rule Designer Guide - Part 2

Transform

The Transform section of the ribbon contains the following five components:

• Pivot
• Unpivot
• Join
• Union
• Calculate

 

 

Pivot

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

 

Design

Select the Source component that you need to Pivot.  The first measure column and first dimension column will be automatically populated as the Aggregated by and Pivot by selection.  Modify as needed.  Then in the Pivot scope area, select all the unique values that you want to pivot in from the Pivot by column.

 

 

Click the settings icon  to display the Default settings window to define the values to default when the column is NULL.

 

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

Unpivot

UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

 

Design

Select a component that you need to Unpivot. All measure columns in the source will show up in the Unpivot section. Check the column that you need to Unpivot. If you are unpivotting by dimension columns, you can also specify the dimension hierarchy as a reference so that the unpivoted columns will be converted to dimension columns directly.

 

 

When Remove zeros is checked, records with measure column value equal to 0 will be excluded from the result.

 

For PREVIEW and SQL tab details, please refer to the Fact section.

 

 

Join

Join allows you to retrieve data from two or more tables based on logical relationships between the tables.

 

Design

Select the first table from the Source drop-down, and select its join table. Column with the same name will be automatically used for the join with the logical operator set to “=”. You can add more join conditions, or remove existing join conditions. You can specify what kind of join to be performed: an inner, left outer, right outer, full outer, or cross join. You can join more tables by clicking ADD button. The alias for the join tables are alphabetical letters ordered from the top to the bottom.

 

 

Column

You can specify the columns you want to include into the result table. By default, all the columns from the first join table are included, whereas the columns from all the other tables are listed on the right side as available columns. Double click to add them to the left side. Or click ADD icon to define your own column.

 

 

Click the settings icon  to open the Column edit window. 

 

 

On the left pane of the column edit box, you can rename the column, enforce the column data type, choose an aggregation method, and define the default value when the column value is NULL.

On the right pane, you can select the type of the column from the following four options:

 

• Inherited column: A column from the source table(s).

 

 

• Member expression: A specific member selected from a dimension hierarchy.

 

 

• Custom expression: Custom expression with reference to a dimension hierarchy. Please note, the Enforce Type on the left side will be enabled and Aggregation will be default to SUM when the type is set to Custom expression. You can modify these two selections as needed.

 

 

• Custom member expression: Custom expression which is allowed by the system. Refer to the EXAMPLE tab for sample quires. To create your expression, directly type in the yellow edit area or double click from the right pane if you need to use columns from the source(s).

 

 

Click OK to save the column definition.

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

Union

Union combines two or more tables into a single result set that includes all the rows that belong to all tables in the union.

 

Design

Select the first union table from the Source dropdown, and then select the second union table. The right side of the mapping grid contains all the columns from the first union table, which are also the target columns in the result table. Column with the same name will be automatically mapped, otherwise you will need to pick from the available columns to accomplish the mapping. You can choose the union type: a union, or union all. You can click ADD button union more tables or click REMOVE to remove union tables. 

 

 

Column

The columns in the result table is default to the columns from the first union table. You can click ADD button to add more columns or click REMOVE to remove existing columns. Use MOVE UP and MOVE DOWN to re-order.

 

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

Calculate

Design

Select an existing component as the source. Then you can define the columns as needed. Refer to the Join – COLUMN section for more details.

 

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

Action

The Action section is only available in an Action Rule, and contains the following 3 options:

• Delete
• Insert
• SQL

 

 

Delete

You can delete records from fact tables in the current application.

 

Design

Choose the target table that you want to delete records from. You can use Join to filter down the delete scope.

 

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

Insert

You can insert records from existing component to a fact table in the current application.

 

Design

Select the target fact table and the source component. Columns with the same name will be automatically mapped, otherwise please select from the available columns to ensure all Target columns are supplied with values.

 

 

When Remove zeros is checked, records with measure value equal to 0 will be excluded at insertion. If the records contains annotation, the Has annotation has to be checked. When Aggregate is checked, all measure values will be aggregated using SUM function at insertion.

 

For PREVIEW, SCOPE, and SQL tab details, please refer to the Fact section.

 

 

SQL

Design

Allow you to add free-form SQL query. Refer to the EXAMPLE tab for sample queries.

 

 

For SQL tab details, please refer to the Fact section.

 

 

Publish (View Rule)

The Publish section that's available in a View Rule contains the following two sections:

• Model
• View

 

 

Model

This component publishes data from an existing component to the Rule partition in any model in the current application.

 

Design

Select a model as the target and select a component as the source. Columns with the same name will be automatically mapped, otherwise please select from the available columns to ensure all Target columns are fed with values.

 

 

When Remove zeros is checked, records with measure value equal to 0 will be excluded from the result.

When Has annotation is checked, the Comment column in the target will show up to allow annotation column mapping.

 

For PREVIEW and SQL tab details, please refer to the Fact section.

 

 

View

A view will be created from the component you defined.

 

Design

Select a component as the source of the view, and give the view a name. A view named [V_R_Publish_Name] will be created in the database.

 

 

For PREVIEW and SQL tab details, please refer to the Fact section.

 

 

Publish (Action Rule)

The Publish section that's available in an Action Rule contains the following section:

• Procedure

 

 

Procedure

A stored procedure will be created from the actions you defined.

 

Design

Give the stored procedure a name. Click Add to add actions to the stored procedure. Select Action component from the dropdown, use Move up and Move down to put the actions in the right order, or use delete to remove existing actions.

 

 

The right pane will give the syntax to execute the stored procedure. The parameters declared in the Settings tab are all taken as input parameters for the stored procedure.

For SQL tab details, please refer to the Fact section.

 

 

Others

The other options that are available in a Rule include the following components:

• Validate
• Run
• Refresh
• Cancel

 

 

Validate

Click Validate at any point to check if the selected existing components are valid.

 

 

Run

Click Run to execute the selected action components. Please use with caution as it will truly modify data in the database. This option is only available in an Action Rule.

 

 

Refresh

Click Refresh at any point to jump to the PREVIEW tab (if exist) of the selected component.

 

Cancel

When the preview page is refreshing, click Cancel to cancel the query so that the refresh will be aborted.

 

 

• Rule Designer Guide - Part 1
• Rule Designer Guide - Part 2