Working with Data Models › Working with Transformations › Using the Two Table Roll Up Transformation

Using the Two Table Roll Up Transformation

You can apply a transformation to roll up and denormalize two related tables and create a single table. Denormalization improves query performance and helps build a more effective physical model for a target DBMS.

Important! Once you apply the Two Table Roll Up transformation, it cannot be reversed. While in the current modeling session however, you can undo the action.

Roll-Up Denormalization Wizard

To roll up and denormalize two tables, select the identifying relationship that connects two tables, then click the Two Table Roll Up icon on the Transformations toolbar. Use the Table Denormalization Roll Up Wizard to:

• Specify the number of column instances that are copied from the child table to the parent table. If you have previously set relationship cardinality for the two tables that you want to denormalize, the cardinality is used to determine the default number of column instances to copy from the child table to the parent table.
• (Optional) Select to include primary key columns from the source child table in the resulting rollup table. Select the check box with the label "Roll up non-FK columns from the primary key area of the child table into the non-primary key area of the parent table." When you select this check box, the primary key columns from the source child table are included in the resulting rollup table. If you clear the check box, primary key columns from the source child table are not included in the rollup. The check box is not selected by default.

  Note: Foreign keys associated with the child table are not affected by this feature.

Result of Rolling Up and Denormalizing Two Tables

When you select the Two Table Roll Up transformation to roll up and denormalize two tables, you:

• Create a denormalized table that contains all primary-key and non-key columns from the source parent tables, and the non-key columns of the source child tables. When you create columns in the denormalized table, you preserve the column order in the source parent table, and then the column order in the source child table. The primary key of the denormalized table is the primary key of the parent table. The denormalized table appears in the Model Explorer under the Tables folder.
• If the source child table that you denormalize contains an owned column, you create a specific number of column instances in the denormalized table based on the relationship cardinality.
• If the source child table that you denormalize contains a foreign key (FK) that does not migrate from the source parent table, you create a specific number of corresponding foreign keys in the denormalized table based on the relationship cardinality. Each copy of the foreign key is rolenamed to match the new foreign key column names.
• Creates all relationships associated with the source parent and child tables that you denormalize, and preserves all migrating keys.
• If the source child table that you denormalize contains a relationship in which the table is a child, you create a specific number of relationship instances to the denormalized table based on the number of column instances that are copied from the source child table to the source parent table.
• Preserves all object properties for the source parent table.

More information:

Roll Up and Denormalize Two Tables with a Transformation