Building Dashboards From Proclaim Data: Getting the Data Out Is the Hard Part

We have written before about why a connected Power BI dashboard beats a static report and which five dashboards most firms should build first. What that article skipped, deliberately, is the question we get asked on almost every Power BI engagement once the partners have signed off on the idea: how does the data get from Proclaim into the dashboard in the first place? The honest answer is that this is where most of the work sits, and where most dashboard projects quietly stall.

Where Proclaim data actually lives

Underneath the screens, Proclaim runs on a Progress OpenEdge database, not SQL Server. That matters more than it sounds. Everything the firm has ever keyed into a matter is in there, but Power BI has no native connector for Progress. Access is through the OpenEdge ODBC driver, which is slower and less forgiving than a SQL Server connection, and most Power BI developers have never seen it. The database was also designed to run a case management system, not to answer management questions.

Case data is stored by field reference rather than by meaningful column name, history entries sit in long narrow tables, and the same concept, a claim value or a limitation date, can live in a different field on every case type. Time, billing and ledger data sit in their own structures again. A query that produces a sensible WIP figure for one department will produce nonsense for the next unless someone who knows the build has mapped it first.

That is the first thing to understand about Proclaim dashboards: the Progress database is the source, but it is not the model, and it is not something Power BI should be talking to directly. Something has to sit between the two.

Four routes out of Proclaim

In practice there are four ways to get data from Proclaim into Power BI, and we have spent years getting it out by every one of them. ODBC, scheduled data exports and Proclaim's own web services each have a place. The right one depends on where Proclaim is hosted, how much the firm is prepared to maintain, and how fresh the numbers need to be.

• Direct ODBC access | Power BI connects to the Progress database through the OpenEdge ODBC driver and a read-only account, usually via an on-premises data gateway. This is the fastest to stand up, because nothing is left out, but where Proclaim is hosted by Access the ODBC access is a chargeable extra, and ODBC queries against Progress are slow enough that a badly written one will be felt by fee earners at ten o'clock on a Monday morning. We will normally use this route to pull a nightly extract into a SQL Server staging database rather than let Power BI query Progress live.
• Scheduled data exports | Proclaim's report writer and data export tools can be scheduled to drop flat files into a folder, which Power BI or the warehouse then picks up. This is the route most hosted firms end up on, because it needs no database access at all. The trade off is that every number has to be defined inside a Proclaim export first, which means the export's limits become the dashboard's limits, and someone has to own those exports when case types change.
• Proclaim web services | Proclaim exposes its data through its own web services, and for firms that want data on demand rather than on a nightly schedule this is the cleanest route out. Matter, history and financial data can be pulled by a scheduled job into a staging database without touching the Progress database or the report writer at all. It needs development work to set up and a licence for the web services, but once built it survives Proclaim upgrades far better than a hand written ODBC query, and it is the route we reach for first where it is available.
• A staged data warehouse | Data is extracted from Progress on a schedule, by ODBC query or by export, landed in SQL Server or a lakehouse, and transformed there into clean matter, time, billing and ledger tables before Power BI ever sees it. This is the most work up front and the only route that scales properly. It is also the only sensible answer where the firm wants Proclaim data alongside accounts, HR or marketing data, or is planning a move to another case management system and wants reporting that survives the migration.

For a single office firm with Proclaim on its own server and a modest set of KPIs, a nightly ODBC extract into SQL Server is usually enough. Where the web services are licensed, we will use those instead. For anything larger, or anything hosted, we start with the warehouse and treat the first dashboard as the first consumer of it, not the project itself.

Why raw Proclaim tables do not make a dashboard

Whichever route the data takes, the work that makes or breaks the dashboard happens in the model, not in the visuals. Partners do not ask for a history table; they ask how much WIP is sitting over 90 days and who owns it. Getting from one to the other means building a small number of clean, well named tables that every dashboard draws from.

For most Proclaim firms that means four: a matter table with one row per case and the fields the firm actually reports on, a time table, a billing table and a ledger table, all keyed consistently so that a fee earner, a department and a case type mean the same thing on every page. Alongside those sit the lookups: who was in which team on which date, which case types roll up to which practice area, which financial year a date falls into.

This is also where the firm's definitions get written down. What counts as a new matter: the date the file was opened, or the date the client care letter went out? Is WIP valued at recorded time or at the agreed fee? Every firm has an answer, usually several, and the model is the first place those answers are forced to agree. That is why we treat the modelling step as Proclaim development work rather than a Power BI task; the people who understand the build have to be in the room.

The visuals are the last ten percent of a dashboard project. The other ninety is getting Proclaim to tell a straight story.

Refresh, hosting and the gateway question

A dashboard that refreshed last month is a report with a nicer font. The whole point of this exercise is that the numbers update without anyone touching them, so the refresh mechanism deserves as much thought as the model.

Where Proclaim runs on the firm's own server, the on-premises data gateway is the piece that lets the Power BI service reach it, with the OpenEdge ODBC driver installed on the same machine. That machine needs to stay on, the service account needs read access and nothing more, and someone needs to know both exist when the machine is next rebuilt. Where Proclaim is hosted by Access, ODBC access is available but charged for, so the firm is weighing that cost against scheduled exports to a shared location, the web services, or asking Access for a nightly extract into the firm's own environment. Both work. Both need to be agreed in writing, because a hosting change or a version upgrade can switch them off without warning.

On frequency, nightly is enough for almost every management dashboard we have built. Nobody needs live WIP to the minute, and a nightly load is kinder to the production system, cheaper to run and far easier to troubleshoot. The exception is operational dashboards on the floor, such as a team leader watching new instructions or a complaints queue during the day. Those justify a more frequent refresh, and they are usually better served from the warehouse than from Proclaim directly.

Security runs through all of it. Proclaim holds client data, some of it sensitive, and a dashboard with row level security that shows each fee earner only their own matters is a different build from one where every partner sees everything. Decide that before the first page is designed, not after.

Where this goes wrong

The most common failure is the dashboard built straight on top of the live Progress database over ODBC by someone who knew Power BI but not Proclaim. It looks right in the demo, the numbers drift as soon as a new case type is added, and the firm stops trusting it within a quarter. The second is the dashboard that depends on a scheduled export nobody owns: a report writer job fails after an upgrade, the files stop arriving, and partners spend a month looking at a figure that has not changed because nothing has refreshed. The third is the model that was never written down, so when the person who built it leaves, the firm is left with a pile of DAX that nobody can explain and nobody dares change.

Underneath all three is the same cause: the data layer was treated as a side task on the way to the pretty bit. It is the project.

Getting the balance right

Firms that end up with Proclaim dashboards they still trust two years on tend to:

• Choose the route out of Proclaim deliberately | ODBC, data exports or web services | based on hosting, licensing and scale, rather than defaulting to whatever the first developer knew.
• Never point Power BI at the live Progress database; extract nightly into SQL Server or a staged warehouse and report from that.
• Build a small set of clean, named tables that every dashboard shares, and document what each field means and where it came from.
• Write the firm's definitions down | what a matter is, what WIP is, when a file counts as closed | and get the partnership to sign them off before the first visual.
• Give the refresh a named owner and a failure alert, so a silent stop is noticed in hours rather than weeks.
• Decide row level security up front, and test it with real users before it goes live.
• Review the model after every Proclaim upgrade and every new case type, because both will break something.

Done properly, the data layer outlasts any individual dashboard, and often outlasts Proclaim itself. If your firm has Power BI licences and a Proclaim database but no reliable way of getting the one into the other, get in touch for a strategy call.