SQL Queries - CAST Central Base - Queries on Metrics - How to calculate Technical Debt manually

Purpose of Query

This page will help you to manually calculate the Total Technical Debt for Applications by using two simplified queries to be run on the Central database.

These queries need to be run on the Central base to retrieve the set of parameter values to be used in the formula to calculate the Technical Debt.

With the below outlined queries you will have all the parameter values that you can replace in the formula for corresponding parameters and calculate the Technical Debt manually. "# of low/medium/high severity violations in Application and Module" can be replaced by "Number of Violations" (result of the second query) for "Violation Category" LOW,MEDIUM,HIGH respectively.

Applicable CAST Version

Release	Yes/No
8.3.x
8.2.x
8.1.x
8.0.x
7.3.x

Applicable RDBMS

RDBMS	Yes/No
Oracle Server
Microsoft SQL Server
CSS2

Query for CSS

Query 1:

SELECT PARAM_NAME     ,
       PARAM_NUM_VALUE,
       PARAM_CHAR_VALUE
FROM   DSS_METRIC_PARAM_TYPES DMPT,
       DSS_METRIC_PARAM_VALUES DMPV
WHERE  DMPT.METRIC_ID   = 68001
AND    DMPT.METRIC_ID   = DMPV.METRIC_ID
AND    DMPT.PARAM_INDEX = DMPV.PARAM_INDEX

Query result example

PARAM_NAME PARAM_NUM_VALUE PARAM_CHAR_VALUE

[CHAR                                                      ]             [FLOAT       ]   [CHAR            ]
-------------------------------------------------------      ----------------     --------------------
% of low severity violations to be fixed                           0                             NULL
% of medium severity violations to be fixed                       50                               NULL
% of high severity violations to be fixed                            100                              NULL
Weighted time, in hours, for fixing LOW severity violation          0.62                             NULL
Weighted time, in hours, for fixing MEDIUM severity violation        0.97                             NULL
Weighted time, in hours, for fixing HIGH severity violation           2.56                             NULL
Cost per hour of developer time                                       75                                NULL
Currency for development costs                                       NULL                             $

Query result interpretation

The above query will retrieve the result set / values to be used for the equation to calculate technical Debt

Query 2:

 SELECT DSS_OBJECTS.OBJECT_NAME                                      ,
       DSS_METRIC_TYPES.METRIC_NAME                                 ,
       DSS_SNAPSHOTS.SNAPSHOT_NAME                                  ,
       DSS_METRIC_RESULTS.METRIC_NUM_VALUE AS "Number of Violations",
       CASE DSS_METRIC_RESULTS.METRIC_VALUE_INDEX
              WHEN 11
              THEN 'LOW'
              WHEN 12
              THEN 'MEDIUM'
              WHEN 13
              THEN 'HIGH'
       END AS "Violation Category"
FROM   DSS_METRIC_RESULTS DSS_METRIC_RESULTS,
       DSS_METRIC_TYPES DSS_METRIC_TYPES    ,
       DSS_OBJECTS DSS_OBJECTS              ,
       DSS_SNAPSHOTS DSS_SNAPSHOTS
WHERE  DSS_OBJECTS.OBJECT_ID          = DSS_METRIC_RESULTS.OBJECT_ID
AND    DSS_METRIC_TYPES.METRIC_ID     = DSS_METRIC_RESULTS.METRIC_ID
AND    DSS_METRIC_RESULTS.SNAPSHOT_ID = DSS_SNAPSHOTS.SNAPSHOT_ID
AND    DSS_METRIC_RESULTS.METRIC_ID IN (68001)
AND    DSS_OBJECTS.OBJECT_TYPE_ID =-102
AND    DSS_METRIC_RESULTS.METRIC_VALUE_INDEX IN (11,
                                                 12,
                                                 13)

Query result example

OBJECT_NAME   METRIC_NAME                SNAPSHOT_NAME       Number of Violations   Violation Category
[CHAR]   [CHAR]                            [CHAR]           [FLOAT]      [CHAR]
------   --------------------------   --------    ----------   --------------
KARMA         Technical Debt                      KARMA G7R1_707      17614                 LOW
KARMA         Technical Debt                      KARMA G7R1_707                62787                 MEDIUM
KARMA         Technical Debt                      KARMA G7R1_707      6000                         HIGH

Query result interpretation

The above query retrieves the Number of Violations for the three types of violation category, namely Low, Medium & High. To be used for the equation to calculate technical Debt.

Query for Oracle

Same as CSS

Query result example

Same as CSS

Query result interpretation

Same as CSS

Query for SQL server

To be done

Query result example

To be done

Query result interpretation

To be done

Formula - Total Technical Debt per Module and Application

( (% of low severity violations to be fixed X # of low severity violations in Application and Module) X
(Weighted time, in hours, for fixing low severity violations) +
(% of medium severity violations to be fixed X # of medium severity violations in Application and Module) X
(Weighted time, in hours, for fixing medium severity violations) +
(% of high severity violations to be fixed X # of high severity violations in Application and Module) X
(Weighted time, in hours, for fixing high severity violations) ) X
Cost per staff hour to fix violations

Notes/comments

DMPT and DMPV are alias name and can be replaced as appropriate.

The technical debt of application being equal to the sum of technical debt at modules is not a correct expectation because the technical debt is calculated as cost of fixing violations and there might be scenarios where some objects are shared amongst many modules.
So if we take sum of debt of modules then we would end up counting cost of fixing same violation several times whereas the cost of fixing one violation should be taken into consideration only once.
This explains why the sum of modules debt is greater than the application debt. Hence, it is normal behaviour.

Related Pages