Category: Data Modeling Tools

New Features in Erwin Release 2020 R1

Leave a comment
  • Use ‘Active’ Model Templates to facilitate reusable modeling metadata and to implement/enforce enterprise standards
  • Erwin Data Modeler is now certified to work with: Db2 z/OS v12
    SQL Server 2019
    PostgreSQL v9.6.15, v10.10 and v11.5
    Oracle 18c (18.3)
  • Erwin Data Modeler now supports the new features and capabilities offered in Db2 LUW 11.1 like: Storage Group object support has been added.
    Table properties for all three types (Permanent, Global Temporary, and Nickname) have been updated. Also, Index and XML Index support has been enhanced.
    Tablespace properties have been updated to support Storage Space Name and Data Tag.
    Materialized query table properties have been enhanced.
  • Erwin DM Scheduler: Enables users to define and schedule database reverse engineering (REDB) tasks in advance
    Minimizes workstation contention by allowing modelers to plan for and run REDB tasks when resources are available
  • Overall modeler productivity has been enhanced via new product functionality and workflows: A “quick” compare option set and “speed” option
    Supertype-Subtype transformation updated to retain separate logical and physical constructs for Identity and Roll-Up option selections
    Deletion of a relationship offers modeler the option of converting migrated FK attribute(s)/column(s) into child entity/table owned attribute(s)/column(s)
    Mart OPEN dialog now displays number of entities, attributes and relationships
    Mart and mart report performance has been significantly improved
    Database “Target server” dialog can be accessed by clicking the database name displayed on the physical model status bar
  • Naming Standards enhanced to fully support physical-to-logical name mapping
  • Users defined in Azure Active Directory can now connect to the Mart from erwin Data Modeler.
  • Enhanced product security measures include: Third-party components upgraded:
    Tomcat v9.0.27
    OpenSSL 1.1.1c
    AdoptOpenJDK (build 11.0.3+7)
  • Erwin Data Modeler now offers an automation option for Reverse Engineering and Forward Engineering through the Erwin API.
  • Erwin Data Modeler includes flexible extended notes on all object types that can be: compared, searched, and edited using the Bulk Editor
    reported via the Report Designer
    managed using the erwin API
  • Erwin SCAPI has been extended to support the application of naming standards.
  • Erwin Data Modeler provides “native” support for Hadoop Hive as a database target – including AVRO files!
  • Licensing improvements include: Silent Install
    Option to “hide” Licensing dialog
    Support for proxy connections to the License/Activation server
    Optional network domain name and subnet IP-based usage restrictions
    erwin Data Modeler “edition”-based licensing
    The ability to “borrow” (Long Term Check Out) cloud-based concurrent licenses
  • Collaboratively administer the Workgroup Edition mart using our web-based Mart Administration utility Session (Offline/Disconnect) Management
    Security (User/Role) Management
    AD Authentication (via LDAP)
    Permission/Authorization Management
    Model Locking
  • Collaboratively administer the Workgroup Edition mart using our web-based Mart Administration utility Session (Offline/Disconnect) Management
    Security (User/Role) Management
    AD Authentication (via LDAP)
    Permission/Authorization Management
    Model Locking
  • Erwin Data Modeler supports a wide variety of reports from a mart, including: administrative
    cross-model
    model object count reports
  • The erwin Data Modeler (DM) User Interface has been revamped to include the following: A Microsoft “Office-like” ribbon-based appearance
    UI themes, font sizes, and component direction settings
    Tabbed and “side-by-side” diagram window arrangement option
    A “Quick Access” toolbar
  • Erwin Data Modeler supports both online and local product documentation and help.
  • Erwin Data Modeler has added “native” support for Redshift as a database target.

  • Report Designer includes the ability to create customized report filters and properties using erwin Data Modeler’s TLX scripting language
  • Erwin Data Modeler supports new features and capabilities offered in SQL Server like: Always Encrypted columns
    Dynamic Data Masking
    Temporal tables
    Memory Optimized Table
    Natively compiled Stored Procedures
  • Erwin Data Modeler supports new features and capabilities offered in SQL Server like: Always Encrypted columns
    Dynamic Data Masking
    Temporal tables
    Memory Optimized Table
    Natively compiled Stored Procedures
  • User enhancements and modeling improvements include: A new option has been added to allow modelers to enforce Relationship Nullability Rules
    A new option has been added to allow modelers to enforce Supertype-Subtype relationship rules
    Oracle 12c support for 128 character TABLE and COLUMN Names and PostgreSQL support for 63 character TABLE and COLUMN names
    New support added for relationship cardinality ranges (for example, 1..5)
    New “Forward Engineering” wizard
    Ability to tag ATTRIBUTES/COLUMNS as PII
    Support for Hive VIEW Partitions
  • Erwin Data Modeler is offered as a native 64-bit application with access to sufficient memory to complete operations on large models (A 32-bit option is still available).

Data Modeling Tools – Xcase

Leave a comment

Xcase – Overview:

Xcase is a powerful and intuitive data modeling tool. It automates database creation and maintenance, making the modeling process simple and visual. The tool provides tight support for the leading DBMS and an impressive set of capabilities.

Using Xcase, data modelers can create data models from scratch or from existing databases using the Reverse Engineering module. Once the model is complete, Xcase Forward Engineering module generates all the DDL scripts necessary to create a new database or update an existing one.

Using Xcase, you can:

⇒ Create Professional Data Models from scratch.

⇒ Reverse Engineer existing database structures.

⇒ View and organize the database structure at different levels of depth.

⇒ Communicate effectively with clients and colleagues.

⇒ Fully control database Metadata within the model and add user defined attributes for each object.

⇒ Manage different versions of your model/database using the advanced Version Control module – compare a model to the actual database, to another model or to a previous version of the same model and generate a detailed report of all discrepancies found.

⇒ Synchronize your model with the database using the 2-way synchronization feature. Xcase provides a detailed report on discrepancies found between the model and the database and allows you to update the model based on the actual database structure or update the database based on your model.

⇒ Automatically generate all the code necessary to create and maintain the database without jeopardizing existing data.

⇒ Validate the data in the database against the business rules defined in the model.

⇒ Access and edit the data relationally using automatic parent/child browsers and lookups.

⇒ Migrate the database metadata and data from one DBMS to another.

Note: For more information, refer the Xcase data modeling web page.

 

Creating a Physical Data Model using Erwin

One comment

How to create a Physical Data Model using Erwin 4.1.4 version?

1: Change the view from “Logical to Physical” from the drop down list.

2: Click “Database” from main menu and then click “Choose Database” from the sub menu. Then select your target database server where the database has to be created. Click ok.

3: Place the cursor on the table “Country” and right click it. From the displayed menu, click columns which will take you to the column wizard. Click the “Database Tab”, which is next to “General Tab” and assign datatypes “VARCHAR2(10), VARCHAR2(50) for columns COUNTRY_CODE and COUNTRY_NAME respectively. Change the default NULL to NOT NULL for the column COUNTRY_NAME. Similarly, repeat the above step for the BANK table. Once you have done all of these, you can see the physical version of the logical data model in the current workplace.

The Physical Data Model created by following the above steps looks similar to the following diagram.

physical

How to generate DDL(sql) scripts to create a database?

1: Select the view as <b>Physical</b> from the drop down list.

2: Click “Tools” from main menu and then click “Forward Engineer/Schema Generation” from the sub menu which will take you to the “Schema Generation Wizard”. Select the appropriate properties that satisfies your database requirements like schema, table, primary key etc. Click preview to see your scripts. Either you can click to generate the table in a database or you can store the scripts and run against the database later.

The DDL(sql) scripts generated by Erwin by following the above steps looks similar to the following script.

CREATE TABLE Country(Country_Code VARCHAR2(10) NOT NULL,
Country_Name VARCHAR2(50) NOT NULL,
CONSTRAINT PK_Country PRIMARY KEY (Country_Code));
CREATE TABLE Bank(Bank_Code VARCHAR2(10) NOT NULL,
Bank_Name VARCHAR2(50) NOT NULL, Country_Code VARCHAR2(10) NOT NULL,
CONSTRAINT PK_Bank PRIMARY KEY(Bank_Code) );ALTER TABLE Bank ADD( CONSTRAINT FK_Bank
FOREIGN KEY (Country_Code) REFERENCES Country );

Note: This is not a complete tutorial on Erwin. We will add more Tips and Guidelines on Erwin in near future. Please visit us soon to check back. To know more about Erwin, contact its official website www.ca.com.

Creating a Logical Data Model using Erwin

Leave a comment

How to create a Logical Data Model using Erwin 4.1.4 version?

In the following section, a simple example with a step by step procedure to create a logical data model with two entities and their relationship are explained in detail.

1: Open All Fusion Erwin Data Modeler software.

2: Select the view as “Logical” from the drop-down list. By default, logical will be your workplace.

3: Click New from File menu. Select the option “Logical/Physical” from the displayed wizard. Click Ok.

4: To create an Entity, click the icon “Entity” and drop it on the workplace. By default E/1 will be displayed as the entity name. Change it to “Country”.

5: To create an Attribute, Place the cursor on the entity “Country” and right click it. From the displayed menu, click attributes which will take you to the attribute wizard. Click “New” button on the wizard and type attribute name as “Country Code”. Select the data type as “String” and click OK. Select the option Primary Key to identify attribute “Country Code” as the primary key. Follow the same approach and create another attribute “Country Name” without selecting the primary key option. Click ok, and now you will be having 2 attributes Country Code, and Country Name under the entity “Country” in the current logical workplace.

6: Create another entity “Bank” with two attributes namely Bank Code and Bank Name by following steps 4 and 5.

7: In order to relate these two tables country, bank, a Foreign Key relationship must be created. To create a Foreign Key relationship, follow these steps.

(a) Click the symbol “Non Identifying Relationship”.
(b) Place the cursor on the entity “Country”.
(c) Place the cursor on the entity “Bank”. Now you can see the relationship(a line drawn from Bank to Country) between “Country” and “Bank”. Double click on that relationship line to open “Relationships wizard” and change the option from “Nulls Allowed” to “No Nulls” since bank should have a country code.

The Logical Data Model created by following the above steps looks similar to the following diagram.

logical

 

Next⇒ Creating a Physical Data Model using Erwin

 

Erwin Tutorial

2 comments

All Fusion Erwin Data Modeler:

All Fusion Erwin Data Modeler commonly known as Erwin, is a powerful and leading data modeling tool from Computer Associates. Computer Associates delivers several software for enterprise management, storage management solutions, security solutions, application life cycle management, data management and business intelligence.

Parallax Capital Partners acquired Erwin and incorporated Erwin Inc, located in CA.

Academic Edition, Standard Edition, Work-group Edition, Navigator edition are the different types of editions available in Erwin.

Standard Edition: Most of the data modelers work with Standard Edition which provides all data modeling features.

Work-group Edition:  In this edition all data modeling features provided plus the data model versioning and multi-user modeling. Many data modeler can work on the same data model concurrently.

Navigator Edition:  Erwin Data Modeler Navigator Edition provide read-only access to models built with Erwin Data Modeler for viewing and analysis.

Academic Edition: The Academic Edition is a time-limited edition and includes a limited number of features. from the main product. It is designed to help students to learn creating simple data models.

Erwin makes database creation very simple by generating the DDL(sql) scripts from a data model by using its Forward Engineering technique or Erwin can be used to create data models from the existing database by using its Reverse Engineering technique.

Erwin workplace consists of the following main areas:

  • Logical: In this view, data model represents business requirements like entities, attributes etc.
  • Physical: In this view, data model represents physical structures like tables, columns, datatypes etc.
  • Modelmart: Many users can work with a same data model concurrently.

What can be done with Erwin?

  • Logical, Physical and dimensional data models can be created.
  • Data Models can be created from existing systems(rdbms, dbms, files etc.).
  • Different versions of a data model can be compared.
  • Data model and database can be compared.
  • SQl scripts can be generated to create databases from data model.
  • Reports can be generated in different file formats like .html, .rtf, and .txt.
  • Data models can be opened and saved in several different file types like .er1, .ert, .bpx, .xml, .ers, .sql, .cmt, .df, .dbf, and .mdb files.
  • By using ModelMart, concurrent users can work on the same data model.

In order to create data models in Erwin, you need to have this All Fusion Erwin Data Modeler installed in your system. If you have installed Modelmart, then more than one user can work on the same model.

How to create a Logical Data Model using CA Erwin Data Modeler Version 9?

In the following section, a simple example with a step by step procedure to create a logical data model with two entities and their relationship are explained in detail.

  • Open CA Erwin Data Modeler Version 9 software.
  • Go to menu ‘file’ and click ‘new’. A new box will be displayed. Click the button logical/physical from type. From the drop down list box, select ‘oracle’ as the database and version as ’10g/11g’. Click OK button. A new workspace is created. To select notation ‘IDEF1X’ or ‘IE’ Go to MODEL/MODEL PROPERTIES/GENERAL TAB to change.
  • Above the workspace, you can see menus File, Edit, View, Diagram, Model, Actions, Tools, Windows, Help. Below that you can see the following tool bars.
    Standard, Toolbox, Formatting, Layout, Zoom, Display, Mart, Database, Alignment, Drawing, Transformations, Customize. On the display toolbar, you can see ‘Logical’ selected by default i.e. you will be working on creation of a logical data model.
  • To create an Entity, click the icon “Entity” from ‘Toolbox’ Toolbar and drop it on the workplace. By default E/1 will be displayed as the entity name and type it as ‘Country’.
  • To create an Attribute, Place the cursor on the entity ‘Country’ and right click it. From the displayed menu, click attribute properties which will take you to the attribute wizard. You can see a button New. Click ‘New’ on the wizard and type attribute name as ‘Country Code’. You can see different headers like ‘Parent Domain’, ‘Logical Datatype’, ‘Primary Key’, ‘Foreign Key, ‘Logical Only’.
    Select the parent domain as ‘string’ and by default logical datatype becomes ‘varchar2(20)’. Change the datatype to ‘varchar2 (2); Click the check box near the primary key.Similarly create another column Country Name with parent domain as ‘string’ and datatype as ‘varchar2 (50)’. Select NULL OPTION as ‘NOT NULL’ from the NULL OPTION list box. THERE ARE DIFFERENT TABS LIKE ‘General’, ‘Constraint’, ‘Link’, ‘Key Groups’, ‘Style’, ‘Definition’, ‘Where Used’, ‘UDP’, ‘Notes’ etc. To create constraints, you can use ‘constraint’ tab. To create indexes, you can use ‘key groups’ tab. For typing attribute definition ‘ you can use ‘definition tab’. For changing font size, font name, font color, you can use ‘style tab’. For seeing the properties like event, date created, created by, data modified, modified by, you can use ‘Notes’ tab. Click ok, and now you will be having 2 attributes Country Code, and Country Name under the entity ‘Country’ in the current logical workplace.
  • Create another entity ‘Bank’ with two attributes namely Bank Code and Bank Name.
  • In order to relate these two tables country, bank, a Foreign Key relationship must be created. To create a Foreign Key relationship, follow these steps.
    • Click the symbol ‘Non Identifying Relationship’ from ‘Toolbox’ Toolbar.
    • Place the cursor on the entity ‘Country’.
    • Place the cursor on the entity ‘Bank’. Now you can see the relationship (a line drawn from Bank to Country) between ‘Country’ and ‘Bank’. Double click on that relationship line to open ‘Relationships wizard’ and change the option from ‘Nulls Allowed’ to ‘No Nulls’ since bank should have a country code.

    The Logical Data Model created by following the above steps looks similar to the following diagram.

  • SAVE: To save the file, click save button in the ‘standard’ tool bar and save it as .’CA_Erwin_Data_Modeler_v9_example’ and file type will be .erwin.

How to create a Physical Data Model using CA Erwin Data Modeler Version 9?

  • Change the view from ‘Logical to Physical’ from the drop down list.
  • If you want to add some physical properties, you can add it here.
Explanation:

In the logical view: When you right click and view entity properties for entity ‘Country’, you can see tabs like volumetrics, definition, style, icon, where used, UDP, NOTES etc. The information that you fill will explain about entity properties.

In the physical view: when you right click and view table properties for table ‘country’, you can see tabs like table options, general, partitions, supplemental logging, validation, materialized view log, style, icon, synonym comment, object creation order, where used, UDP, Notes etc.

If you are an expert, then you can fill relevant details in the above mentioned tabs so that it will be useful while creating scripts. If you are not an expert, you can discuss with DBAs, get relevant information and fill details.

How to generate DDL(sql) scripts to create a database using CA Erwin Data Modeler Version 9?

  • Click menu ‘edit’. Navigate to ‘selection’ and click ‘select all’. Objects bank and country will be highlighted.
  • Click the ‘forward engineer schema generation’ icon in the ‘database’ tool bar. A new Wizard appears.
  • There are two boxes. On the left side box, you can several database objects. On the right side box, you can see different options for each object.

[ultimatetables 25 /]

Click preview. You can see the sql code generated by Erwin data modeler tool.

CREATE TABLE Country ( Country_Code VARCHAR2(2) NOT NULL ,
Country_Name VARCHAR2(50) NOT NULL ,
CONSTRAINT XPKCountry PRIMARY KEY (Country_Code) );

CREATE TABLE Bank ( Bank_Code VARCHAR2(2) NOT NULL ,
Bank_Name VARCHAR2(50) NOT NULL ,
Country_Code VARCHAR2(2) NOT NULL ,
CONSTRAINT XPKBank PRIMARY KEY (Bank_Code),
CONSTRAINT Country_to_Bank FOREIGN KEY (Country_Code) REFERENCES Country (Country_Code) );

Next⇒ Creating a Logical Data Model using Erwin

 

We are providing Online Data Modeling Training on:

OLTP, Data Warehouse, Datamart, Dimensional and Snow Flake Data Modeling and Normalization, end to end process with ERWIN Tool.

To know more about the Syllabus, please visit the page Online Data Modeling Training Syllabus.

 

Data Modeling Tools – What to Learn? Part 2

Leave a comment

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?

Leave a comment

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

How to create a physical data model from a logical data model using Embarcadero’s Data Architect?

Leave a comment

We are focusing to create this physical data model with oracle as the database. So we have selected oracle 12c as the database.

Open existing logical data model “02_LDM_creating_entity_version2”.

Click menu Model/Generate Physical Model. A small screen will appear. Click ok.

A new screen is opened. There are 5 steps in this process.

We are explaining step 1 and Step 3 only.

Step 1 of 5:

Name the physical data model: pdm_creating_entity_version2

Type of physical model: relational

Target_database_platform: oracle12c.

Click next and again click next.

Step 3 of 5:

Attach the naming template “enterprise_naming_standards.nst”. Click finish.

You can now see the physical data model.

04_physical_data_model_diagram

 

 

How To Create Naming Standards In Embarcadero’s Data Architect?

Leave a comment

Create a .csv file named “Naming_Standards.csv” and store the following information in that csv file. The reason for creating this file is to avoid errors and typos in our naming conventions and data modeling naming convention standards can also be followed.  We have eliminated VOWELS in the actual word so that physical object names will not exceed the actual length provided by the database. Later this file will be created in .nst format (embarcadero’s naming standards format) and has to be attached to the physical data model that we will create in the next section. This file (.nst) can be attached to all data models created across the enterprise.

Note: We are not providing naming standards for logical data model.

Step1: Creating .csv file and storing it as “Naming_Standards.csv”

Open your Microsoft Excel and type the following data. Then store it in .csv format.

DEPARTMENT DPRTMNT
NUMBER NMBR
NAME NM
ADDRESS ADDRSS
IDENTIFIER IDNTFR
EMPLOYEE EMPLY
MANAGER MNGR
SALARY SLRY
DATE DT
OF OF
JOINING JNNG
PHONE PHN
NUMBER NMBR
INCENTIVE INCNTV
INDICATOR INDCTR

Step2: Creating .nst file and storing it as “Enterprise_Naming_Standards.nst”

Open existing logical data model “02_LDM_creating_entity_version2”

Click menu Tools/Naming Standards Template Editor.

A new screen appears.

On top of the screen, there are four different tabs. Name, Logical, Physical, and Mapping.

Under name tab: type the file name as enterprise_naming_standards.

Under Logical tab, you can change max length of entity, attribute, view, key, relationship. Select case as upper for entity.

Under physical tab, you can change you can change max length of entity, attribute, view, key, relationship.  You have to change the length as per the target database standards. Select case as upper for table.

Under mapping tab, click import. Select the file “naming_standards.csv”.

In mapping tab, below the screen there are three tabs abbreviations, order, and general. Click general tab.

Since we will be placing underscore between each word of the column, type “_” in physical word separation under heading “delimiter options”.

Click save. Save the file as “enterprise_naming_standards.nst”

03_how_to_convert_.csv_file_to_.nst_file

1 2