Measuring the technical debt of a BI estate: why and how

Technical debt is a concept everyone understands intuitively but nobody measures. In the software development world, tools like SonarQube solved this problem long ago. In the BI world, nothing equivalent existed — until we built our scoring methodology.

This article details the 12 criteria we use to quantify the technical debt of an SSIS or SAP BO estate, and explains why this quantification changes the nature of the decisions a CIO makes.

The "I know it's bad but I don't know how bad"

Most CIOs know their BI estate has technical debt. They feel it through indirect signals: overnight processing that overshoots its window, recurring incidents on the same packages, enhancements that take three times longer than expected.

But without precise measurement, prioritisation is impossible. Should you invest €200K in a full migration? Or €50K to fix the 20 most critical components? Without data, the decision is made by gut feel — and gut feel tends to favour the status quo.

The 12 measurement criteria

Our technical debt score runs from 0 (new, well-structured estate) to 100 (immediate operational risk). It is calculated automatically through source code reverse-engineering, with no access to data or production systems.

Criterions de structure (40% du score)

Average cyclomatic complexity. Measures the number of possible execution paths in an SSIS package. A package with complexity >30 is practically impossible to test exhaustively and very risky to modify. The healthy average sits between 5 and 15.

Circular dependencies. When package A calls package B which calls package C which depends on the output of A, you have a circular dependency. This is a marker of degraded architecture. Zero is the target; above 3, it’s an alarm signal.

Call chain depth. A SQL Server Agent job that launches a master package, which launches 4 sub-packages, one of which in turn launches 3 sub-sub-packages, has a depth of 3. Beyond 4 levels, debugging becomes virtually impossible in a production incident.

Duplicated code ratio. Percentage of identical or near-identical transformations or SQL queries present in multiple packages. Above 15%, it’s a sign the estate was built by copy-paste rather than through proper factorisation.

Criterions de maintainability (30% du score)

Packages without logging. An SSIS package without an OnError Event Handler or logging to a dedicated table is a blind package: when it fails in production at 3 AM, nobody knows why. The acceptable rate is below 10%.

Error handler coverage. Even when an OnError Event Handler exists, is it present at every level (package, container, task)? Partial coverage is sometimes worse than no coverage at all, because it creates a false sense of security.

Hard-coded connections. Percentage of connection strings (servers, databases, credentials) hard-coded in packages instead of using environment variables or external configurations. A single server name change can break dozens of packages in production.

Environment variable usage. Complementary to the previous criterion: well-structured packages use environment variables for connections, file paths, and configuration parameters. Their absence is a sign of technical debt.

Criterions d’obsolescence (30% du score)

Dead references. Packages that reference servers or databases that no longer exist. Either the package fails silently (and nobody knows because there’s no logging), or it has been disabled but never removed — adding noise to the estate.

Deprecated components. Script Tasks in VB.NET (deprecated since SSIS 2012), third-party components from defunct vendors, data sources in obsolete formats (.dbf files, 32-bit ODBC connections).

Legacy format versions. A package created in SSIS 2008 can run on an SSIS 2019 server, but its internal format is obsolete. It doesn’t use newer features (Project Deployment Model, Parameters) and can cause issues during upgrades.

Unused component rate. For SAP BO: percentage of WebI reports not executed in more than 12 months (measured via CMS logs). For SSIS: packages never executed or disabled in the Agent job.

What the score changes in decision-making

A score below 30 (“healthy estate”) justifies standard maintenance with targeted improvements. A score between 30 and 60 (“significant debt”) calls for a targeted remediation plan: identify the 20% of components causing 80% of the risk and address them first.

The score is not an end in itself. It’s a dialogue tool between IT, business, and executive management. Saying “our estate has a technical debt score of 54/100, here are 3 investment scenarios” is infinitely more convincing than “we need to modernise our BI.”