In Clarity's large corporate client base—namely Banks, Insurance Companies, and State Institutions—we often encounter systems underpinned by highly complex transactional (OLTP) or analytical (DWH) databases. These are frequently legacy-type database layers that have been continuously developed, in some cases for up to 10-15 years, without any major redesign or rethinking.
Over time, up to hundreds of developers may work on these systems, following different development conventions and methodologies, sometimes under very tight deadlines. Since complex database systems are connected to other companion systems through dozens of interfaces, as the volume of data grows, the mapped business logic and, with it, the volume and complexity of ETL processes also continuously increase. The lack of transparency often leads to reliability and performance issues, frequently severely slowing down the pace of implementing business requirements.
In the case of larger developments, it is necessary to have an overview of most of the systems, otherwise the scheduling of development tasks (and thus initiated projects) can be delayed by up to months, due to unplanned development and testing tasks caused by continuously appearing, unknown operational logic.
With appropriate data governance tools, and specifically by applying the toolset of data lineage (data flow analysis), an automatically generated data flow database can significantly speed up and make the design process more accurate, as it can reduce the manual source code interpretation task to a minimum.
Data lineage tools on the market
To solve the above problem, several giant companies have developed data flow analysis and database discovery products; however, these primarily support documentation rather than daily design and development work. Moreover, for marketing and sales reasons, these software products are sold as part of broad product suites, making them not worth implementing solely for data lineage functionality.
The years of R&D
In 2016, Clarity decided to develop a product specifically designed for the data flow analysis of custom-built systems written in Oracle PL/SQL, which can be effectively used to discover and interpret database logic, and also supports automated impact analysis.
This vision was supported by non-refundable European Union funding under the GINOP-2.1.1.-15 framework, allowing the development of scientific foundations to begin in collaboration with the University of Szeged. Our research results were published in the Acta Polytechnica Hungarica journal under the title Capturing Expert Knowledge to Guide Data Flow and Structure Analysis of Large Corporate Databases.
In the second phase of the R&D project, we presented the system to several of our banking and insurance partners, so the first widely available version included numerous features developed in response to our clients' market needs.
Clarity's Data Lineage Tool
Our application implementing the data flow analysis functionality was named DALIA (Database Lineage & Impact Analyzer), which has become capable of automatically discovering Oracle PL/SQL program elements and stored procedures alongside the data structure. The resulting data flow graph from the discovery can be viewed and filtered visually, where data relationships and programmed connections can be analyzed and interpreted based on metrics. Thanks to the impact assessment module, we can also get a picture of the potential consequences and complexity of modifications planned by developers affecting the source code.
Figure 1: Table-level data flow graph
The DALIA system can typically be used to analyze databases of custom-developed data warehouses or OLTP systems operating with a large number of tables and relatively many and/or complex database programs. According to Clarity's experience, an average large enterprise has tens of thousands of tables, hundreds of thousands of columns, and hundreds of thousands of lines of PL/SQL source code in its analytical systems and data warehouses, the review and traversal of which is extremely time-consuming without automated tools – thus DALIA has significant validity in reducing manually performed tasks for similar, rapidly developing large enterprises.
According to our experience, a large enterprise spends hundreds of man-days (FTE) annually on design, assessment, and refactoring alone in connection with the further development of its database systems, of which 25-50% can be saved with the help of automated data flow analysis, i.e., with DALIA!
Areas of Application
In addition to being able to present the relationships implemented in the code, DALIA also calculates a set of indicators (DALIA-index) for individual objects, which assists the work of the expert performing the impact analysis:
It shows during planned modifications or refactoring which objects are affected and how large of a task understanding them is expected to be.
It helps identify parallel existing logics to reduce unnecessary/redundant developments.
Thanks to its historical operation, it shows how new developments and released versions have affected the overall complexity of the system.
By hiding less complex areas, it speeds up the process of interpreting the affected logics and code snippets.
In the case of legal compliance requirements (GDPR, depersonalization), it helps identify the ripple effects of operations to be executed (e.g., deleting client data) at the field level.
Integration
DALIA does not require a direct database connection, as it can perform analyses on simple source code and data structure exports, thereby facilitating acceptance by corporate IT security and Compliance departments.
Analysis processes can even be automated and integrated into development workflows, as DALIA features an easily integrable API layer, allowing it to be used as part of a DevOps pipeline as well.
Privilege Management
DALIA is designed to function properly even in highly sensitive environments, meaning analysis tasks can be performed without a network connection, using local user management.
Of course, it can also be used in traditional scenarios, thanks to AD/LDAP-based authentication and authorization.
Licensing
Licensing of the data lineage toolset is flexible, as it allows pricing based on database size and complexity when analyzing smaller environments, or it can be licensed without limits at the organizational level.
In terms of operation, alongside full local (on-premise) installation and traditional annual software support, a cloud-based (SaaS) approach is also supported, making it cost-effective to apply even for projects lasting just a few months.
Free Analysis
If our data flow analyzer tool has piqued your interest, you can request a free demonstration analysis of one of your own databases using the contact details shown on DALIA's website.
