Tutorials: Denormalizing the Physical Model

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Introduction

ER/Studio Data Architect comes equipped with denormalization wizards to help you optimize the physical design once the physical model is generated. The wizards help automate the process and keep the ties between the physical tables and the logical entities.

The active, denormalization wizards available depend on which tables are selected in the physical model when you select Denormalization Mapping. For example, if two tables that are related to each other are selected, the valid operations would be Rollups or Rolldowns.

Denorm WIz.gif

When only one table is selected, the option to split the tables becomes available. The Table Merge option is available when two unrelated tables are selected.

Let’s walk through an example of a denormalization operation using the generated physical model in a previous session of this tutorial. We may want to reduce the overhead on the Custmr table by splitting it into two physical tables, Custmr_East and Custmr_West. Splitting the table can reduce the query time and provide opportunities to store the tables at different locations which could further reduce lookup time.

Before the operation, the Custmr table should look like: Custmr Table.gif

  1. Open the Orders1.dm1 model you modified and saved in the last session.
  2. In the Data Model Explorer, right-click the Custmr table in the Physical Model.
  3. Choose Denormalization Mapping > Horizontal Splits.
    Notice that since only Custmr is selected, the only possible mappings are vertical and horizontal splits.
    The Horizontal Table Split Wizard launches.
  4. On Page 1, type 2 for the number of splits.
  5. On Page 2, rename Custmr1 and 2 to Custmr_East and Custmr_West respectively.
  6. On Page 3, click Next.
    We will keep all the relationships.
  7. On Page 4, type a name and definition for the denormalization operation, and then click Finish.

Finished! After the split the Custmr table will be two physical tables that look like this:

HorizontalSplitResult.gif The two tables are identical except for the name.

You can selectively choose which attributes are included in the resultant tables by using a vertical split.

The denormalization mapping is stored with each of the tables.

Denorm Mappings.png

You can use the denormalization information to undo the operation or see the history of what happened. ER/Studio Data Architect tracks the before and after states of these operations. This comes in handy in the next section where we discuss the Where Used analysis that can be performed between the logical and physical models.


See Also