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.
Comments
0 comments
Please sign in to leave a comment.