The Seven Use Cases of ER/Studio

by Mar 10, 2020

We have been going back to basics with ER/Studio and making sure we do a great job at what the product sets out to deliver. We have narrowed down to 6 core use cases and a seventh which we will talk about lots more in future bogs.

These use cases are as follows:

  1. Design a new database
  2. Understand a database
  3. Consolidate some databases
  4. Migrate a database to a new technology
  5. Understand a data warehouse
  6. Design a data warehouse
  7. Govern our data

Let's talk through them.

Design a new database

In this use case the mission is for a Data Architect who wants to take requirements from their customer in the business community in business language and then produce a design and deploy it to a live database. Our approach is to craft a model starting with Business Data Objects in ER/Studio, then expanding to a logical model adding Entities, Attributes and Relationships. We can document additional requirements using additional user defined properties. We then apply naming conventions and generate a physical data model specific to a database product which can be deployed as DDL code to a database. As requirements change, we can understand those changes and deploy them to the same database. This is a fundamental use case for our tool.

Understand a Database

This is our second fundamental use case. We have a data asset, usually a database, and we need to be able to document it to understand its contents. We have three approaches to this:

  1. As a Localised Project
  2. As Part of a Broader Data Modeling Initiative
  3. As Part of a Data Governance Initiative

In all three approaches we have two super models: one of the information that business users can easily understand, one as a faithful snapshot of the data asset in technical terms.

As a localized project we will look at the database in isolation. We reverse engineer direct form the database creating a physical model in technical terms. We use ER/Studio to help us then build a logical model of the data asset creating expanded logical names that make sense to the business community which can be published.

As part of a broader data modeling initiative we will maintain a single view of the information of the organization as a corporate logical data model or canonical model. We can then reverse engineer data assets and map them to that single logical model.

A part of a broader data governance initiative the information model of the organization is held in a business glossary. We reverse engineer data assets then map the physical models to business terms int he Business Glossary.

Nowadays we find that all three approaches may need to be supported in the same model. ER/Studio can help you to do this.

Consolidate some databases

A common scenario is where we have two or mode data assets that contain similar information. maybe we have a CRM system and acquire another organization. We want to combine the two databases into one. ER/Studio can help with this process, documenting both databases in logical terms then using our compare/merge tool to identify common information and merge into a new design which can then be deployed. The last step here is to design source-target mappings for the database which will be absorbed into the target.

Migrate a database to a new technology

In the new world of cloud databases we see this more and more often. This might be a simple 'lift and shift' where we simply move from one technology to another. Or will involve some analysis along the way. Either way, ER/Studio can help. We will reverse engineer the source, create an inventory of the database with stored procedures, functions, users etc and then the schema with physical and a logical view to understand the content and structure of the database. From the logical model we create a new physical model employing a series of data type mappings to ensure the consistency of the database in the new product from the old.

Understand a data warehouse

A similar use case to the understand a database but extends the base use case to include warehousing schema such as star schema. Also we will understand data movements from operational data assets into the data warehouse through ETL operations.

Design a data warehouse

Again, similar to designing a database except that we will utilise warehouse schemas and also design source target mappings for data movements such as ETL.

Govern our data

This is a big one. We are very much being asked by our clients to help integrate data architecture with broader data governance initiatives. Either Data Architects are being asked to deliver a data governance program or to integrate with one. ER/Studio can help with this. For any Data Governance initiative there will be two core components:

  1. A Business Glossary – defining the information model of the organization as a model of business terms used by the organization. 
  2. A Data Catalog – effectively an asset register of assets that contain data. This will mainly be database systems but may also contain files and documents.

ER/Studio allows you to build and manage these components but also to integrate it with your traditional data models to create a unified understanding of data. Data Architect can reverse engineer a wide range of data asset types from semi structured data like JSON or flat files through to Data Lake structures using the core import tools or the MetaWizard bridges. The built in Glossary tool within Team Server allows Data Stewards to build and manage Business Terms which are used to classify logical and physical data models to answer those important questions of what data do we have and where is it?