What changes in your reports after an ERP update

Data Lineage / Metadata Management / Enterprise Architecture

Why one change in ERP can affect dozens of corporate reports

A modern enterprise rarely uses a single information system. Financial operations are processed in ERP, customer data is stored in CRM, documents go through an electronic document management system, and corporate analytics uses a Data Warehouse and specialized information marts.

Numerous integration processes operate between these components. For example, sales information comes from ERP to the staging area, goes through ETL, is matched with the corporate product catalog, and loaded into the DWH.

Based on the results, management dashboards, financial reports, and forecasts are created.

As long as the architecture remains unchanged, this model can work for years.

However, information systems are constantly evolving.

Suppose an enterprise decides to change the order_status field in the ERP. Previously, the status ‘Completed’ meant order completion. After the ERP update, it will mean shipment completion, and financial confirmation will be reflected by a separate characteristic.

Technically, the field structure can remain unchanged.

ETL will continue to work.

However, corporate reporting may start misidentifying completed sales.

Another scenario is renaming a column or changing a data type. Here, the consequences can be more obvious: an SQL query will stop executing, and the nightly load will fail.

In any case, the enterprise must determine which information resources depend on the changed object.

This is precisely the task that Impact Analysis solves – analyzing the consequences of changes, and Data Lineage provides the informational basis for such analysis.

The main idea is to consider corporate data not as independent tables, but as an interconnected system of sources, transformations, reference data, and business metrics.

What is Data Lineage

Data Lineage is a description of the origin of information and its transformation between systems.

For example, a corporate net revenue metric can be formed based on several ERP tables. During ETL, rules for selecting confirmed documents, currency conversions, and customer matching from MDM are applied.

After this, the result goes to the analytical data mart.

Lineage allows displaying such dependencies.

ERP - Sales Documents

MDM - Customers and Products

ETL / ELT

Selection · Matching · Currency Conversions

DWH / FactSales

Corporate Fact Table

Finance Data Mart

Approved Revenue Methodology

BI — Financial Report

Simplified Data Lineage. In a real architecture, relationships may include additional sources, multiple transformation stages, and historical versions.

Lineage can perform two main functions.

The first is to explain where a specific analytical result comes from.

The second is to help determine what a change in the source system will affect.

For example, if the ERP table structure changes, all dependent ETL processes and reports can be identified.

This is especially important during major upgrades.

Three Levels of Data Lineage

Not every company immediately requires detailed field-level Lineage.

It is advisable to distinguish several levels.

1. System-Level Lineage

Shows the interaction between information systems.

For example:

ERP → ETL → DWH → BI

This description is useful for general architectural analysis.

However, it does not allow us to determine which specific SQL tables are used.

2. Table-Level Lineage

Shows dependencies between tables and data sets.

For example:

ERP.InvoiceLines → STG.InvoiceLines → DWH.FactSales

This level is useful for analyzing structural changes.

If an ERP table is planned for replacement, corresponding dependent objects can be identified.

3. Column-Level Lineage

Allows tracing the origin of specific attributes. For example, the net_revenue metric might use document total, discount, returns, and currency exchange rate.

This level is particularly important for financial metrics.

However, its automation is more complex.

For example, SQL might use nested subqueries, temporary tables, dynamically generated queries, and user-defined functions.

Not every metadata tool is capable of correctly reproducing all relevant semantics.

Therefore, for critical business metrics, automatic lineage needs to be supplemented with approved rule descriptions.

Technical lineage does not equal business meaning

Let’s consider an SQL query:

SELECT
    customer_id,
    SUM(amount) AS total_amount
FROM sales_documents
WHERE status = 'POSTED'
GROUP BY customer_id;

Technical analysis allows us to establish that total_amount depends on the amount field of the sales_documents table.

However, this is not enough for a business explanation. For example, does amount include VAT?

Are returns taken into account? What does the POSTED status mean?

What date determines the reporting period?

A Business Glossary is required for the answer.

Therefore, a complete corporate Lineage must combine technical structure and business metadata.

This is especially important during ERP modernization.

Changing the status value may not violate SQL syntax, but it can change the financial semantics.

Therefore, Impact Analysis should not be limited to searching for table and field names.

Apache Atlas 1.1: A Graph Model of Enterprise Metadata

As of February 2019, one of the available open-source metadata management tools is Apache Atlas 1.1.0. This version was released on September 17, 2018.

Newer major releases of Atlas, 2.0 and 1.2, are not yet available: they will be released in May and June 2019, respectively.

Apache Atlas allows for the description of information objects, their attributes, classifications, and relationships.

For enterprise architecture, this creates an opportunity to represent information resources as a graph.

Entities

Individual tables, files, or other resources can be represented by corresponding entities.

Relationships

Relationships allow describing dependencies between information objects.

For example, a process can receive a source table and generate another table.

Classifications

Appropriate classifications can be defined for resources.

For example, a certain dataset can be marked as containing personal data.

Business Glossary

Allows linking technical resources with business concepts.

Lineage

Helps visualize information dependencies.

However, it is important to understand the limitations.

Atlas 1.1 is not a universal automatic analyzer for any SQL, Python, SSIS, or ERP. For some sources, there are corresponding integration mechanisms.

For others, it is necessary to develop additional connectors or metadata transfer procedures.

Therefore, before implementation, it is necessary to determine which systems are supported and what level of Lineage can realistically be obtained.

How to automatically collect metadata

One of the automation directions is to obtain technical metadata directly from corporate systems. For example, SQL Server contains system views that allow analyzing table structures, views, and defined dependencies.

PostgreSQL has its own system catalogs.

Apache Hive uses Metastore to describe tables and corresponding structures.

Database Metadata

From the DBMS, you can get a list of tables, columns, types, keys, and other characteristics.

ETL Metadata

For supported integration platforms, you can get descriptions of sources and target sets.

Execution Metadata

Orchestration systems can store information about process execution.

Business Metadata

Require additional definitions and approval by responsible persons.

For automation, it is important to establish uniform resource identification rules. For example, a table can be represented by a full name that includes the server, database, schema, and name.

If only the name Customers is used, different tables from several ERPs might be mistakenly considered a single resource.

Therefore, the catalog must ensure correct technical identity.

SQL Server and Dependency Analysis

For Microsoft SQL Server, system mechanisms for analyzing SQL objects can be used.

For example, sys.sql_expression_dependencies contains defined SQL expression dependencies.

A simplified diagnostic query:

SELECT
    OBJECT_SCHEMA_NAME(
        referencing_id
    ) AS referencing_schema,
    OBJECT_NAME(
        referencing_id
    ) AS referencing_object,
    referenced_schema_name,
    referenced_entity_name
FROM sys.sql_expression_dependencies;

This is an example of a mechanism available in SQL Server 2016 and 2017. It can help identify dependencies between stored SQL objects.

However, the results are not a complete enterprise lineage.

For example, dynamic SQL or an external Python process may not be displayed as a regular static dependency.

Similarly, SQL Server does not necessarily know that a table’s output is used in an external BI system.

Therefore, additional integrations are needed for a complete picture.

Practical Model

The enterprise can regularly receive system metadata, supplement it with ETL information, and transfer it to the corporate catalog. It is necessary to control deleted, renamed, and newly created objects.

It is also necessary to distinguish between the absence of a dependency and the inability to automatically determine it.

PostgreSQL and Information Structure Metadata

PostgreSQL also supports system catalogs and corresponding views for analyzing the structure.

For example, information_schema.columns allows obtaining information about columns.

SELECT
    table_schema,
    table_name,
    column_name,
    data_type
FROM information_schema.columns
WHERE table_schema NOT IN (
    'pg_catalog',
    'information_schema'
);

This allows for the creation of a technical inventory. However, the column list does not automatically show which SQL transformations use these attributes.

For complete lineage, it is necessary to analyze the corresponding views, functions, ETL scripts, and external processes. The situation is particularly complex when SQL is generated dynamically.

For example, the table name is determined by a program parameter. Therefore, automatic analysis must have a mechanism for marking incomplete or unconfirmed dependencies.

This is important for large enterprises: the catalog should not create a false sense of completeness for the information map.

Impact Analysis: How to Assess the Consequences of Changes

Impact Analysis uses information dependencies to determine potential impact.

For example, an enterprise plans to change the structure of the ERP.Sales table. It is used in several ETL processes.

One builds a financial DWH. Another creates an operational analytical showcase.

A third transfers defined events to Kafka. With Lineage available, corresponding dependent components can be identified.

It is important to distinguish between technical and business impact. For example, an SQL procedure may continue to execute after a reference data change, but the result will be different.

Therefore, the analysis should include not only structures but also transformation rules.

Data Lineage and CI/CD

One of the promising directions of DataOps is the integration of dependency analysis with the change implementation process. For example, a developer creates an SQL migration.

After this, the system can determine which objects are potentially affected. Based on the result, the necessary tests are formed.

Structural Changes

If a table is changed, dependent SQL objects must be checked.

Analytical Changes

If a metric formula is changed, corresponding Regression Tests must be performed.

MDM

If matching rules are changed, the impact on historical reports must be checked.

Security

If a personal attribute is added, the corresponding access rights and processing purposes must be assessed.

Documentation

After productive implementation, the catalog must receive a new version of metadata. However, in February 2019, modern standardized OpenLineage integrations should not be described as an existing universal infrastructure.

For this period, it is more accurate to talk about the metadata of specific ETL platforms, Apache Atlas API, and specialized corporate automation mechanisms.

Relationship with Apache Airflow

Apache Airflow can be a source of operational information about task dependencies.

For example, a DAG defines a sequence:

extract_erp → transform_sales → load_dwh

This is a useful part of describing the information process. However, the task graph itself does not explain how a specific ERP field is transformed into a DWH metric.

For example, transform_sales might run an SQL script with dozens of transformations. Therefore, it is necessary to combine the DAG description with the corresponding SQL metadata and business rules.

As of February 2019, Apache Airflow has become a Top-Level Project of the Apache Software Foundation — the corresponding decision was announced on January 8. Version 1.10.2 has been available since January 2019 and can be considered for relevant environments.

However, it should not be confused with future generations of Airflow 2.x or 3.x.

DAG Versioning

Python code should be stored in Git.

Description of Sources and Results

For critical tasks, it is advisable to document input and output datasets.

Run Identifiers

They help establish which process version generated a specific result.

Data Lineage

Can be built on the basis of a set of technical and business metadata.

Thus, Airflow and Atlas can perform complementary functions, but their integration needs to be designed separately.

Data Lineage and Personal Data Control

After the start of GDPR application in May 2018, enterprises whose activities fall under the regulation must ensure appropriate rules for personal data processing. Data Lineage can help determine the spread of information between systems.

For example, contact details of an individual are stored in CRM and transferred to DWH. The catalog can show relevant dependent tables.

However, this does not mean that the presence of Lineage automatically ensures regulatory compliance.

Data Inventory

It is necessary to know which resources process relevant categories of personal data.

Purpose Limitation

The purpose of processing must be defined.

Access Control

The metadata catalog does not replace actual DBMS access rights.

Data Retention

Data retention periods must be controlled in all relevant environments.

Change Impact

New information flows must undergo an appropriate assessment. It is particularly important to consider that not all derived sets from a personal source necessarily remain personal.

For example, properly anonymized aggregated statistics may have a different legal status. Therefore, automatic propagation of classifications requires verification.

Practical Scenario: Changing Customer Directory in a Holding Company

Let’s consider a hypothetical holding company with four ERP systems, a central CRM, an MDM, and a corporate DWH. The company plans to change its customer segmentation model.

Previously, three categories were used. Now, the business wants to introduce five.

Stage 1. Change Registration

A corresponding Change Request is created.

Stage 2. Business Definition

The Data Owner approves the new categories and the rules for their usage.

Stage 3. Metadata Analysis

The team identifies all tables and streams that use the client segment.

Stage 4. Impact Analysis

A list of dependent ETLs, Data Marts, and reports is generated.

Stage 5. MDM

Changes to corporate directories are checked.

Stage 6. Schema Evolution

Structures and information contracts are adjusted as needed.

Stage 7. Testing

Compatibility checks and regression tests are performed.

Stage 8. Historicity

Determines whether new segments apply only to future operations or also to history.

Stage 9. Productive Implementation

Changes are migrated according to the approved order.

Stage 10. Catalog Update

Lineage and Business Glossary are brought into compliance with the new model.

Stage 11. Monitoring

ETL results and analytical reports are monitored.

Thus, Data Lineage helps to make modernization more predictable.

How to Measure the Effectiveness of Automated Lineage

It is important for an enterprise to assess practical utility.

It should be noted that a large number of automatically collected dependencies does not guarantee their correctness. For example, a catalog may contain thousands of dependencies but miss one critical dynamic SQL query.

Therefore, additional verification is required for important business processes.

Organizational Responsibility

• To support Lineage, roles must be defined.
• Data Owner approves the business purpose of the data.
• Data Steward maintains relevant metadata and ensures its accuracy.
• Data Architect defines information dependencies.
• ETL Developer provides technical descriptions of transformations.
• DBA maintains relevant DBMS metadata.
• Integration Architect defines interactions between enterprise systems.
• BI Developer documents analytical report dependencies.

It is important that the catalog is updated as part of regular Change Management, not just during a separate audit.

Intecracy Group Competencies in Data Lineage and Impact Analysis

Metadata automation requires the integration of several technological areas. Data Management IG (dmig.com.ua) specializes in ETL, MDM, Data Quality, DWH, and enterprise analytics. These competencies are directly related to describing information models, Data Lineage, Business Glossary, and controlling the impact of changes on analytical results.

IQusion IT (iqusion.com) operates in the areas of system integration, electronic document management, and BPM. These competencies are important for analyzing inter-system dependencies, API contracts, and change management processes. Softline IT (softline.kiev.ua) has competencies in server, network infrastructure, and storage systems. They can ensure the reliable operation of the metadata platform and its connection to enterprise sources.

SL Global Service (sgs4business.com) specializes in cloud integration, virtualization, and hybrid infrastructure. These areas can be used for cataloging information resources distributed across on-premises and cloud environments.

At the same time, the correctness of business definitions and analysis priorities should be established by the respective owners of information domains.

Practical Guidelines for Implementation

Data Lineage is becoming an important component of enterprise information architecture governance.

With the increasing number of ERP, CRM, ETL, DWH, and analytical services, it is becoming increasingly difficult for enterprises to establish all dependencies manually. Apache Atlas, DBMS system metadata, and ETL platform information can be used for automation.

However, automatic metadata collection does not guarantee a full understanding of business semantics. Therefore, it is necessary to combine technical Lineage with Business Glossary, MDM, Data Governance, and corresponding verification procedures.

Impact Analysis is of particular importance, as it allows determining the consequences of changes before their productive implementation.

The main principle of automated Data Lineage is that every critical corporate change must be accompanied by a clear information card: where the data originates, what transformations it undergoes, and which systems and business indicators may be affected.