What is our primary use case?
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.
How has it helped my organization?
We use erwin Data Modeler for all enterprise data warehouse-related projects. It is very vital that the models should be up and running and available to the end-users for their reporting purposes. They need to be able to go through them and to understand what kinds of components and attributes are available. In addition, the kinds of relationships that are built in the data warehouse are visible through erwin DM. It is very important to keep everybody up to the mark and on the same page. We distribute erwin models to all the business users, our business analysts, as well as the developers. It's the first step for us. Before something gets approved we generally don't do any data work. What erwin DM does is critical for us.
erwin DM's support for Snowflake is very helpful from the data modeling perspective and, obviously, the JDBC and native connectivity also helps us in simplifying the push mechanism we have in erwin DM.
What is most valuable?
Primarily, we use erwin for data modeling only, the functionality which is available to do logical models and the physical model. Those are the two areas which we use the most: we use a conceptual model first and the logical model, and then the physical model.
When we do the conceptual data model, we will look at the source and how the objects in the source interact, and that will give us a very clear understanding of how the data is set up in the source environment. The logical model gives developers, as well as the data modelers, an understanding of exactly how each object interacts with the others, whether a one-to-many, many-to-many, many-to-one, etc. The physical model, obviously, helps in executing the data model in Snowflake, on the physical layer.
Compatibility and support for cloud-based databases is very important in our environment because Snowflake is the only database to which we push our physical data structures. So any data modeling tool we use should be compatible with a cloud data warehouse, like Snowflake. It is definitely a very important functionality and feature for us.
What needs improvement?
We are planning to move, in 2021, into their server version, where multiple data modelers can work at the same time and share their models. It has become a pain point to merge the models from individual desktops and get them into a single data model, when multiple data modelers are working on a particular project. It becomes a nightmare for the senior data modeler to bring them together, especially when it comes to recreating them when you want to merge them. That's difficult. So we are looking at the version that will be a server-based model, where the data modelers can bring the data out, they can share, and they can merge their data models with existing data model on the server.
The version we're not using now—the server version—would definitely help us with the pain point when it comes to merging the models. When you have the desktop version, merging the models, two into one, requires more time. But when we go over to the server, the data models can automatically pull and push.
We will have to see what the scalability is like in that version.
Apart from that, the solution seems to be fine.
For how long have I used the solution?
I've been using erwin DM for years, since the early 2000s and onwards. It's a very robust tool for data modeling purposes.
What do I think about the scalability of the solution?
We have five to seven data modelers working on it at any moment in time. We have not seen any scalability issues, slowness, or that it is not supporting that level of use, because it's all desktop-based
When we go into the server model, where the web server is involved, we will have to see. And the dataset storage in the desktop model is also very limited, so I don't think going to the server model is going to impact scalability.
In our company, erwin DM is used only in the data warehouse area at this moment. I don't see any plans, from the management perspective, to extend it. It's mostly for ER diagrams and we will continue to use it in the same way. Depending on the usage, the number of concurrent users might go up a little bit.
How are customer service and technical support?
I have interacted with erwin's technical support lately regarding the server version and they have been very proactive in answering those questions as well as following up with me. They ask if they have resolved the issue or if anything still needs to be done. I'm very happy with erwin's support.
What other advice do I have?
The biggest lesson I have learned from using erwin DM, irrespective of whether it's for Snowflake or not, is that having the model upfront and getting it approved helps in reducing project go-live time. Everybody is on the same page: all the developers, how they interact, how they need to connect the various objects to generate their ETL processes. It also definitely helps business analysts and end-users to understand how to write their Tableau reports. If they want to know where the objects are, how they connect to each other, and whether they are a one-to-one or one-to-many relationship, etc., they can get it out of this solution. It's a very central piece of the development and the delivery process.
We use Talend as our ETL and BI vendor for workload. We don't combine it with erwin DM. Right now, each is used for its own specific need and purpose. erwin DM is mostly for our data modeling purposes, and Talend is for integration purposes.
Overall, erwin DM's support for Snowflake is very good. It's very stable and user-friendly and our data modelers live, day in and day out, on it. No complaints. There is nothing that impacts their performance.
Which deployment model are you using for this solution?