Bi-Data Stream Conversion-Perspective

This is the same as the role of role and unrole in the T-SQL. Pivot conversion can standardize data or make it more readable in reports.

By pivoting the input data of column values, pivoting converts a standardized dataset into a version that is less standardized but more concise. For example, in the orders data set with the customer name, product, and purchased quantity, there are multiple lines for any customer who buys multiple products, each line shows the detailed ordering information of a product. In this case, if you view a dataset in the product column, the perspective conversion can output a dataset with only one row for each customer. This line lists all products purchased by the customer. The product name is displayed as the column name, and the quantity is the value of the product column. Not every customer buys all products, so many columns may contain null values.
When you pivot a dataset, the input column plays different roles during the pivot process. Columns can be used as follows:

• Pass the columns to the output intact. Because many input rows can only generate one output row, the conversion only copies the first input value of the column.
• A column is a part of the ID key or ID key of a group of records.
• Column definition perspective. The values in this column are associated with the columns in the pivot dataset.
• The column contains the values in the column created by the pivot.

Sample dataset Configuration
The configuration of the sample dataset shown in the graph is as follows: Set the reset tusage attribute of the cust column to 1 to indicate that this is the set key column; set the reset tusage attribute of the product input column to 2, to indicate that a column must be created for each product. to indicate that the number value is placed in the pivot column, set the value of the reset tusage attribute in the qty input column to 3.
Set the conversion output to contain six columns. You can add these columns in the "advanced Editor" dialog box and name them Cust, ham, soda, milk, beer, and chips respectively. Set the effectkeyvalue attribute of the ham column to ham to indicate that the conversion should look for the value in the input column. Similarly, set the effectkeyvalue attribute of the soda column to soda, and so on.
Map the columns in the conversion input to the columns in the output.
Configure the sourcecolumn attribute of the cust column to use the lineage identifier of the cust input column. Configure the sourcecolumn attribute of the ham, soda, milk, beer, and chips columns to use the lineage identifier of the qty input column. Another way to do this is to set the sourcecolumn attribute of the ham, soda, milk, beer, and chips columns to-1, which inserts the true value instead of the data value. For example, the beer column does not contain values 12 and 24, but contains values true, so that only the customer has purchased the product, but does not display the number of purchases.
The rows in the conversion output contain values from the cust and qty input columns.

Bi-Data Stream Conversion-Perspective