Vertica and erwin Data Modeler: Technical Exploration

About this Document

The goal of this exploration is to understand the use case of erwin Data Modeler (DM) with Vertica. There are two main use cases of erwin DM in the development life cycle of a Vertica database:

1. Changes to the schema are made to the model first and then forward-engineered to the Vertica database.

2. Changes are made directly to the schema in Vertica and the schema is reverse-engineered into the model in erwin DM. This action will synchronize the schema and the model.

The basic steps to reverse and forward engineer a Vertica database using erwin Data Modeler are described in this document.

erwin Data Modeler Overview

erwin Data Modeler is a data modeling software. A data model is a graphical representation of a database. You can use erwin to forward or reverse engineer your database. Forward engineering refers to designing a data model before building the database. Reverse engineering refers to generating a data model from an existing database. erwin Data Modeler provides tools for collaboration, synchronization, version control, and documentation.

erwin Data Modeler is a 64-bit application available for Windows and uses Generic ODBC to connect to Vertica.

Test Environment

  • erwin Data Modeler Workgroup Edition 2021 R1 (64-bit) Build: 23459

  • Windows Server 2019 Standard

  • Vertica ODBC 11.1.0-0

  • Vertica Analytical Database 11.1.0

  • Red Hat Enterprise Linux 7.3

Installing erwin Data Modeler

  1. Go to https://www.erwin.com/products/erwin-data-modeler/.
  2. Click Start Free Trial.
  3. Fill out the form and click Request Trial.
  4. Download the software from the download link provided to you.
  5. Double click the installer and follow the prompts for installation.

Installing the Vertica ODBC Driver

erwin Data Modeler uses ODBC to connect to your Vertica database. To install the Vertica ODBC driver, follow these steps:

1. Download the latest version of the ODBC client package from the Client Drivers page on the Vertica website.

2. Double-click the installer and follow the prompts to install only the ODBC component. The client package includes other drivers such as ADO.NET. You only need to install the ODBC driver.

Once you have installed Vertica’s ODBC driver in your erwin DM machine, create a 64-bit Data Source Name (DSN) to connect to Vertica. For details on creating a Vertica 64-bit DS see, Setting Up an ODBC DSN

Implementing an erwin Data Model in Vertica - Forward Engineering

You can design a database model using erwin Data Modeler. Once you have finished your design you can implement it in Vertica. erwin Data Modeler generates a SQL script or DDL that you can execute in Vertica to create the database.

Follow these steps to implement a database model in Vertica:

  1. Design a logical/physical diagram of the database you want to implement in Vertica. In this example, we designed a data model called Inventory Analysis based on the VMart sample database. The inventory schema consists of one fact table (inventory_fact) and 3 dimension tables (date_dimension, warehouse_dimension and product_dimension).

  2. Go to Actions > Forward Engineer > Schema Generation.

    The Forward Engineer Schema Generation Wizard opens.

  3. Click Next.

  4. In the Schema Generation Options window, customize the options if necessary and click Next.

  5. Click Next twice.

  6. Select the tables you want to include in the DDL script (Data Definition Language).

  7. Click Next.

  8. Preview the DDL generated based on your design.

  9. Click Save to keep a copy of the script.

    Note There are two options to create your database using the generated DDL script:

    1. Copy and paste this script and execute it directly in VSQL or in a SQL editor such as DB Visualizer, or

    2. Click Generate. erwin Data Modeler connects to Vertica and executes the script for you, as explained in steps 10 to 12.

  10. Click Generate.

    The ODBC Connection window opens.

  11. In the ODBC Connection window, select or enter the following information:

    • Database: ODBC 3.x
    • Authentication: Database Authentication
    • User Name: Your DB user name
    • Password: Your DB user password
    • ODBC Data Source: Select your Vertica 64-bit DSN from the drop-down.

  12. Click Connect. When you click Connect, erwin Data Modeler connects to Vertica and executes the script.

  13. An informational window displays the results of the execution.

  14. Click OK and then click OK again.

  15. You can now view the database objects created by erwin Data Modeler in your Vertica database using VSQL:

Reverse Engineer a Vertica Database Using erwin Data Modeler

You can generate a database design from your database in Vertica using erwin Data Modeler. erwin DM will generate a dimensional or Entity-Relationship schema from the existing tables/views in your database.

Follow these steps to reverse engineer the design from your database:

  1. Launch erwin Data Modeler 2021 R1 (64-bit) by going to Start > erwin > erwin Data Modeler 2021 R1 (64-bit).
  2. In the Welcome screen, click Model Actions > Reverse Engineer.
    The New Model dialog window displays.
  3. In the New Model window, select the following information:
    • Type: Logical/Physical
    • Target Server: For Database, select ODBC and for Version, select 3.x.

  4. Click Next.
    The Reverse Engineer – Set Options window opens.
  5. In the Reverse Engineer – Set Options window, select the following information:
    • Reverse Engineer from: Database.
    • Items to Reverse Engineer: either tables, views, or both.

      Note The Vertica object types that Erwin DM recognizes are tables and views.

    • Tables/Views owned by: Select All to include all tables from all schemas or specify a schema or a list of schemas in Owners text box. For example: public, online_sales.
    • Infer: If your database does not have referencial integrity defined, select Primary Keys and Relationships from Names.

  6. Click Next.
    The ODBC Connection window displays.
  7. In the ODBC Connection window, select or enter the following information:
    • Database: ODBC 3.x
    • Authentication: Database Authentication
    • User Name: Your DB user name.
    • Password: Your DB user password.
    • ODBC Data Source: Select your Vertica 64-bit DSN from the drop-down.

  8. Click Connect.

    The model generation begins. A window with status progress displays. Once the reverse engineering process ends, the erwin Data Modeler Workspace opens.

    You can now examine the diagram generated from your database in Vertica. This is an example of the diagram generated by erwin on the public.Inventory_fact schema in Vertica:

For More Information