Many BI databases have an organic growth that does not allow for correct modeling, resulting in a huge database without the necessary documentation for the implementation of reports, such as database reverse engineering.

Documentation of physical modeling or database processes is vital to maintaining high performance teams and the company’s competitive advantage . It is done through reverse engineering of the database in order to prevent knowledge about the system from being lost during corporate growth or change of professionals.

As the organization develops, the complexity of the database grows exponentially. The amount of tables becomes countless. Especially when there is a high turnover of the BI team.

With this high turnover, the most valuable asset for companies is lost: knowledge. Without people who know the structure of the tables well, there is no way to have training, maintenance and updating workshops for the database. Therefore, it is essential to document the structure, operations and their respective functions.

So that you don’t have this problem, read on until the end and learn what reverse engineering is, how it works and how to do it properly.

What is reverse engineering?

Reverse engineering is a process of analyzing a system, object or device (database, part of a machine, a machine, etc.) that seeks to understand how it works by analyzing structures, functions and operations. That is, it redoes all the development until its initial project.

A great example of what reverse engineering is is disassembling a machine to analyze its parts and technology and find out how it works.

If traditional engineering works in the planning > analysis > design > construction scheme, the reverse works with construction > design > analysis.

When applied to the database, it is defined as the activity of redoing all the development part using methodologies to return to the initial project from the current physical project.

Database reverse engineering is then a document that maps the properties of the database to provide a physical model that facilitates understanding about it.

How does database reverse engineering work?

The development of data repositories such as Big Data , ODS and Data Mart is normally incremental . That is, its growth is organic because it accompanies the evolution of the company and the various changes over the years, such as changes in strategic planning and turnover of the BI team.

These changes can harm the transmission of knowledge of the structure of the BI data repository. That’s why it’s important to invest in database reverse engineering to ensure that no knowledge is lost.

The complexity of the ETL process, data modeling and the constant improvements in the BI process mean that, many times, this documentation is left aside and the transfer of knowledge occurs through periodic workshops or simply with the old conversation.

However, as we know, BI databases have hundreds of tables, often without Foreign Key and without respecting 3NF. Thus, there is high redundancy of information. In some cases, there are even report summary tables, where the data is already prepared for presentation.

So how do you start this reverse engineering without coming up with a cobweb from your model? See below!

How to reverse engineer a database?

The use of appropriate tools that facilitate reverse engineering is also important in this process. Doing everything by hand and with a fixed deadline makes it humanly impossible to maintain the desired quality.

Therefore, our first tip on how to reverse engineer a BI database is: search the market for appropriate tools for physical modeling and with powerful reverse engineering functionality.

Check out some more tips below to avoid harming the maintenance or future updates of your database.

1. Segregate subjects within your database: this way, you can see the relationship between tables and manage to work with mini-models.
2. Identify the corporate tables: the registration tables that are used in several mini-models, such as tables of customers, products, suppliers, etc.
3. Leverage modeling to identify improvements to your ETL process and document the ETL, including source table, performed transformation, and target table.
4. Lastly, don’t let this documentation die . Updating the database reverse engineering document must be constant. This is what keeps it alive and useful for your team and new members.

Convincing management or board that you should “spend” your time with documentation is another challenge in this process. Most of the time, the work is done for external clients and having time to “fix the house” is practically impossible.

But, then, how to gain the permission of the company’s managers and leaders? A good presentation of current and expected results is essential to getting this endorsement from your management. So prepare a good presentation and good luck!