 | Creating physical data model from a template
Let's now create the physical data model for sales_demo from
scratch (from an empty template).
- Create a new physical data model using the sales_demo
database and the specifications from before, except select
Create from template this time, then click on
Next.
Figure
29. Template option
- The next screen provides an option to use an existing template. Go
with the default (empty template), and click on Finish.
Figure
30. Selecting a template
Both data models have been created under the Data Models folder
in the Sales Demo – ELT and DW Project. The data
diagrams are created under the Data Diagrams folder in the
project.
Figure
31. Location of the data models and data
diagrams
- Notice that the sales_demo.dbm does not have a database specified.
It is named Database. Let's change that to
sales_demo:
- Click on Database under sales_demo.dbm (you
might need to double click on sales_demo.dbm to
expand the tree).
- The Properties window appears on the lower right panel.
Change the name "Database" to
sales_demo, and select
Save.
Figure 32. Changing the default database
name
- Do the same for Schema. To do so, expand the database
folder (now sales_demo) and change the schema property
name.
The expanded tree of the sales_demo physical data model should
now look like Figure 33 (notice the Diagrams folder is also
expanded):
Figure 33.
Expanded tree of sales_demo physical data model
Creating new tables for sales_demo
The diagrams are representative of the databases themselves in the form
of Entity-Relationship model. In this section, explore the diagrams
and learn how to manipulate them. You'll also create new tables for
the sales_demo database.
- Double-click to open the Diagram1 under the Diagrams
folder. You see a blank diagram with the Palette on the right.
This palette allows you to drag and drop objects on to the blank
canvas to create entities and relationships.
Figure
34. Blank diagram with palette
Explore the Palette and notice the Data and Geometric Shapes
folders. You're going to drag and drop the Table object on to
the blank canvas to create a table.
- Drag and drop the Table object on to the canvas.
Figure
35. Drag and drop Table object
- Let's now make a fact table called sales. To rename the
table from Table1 to sales, click on the name and
enter
sales. Alternatively, you can
click on the Table1 table and the properties window will
appear on the lower panel where you can rename Table1 to
sales.
- Add the columns to the sales table. Click on the
Columns tab in Properties window, and select the Add
Column button (the plus sign icon) to add the columns to
the sales table. Add the following columns, as illustrated in
Figure 36: customer_code, district_code, time_code, product_code,
units_sold, revenue, cost, net_profit. Make sure Not Null
is specified for customer_code, district_code,
time_code, and product_code. They are primary
keys, but leave the Primary Key check box unchecked for all
of them because you do not want to worry about referential
integrity after the first bulk load for now.
Figure
36. sales fact table
- Repeat the steps for the dimensional tables: geography
(Figure 37), product
(Figure 38), customer
(Figure 39), and time
(Figure 40).
Figure 37.
geography dimensional table
Figure 38.
product dimensional table
Figure 39.
customer dimensional table
Figure 40.
time dimensional table
| |
- Click on Save. Figure 41 illustrates how the diagram will
look:
Figure
41. Updated sales_demo diagram
- Expand the schema to view the newly created tables.
Figure
42. New tables under admin schema with the sales table
expanded
You want to update the database in the actual server with the
new tables.
- Right-click on the schema (in this case, admin), and click
on Generate DDL....
Figure
43. Generate DDL
- The Generate DDL wizard appears. Use the default option (CREATE
statements only), and click on Next.
Figure
44. Generate DDL wizard screen 1
- The second screen asks you which objects should be included in the
script. Keep the default, and click on Next.
Figure
45. Generate DDL wizard screen 2
- The next screen allows the DDL to be reviewed. Notice the folder
and file name. They can be edited to your preference. This example
uses the default name and folder. You want to run this script
against the server right away, so check the box next to Run DDL
on server, and click on Next.
Figure
46. Generate DDL wizard screen 3
|
Warning
Please note that if you want the tables created under the admin
schema, you have to be connected as user admin. If you are
connected as informix user, it will create tables under the
informix schema.
|
|
- Select the sales_demo database in the next screen, and
click on Next.
Figure
47. Generate DDL wizard screen 4
- View the summary screen, and click on Finish.
Figure
48. Generate DDL wizard screen 5
- To make sure the script was run successfully, take a look at the
SQL Results view in the lower right panel. It also
displays the job status on the right.
Figure
49. Reviewing the job status
This view is useful if the script execution is not successful.
You can expand the status and analyze every DDL statement
executed. Sometimes it would say the table already existed in
the database server.
-
You can verify if the tables are created by opening a dbaccess
session and viewing the database.
Figure
50. Verifying the tables are created
Congratulations!! You have completed this tutorial! Now you have
created your new Informix-based warehouse repository,
sales_demo, and you are ready to go through the other
tutorials in this series to create and deploy the processes needed to
populate your data warehouse.
| |
