Designing and deploying live portfolio analytics

Every week, someone rebuilt the report by hand. Extracts pulled, pasted into the same workbook, cells coloured, tabs checked, then sent round as an attachment. It took the better part of a day and it went out to everyone at once.

The data was already stale when it arrived. People opened it and replied to say a case had moved on Friday, a disbursement had cleared, a status was wrong. They were right, and there was nothing the file could do about it, because it was a photograph of a system that had carried on without it.

The deeper problem was that everyone got the same file. One format, one scope, no addressee. A workbook covering every portfolio tells each reader that most of it is somebody else’s problem, and leaves them to work out which part is theirs. When a report is addressed to everyone, nobody owns anything in it.

What follows is that system rebuilt: a financing portfolio, its facilities, the periodic reviews that release money against them. The names and figures are invented. The proportions, the model shape and the logic are the ones I actually built.

Rebuilding it started with a dull question: where does any of this live? Almost all of it lived in one place. The operational system of record held 35 objects worth of it, from facilities and reviews down to the audit trail of every status change. The awkward part is what that system did not hold.

It did not hold the rules. Every step in the workflow has a clock: a date it counts from, a number of days it is allowed, a threshold past which it is overdue. All of that lived in policy documents, which meant no report could reach it and every judgement about lateness was being made in somebody’s head. I read the documents and typed the rules into a table, one row per status, and from that point the timeline was something the model could read. The same goes for order: statuses run in a sequence that means something, and left to itself a report sorts them alphabetically and puts Completed before Ongoing.

Everything downstream of that is Power BI. Power Query does the landing and the shaping, the model holds the relationships and the derived columns, and the service publishes a single dataset on a refresh schedule. Nothing is emailed. The rest of this piece is about what happens between those three stages.

With the sources landed, the next thing to fix was the single file for everyone. A division head and an operations analyst want the same subject at wildly different resolutions. Give the head a row-level table and they will ask someone to summarise it. Give the analyst a headline count and they will ask for the export. Both requests are the same complaint, which is that the level of detail is wrong for them.

So the report was built at four levels of detail, and moving between them became the navigation. Each level answers one question, sits a click from the next, and is scoped so a reader arrives at their own work rather than everyone else’s.

The fourth rung is the one that matters. It leaves the report entirely and opens the record in the system where the work is done. A reader who spots a stalled case does not copy its reference into a search box somewhere else; they click it and they are in it.

Serving all four of those views from one live source only works if a single model can answer every question the four of them ask. That model ended up holding 43 tables and 804 columns, joined by 191 relationships. Those counts tell you almost nothing on their own, so below is a readable slice of it, drawn the way a modelling tool draws one.

The shape is a star. Every question in this portfolio starts with a facility and works outwards, so almost everything joins back to one table. Dimensions on the left describe it, the facts on the right record what happened to it, and the policy tables on the far right hold the rules those facts are judged against.

A plain star is the right answer here. The columns marked fx are where the work went: Amendment carries a deadline and a status that no source system supplies, and Review carries the age of each case and whether it needs a decision. A model that needs a complicated relationship graph to function has usually pushed its logic into joins, where it is hard to see and harder to change.

Into the columns, mostly. 62 of them are computed at load in Power Query and another 72 inside the model, which puts 134 derived columns between a raw field and any measure at all.

Here is why, using the one thing every reader wanted to know: is this late. The source system gives you a created date and a status. It has no opinion on whether that is acceptable, because acceptable depends on which step the case is on, and every step gets a different allowance. Someone has to work that out, and the model is the right place.

The part worth stealing is the policy table. The allowance for each status, and which date to count from, live in data rather than inside a formula. When operations decided a step deserved ten days instead of five, they changed a row. Every deadline in the model moved, and nobody opened DAX.

The model could now say that a case was late. What the pages did with that was a separate decision, and it is the one most reporting gets wrong. Reporting drifts toward the past because the past is easy to count: how many closed, how many ran late, how many last quarter. Every one of those is true, and none of them helps on a Monday morning, which is how a page teaches people to stop opening it.

Two habits fixed most of that. The first was showing age rather than dates. A column reading 31 March means nothing without arithmetic, while one reading 83 days in this step is a judgement you can make at a glance. Every queue in the report sorts on that column, so the thing that has been stuck longest is the thing you see first.

The second was making the pages look forward. Alongside what is already late sits what falls due inside the next month, split into coming month, delayed and later. The panel that names what needs this reader appears in the same position on every page, which is how it became the thing people actually opened the report for.

All of that has to live somewhere, and by the end it lived across 18 pages. Past about six, navigation stops being decoration and turns into the product itself. Several domains ended up with two pages instead of one, which reads as duplication until you watch who opens which.

A portfolio manager opens the queue: one row per case, sorted by how long it has been still, each row a link into the record. A division head opens the matrix: portfolios down the side, stages across the top, three cohorts deep. Same data, same measures, different question. Neither view is a worse version of the other.

Holding 18 pages together took a skeleton every page shares and a switcher that never moves, so learning one page teaches you all of them. It also took an opening screen that spends its whole surface telling you where to go rather than showing you a number nobody asked for.

There is a detail here I like more than I should. Of the 496 visuals in the file, 328 are shapes. Power BI ships no card, so the panels holding all of this together are drawn by hand, over and over. Two thirds of the object count is a design system that had to be built because the tool does not have one.