erwin Data Modeler by Quest Reviews

I have used Erwin for the last 20 to 30 years. As a data architect, I primarily use the data modelling tool. The MODEL MART functionality offers version control.

One of the best aspects of the tool is its reverse engineering capability. I can connect to a database and generate a new model. It creates foreign key relationships, both defined and implied, mapping them in my diagram. The ease of toggling from a logical model to a physical model is a great feature. It makes my job much easier to visualise my data model and relationships. Transitioning from a logical to a physical environment with just a switch is key. If I define my target database, it can generate my DDL for forward engineering.

We use erwin to design conceptual, logical, and physical data models for new projects. We use a Forward Engineering tool to forward engineer data models into new database structures. We use the reverse engineering tool to bring databases into data models and erwin. We also generate HTML reports of the models to share with our customers.

Whenever we do have a new project that requires a new approach, we do try using erwin for it. For example, if we have an XSD message file, then we would try to see if there is a way to get that into erwin for better visibility of the structures that we have to work with.

The product has helped us standardize our data modeling efforts across the enterprise in regards to visuals and naming. We also use the Mart Tool from erwin, which allows us to store our data models in a centralized repository, which gives everyone visibility on what is out there and how it is all related.

We discuss existing and new business requirements with business users, data architects, and application developers to figure out how to capture and visualize concepts in their relationships. One thing we do have standard in all of our models is that we use the information engineering notation. This is standard across our enterprise. We do use a diagram hierarchical layout to help visualize things, especially when we reverse engineer a database, as we want to have some sort of a clear visual layout of things.

erwin Data Modeler does conceptual, logical, and physical database or data structure capture and design, and creates a library of such things.

We use erwin Data Modeler to do all of the levels of analysis that a data architect does. We do conceptual data modeling, which is very high-level and doesn't have columns and tables. It's more concepts that the business described to us in words. We can then use the graphic interface to create boxes that contain descriptions of things and connect things together. It helps us to do a scope statement at the beginning of a project to corral what the area is that the data is going to be using.

Then we do logical data models, which are completely platform-independent. They're only about datasets, the owned attribution, and different key analyses to determine what primary keys we want.

And then we do database designs, which relates to the physical data models. 

We also do reverse-engineering where we are capturing the catalogs of existing systems, or purchased software, or even external vendor datasets. They send us data sets and we can reverse-engineer what they send us, especially the backup snapshots where a vendor in the cloud will send data as a backup restore. So to help the documentation for the reporting team, we do reverse-engineering so that they know what the table and column structure look like, along with sizing, nullability, and keys and constraints.

erwin is on-prem. We have the Workgroup Edition, which means that we don't just have client-side software. We have client-side software that stores the data models back into a database which is on an on-prem server.

When I got to my current company two years ago, it didn't have any collection of its data assets into reporting services. If someone wanted to know where a social security number was in all the databases, they had to download all of the structures and do all of the research. I came in and did a full production-environment, reverse-engineer library. Once I did that using erwin front end, I could help the CCPA team find the PII data by simply doing Workgroup Edition Data Mart reports that crossed all of the environments. 

The cool thing about that is that the erwin models will bring in data from a dozen or two dozen different platforms. But once those models are in your Mart structures, you can do your search, looking for something like names of columns, across all of them. So you could be doing a search across Oracle, PostgreSQL and, because it's in your library, you can look at your assets holistically. For us, we went from zero to 500,000 columns of information. You can do that in Excel or in other ways, but this is a very simple way to do it. And you don't need to be highly-trained and skilled. You could actually bring in a college intern and set them loose with creating those libraries for you. Not needing highly-skilled people is one of the great things about erwin. It's very intuitive and it's not hard to use.

At my current company, we're not using it for much custom work, but in my past, the solution's ability to generate database code from a model for a wide array of data sources absolutely helped to cut development time. If you do your design on paper or in an erwin model before the developers start coding, and you review them to make sure that you've got everything in there, you do much less break-and-fix. If you can have an overview model, even for your Agile developers, and say, "This is where we're going," even if you don't deploy at all, it makes it much simpler. You don't have to drop your structures and recreate and reload your test data because you're pretty confident you've gotten your database design right, before people start coding.

erwin improves your standards because your naming standards and your design standards can all be reviewed much easier. You can make sure that misspellings, for instance, don't get all the way to production, or to the point where you have to live with them because people are already coding against them. You can do so much more QA analysis on your structure before it's deployed, if you're using a model.

We work on different platforms like SQL Sever, Oracle, DB2, Teradata and NOSQL. When we take in requirements, it will be through Excel spreadsheet which is a Mapping Document and this contains information about Source and Target and there mapping and transformation rules. We understand the requirements and start building the conceptual model and then the logical model. When we have these Data Models built in erwin Data Modeler tool, we generate the PDF Data Model diagrams and take it to the team (DBA, BSAs, QA and others)  to explain the model diagram. Once everything is reviewed, then we go on to discuss the physical Data Model. This is one aspect of the requirement from Data Warehouse perspective. 

Other aspect of the requirement can be from the operational systems where the application requirements might come through as DDLs with SQL extension files where we reverse engineer those files and have the models generated within erwin Data Modeler. Some of them, we follow the same templates as they are. But some others, once we reverse-engineer and have that Model within the erwin, we make changes to entity names, table names and capture metadata according to RBC standards. We have standards defined internally, and we follow and apply these standards on the Data Models.

There are different access level permissions given to different users who are Data Modelers, Data Architects, Database Administrators, etc. These permission have read, write and delete options. Some team members only have read-only access to the Data Models while others have more. Therefore, this helps us with security and maintain the Data Models.

The solution’s ability to generate database code from a model for a wide array of data sources cuts development time in only some scenarios for us where we have the data model build into the erwin tool. E.g., I can generate a DDL for the DBAs to create tables on the database. But, in other scenarios, it will be the DBAs who will access the erwin tool with read-only access. They will fetch the DDLs from the models that we created. Once the DDL is generated from the erwin tool, it is all about running the script on the database to create tables and relationships. There are some other scenarios where we might add an index or a default value based on the requirements. 90 percent of the work is being done by the tool.

The solution’s code generation ensures accurate engineering of data sources, as there is no development time. Code doesn't even have to be reviewed. We have been using this solution for so long and all the code which has been generated is accurate with the requirements. Once we generate the DDLs out of the erwin tools, the development team does a quick review of the script line by line. They will just be running the script on the database and looking into other requirements, such as the index. So, there is less effort from development side to create tables or build a database.

We are using it for a very specific use case, and it works pretty well for us. We do all of our database modeling based on this tool, and it is a repository of all data models in our business intelligence ecosystem. The logical representation of our metadata and anything that is created in a database, such as tables, is in it. 

It is an on-prem workgroup. We have a workgroup server that hosts our model.

We utilize it for its cross-database capability and logical representation of the data model. We have recently started to use its collaboration features, and we also use it to define all our relationship constraints and referential integrity within our data model. So, a lot goes out of it.

It has standardized our practices. For example, all customer-related entities and attributes have to follow a certain naming convention. It has helped in standardizing the process of creating our data models so that when we go and explore the data, we can combine them in a way in which we are confident of producing the right results. It has made a lot of difference in terms of naming standards, processes around our metadata, and the schema in which we create a database. We have a proper template to put the information through a well-structured data model. It helps users in getting the maximum value of the information that is available in the BI ecosystem. erwin Data Modeler makes it very simple and easy to navigate our very complex data.

Its visual data models are very good and helpful for overcoming data source complexity and enabling understanding and collaboration around maintenance and usage. We have a complex business environment where we have retail and supply chain space for distribution. There are a lot of cases where we use the models for customer promotions and events and loyalty systems. Different data modelers can do their own subject areas, and then they can bring them together in a workgroup workspace. It has allowed us to collaborate and distribute the data modeling work. Previously, it used to be very single-threaded. Now, a lot of different teams can run their own modelers, and then, later on, integrate them, which is very useful. It is also very useful in the database migration process. You can take a logical model and seamlessly transfer it over to the database. That's very useful as well. 

We use its modeling support for Snowflake Cloud. We don't use it in any special way. We use it the way we use an existing on-prem database. It just needs to follow Snowflake conventions, which it does. We have a standard logical model that can then translate to a physical model for any database we choose, and that's where erwin has been very helpful. We can set those naming standards, and it also does logical to physical translation seamlessly. This support for Snowflake is helpful. We have enough help to port our model from DB2 to Snowflake in terms of model creation. It has proven very helpful that way.

It can create table structures across a wide variety of sources, which is very useful for us. It cuts the development time of our database code quite a bit. Otherwise, we would have to rely on Excel sheets. Currently, our average project size is anywhere from 3,000 to 4,000 hours, and out of that, we spend around 5% on data modeling. If we didn't have this tool, it will take almost twice more time for any project.

We use the Workgroup Edition for sharing data models across the organization. The primary reason we're using the Workgroup Edition over the Standard Edition is the centralized repository of models.

Standard Edition requires individuals to determine how to share data models, whether you share them in a local LAN directory, through email, SharePoint, or Livelink. You have to come up with your own versioning scheme and your method for sharing models. With the Workgroup Edition, because it has the centralized repository with version control embedded in it, it standardizes how your organization does versioning and centralizes all the models at the same time.

The solution's ability to compare and synchronize data sources with data models is fantastic. We use it for that on a regular basis to make sure that changes haven't been made to the database outside of the modeling process. I can take existing databases and reverse engineer them and understand their structure within 15 minutes. If I didn't have Data Modeler it would take hours. It increases our productivity and helps in understanding our legacy application.

The organization that I'm currently with has not historically done data modeling as a discipline. We're using the tool to introduce data modeling into the organization because it is an intuitive tool. There are other modeling products on the market that aren't as intuitive, tools that are currently in use at my company. With this tool, we have an easier ramp-up with our new staff because it is more intuitive.

The solution's support for Snowflake affects our organization's data modeling tremendously because we didn't have a decent tool for the Snowflake product. There are a couple of tools on the market, but they are not good modeling tools. The fact that erwin Data Modeler supports Snowflake is a huge benefit to this organization. The existing procedures couldn't be satisfied with the tooling that my company was using. It's a huge plus because it was difficult for teams that are migrating to Snowflake to adhere to the internal company standards of data modeling, because there were no products that offered Snowflake as one of the solutions.

It also reduces development time because you don't have to write DDL manually anymore. You use the data modeling tool to generate the DDL. It reduces development time by about 10 percent. Workgroup's code generation helps to ensure accurate engineering of data sources, which reduces the number of errors during development. If someone is writing DDL by hand and they don't get the syntax right, they have to figure out what's wrong with it to be able to fix it. It helps you build your table structures more quickly so that you can actually begin development of the business logic in your application layer.

We have a couple of really important use cases for erwin. One of them is that we automate the pull of metadata from the repository itself, so that we have all the model metadata that we can then put into a centralized hub that we can access with other applications. Another reason we pull all the metadata out of the model is to run it through our model validation application, which telsl us if this model is healthy or not and if it meets our standards or not.

The other use case that's really important is managing the abbreviations file that erwin uses to convert logical terms into physical terms. The way that you manage it today within erwin is very manual and you'll go from a spreadsheet, make changes, and upload, et cetera-- but we've created an API application where if we take the main standard file and keep it in the database, make the changes in the database, then we have an application that goes out into the Mart file, deletes the glossary, replaces it with the table from the database. It's all automated at the push of a button. It's things that would take us days to make changes in the standard files and do updates in eight different files.

Data warehousing is the best example of how this product can make a huge difference because it's an integration of a lot of different source systems. You have to be able to visualize how you are going to make the information from sources A, B, and C merge together. It makes it very important.

The ability to automatically generate DDL and have the to do it in different flavors (Teradata DDL or Oracle, et cetera), and to be able to fine-tune the forward engineering file so that it comes out the way your shop likes to see the DDL done is critical. It's soup to nuts from the design all the way to implementation. It's really critical.

We find that its ability to generate database code from a model for a wide array of data sources cuts development time. The ability to create one model in your design phase and then have it generate DDL code for Oracle or Teradata, or whichever environment you need is really nice. It's not only nice but it also saves man-hours of time. You would have to take your design and just type in manually. It has to take days off out of the work.

The code generation ensures accurate engineering of data sources especially because you can tweak it.

Development time is another critical issue. If you had to tweak every single piece of code that comes off the line because there's only a one-size-fits-all solution, then the problem would not be worth anywhere near as much as it is. It has the ability to create a customized forward engineering code that you can use to generate your code for your shop so that it always comes out the way you want it.

We use the erwin Data Modeler tool to document conceptual, logical, and physical data design. Business data models capture the understanding of the data from a business perspective, which can then drive physical design to ensure data is represented and used correctly.

The automated generation of the DDL ensures that the data store looks exactly as the data design. It also ensures that the standards that are governed are followed and implemented successfully.