Data Modeling Tools – What to Learn? Part 2

Dimensional Data Model:

  • Is there any specific notation to identify a Data Warehouse/Data mart data models?

Subject Area:

  • How to create subject area and assign relevant entities to subject area?

Reports:

  • How to generate reports from data model and export to .XLS, .DOC, .XML file formats?

Naming Options:

  • Is there any method to change the entity/table, attribute/column name from
    upper case to lower case or lower case to upper case?

Import & Export:

  • How to create data models from .xls, .txt files etc.?
  • How to import and export meta data into ETL tools?

Abbreviation Document:

  • How to create/attach a standard abbreviation document(for naming tables, columns etc.)?

Print:

  • How to send data models to printer/plotter/Acrobat Reader?

Backup:

  • How to take backup of data model?

Others:

  • How to split a data model to logical and physical data model?
  • How to copy and paste objects within data model and across data models?
  • How to search an object within a data model?
  • How to change the font size and color of entities,attributes,relationship lines?
  • How to create a legend?
  • How to show a data model in different levels like entity level, attribute level, and definition level?

 

Data Modeling Tools: What to Learn?

Data modeling tools are the only way through which we can create powerful data models. Following are the various options that we have to know and learn in data modeling tools before start building data models.

Logical Data Model:

  • How to create entity and add definition, business rule?
  • How to create domains?
  • How to create an attribute and add definition, business rule, validation rules like default values and check constraint?
  • How to create supertypes, subtypes?
  • How to create primary keys, unique constraint, foreign key relationships, and recursive relationships?
  • How to create identifying and non-identifying relationship?
  • How to assign relationship cardinality?
  • How to phrase relationship connecting two tables?
  • How to assign role names?
  • How to create key groups?
  • How to create sequence no’s?

Physical Data Model:

  • How to rename a table?
  • How to rename a column,validation rules like default and check constraints?
  • How to assign NULL and NOT NULL to columns?
  • How to name foreign key constraints?
  • How to connect to databases like MS Access, Oracle, Sibase, Terradata etc?
  • How to generate sql code from data model to run against databases like MS Access, Oracle, Sibase, Terradata etc.?
  • How to create a data model from an existing database like MS Access, Oracle, Sibase, Terradata etc.?
  • How to add database related properties to tables, indexes?
  • How to check different versions of the data model?
  • How many data modelers can concurrently work on the same version of a data model?

Data Modeling Tools – What to learn? Part 2

Data Modeling Standards | Modeling Data

Data Modeling standardization has been in practice for many years and the following section highlight the needs and implementation of the data modeling standards.

Standardization Needs | Modeling data:

Several data modelers may work on the different subject areas of a data model and all data modelers should use the same naming convention, writing definitions and business rules.

Nowadays, business to business transactions (B2B) are quite common, and standardization helps in understanding the business in a better way. Inconsistency across column names and definition would create a chaos across the business.

For example, when a data warehouse is designed, it may get data from several source systems and each source may have its own names, data types etc. These anomalies can be eliminated if a proper standardization is maintained across the organization.

Table Names Standardization:

Giving a full name to the tables, will give an idea about data what it is about. Generally, do not abbreviate the table names; however this may differ according to organization’s standards. If the table name’s length exceeds the database standards, then try to abbreviate the table names. Some general guidelines are listed below that may be used as a prefix or suffix for the table.

Examples:

Lookup – LKP – Used for Code, Type tables by which a fact table can be directly accessed.
e.g. Credit Card Type Lookup – CREDIT_CARD_TYPE_LKP

Fact – FCT – Used for transaction tables:
e.g. Credit Card Fact – CREDIT_CARD_FCT

Cross Reference – XREF – Tables that resolves many to many relationships.
e.g. Credit Card Member XREF – CREDIT_CARD_MEMBER_XREF

History – HIST – Tables the stores history.
e.g. Credit Card Retired History – CREDIT_CARD_RETIRED_HIST

Statistics – STAT – Tables that store statistical information.
e.g. Credit Card Web Statistics – CREDIT_CARD_WEB_STAT

Column Names Standardization:

Some general guidelines are listed below that may be used as a prefix or suffix for the column.

Examples:

Key – Key System generated surrogate key.
e.g. Credit Card Key – CRDT_CARD_KEY

Identifier – ID – Character column that is used as an identifier.
e.g. Credit Card Identifier – CRDT_CARD_ID

Code – CD – Numeric or alphanumeric column that is used as an identifying attribute.
e.g. State Code – ST_CD

Description – DESC – Description for a code, identifier or a key.
e.g. State Description – ST_DESC

Indicator – IND – to denote indicator columns.
e.g. Gender Indicator – GNDR_IND

Database Parameters Standardization:

Some general guidelines are listed below that may be used for other physical parameters.

Examples:

Index – Index – IDX – for index names.
e.g. Credit Card Fact IDX01 – CRDT_CARD_FCT_IDX01

Primary Key – PK – for Primary key constraint names.
e.g. CREDIT Card Fact PK01- CRDT-CARD_FCT_PK01

Alternate Keys – AK – for Alternate key names.
e.g. Credit Card Fact AK01 – CRDT_CARD_FCT_AK01

Foreign Keys – FK – for Foreign key constraint names.
e.g. Credit Card Fact FK01 – CRDT_CARD_FCT_FK01

Note:

If you are joining in a company as a data modeler, first speak with DBAs or the software configuration management team to know the standards.