Relation usage frequency

Relation usage frequency measures how often a defined relationship between two data entities is actually traversed in queries, reports, and workflows over a given period. In a data model a relation is a link, such as customer to order, or order to invoice. Usage frequency counts how many times that specific link is followed when people query the data, normalised to a period such as a week or month.

The metric matters because every relation carries a cost that is invisible until you look for it. A modelled relation has to be kept correct as schemas change, it widens the surface area for bugs, and it makes the model harder to learn. A relation that is queried hundreds of times a week is paying for that cost many times over. A relation queried zero times is pure overhead, and you only discover which is which by measuring usage.

The number is most useful as a relative ranking across the relations in a model, not as an absolute. A relation used 5 times a week may be vital in a small, slow-moving model and trivial in a high-traffic one. What you are looking for is the long tail of relations with near-zero usage, because that tail is where you can safely simplify, and the small set of heavily used relations that deserve the most care and the fastest average resolution time when they break.

The calculation divides the number of times a relation is traversed by the length of the period, giving a usage rate such as queries per week. The work is less in the arithmetic and more in defining what counts as a use and where you capture it. The inputs below are what you need to settle before the rate is trustworthy.

1. 1

   Times a relation is used

   The count of queries, joins, dashboard loads, or workflow runs that actually traversed the relation in the period. Decide whether a single dashboard refresh that uses the relation many times counts once or many times, and hold it consistent.

2. 2

   Time period

   The window over which you count, typically a week or month. A consistent window is what makes the rate comparable across relations and over time, so the long tail stays visible.

3. 3

   Source of usage data

   Where you capture usage, usually query logs, a query history, or workflow execution records. The rate is only as good as the coverage of this source. A relation used only through a path you do not log will look unused.

4. 4

   Definition of a relation

   The exact link being measured, named by its two entities and direction. Counting a many-to-many relation through a join table needs a clear rule for what traversal means, or the count drifts.

A worked example. Over four weeks, query logs show the customer-to-order relation was traversed in 1,200 queries and the order-to-warranty relation in 6. Dividing by the four-week window gives 300 traversals per week for the first and 1.5 per week for the second. The first relation is core and worth optimising and protecting. The second is a candidate to question, because it is carried in the model and almost never used. The two numbers, side by side, are what make the decision obvious.

A metric tree decomposes usage frequency into the things that drive a relation to be queried, then traces each down to a cause you can act on. This turns a flat count into an explanation of why a relation is used a lot, a little, or not at all.

The first level splits usage into where it comes from: queries written by people, dashboards and reports, and automated workflows. Each source decomposes further. Query usage depends on whether analysts know the relation exists and whether it is the natural path to the answer. Dashboard usage depends on how many live dashboards depend on it. Workflow usage depends on how many automations traverse it. When a relation shows near-zero usage, the tree tells you whether it is genuinely redundant, or whether it is useful but undiscovered because nobody knows it is there.

This is the gap between a dashboard and a decision. A dashboard says a relation has low usage. The tree shows that usage is zero across queries and workflows but the relation is the only path to a compliance report run once a quarter, which is exactly the case where low frequency does not mean safe to remove.

There is no universal target for usage frequency, because it scales with the size and traffic of the data model. The useful benchmark is the distribution within your own model, where relations almost always fall into a few clear bands. The ranges below describe those bands in traversals per week, which is the level of detail that drives a decision about each relation.

Watch the shape of the distribution and the trend on individual relations, not a single average across the model. A core relation whose usage is falling may signal a report being retired or analysts moving to a different path. The bands tell you which relations to care about, the trend tells you when something is changing underneath.

Improving usage frequency is rarely about driving every relation up. It is about lifting the relations that should be used but are not, and retiring the ones that genuinely are not needed. The metric tree shows which case you are in, and each has a concrete move.

The decomposition decides the move. If a relation is unused because it is undiscovered, documentation is the fix. If it is unused because it is slow, indexing is the fix. If it is unused because it is genuinely redundant, retiring it is the fix. Treating every low-usage relation as deletable, without checking the discoverability and rare-query branches, is how teams remove the one path to a report they only run at year end.

KPI Tree lets you model this by connecting usage frequency to the relations and owners behind it. Each relation in the model can carry RACI ownership, so a single accountable person owns the decision to keep, document, or retire it rather than it being nobody call. When usage on a core relation drops past a threshold, the metric pushes to that owner so a change in how the data is queried is noticed early. The verified impact loop then checks whether retiring or documenting a relation had the intended effect, so the model gets simpler without losing the paths that quietly mattered.

1. 1

   Measuring existence, not usage

   A relation defined in the schema is not the same as a relation being used. Counting what exists rather than what is queried misses the entire point, which is to find carried-but-unused links.

2. 2

   Trusting incomplete logs

   If usage is captured from only one query path, relations used through other paths look dormant when they are not. The rate is only as honest as the coverage of the source you read.

3. 3

   Retiring rare-but-critical relations

   A relation used once a quarter for a compliance report has near-zero frequency but is far from safe to delete. Always check whether a low-usage relation is the sole path to an important query before removing it.

4. 4

   Confusing low usage with low value

   Low usage can mean the relation is undiscovered, not unneeded. Acting on frequency alone, without checking discoverability, leads to deleting things that should have been documented.

5. 5

   Ignoring the trend on core relations

   A heavily used relation whose usage is falling is a leading signal that something upstream is changing. Watching only the long tail misses the more important shift happening at the top of the distribution.