Get started with Informix Warehouse Feature, Part 1: Model your data warehouse using Design Studio

Page 6 of 11  Document options PDF - Fits A4 and Letter 1890 KB (56 pages) Get Adobe® Reader® Discuss My developerWorks needs you! Connect to your technical community Rate this tutorial Help us improve this content

Creating physical data model from reverse engineering

Now, let's create the physical data model for the existing stores_demo database using the reverse engineering option. In this section, you'll also create an overview diagram to easily see the Entity-Relationship diagram.

1. Right click on the Sales Demo – ELT and DW project, and select New > Physical Data Model, as illustrated in Figure 20:
   
   Figure 20. Creating a physical data model
   
   
   
   
   
2. The New Physical Data Model wizard appears. Change the Destination Folder location to the project name, Sales Demo – ELT and DW project. To do so, you use the Browse… button and select the project.
   
   
3. Rename the file name in the File name field to stores_demo_model.
   
   
4. Change Database to Informix, and change Version to 11.50.
   
   
5. Select the radio button next to Create from reverse engineering, then select Next.
   
   Figure 21 illustrates the New Physical Data Model dialog window with the values suggested in the steps above:
   
   Figure 21. New Physical Data Model wizard
   
   
   
   
6. Select the stores_demo connection, and click on Next again.
   
   Figure 22. Selecting stores_demo connection
   
   
   
7. Select the admin schema (or a different schema if you did not use admin when you created the stores_demo database), then click on Next.
   
   Figure 23. Selecting the schema
   
   
   
8. The next screen asks you to select the database elements. Keep the default values, and click on Next.
   
   Figure 24. Selecting database elements
   
   
   
   
9. Generate the overview diagram by checking the box next to Overview in the Generate diagrams area. Also, check the box next to Infer implicit relationships, then click on Finish.
   
   Figure 25. Finishing the Physical Data Model wizard
   
   
   

   The newly created database model stores_demo_model will appear on the left panel, under the Data Models folder, and the database model Properties will appear on the right panel. Save all the changes.

10. If you need to display the visual ER diagram of the discovered stores_demo data model with the tables under admin user/schema, go to Data Project Explorer > Data Diagrams. Then, go to the data diagram for stores_demo, stores_demo_model.dbm, and double-click on the schema/user name admin. The ER diagram will appear on the right side.
    
    Figure 26. Opening the Overview diagram (ER model) of stores_demo database for admin schema
    
    
    

    If you do not see the ER model (overview diagram), you might need to go to Data Models > stores_demo_model.dbm > admin schema, right-click on admin, select Add to Overview Diagram, and create a new diagram that includes the tables under admin schema.

11. Arrange the model to your visual preferences. The following are suggestions to help arrange the entities and relationships in the Overview diagram displayed inside the canvas:
    1. To zoom out the diagram, click on the Zoom out button on the Palette, located on the right side of the workspace, and then click on the diagram area to zoom it out.
    2. Use the Select button to drag and drop the entities to make them closer to one another and wherever they would better go in the diagram.
    3. In order to automatically arrange the relationship connectors to the new order of the entities that you created, select Select All Connectors from the Select drop-down list on the top toolbar (or alternatively use the Menu option: Diagram > Select > All Connectors). All the relationship connectors will be selected.
    4. Next, select Arrange Selected Connectors from the Arrange drop-down list on the top toolbar. This action automatically arranges the connectors based on the new positions where you placed the entities.
    5. Now, you can zoom in again and use the Select tool to move the entities and relationships according to your preferences.
    6. Continue iterating this way until you see an ER diagram you feel comfortable with. Then save the changes.
12. An overview diagram like the one in Figure 27 is obtained:
    
    Figure 27. Overview diagram (ER model) for stores_demo after arranging entities and relationships
    
    
    
    

Modifying the stores_demo diagram

1. Open the Overview diagram for stores_demo.
   

Tip If the diagram gets messy, you can always right-click on some blank space there and click on the Arrange All menu.

2. While you are still in the stores_demo diagram window, you can remove the ER entities call_type and catalog because they will not be used to populate the data mart sales_demo. After removing the two entities, rearrange the remaining entities so they can be visible in the same space.
   
   Figure 28. Stores_demo diagram showing the relationships
   
   
   
3. Save all the changes.
   
   
4. You could decide to create the missing non-identifying relationship between tables customer and state. To do so, you can use a relationship connector that links state(code) (parent) with customer(state) (child).

Back to top

	Page 6 of 11