How do you or your organization use this solution?
Please share with us so that your peers can learn from your experiences.
We use erwin DM as a data modeling tool. All projects in the data warehouse area go through the erwin model first and get reviewed and get approved. That's part of the project life cycle. And then we exude the scripts out of DM into Snowflake, which is our target database. Any changes that happen after that also go through erwin and we then make a master copy of the erwin model. Our solution architecture for projects that involve erwin DM and Snowflake is an on-prem Data Modeler desktop version, and we have a SQL database behind it and that's where the models are stored. In terms of erwin Data Modeler, Snowflake is the only database we're using. We are not utilizing a complete round-trip from DM for Snowflake. We are only doing one side of it. We are not doing reverse-engineering. We only go from the data model to the physical layer.
We use it in order to create models, do some reverse engineering in the case of existing databases, and for comparing models, e.g., what is in the design vs reality.
The use case was normally to update data model designs for transaction processing systems and data warehouse systems. Part of our group also was doing data deployment, though I personally didn't do it. The work I did was mostly for the online transaction systems and for external file designs. I didn't use it for data sources. I used the solution for generation of code for the target in the database. Therefore, I went from the model to the database by generating the DDL code out of erwin. We had it on-premise. There was a local database server on SQL, then we each had a client that we install on our machines.
My previous employer's use case was around data warehousing. We used it to house our models and data dictionaries. We didn't do anything with BPM, etc. The company that I left prior to coming to my current company had just bought erwin EDGE. Therefore, I was helping to see how we could leverage the integration between erwin Mapping Manager and erwin Data Modeler, so we could forward engineer our models and source port mappings, then mapping our data dictionary into our business definitions. We didn't use it to capture our sources. It was more target specific. We would just model and forward engineer our targets, then we used DM to manage source targets in Excel. Only when the company first got erwin EDGE did we start to look at leveraging erwin Mapping Manager to manage source targets, but that was still a POC. As far as early DM source specific, we didn't do anything with that. It was always targeted.
When I work from home, my use case for erwin is for when I get a request for a database upgrade. Usually, the request comes in with a whole bunch of tables and names so I'll go into the DM and I'll start building out what they're asking for. Once we actually get them to be able to view it and understand it, then we'll go back and forth with the developers and the requesters to make sure that it's exactly what they're looking for. We'll spend a few days making sure everything looks correct. Once that's finished, I'll send it out. Unfortunately, I can't do a PDF straight from erwin so I'll copy everything into Word and then save my Word as a PDF. With that PDF, I'll be able to send it off to all the stakeholders, not just the developers and the requesters, so that everybody can see it, even the ones that don't have erwin itself. My office use case is pretty much the same, except with the office, we add in Model Mart. We have our entire network, all the databases, and everything in Model Mart and it's over 1,500 different tables, relationships, attributes, and things like that. It's a really large model. Then, we break down that model into individual subject areas and we work through those. We go back to any new requests, we'll build them in Data Modeler and we'll go back and forth with the requesters, making sure everything looks like what they're expecting it to. They'll usually just send us either a spreadsheet of names and data types and then we build from there.
I was part of a standards organization and we built a data model that is a standard data model for use in retail. That data model is now been released in version 7.3 and it is implemented all over the world. We don't implement the model, we've built the logical model and then the companies build their own physical model from there. erwin is a retail data model, which means that it handles the operational side of retail, which means there are somewhere around 8,000 attributes in it. It has got around 10 groupings of things. We have a grouping on transactions and there are all kinds of transactions that can occur in retail. The whole customer life cycle is covered in the inventory, items, and all that. The use case is for retail operations. It's massive. There are hundreds of use cases in this.
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.
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.
I am responsible for both a combination of documenting our existing data models and using erwin Data Modeler as a primary visual design tool to design and document data models that we implement for our production services. My primary role is to document our databases using erwin to work with people and ensure that there is logically referential integrity from the perspective of the data models. I also generate the data definition language (DDL) changes necessary to maintain our data models and databases up to our client requirements in terms of their data, analytics, and whatever data manipulation that they want to do. I use erwin a lot. It is either installed locally or accessed through a server, depending on where I have been. I have had either a single application license or pooled license that I would acquire when I open up erwin from a server.
I'm an application developer with a fair amount of database background so I mostly use the tool to do physical modeling to support our application development. I'm a firm believer in not just adding columns to a table but to actually think about it, put together an Erwin model, and look at the relationships. I used to like to generate the model and generate changes all through the tool but being honest, one of my biggest frustrations with Erwin is that it's very difficult to forward engineer and keep things in sync. It used to be so easy and now it's very difficult. It's very frustrating to use this tool for that. We use it for data modeling but they also do a lot of logical modeling and architecture, and we also use the naming standards capability to force corporate standards across the models.
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 use cases are for our enterprise data warehouse where we have an enterprise model being maintained and we have about 11 business-capability models being maintained. Examples of business capabilities would be finance, human resources, supply-chain, sales and marketing, and procurement. We maintain business domain models in addition to the enterprise model. We're on-premise, a virtualized data center. We're running this as client-server, the client being PC-driven and the back-end for the erwin Mart is virtualized Windows Servers.
We use it for our conceptual business-data model, for logical data modeling, and to generate physical database schemas. We also create dimensional modeling models.
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.
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.
The whole purpose of the erwin tool is for the designing of databases. We use it for our conceptual, logical, and physical database modeling.
Let the community know what you think. Share your opinions now!