Many people who know me know that I have a certain fondness for tools that can help to make our jobs easier. I agree that a fool with a tool is still a fool, but I would argue that craftsman with the right tool can be an even better craftsman. In essence, the problem isn’t the tool,it is the person wielding the tool. So let’s not be too quick to paint the use of all tools in a negative light.
Agile Data Modeling
It has always been a struggle to determine how we can manage our Data Models and Databases in an Agile way. In the past, the Data Modeling tools were limited in the functionality they provided that allowed both collaboration and versioning. Recently the Data Modeling tools have become much better at providing that functionality, but most of the Data Modeling tools I have seen have still provided that collaboration and versioning functionality in a proprietary way that was created by the Data Modeling vendor.
This has made the Data Model and Database operate as second class citizens on Agile projects. Usually separate processes need to be created to determine how we will integrate the Data Model and database into Continuous Integration environments. I believe that some of the resistance of Database Modelers and Database Administrators to implement Agile for Data Design activities is due to the fact that the tools at their disposal have placed them at an Agile disadvantage. Due to this the Data Modelers will prefer the illusion of a more stable Data Model so that they are potentially limiting the number of SQL change scripts they need to generate. (and thereby limiting their technical debt) SQL Change Scripts still seem to be the primary way that people roll out changes to their databases. It is not uncommon for projects to have a huge number of these scripts that then themselves need to be maintained. I have seen a wide variety of ways people use to generate these scripts that vary from a Data Modeling Tool generating them to having a DBA write them from scratch.
Having a large backlog of these scripts and having them be generated in such a way is a huge hinderance to being Agile and accepting change. Having to then try to synchronize these scripts in alignment with check-in versions can be beyond frustrating. And I don’t even want to talk about having to maintain all of these scripts and manually checking them in.
Agile Data Warehouse
For a recent project, we were tasked with creating a Data Warehouse and we needed to create the Data Warehouse in an Agile way. We needed to embrace Agility for two reasons:
- To deliver the maximum value to the client and deliver the best design that meets actual business needs
- To address the reality that we will be required to accept changes throughout the duration of the projects as sub-projects are completed
After reviewing Data Modeling tools that I have used in the past, I was still struggling with how we could develop a process that would allow us to easily adopt Agile practices for our Data Models and databases that would allow the Data Warehouse to be run as Agile as other projects.
Oracle SQL Developer Data Modeler
I did some initial investigation of what Data Modeling tools are now available. My objectives were to find an affordable tool that could be used to do the Data Modeling and that all developers could use to view the Data Models and collaborate on them. Most of the tools seemed to fall into two camps:
- Affordable Data Modeling tools that provided good collaboration but limited Data Modeling and integrated versioning functionality
- Expensive Data Modeling tools that provided good Data Modeling functionality but limited collaboration and integrated versioning functionality
Then on a list that reviewed the best Data Modeling tools, there was mention of Oracle SQL Developer Data Modeler. It was stated that it was a robust Data Modeling tool that was free. This was certainly something that needed further investigation.
Rarely does a new tool present such functionality that you require time to determine how to use it.
The functionality that was provided with Oracle SQL Developer Data Modeler absolutely changed our project. We now had the ability to be a first class Agile citizen with the rest of the Agile projects and be able to adapt to change easily. We would be able to roll out changes easily and efficiently as new requirements became apparent.
Oracle SQL Developer Data Modeler provided the following functionality:
- Full, integrated functionality with Subversion. The Data Models can be checked in and versioned along with the application code.
- Ability to define a domain library, naming standards, and glossary to ensure a consistent model
- Ability to apply design rules to a model to ensure Data Modeling standards are being followed
- Ability to model Logical, Relational, Dimensional, and Physical Models
- Ability to store DML to populate data along with each table definition. (and determine when they should be executed – at the start/before object creation/after object creation/at the end)
- Use of Barker notation. (Pet Peeve!)
- Extensive functionality to generate different types of models and compare different types of models
- Full versioning functionality to report on the differences between the versions and what changes will be committed on check-in.
- Robust Data Modeling functionality to create Data Models and create physical databases.
Did I mention free so all the developers could use it to reviews the models and make requests?
Although we still need to use Database Re-factoring practices to adapt to changes, the versioning and collaboration features of Oracle SQL Developer Data Modeler did cause us to pause and determine how we could best use its functionality. In fact, it caused us to define a process for how we could efficiently roll out Re-factoring changes in an automated way as Oracle SQL Developer Data Modeler now made the Re-factoring manageable.
If you are looking for an Agile Data Modeling tool, I would highly recommend it!
Did I mention it is free?
And the support for the product from Oracle is top-notch. Questions I have had have been answered same-day..