UML & SysML modelling languages

Expertise and blog articles on UML, SysML, and Enterprise Architect modelling tool

version francaiseTwitterVideos UMLChannel SparxSystems EA YouTubeLinkedIn
Tuesday, 17 May 2016 13:07

Reverse engineer your Sparx Enterprise Architect project DB schema with its built-in DB Builder

Written by
Rate this item
(8 votes)

reverse database sparx enterprise architect dll title

Data from a Sparx Enterprise Architect modelling project is stored in a database. When one works on a local Sparx EA project, the file used has an EAP extension and is effectively a Jet Engine database that can be opened with Ms Access.

Sparx reverse engineering features on relational databases can be run on an Enterprise Architect project to extract its schema definition such as tables, columns and relations managed by Sparx modelling tool.

This article provides a procedure to achieve this using Enterprise Architect 12.1 built-in DB Builder tool.

  • Create a copy of your local EA project file and rename its eap extension to mdb.
  • Open Enterprise Architect and create the target project where the reverse engineering will be run.
  • Configure an ODBC Ms Access DSN:
    • Open the menu Tools > ODBC Data Sources
    • Add a user data source
    • new odbc access data source
    • Select Microsoft Access Driver (this driver should be available if Access has been installed. If not, try the following link: https://www.microsoft.com/en-US/download/details.aspx?id=13255).
    • new odbc data source access driver
    • Provide a title and select the MDB file defined earlier on.
    • new odbc data source access driver
  • Having the ODBC user data source available, open the EA menu Tools > Database Builder in the target EA project.
  • Create a new data model (right click > New Data Model).

sparx db builder new data model reverse

  • Select Data Model – MSAccess from the list.

sparx db builder model wizard

  • A "DataModel" stereotyped package has been created within the selected package with a predefined tree where tables, queries and views will be stored for each database.

sparx enterprise architect project browser datamodel

  • Right click on the data model > Load.

sparx enterprise db builder load

  • Set up a new connection to the ODBC data source that matches the MDB file (right click on Connections > Add new DB connection).
  • Double click on the connection and select ODBC based database.

sparx enterprise db builder odbc access

  • Open the System data source tab and select the entry created earlier on.

sparxsystems enterprise db builder odbc dsn

  • Right click on the new connection > Set as Active DB Connection.
  • Right click data model root > Import DB schema from ODBC.

sparx enterprise db builder import db schema from odbc

  • Change any option if needed and click on Import.

sparx enterprise db builder import db schema from odbc screen

  • Select all tables and views.

sparx enterprise db builder import tables

  • Tables and views have been imported in the Data Model including column details, primary keys, foreign keys, indexes, relations, etc.
  • Selecting a table from the screen below provides access to its columns list.
    • Note: Sparx EA DB Builder tool provides a dedicated user interface to work on data models. It uses features that have been available for a long time in Sparx Enterprise Architect modelling tool. The DB Builder automatically manages a suitable package tree structure where all the information such as tables is stored and updated accordingly. As a result one can work in a more efficient manner compared with accessing tables properties windows.

sparx enterprise db builder content screen

  • The following Project Browser view illustrates the list of imported tables from a Sparx EA project;

sparx enterprise db builder project browser

  • Imported views include details on the tables used and the associated SQL query:

sparx enterprise db builder views