"Bi thing" data stream transformation--perspective

Source: "Bi thing" data stream transformation--perspective

This is the same as the pivot in T-SQL and the Unpivot function. A pivot transformation can make a data specification or makes it more readable in a report.

Through the input data of the pivot column values, the perspective transformation transforms the canonical dataset into a slightly less canonical, but more concise version. For example, in an Orders dataset with a specification of customer name, product, and purchase quantity, any customer who buys multiple products has more than one line, and each line displays a detailed ordering information for a product. At this point, if you have a pivot dataset for a product column, the pivot transformation can output a single row of datasets per customer. This line lists all the products that the customer purchased, the product name is displayed as a column name, and the quantity is displayed as 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 columns play a different role in the perspective process. Columns can participate in the following ways:

• Passes the column intact to the output. Because there are many input rows that produce only one output row, the transformation replicates only the first input value of the column.
• Column as part of the identity key or identity key for a set of records.
• Column defines a perspective. The values in this column are associated with columns in the pivot dataset.
• column contains values that are placed in the columns created by the perspective.

To generate test data:

CREATE TABLEfactorders (IdINT IDENTITY, CustVARCHAR( -), ProductVARCHAR( -), QtyINT    )    INSERT   intofactorders (Cust, Product, Qty)SELECT  'Kate' ,                'Ham' ,                2        UNION  All        SELECT  'Kate' ,                'Soda' ,                6        UNION  All        SELECT  'Kate' ,                'Milk' ,                1        UNION  All        SELECT  'Kate' ,                'Beer' ,                 A        UNION  All        SELECT  'Fred' ,                'Milk' ,                3        UNION  All        SELECT  'Fred' ,                'Beer' ,                 -        UNION  All        SELECT  'Fred' ,                'Chips' ,                2                SELECT  * fromFactorders

To configure a sample data set
The sample datasets shown in the diagram are configured as follows: Set the PivotUsage property of the Cust column to 1 to indicate that this is a set key column, set the PivotUsage property of the product input column to 2 to indicate that a column must be created for each product, and a Piv for the Qty input column; The Otusage property is set to 3 to indicate that the quantity value is placed in the pivot column.
Set the conversion output to include six columns. These columns can be added using the Advanced Editor dialog box, named Cust, Ham, Soda, Milk, Beer, and Chips, respectively. Set the PivotKeyValue property of the Ham column to ham to indicate that the conversion should look for the value in the input column. Similarly, the PivotKeyValue property of the Soda column is set to Soda, and so on.
The columns in the transformation input are then mapped to columns in the output.
Configure the SourceColumn property of the Cust column to use the lineage identifier of the Cust input column. Configure the SourceColumn properties of the Ham, Soda, Milk, Beer, and Chips columns to use the lineage identifier of the Qty input column. Another way to make this configuration is to set the SourceColumn property of the Ham, Soda, Milk, Beer, and Chips columns to 1, which inserts a True value instead of a data value. For example, the Beer column does not contain values 12 and 24 but contains the value True to instruct the customer to purchase only the product, but does not display the purchase quantity.
The rows in the transform output contain values from the Cust and Qty input columns.

Bi thing data Stream transformation--perspective