In this guide, we will comprehensively cover the function of each component in the Rule Editor. The available components in View and Actions Rules do differ, so we will also explain the individual functionality for each Rule type.
View Rule components
Action Rule components
Note: We recommend you read the Rule Editor Overview first as it explains all the aspects of the UI that will not be explained here. For the most part, we will be looking at the component's Design tab in this guide.
Transform
The Transform section of the ribbon contains the following five components:
- Pivot
- Unpivot
- Join
- Union
- Calculate
Pivot
Pivot rotates a table-valued expression, turning the unique values from one column in the expression into multiple columns in the output. It can also perform aggregations where they are required on any remaining column values in the final output.
1. Select a data source from the Source drop-down.
2. Configure Aggregated by (first measure column) and Pivot by (first Dimension column) as desired. They will be automatically populated.
3. From the Pivot scope drop-down, select all the unique values that you want to pivot in from the Pivot by column.
(Optional) Click the settings icon to change the default value for NULL cells.
Enter values in the fields and click OK.
Unpivot
Unpivot performs the reverse operation of Pivot by rotating columns of a table-valued expression into column values.
1. Select a data source from the Source drop-down.
2. Select the column(s) to unpivot.
(Optional) If you are unpivoting by Dimension columns, use the Dimension drop-down to specify the Dimension Hierarchy. The unpivoted columns will be converted to dimension columns directly.
All measure columns in the source will populate the table at the bottom. Select to change the column names.
Note: When Remove zeros is checked, records with measure column values of 0 or NULL will be excluded from the result.
Join
Join allows you to retrieve data from two or more tables based on their logical relationships.
Design
1. Select the first table from the Source drop-down
2. Select its join table.
Two columns with the same name (e.g., AccountID) will be automatically used for the join with the logical operator set to =.
3. Specify what kind of join to be performed: INNER , FULL, LEFT, RIGHT, or CROSS.
4. Configure the operation as desired.
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 in 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 an available column to define it. You can also select to define your own column.
Click to edit a column.
Settings
You can rename the column and select between four column types:
- Inherited column: A column from the source table(s).
- Member expression: A specific Member selected from a Dimension Hierarchy.
- Custom member expression: A custom member expression allows you to define any custom SQL that resolves to the MemberId of a Member in the selected Dimension Hierarchy.
To create your expression, type in the Design field and/or double-click from the right pane if you need to use columns from the source(s). For example, the following SQL script dynamically resolves to the Entity with the highest MemberId.
- Custom expression: Custom expression which is allowed by the system. Refer to the Example tab for sample queries.
To create your expression, type in the Design field and/or double-click from the right pane if you need to use columns from the source(s).
Note: In the Advanced tab, Enforce Type 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.
Click OK to save the column definition.
Advanced
- Aggregation: How the data will be aggregated (e.g., SUM, COUNT, MAX).
- Enforce type: The format the data must be in.
- When NULL: SQL expression that will replace a NULL value.
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
1. Select the first union table from the Source drop-down, and then select the second union table.
2. Configure the definition for the union.
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. Columns with the same name will be automatically mapped, otherwise you will need to pick from the available columns to accomplish the mapping.
3. Choose the union type: UNION ALL or UNION.
Column
The columns in the result table defaults to the columns from the first union table.
Calculate
1. Select an existing component from the Source drop-down.
2. Define the columns as needed.
Tip: Refer to the Join – Column section for more details.
Action (Action Rule only)
The Action section contains the following options:
- Delete
- Insert
- SQL
Delete
The Delete component allows you to remove data from fact tables in the current Application.
1. From the Source drop-down, select the table from which you want to remove data.
2. Select to filter down the delete scope.
Insert
The Insert component allows you to add data to fact tables in the current Application.
1. Select a component for the Source and a fact table as the Target.
2. Columns with the same name will be automatically mapped, otherwise select a source for the available columns to ensure all Target rows are supplied with values.
When selected:
- Remove zeros excludes records with measure value equal to 0 at insertion.
- Has annotation includes any annotations made to the table.
- Aggregate calculates all measure values using the SUM function at insertion.
SQL
The SQL component allows you to add free-form SQL queries to your Rule. Refer to the Example tab for sample queries.
Publish (View Rule)
View Rules have two components in their Publish section:
- Model
- View
Model
The Model component publishes data from an existing component to a Rule partition.
1. Select a component as the Source, and a Model for the Target.
2. Columns with the same name will be automatically mapped, otherwise select sources ensure all Target rows have an accompanying source.
When selected:
- Remove zeros excludes records with measure value equal to 0 at insertion.
- Has annotation includes any annotations made to the table.
View
The View component allows you to create a view from a component.
1. Select a component from the Source drop-down.
2. Enter a name in the SQL View Name field.
As the message states, a view named [V_R_Publish_Name] will be created in the database.
Publish (Action Rule)
Action Rules have the Procedure component in their Publish section.
Procedure
The Procedure component creates a stored procedure from the defined actions.
1. Enter a name for the stored procedure.
2. Click to add actions to the stored procedure.
3. Select an Action component from the drop-down.
The right pane provides syntax to execute the stored procedure. The parameters declared in the Settings tab are all taken as input parameters for the stored procedure.
Manage
The other options that are available in a Rule include the following components:
- Validate
- Run (Action Rule only)
- Refresh
Validate
The Validate component can be used at any point to check if the components used in the Rule are configured properly.
1. Click Validate in the ribbon.
2. Select the components you want to validate.
3. Click Validate.
Run
The Run component allows you to execute Action components.
Attention: Running a component will modify data in the database.
1. Click Run in the ribbon.
2. Select the Action components you want to run.
3. Select Run.
Refresh
Click Refresh at any point to jump to the Preview tab (if applicable) of the selected component.