Consolidation is a technique with a history of proven benefits in many aspects of business and personal affairs. Large corporations as well as many individuals have taken advantage of the financial savings that can be obtained by consolidating their debt. Consolidation is a strategy that is often used to corner a market by merging multiple companies into one large entity. One of the reasons that consolidation is considered is to eliminate the inefficiencies associated with performing multiple iterations of similar activities.
Database professionals are often tasked with maximizing the efficiency of their systems. It makes sense that if two databases have similar information, they may be able to be consolidated. This can eliminate the cost and complexity of maintaining additional systems and may also result in the more effective use of the consolidated data resources.
Using a Database Tool to Facilitate Consolidation
IDERA’s ER/Studio Data Architect can prove to be a valuable tool for teams who want to consolidate some of their data assets. The application can be used throughout the process of understanding, documenting, and combining two databases into a single entity. Following is an overview of the steps data architects need to take using this tool to consolidate two databases that contain similar, though not identical, information.
The source database is the new one that may have been introduced into the environment by a merger. The target database is an existing data asset that will end up being merged with the source and store the consolidated information in a newly created database.
- Reverse engineer the source database – The specific tables and objects that will be extracted from the source database need to be identified and selected. Once this is done, a logical and physical model of the database is created and displayed. You can apply defined naming standards to clean up the logical model so it conforms to enterprise standards. This puts the source data in better shape to be merged with the existing database and allows the logical model to accurately document the resource.
- The next step in the consolidation process is to compare the logical models of the source and target database. Many setting options allow you to control which database objects are included in the comparison. Using the compare and merge functions of ER/Studio, data object attributes can be displayed in fine detail so choices can be made regarding how objects will be matched in the two databases. When you have made your choices, a report can be generated that shows the differences in the two models to be merged. This report allows you to see the additions that will be made to the target database and enables you to share this information with other interested parties such as the development team.
- Once the logical models are merged, you have a new logical model formed from the two original data assets. Once the proposed changes have been approved, ER/Studio lets you generate the new physical model. You can view the metadata of each object in the physical model and customize the entities as needed. Once you are satisfied with the physical model, it time to create the new merged database. This is accomplished with a few clicks in ER/Studio.
- Designing the data movement is the final step, and there are various methods of performing this activity available in the tool. During this step, you can further document the merged database as you combine the physical models. You can create data movement rules that influence how specific objects are moved. This feature allows you to fine-tune the final format of column and table data.
A Guide to Using ER/Studio Data Architect for Consolidation
An illustrative IDERA video that details the use of ER/Studio Data Architect in performing a database consolidation is available for your viewing pleasure. It guides the viewer through the steps outlined above and provides copious screenshots and hands-on demonstrations of the tool’s features. The video is just under an hour long which is time well-spent when faced with consolidating databases. The application supports a wide range of relational and NoSQL database platforms. Give it a look and test drive ER/Studio Data Architect for free to see how it can help your team be more productive and successfully consolidate data assets.