ERwin Data Modeler r8 (new features)

 

The key learning below is created from webcast “Sneak Peak: CA ERwin Modeling r8 Preview Demonstration” – presentation

ERwin Data Modeler goals

1. RDBMS and Data Warehouse design
2. BI
3. Enterprise Application Integration (EAI)
4. Data Administration and Governance
5. Data Consolidation and Migration
6. Master Data Management (MDM)
7. Business Process Management and SOA development

You are No longer limited in space on the model.

On-demand UI windows

Productivity enhancements

Precision object placement

User defined styles and theme settings (ex: DEV/ITG/PRO)

Place the cursor on the relationship and get callouts.

Functional Dialog Improvements

A quick look at the ERwin DM and PM

 

The key learning below is created from webcast Optimizing design of your data warehouse – presentation

Static views of data can be captured in ERwin data modeler, but how do we capture dynamic views? Here are the most common techniques that we generally follow:

1. spreadsheets to capture mappings
2. process flow diagrams in visio and power point
3. UML diagrams in IBM and Sparx tools
4. Data Flow Diagrams (DFD) using visio

The problem with all this is that it is hard to maintain.

Solution – ERwin Data Modeler (DM) and ERwin Process Modeler (PM)

ERwin DM -

1. used to model databases (static data stores)
2. supports logical and physical models
3. conceptual models can be created in visio and power point
4. User Defined Properties (UDP) is used to define the actual type of a table (UDP can be defined to represent Flat files, SQL tables, Oracle tables etc)

ERwin only supports one type of database per model. So, if we use both Oracle and SQL Server, we can have one logical model but two physical models in ERwin. Data warehouses deals with flat files, SQL Server, Oracle, Cubes etc..

ERwin PM -

1. previously called BPwin
2. supports multiple diagram types
3. used to design the processing of data

Process Modeler Diagram

Drill into Customer Profitability

The diagram shows all the key components of the solution.

Drill down further into Commercial BI Data. You can decompose the model until you are comfortable or decompose until one developer can build it. These diagrams can show:

1. Activities
2. Data Stores
3. Data Flows
4. External Entities

The above can be tied back to ERwin Data Modeler. It can give the developer the level of details needed. We can use naming conventions such as:

1. A01.01.01 (activity name)
2. D01 (data store name)
3. E01 (external entity)

Example of Customer Loan external Entity in ERwin DM

With the DM, PM and the narrative in the PM, the developer has all the information to develop portion of the solution.

Summary

With ERwin DM and PM, we can model both the static views of a data warehouse and the flow of data. Use PM to decompose the solution. Use DM to model the database.

Enterprise Standards with ERwin Data Modeler

 

Key learning below is created from the webcast Creating Enterprise Standards with CA ERwin Data Modeling.

Bad data or Bad design leads to bad biz. decisions.

Benefits of Standards

• Increased efficiency & data quality
• Reuse across Org.
• ex: CUST represents customer

Why use naming standards?

A glossary of words and abbreviations that can be used for object names. Use Naming Standards editor to:

• develop model naming standards (table prefixes etc)
• name model objects derived from other objects
• reuse across Org.

You can import or export naming standards from/to a file such as a spreadsheet to be shared with other users.

You can attach a naming standards file to your model

Note the naming standards file changes the physical model. Not the abbreviated names.

You can check the naming standards using the naming standards compliance wizard

Name hardening lets you keep legacy names thereby leaving it untouched by the naming standards.

Why use Domains?

Domains are rules assigned to data elements to quickly assign properties to columns. This helps:

• speed design process
• ease of maintenance

Using Macros within Domains

ex: %Lower(%OwnerEntity %AttDomain)

Macros can be inherited across the model. If you apply the above macro to a domain “address” and add that domain to the entities customer and order, note that address is automatically changed to ‘customer address’ in customer table and ‘order address’ in order table.

Why use UDPs?

These are custom model object properties. These are used to

• further document the model objects (more documentation for ease of understanding)
• clarification for the users of the model
• standardize usage across models

Examples – Data Stewards, Status, Documentation attachments(system requirements documents)

UDPs can be set at any level

The below UDP contains:

Data Steward created as list. You can list default values.

System Requirements created as a command to open a document.

Date Modified as a Date

Max table size as Int

UDPs can be viewed under the UDP property of an entity

Why use Data type Standards?

You can define user defined datatypes and mapping information that is needed to convert logical and physical datatypes. These are used to:

• convert models from logical to physical
• convert models from one DBMS to another (ex: SQL Server to Oracle)
• derive other models
• new data types

You can use the Datatype standards editor in ERwin to convert a SQL Server Physical Model to Oracle Physical Model and ERwin will convert the datatypes of the model automatically for you.

Use Derive New Model menu to convert a SQL Server ERwin model to Oracle.

 

Target Oracle Model

Why use Model Templates?

A starting point for new models for reuse:

• domains
• naming standards
• formatting options
• speeds up development of models

Importing Domains, UDPs, Naming Standards can be imported from another ERwin model using the Complete Compare option

Models can be reverse engineered from script files:

You can reverse engineer a model from a script or database using a template

Why use ERwin Model Manager?

Server based central location for storing standards

• naming standards
• datatype standards
• model templates
• reuse & team collaboration
• does automatic versioning

Reporting to Stakeholders

ERwin has a lot of reporting capabilities that helps sharing information in customizable formats:

• HTML
• PDF
• Text
• RTF
• Crystal Reports
• ODBC queries

Reports can be filtered by Subject Areas or Stored Displays

Query ERwin