SqlDBM is an online, collaborative data modeling and diagramming tool. It is used by thousands of companies worldwide, providing them with clarity and efficiency in visualizing and developing their data models. While you can get an idea of how it achieves this by looking through the features and highlights on the SqlDBM website, walking through a practical use case could demonstrate its full potential.
Yew Inc. is a completely fictional yet, data-driven company. They have an on-premise enterprise database application that drives their business and a data warehouse to track their key performance metrics. Yew Inc. has grown its business steadily over recent years and has decided that it was time to start using SqlDBM to maintain and manage the ever-increasing complexity of their data layer.
So, what can SqlDBM do for Yew?
Due to Yew’s rapid growth and success in recent years, the underlying database which powers its business has also grown in complexity. Less than a year ago, an effort was made to manually map out the DB landscape, but the static diagrams have not been maintained and are no longer representative of the live environment. Lacking up-to-date documentation, knowledge silos are starting to form within the team.
The senior DB architect, Marta, tasked with maintaining and expanding the system’s functionality, instead finds herself spending inordinate amounts of time answering questions through email and bridging knowledge gaps among her team members.
Within minutes of registering with SqlDBM, Marta heads to the “Reverse Engineer” pane, and with a few clicks, imports her entire productive database. SqlDBM automatically reads and imports the DDL — never accessing the underlying data or even storing login credentials.
Yew’s entire database is now visually laid out in an ER diagram, complete with logical relationships detected through existing primary and foreign key relationships.
For a DB architect, the “Forward Engineer” feature can be just as important, especially for maintenance at a global level. For example, Marta has been putting off the tedious task of standardizing the naming conventions in Yew’s ever-expanding database. Despite having sent out countless memos with naming guidelines to the team, such details often get overlooked under tight deadlines. Marta will now have to scrutinize each table and write the script to alter the columns by hand.
Or she can use SqlDBM’s “Naming Conventions” feature!
All it takes is one click to standardize her entire database to the naming convention of her choice and another click to generate the corresponding ALTER SQL. Furthermore, SqlDBM can automatically enforce the chosen conventions going forward — no more memos, no more policing.
The development team also realizes the immense utility of marrying the insight of interactive ER diagrams to “Forward Engineer” functionality. Instead of tabbing between diagrams and a SQL editor, the team can now apply the changes directly through a responsive GUI and have SqlDBM generate the corresponding CREATE and ALTER scripts. No more time spent on formatting. No more syntax errors. No more coding!
One of the developers, Dave, has taken a particular interest in SqlDBM. Dave joined Yew Inc. only two weeks ago and is eager to become self-sufficient. However, he has felt the burden of the outdated (and in some cases, non-existent) documentation most acutely. As a new hire, Dave feels sheepish about constantly asking his coworkers about the name or location of this or that column or entity.
Using SqlDBM’s “Database Explorer,” Dave can visualize and search for anything in the database and see where it fits into the overall architecture. Dave no longer relies on Marta for help — instead, he is now answering some of the user questions which used to go directly to her.
As a project manager at Yew Inc., Miriam is responsible for making sure that user requirements are captured and understood by the development team. However, once work has begun and until the change is released for testing, Miriam often feels blind as to whether all the key concepts have been fully understood. When a detail is missed, Miriam wishes that she could have caught it earlier in the development cycle, thereby avoiding the unnecessary and time-consuming rework.
As soon as Miriam hears that the dev team has begun using SqlDBM, she immediately sees its potential for her own work and asks for a user to be created. Using SqlDBM, she is able to navigate directly to the tables she is concerned with and review the changes as they happen — highlighting changes in or between environments.
Previously, Miriam would have had to manually scan through raw SQL in the code repository’s change logs to get this kind of detail — a process which was often so time-consuming as to make it impractical. Now, it’s just a click away on the “Compare Revisions” pane.
Just as Miriam suspected, several important attributes have not been included in the new entity being created. The ability to stay ahead of the release and detect issues before they get deployed is what makes an effective project manager. Thanks to SqlDBM, Miriam feels like she is finally able to live up to that standard.
Miriam soon realizes that the same tool she used to detect the issue can help her communicate with the rest of the team to get it resolved. She leaves a note with the details of what needs to be changed right on top of the affected table and tags the developer assigned to this task.
Knowing that SqlDBM will automatically alert her once the change is made, Miriam now has one less thing to worry about.
If SqlDBM can give Miriam visibility over her own projects, can’t it do the same for other projects that she depends on? Whenever her projects depend on another team’s changes, she can now track them just as easily with SqlDBM instead of having to chase other PMs for updates. Fewer emails to write and fewer meetings to attend means more productivity for everyone.
As companies expand, increased technical complexity and growing team sizes bring their own collateral challenges. These challenges need to be addressed quickly before they escalate into serious problems that can no longer be ignored.
Trevor is a senior member of the Business Intelligence team at Yew and has often felt frustrated trying to build a data warehouse on top of an application that grows faster than its documentation. Having heard about SqlDBM, Trevor realizes that opportunity lurks where responsibility has been abdicated.
Trevor asks for access to SqlDBM and promptly heads for the “Database documentation” pane. Thanks to his BI experience, he can add functional definitions for many of the database tables and columns. For the concepts he is unsure about, Trevor exports the existing dictionary to excel and emails it to the relevant business teams. As the replies come in, Trevor is able to import them back into SqlDBM with just one click!
Like that, Yew’s corporate data dictionary is born.
With SqlDBM, Yew Inc. went from having scattered knowledge silos and outdated documents to having a single source of truth for its business concepts.
As more departments begin to take advantage of SqlDBM’s various functions, it becomes obvious to everyone — from the dev team to BI — that its utility grows exponentially. Having a data dictionary integrated into the ER diagram gives the dev team much-needed insight into how the data is used, just as it gives the BI team and business users a contextual reference for where their data comes from and how it ties together.
Although SqlDBM makes maintaining the data dictionary trivially easy, Trevor realizes that he needs help tracking down the more complex business concepts and unifying the competing sources of master data that SqlDBM’s diagrams have helped him uncover. The management at Yew Inc. has caught on to the value of centralized data governance and decides to formally create a Data Governance team to help Trevor carry on his initiative.
Business as Usual
A BI team stands at the crossroads of a company’s raw data and conceptual business logic. As one of Yew’s BI professionals, Amit is heavily dependent on the dev team to help identify and extract data and business users to help turn that data into meaningful insights. Having heard that Yew’s dev and governance teams have adopted SqlDBM, Amit and the rest of BI decides to jump on board.
For the first time, Amit sees a complete and up-to-date visual representation of Yew’s underlying database, further enhanced by business logic definitions maintained in the Data Dictionary. However, unlike the dev team that maintains the entire application landscape, BI creates function-specific data marts that are often independent and unrelated.
While the dev team derives value from seeing all their tables in one diagram, BI benefits from compartmentalizing their models and working with them individually. Luckily, SqlDBM provides a solution that will make both teams happy: “subject areas.”
Subject areas make it possible to separate and organize diagrams within SqlDBM’s “Diagrams” pane while maintaining a unified view in other panes like “Database Explorer” and “Database documentation,” where it makes sense to do so.
Using the “Reverse Engineer” functionality, Amit can pull in all the existing BI data marts and satellite models into neatly classified diagrams and review them individually — greatly reducing the noise and complexity of his technical analyses.
As Yew’s business continues to grow, Vernon, the CTO, begins to feel the constraints of its on-premise hardware. He’s considering a possible move to a cloud-based database but realizes what such a migration would cost in terms of time and effort.
So, Vernon sits down with Igor, the BI architect, and asks him to allocate a few team-members to begin analyzing their database and estimating the re-work required to adapt the tables and views to the format supported by the leading cloud vendors.
“Sorry, I know it’s going to be a tedious few weeks, but, at this rate we’ll outgrow our server capacity by the end of the year.” Vernon doesn’t notice the smirk on Igor’s face.
Igor was quick to embrace SqlDBM to help him organize and visualize the various schemas and data marts that his team maintains. Then, something else caught Igor’s attention: SqlDBM’s “Convert Project To…” functionality.
Yes, SqlDBM can automatically convert all database objects from one SQL provider to another, mapping compatible data types and functions to supported formats. Once converted, the familiar “Forward Engineer” functionality will generate the entire database DDL, ready to be imported natively into the target SQL environment. Best of all, Igor sees that SqlDBM’s database conversion already supports the leading cloud providers (Snowflake, Azure, Redshift, and more coming soon).
For a moment, Igor considers taking a three week holiday, then surprising Vernon with not just an estimate but the full database DDL in the cloud vendor’s native SQL format. But he decides to play it straight and just gives Vernon the good news.
Analysts and Business Users
A major contributor to Yew’s success has been its ability to provide the right tools and the right data for its business users to empower them to make informed decisions. A tight coupling of functional and technical experts has ensured that Yew Inc. was constantly extracting true insights from the massive streams of pure information that they were generating.
When the analysts heard about a graphical tool that could bring them closer to the source of their upstream information, they immediately saw its potential to help them better understand key concepts and make informed requests to the dev and BI teams.
Especially eager to take advantage of this opportunity is Evelyn. She is one of the strategy team’s most knowledgeable analysts, despite not having a technical background.
The fact that SqlDBM provides multiple visions of the data model (Logical, Key-based, Table-based, descriptive, etc.) gives people like Evelyn a way to explore and interact with the underlying architecture at whatever level they feel comfortable. In the past, Evelyn felt like she was missing important details, often getting lost in the source tables and their technical nomenclature.
Thankfully, the Governance team has torn down this barrier by maintaining the functional names and descriptions in SqlDBM’s “Data Dictionary”.
SqlDBM takes this one step further by allowing Evelyn to search by name, description, or any meta-data-related detail all from one place. Never before have the business users been able to discover attributes and metrics of interest without tracking down and interrogating employees from other departments.
Now that everyone at Yew is aligned on their data sources and functional concepts, Evelyn finds that conversations related to strategy and new initiatives are smoother and more direct.
There are good consultants, and there are bad consultants, but unfortunately, there are no cheap consultants. Leonard is a freelance professional who signed on to assist the BI team on a short-term project. This is his first time collaborating with Yew, so it’s too early to say which category he falls into, but his hourly rate certainly confirms the old saying.
The BI team is painfully aware of the burden posed by Yew’s outdated documentation and lack of a centralized knowledge repository. Consultants on previous projects have spent upwards of a week tracking down relevant technical information — eating up both consultant and staff time.
Yew was effectively paying senior developer rates for junior-level documentation work.
With the architecture diagrams and data dictionary maintained in SqlDBM, this onboarding process has been dramatically shortened. In fact, Leonard manages to get up and running in a single afternoon. Not only does he find all the information in one centralized knowledge repository — saving countless meetings and emails — he continues using SqlDBM just like the rest of BI does to complete his modeling work.
Raul is a sophomore enrolled in a computer science program at his state university. He has had to create ER diagrams in previous courses — relying on trial software, often, with limited features — but he spent the summer interning at Yew Inc. and got to see SqlDBM in action.
The SqlDBM team is committed to helping instill best-practices and foster technical literacy for future generations of engineers. After all, students like Raul will become the future team-members for Yew Inc., and companies like it. This is why we have made SqlDBM free for anyone enrolled in a course using SqlDBM.
The same communication features which make SqlDBM so versatile in a team setting can be used for classroom collaboration for projects and assignments. As more and more classrooms go virtual, a teacher’s ability to comment and review students’ work remotely has never been more vital.
Instructions on how to take advantage of this opportunity are on our website.
As companies grow, it makes sense to invest in tools to automate repetitive and manual tasks and drive that growth even further. As new challenges emerge, a small group’s seamless workflows become pain points in a larger organization if left unchecked.
Since each organization is different, it is impossible to describe how a feature-packed and ever-expanding tool like SqlDBM can help it achieve its individual goals.
We have seen what SqlDBM did for Yew Inc.: foster collaboration, automate manual tasks, promote data stewardship, and reduce costs through time-saving measures.
The real question is, what will SqlDBM do for you?