ERWin Dimentional Modeling

原创 2012年03月26日 14:26:54

Data structure and relationships among entities.

Data Model (conceptual level, logical level, physical level)

Diff b/t Dimensional Model and ER Model

Dimensions, Facts, Granularity and various types of dimensions.


Conceptual Data Model

(collating requirements, focus on entities and relationships)


Logical Data Model

(attributes, key groups, relationships, business rules)


Physical Data Model

(performance of database, strategy of indexing, physical storage, denormalization are considered in this phase)


Logical Data Model

Physical Data Model

Represents business info and defines business rules

Represents the physical implementation of the model in a database.





Primary key

primary key constraint

alternate key

unique constraint or unique index

Inversion key entry

non unique index


check constraint, default value


foreign key






Basics of RDBMS

1.       RDBMS

2.       Data modeling approaches(Conceptual, Logical, Physical)

3.       ER Modeling

4.       Keys(CK, PK, FK)

5.       Normalization Levels

6.       OLTP

7.       Data mart

8.       OLAP

9.       Datawarehouse(DWH)


Identifying Relationships

(It exists when the child table cannot be uniquely identified in the absence of the parent)

Ex : HolderAccount (p),  Account, Holder

Non-Identifying Relationships

(the child can uniquely be identified without the parent)

Ex : Account, AccountType


Types of Entities :

Independent entity, dependent entity

Ex: unit, unit head


Referential Integrity

(Every non-null foreign key value must be matched by an existing primary key value)


Types of Attributes :

Key and Non-key attributes

Required or optional attributes

Simple and composite attributes

Single-valued and multi-valued attributes

Stored, coded, or derived attributes


ER Model only supports two dimensional view of data.

Dimensional database is optimized to retrieve data and analyze it. It tends to be subject oriented.


Dimensional Modeling(DM) is a logical design technique used in DWH.

Mostly denormalized relational model

Made up of entities with attributes

Relationships defined by primary keys and foreign keys

Used by most contemporary BI solutions “Right” mix of normalization and de normalization often called Dimentional Normalization.

Consists of two types of tables : Dimension tables and Fact tables.


Dimension Keys

Dimension Business Key

(Column or columns that uniquely identify an instance of the business record)

(Used in the process of  ETL to tie fact records with dimension members)

Dimension Record Surrogate Key

(Defines the dimension’s primary key)

(Relates to the fact table foreign key field)

(Numeric data type, typically integer)


Dimension Surrogate Key

a.       Surrogate Key Usage

Consolidates multi-value business keys

Allows tracking of dimension history

Standardizes dimension tables

Limits fact table width for optimization


b.      Surrogate key Design Practices

Avoid smart keys

Avoid production keys (may changes!)

A company may acquire a competitor and thereby change the essential building rules changed-record, but intentionally not change the key.

Narrow as possible


Dimension Types

1.       Slowly Changing Dimension

It is a dimension whose attributes or attributes for a record(row) change slowly over time.

Type-1(Overwriting the history)

Type-2(Preserving the history)

Type-3(Preserving one or many versions of history)

2.       Junk(garbage) Dimension

A garbage Dimension is a dimension that includes low-cardinality columns such as codes, indicators and status flags.

The attributes in a Junk Dimension are not related to any hierarchy.

3.       Role-playing Dimension

A single dimension which gets differently expressed in a fact table using views is called a role-playing dimension.

Ex : Create two views for OrderDate and ReceivedData based on the same date dimension table.


Facts (or measures) can be categorized by

a)      Numerical data type

b)      Aggregation type

c)       Additive nature

d)      Granularity

Useful facts normally are numeric and additive.

Fact Types :

1)      Transactional

2)      Additive

Additive (Facts that can be summed up/aggregated across all dimensions within the fact table)

 ex : dollars sold

Semi-Additive (Facts that can be added up for some of dimensions in a fact table, but not for others)

 ex : account balance, inventory level, distinct counts

Non-Additive (Facts cannot be summed up for any of the dimensions which are present in the fact table)

Ex: measurement of room temperature

3)      Snapshot or inventory

4)      Factless

5)      Derived

6)      Aggregate

When to use snowflake

1)      The dimension table has two or more sets of attributes which normally define information at different grains.

2)      The attribute sets of the same dimension table are getting populated by different source systems.

It is not possible to implement a many to many relationship in a physical model as two tables can’t be children of one another

It needs to use a bridge table to solve this issue.

Introduce a junction table Customer_PaymentModes, which has many to one relationship with both Customer and PaymentModes.


ERwin初学者使用1、如果只做文档,可以选择只建立logical view2、如果是做项目,需要同时使用Logical view 和physical view3、一般Logical view是用于做...
  • flx_zxl
  • flx_zxl
  • 2007年04月04日 16:16
  • 634


1,定义数据字典,把需要用到的数据类型创建好  2,在物理模式下,设置数据字典,修改comment为%AttName 这样会默认使用逻辑模式下,实体属性的名称作为字段的注释 3,物理模式下选...
  • fengjunoo
  • fengjunoo
  • 2016年06月11日 14:44
  • 1092

ERwin Data Modeler 建模实践

ERwin Data Modeler 建模实践 ERwin Data Modeler (以下简称 ERwin) 是 CA公司 (Computer Associates) 的一款优秀建模工具,历经数年...
  • TechChan
  • TechChan
  • 2013年10月25日 11:25
  • 8358


简单概念: 1、 如果只做文档,可以选择只建立logical view。 2、 如果是做项目,需要同时使用Logical view 和physical view。 3、 ...
  • judyge
  • judyge
  • 2014年11月14日 14:00
  • 755


  • duguduchong
  • duguduchong
  • 2010年11月30日 22:50
  • 1754


在以前的帖子中说过,要整理下ERwin由逻辑模型到物理模型的映射,一直没时间,今天终于整理了,如下:        项目的建模工具,用的较多的有PD、Rose,我学生时代,就没听说过ERwin,...
  • huaxin520
  • huaxin520
  • 2012年07月29日 16:59
  • 6998


标识关系,实线表示 非标识关系,虚线表示 实心圆端所在的那端为一对多关系中的多的那端标识关系贡献父实体的键给子实体,所有键变成子实体的键,成为了子实体的键的一部分,意思是子标识依赖于父,也就是子实...
  • CZWin32768
  • CZWin32768
  • 2016年09月17日 09:22
  • 1709

通过Erwin的反向工程导入实体 之二(自动为物理模型命英文名)

在以前的帖子中说过,要整理下ERwin由逻辑模型到物理模型的映射,一直没时间,今天终于整理了,如下:        项目的建模工具,用的较多的有PD、Rose,我学生时代,就没听说过ERwi...
  • TechChan
  • TechChan
  • 2015年10月21日 09:23
  • 2889

通过Erwin的反向工程导入实体 之一

1、通过数据库导入实体(此处已Oracle11g,作为实验数据库)    (1)  打卡erwin, 单击菜单栏Tool -> Reverse Engineer,弹出如下界面   ...
  • TechChan
  • TechChan
  • 2015年10月21日 09:22
  • 2761


ERwin的学习总结一 新建domainmodel:file>newnew model type   一般Logical view是用于生成文档的,而physical view是用于生成或者导出脚本的...
  • xx_liuhui
  • xx_liuhui
  • 2010年03月10日 15:08
  • 1198
您举报文章:ERWin Dimentional Modeling