Data Warehousing Concepts in simple words

 

You can create a logical model, a physical model, or a Logical/Physical model.

To create a new Logical/Physical model:

1. On the File menu, click New to view the Create Model-Select Template dialog box, as shown in Figure

The Create Model-Select Template Dialog Box

2. In the New Model Type area, click the Logical/ Physical radio button.

3. From the Target Database panel, select the target database and its associated version.

4. Click OK to open a new data model with a blank diagram window.

The Entity tool adds new entities to the logical data model.

To add entities to a data model:

1. Click the Entity tool icon on the ERwin toolbar, as shown in Figure

Entity Tool Icon on the ERwin Toolbar

2. Click the diagram window to place the entity in the data model.

3. Specify a name for the entity.

4. Click Save on the File menu. The data models are saved in ERwin in files with the extension, .er1.

Attributes are added to entities to define the data structure, which stores the values in the physical model.

To add attributes to an entity:

1. Place the entity box on the diagram window and name it as Employee.
2. Click the plus sign next to the Entities icon to list all the entities in the data model.
3. Click the plus sign next to the Employee entity in the Model explorer to expand the object list.
4. Right-click the Attribute icon and select New from the context menu, as shown in Figure

Attribute Context Menu

5. In the Edit textbox, type the name of the new attribute as Emp-NAME. This changes the data model view in the diagram window to reflect the addition of the Emp-NAME attribute.

6. Click the Employee entity to select it and press the Tab key.

7. Add a Primary Key attribute, Emp-ID, to the entity. This changes the data model view in the diagram window to reflect the addition of the Emp_ID attribute.

Adding Attributes

8. Press Enter to add another Primary Key attribute.

9. Press Tab to add the non primary attributes to the entity.

Setting-up Relationships

The relationships in an ER model show the association between two entities of the data model. ERwin uses different types of lines to indicate different relationships. A solid line with black dots near both its heads depicts a many-to-many relationship. A solid line shows the

Identifying relationship with a single prominent black dot near the head that terminates at the child entity. A dashed line depicts a non-identifying relationship. The lines that indicate relationships are available in the ERwin Toolbox, as shown in Figure 1-1-6:

Figure 1-1-6: Toolbox Buttons

To define a relationship between two entities:

1. From the ERwin toolbar, select the type of relationship to be applied.
2. Click the parent entity.
3. Click the child entity. The primary key of the parent entity automatically gets propagated as the foreign key of the child to identify the relationships.
4. Right-click the relationship line that connects the two entities. Select Relationship Properties from the context menu to open the Relationships dialog box, as shown in Figure

Relationships Dialog Box

5. In the Cardinality pane, click the appropriate radio button to assign cardinality to the relationship.

6. In the Type pane, click the appropriate radio button to select the relationship type.

The foreign key in the child entity can be assigned an alias name that describes its role in the child entity. This new name is called a role name.

To assign a role name to an attribute of the data model:

1. Right click the relationship in a data model and select the Relationship Properties option from the context menu.

2. Click the Role name tab to view the Role name Info pane.

3. From the Migrated Key drop-down list box, select the migrated key to which a role name must be assigned.

4. Enter the role name for the migrated key in the Role name field.

5. Click OK to assign the new role name to the migrated key.

Using Subject Areas and Domains, You can use ERwin to apply the top-down approach to develop data models. Large data models can be divided into small parts to simplify data modeling. The Subject Area in Erwin contains a subset of objects that exist in a data model. The main Subject Area is the default Subject Area that holds all the objects in a data model.

You can use ERwin to create separate Subject Areas for the logical and physical models.

Each Subject Area is dynamically linked to the data model, which means that any changes in Subject Area are reflected in the data model. The Subject Area Editor can be used to create, modify, and delete Subject Areas.

To create a Subject Area:

1. On the ERwin Standard toolbar, click the Subject Area Editor button as shown in Figure :

Subject Area Editor Button

2. Click New to assign a name to the Subject Area.

3. Click the Members tab to select the members to be included in the Subject Area.

4. Click OK to create a new Subject Area.

To move to the new Subject Area, click the Subject Area drop-down arrow on the Erwin Standard toolbar. You can also list the Subject Areas in the Model explorer. Alternatively, click the Subject Area icon at the bottom of the Model explorer to list all the Subject Areas,

A new Subject Area can also be created in the Model explorer.

To create a new Subject Area from the Model Explorer:

1. Right-click the Subject Area folder and select New from the context menu, as shown

Subject Area Icon - Model Explorer

2. Right-click the icon and select New to create a new Subject Area.

3. Assign a name to the new Subject Area.

4. Click the plus sign next to Main Subject Area icon to expand the main Subject Area.

5. Drag-and-drop the entities from the Main Subject Area to add the entities to the new Subject Area.

A domain is a model object that is used to assign properties to the columns and the attributes. Domains group attributes together to help reduce the time to develop and maintain a model. The domains are created using a Domain Dictionary.

To create a domain:

1. Select Model -> Domain Dictionary.

2. Click New to open a new domain, as shown in Figure

Domain Dictionary

3. Click New to create a new domain in the data model.

4. Assign a name for the new domain.

5. Select the Data type tab in the logical model or the Database tab in the physical model to assign the data type domain property, as shown.

6. Click the button adjacent to the valid field to show the Validation Rules window.

7. Click New in the Validation Rules window to assign the name between to the validation rule.

8. Select the validation property type from the Type pane.

9. Enter a valid value in the Default field or click the button next to the Default field to assign a default value to the domain property.

10. Click OK to create the domain.

To assign a domain property to the Employ-ID field:

1. In the Model explorer, right-click the Employ-ID icon to select the Properties option from the context menu.
2. Click the Data type tab to assign a data type to the attribute.
3. Assign the validation rule, between, from the valid drop-down list.
4. Click OK to apply the domain property, between, to the Employ-ID field.