How to create New RPD - Part3

In this post, we will learn How to create the relation between the tables using Physical Layer Diagram.

Using the Physical Diagram approach you can define both Primary and Foreign keys in one step.

In SH Schema, the following is the key that you are going to join between the table.

PROMOTIONS. PROMO_ID = SALES. PROMO_ID

TIMES. TIME_ID = SALES. TIME_ID

CUSTOMERS. CUST_ID = SALES. CUST_ID

CHANNELS. CHANNEL_ID = SALES. CHANNEL_ID

Follow the steps to create physical layer Diagram:

1. In the Physical layer, right-click the DW data source > select Physical Diagram > Object (s) and All Joins. The option will be the screen below,

The physical diagram may appear as in screenshot below:

2. Objects will appear very big, but the zoom can be adjusted. To adjust the zoom level, right click on the white space and set the zoom level to 100%, as shown in screenshot below :

3. You can now drag the tables to move around and rearrange them.

Click the Create New Join button on the toolbar. Using the Foreign Key,
make a join between the tables in the physical Layer.

4. Click the PRODUCTS table first and then on the SALES table. The Physical Foreign Key dialog box will open up.

Select PROD_ID from PRODUCTS and PROD_ID from SALES.

5. Click Ok. If prompted for any question, please select Yes.

6. Repeat the above steps to define join between all the tables. The join conditions are as follows:

7. When you are finished, click the X in the upper right corner to close the Physical Diagram.

8. Select File > Save or click the Save button on the toolbar to save the repository.

9. Click No when prompted to check global consistency.

10. Leave the Administration Tool and the DW repository open for the next topic.

Keep saving the repository at regular intervals.

Note:

The order of selecting the tables matters while defining the joins. The join has one to many relationships. The first table and the second table have one to many relationships. Generally, there is one to many relationships between Dimensions and Facts. So, we click on dimension first and then on Fact.

Congratulations! You have successfully created a new repository, set up an ODBC data source and imported a table schema from an external data source into the Physical layer, and defined keys and joins.