Applied Systems agency data into Power BI, done for you
Applied Epic — the Applied Systems agency management system, not Epic the health record and not Epicor — has no Power BI connector. So this starts with a real extraction: your Applied data lake, Bulk Data Extract or API feed pulled into a modelled star schema, then DAX for policy retention, renewal book at risk and producer book reporting that the native role-gated dashboards fence off.
Representative dashboard — sample data
Before anything else
Applied Epic is the agency management system from Applied Systems that independent insurance agencies and brokerages run their book on — clients and accounts, policies and lines, renewals, service activities, and agency-bill and direct-bill accounting, all structured by agency, branch, department and profit center. It is not Epic Systems' electronic health record, not Epicor's manufacturing ERP, and not Epic Games. That collision is not trivia: it is the reason searching for Epic reporting advice returns hospital analytics and shop-floor ERP, and the reason almost nothing practical has been written about moving Applied Epic data into a Power BI model.
The work itself is unglamorous and it is nearly all upstream of the report page. Establish which data-access route your Applied agreement actually gives you, pull clients, policies, transactions, producers and carriers out of it, conform them into dimensions and facts with a real date table, and only then write the DAX that turns an expiration date into a renewal pipeline and a policy count into a retention rate. Charting what comes out is the easy half.
What we build from Applied Epic
Buy a block of prepaid Flex hours and, if you're unhappy within the first 4 hours, we refund you in full — no questions asked. Hours are deducted only for work you've reviewed and approved, there's no project minimum, and your banked hours never expire.
What the build covers
Every one of them sits upstream of the first visual, and every one is where an agency's own attempt tends to stall.
Data lake, Bulk Data Extract, REST API or scheduled export — which of these you have depends on your Applied package and contract, not on what a vendor page implies. We establish it in scoping, before anything is designed around it.
A renewed policy is a new record with a lineage back to the expiring one. Retention only means something if that chain is reconstructed — otherwise every renewal reads as new business and every expiry as a loss.
Agency, branch, department and profit center decide how revenue is allocated inside Epic. The model carries that hierarchy as a real dimension so a Power BI total reconciles to what the accounting side already reports.
Written, billed and received are three different numbers on two different billing paths. We agree which one each visual reports before writing a measure, so nobody has to ask which revenue this chart means.
Row-level security scoped to producer, branch or department, driven from a mapping table — so a producer opening the report sees their book and nobody else's, and onboarding one is a data change.
Scheduled or incremental refresh sized to your history, with failures fixed by us out of your banked hours rather than landing on whoever in the agency happened to build it.
Through whichever data-access route your agency licenses, because there is no direct connector. Applied Epic does not appear on Microsoft's published list of Power Query and Power BI connectors (opens in new tab), so nobody is clicking Get Data and choosing Epic. What exists instead is a small set of documented paths, and which of them is open to you depends on your Applied package and agreement rather than on anything technical.
This matters more in insurance software than in most sectors, because data access is frequently a licensed add-on rather than an included feature. We confirm the route, the licensing, and any vendor charge in the scope estimate before a query is written — the same discipline we apply to loan origination systems on our Power BI dashboards for mortgage lenders page, where reportable-database access is also contractual.
| Route | What it is | What it suits — and what to check |
|---|---|---|
| Applied's data lake | Applied has built its analytics platform on Google BigQuery, with Looker on top; the lake holds structured Epic data for agencies whose package includes it | The cleanest path when it is available — Power BI has a first-party BigQuery connector, so the model reads a warehouse rather than an API. Confirm entitlement and which tables you are authorized to see |
| Bulk Data Extract (BDE) | A replicated copy of your Applied Epic database that agencies license and query with SQL and third-party reporting tools | The long-standing route for agencies that already write their own reports. Confirm refresh cadence of the copy and who administers the server |
| Applied Epic REST APIs | APIs published through Applied's developer center over OAuth 2.0, covering client, contact, policy, activity and attachment surfaces, alongside a legacy SDK | Suits targeted feeds and integrations. Sandbox registration is self-serve, but production access requires Applied's approval and an SDK-API license on the agency side |
| Scheduled Epic exports | Report and dashboard output delivered as CSV on a schedule by email | A bridge, not an integration — useful to prove the metric set in week one while a durable route is licensed. It will not carry the history a retention trend needs |
| A warehouse you already own | Epic data already landed into your own SQL Server, Fabric or BigQuery environment by an existing pipeline | Model from that copy. It refreshes faster, and it is far easier to join to payroll, carrier statements and the general ledger |
The point of naming all five is that agencies are usually told there is one answer, and there isn't. We work with what your contract already covers, and if none of it is in place we will say so plainly rather than quoting a build that depends on access you would have to buy first.
Applied's own embedded analytics, Epic Dashboards, is genuinely useful and it is the fastest answer to an in-Epic question. It presents role-based visuals for seven agency roles — principals, servicing department leaders, sales managers, accountants, servicing reps, producers and IT managers or system administrators — with data exportable to PDF, CSV or PNG and schedulable by email. It also ships data-quality views that flag things like policies missing estimated premiums and active clients with no in-effect policy.
Two structural limits push agencies past it. First, it is gated: administration guidance describes close to forty dashboard modules, each of which has to be switched on per user by role under the security configuration, with a data-consumer role that can be granted with or without download rights — and whether you have Dashboards at all depends on your Applied package. Second, and more decisively, it is not Microsoft. Applied's platform is built on BigQuery and Looker (opens in new tab), so an agency standardized on Power BI ends up with its book of business reported in one vendor's BI tool and everything else in another.
| What you are trying to do | Native Epic Dashboards | Custom Power BI model |
|---|---|---|
| Answer an in-Epic question quickly | Its core strength — already built, nothing to engineer | Possible, but not the reason to build a model |
| Give every user the view they need | Role-gated across close to forty modules, and tied to your Applied package | One workspace, access governed by row-level security you control |
| Define retention the way your agency defines it | Fixed to the definitions built into the module | Each definition an explicit, documented DAX measure you can change |
| Join Epic to carrier statements and the general ledger | Reports on Epic data, in Epic's shape | Those sources modelled as additional facts beside policies and transactions |
| Trend across reorganized branches and departments | Constrained by the current structure configuration | Slowly changing dimension keeps prior years comparable |
| Standardize on Microsoft | Applied's analytics stack is Looker-based | Lives in your own Power BI tenant beside every other report you run |
| Where the logic lives | Inside the vendor's reporting layer | In a model file in your tenant, which you own outright |
The honest answer for most agencies is both. Keep Epic Dashboards for the questions Epic can answer on its own, and build the Power BI model for the numbers that decide producer compensation, carrier negotiations and where the next hire goes. If your organisation also adjusts or administers claims as an MGA or TPA, the Power BI insurance claims dashboard covers cycle time, open inventory and reserve movement on that side of the business.
Fewer than agencies expect. Almost every question a principal asks — what is renewing, who is retaining, which carriers carry the book, where service capacity is going — resolves to some combination of clients, policies, transactions, producers and carriers hung off the Epic structure. The table below maps each one to the job it does once the data is modelled rather than exported.
| Entity | What it holds | Role in the model |
|---|---|---|
| Clients and accounts | The insured, its contacts, servicing roles and account status | Client dimension — behind concentration, account rounding and client-level revenue |
| Policies and lines | Effective and expiration dates, line of business, premium, billing method and the renewal lineage back to the prior term | The central fact — every retention, renewal and in-force measure resolves to it |
| Transactions | Premium and commission postings, endorsements, cancellations and reinstatements | Fact for written and earned revenue, and for mid-term movement a policy header hides |
| Producers and employees | Producer of record, servicing rep, account manager and their assignment history | Producer dimension — and the key row-level security filters on |
| Carriers and writing companies | The market a policy is placed with, including intermediaries | Carrier dimension, behind concentration, mix and appointment-level review |
| Structure | Agency, branch, department and profit center — the hierarchy revenue is allocated across | The organizational dimension every total has to reconcile against |
| Lines of business and coverage codes | How the agency classifies what it sells | Product dimension for personal, commercial and benefits splits |
| Activities | Service notes, tasks, follow-ups and diaries logged against a client or policy | Workload fact behind service capacity and renewal-touch tracking |
Two details cause most of the rework. Renewal lineage is the first: a renewed policy is a new record, and unless the chain back to the expiring term is reconstructed, every renewal reads as new business and every expiry as lost business. The second is that agencies have almost always made something load-bearing out of a field nobody outside the agency would guess — a profit center that really means a program, a status code that really means non-renewed by the carrier. Both get documented in scoping and conformed into a star schema before a measure is written.
In DAX, over that star schema, against a date table covering the full history. The measures below are the ones agency leadership argues about, because each embeds a definition that has to be agreed before the number means anything. We write them explicitly and document them rather than burying the logic in a query step.
| Measure | How it is defined | Why it is written this way |
|---|---|---|
| Policies in force | Distinct active policies as at the selected date, not as at the refresh date | Lets you see the book as it stood last March instead of only as it stands today |
| Policy retention rate | Policies renewed divided by policies that came up for renewal in the period | Denominator is expiring policies, so a growing book cannot flatter the rate |
| Premium retention rate | Renewed premium divided by expiring premium over the same cohort | Separates keeping accounts from keeping revenue — they diverge in a hard market |
| Renewal book at risk | Premium expiring in the next 30, 60 and 90 days, by producer and carrier | Forward-looking, so the conversation happens before the expiration date, not after |
| New vs renewal written premium | Written premium split by whether the term has a prior-policy lineage | Growth from new accounts and growth from rate increases are different stories |
| Lost business | Expiring premium with no renewing term, attributed by reason code where captured | Makes non-renewal visible by cause rather than as an unexplained gap in the book |
| Producer book of business | In-force premium and policy count by producer of record, as at any selected date | Assignment history is respected, so a mid-year transfer does not rewrite last year |
| Carrier concentration | Share of in-force premium by writing company, with year-over-year movement | The number to take into an appointment review or a contingency negotiation |
| Policies per client | Average lines held per active account, by segment and producer | Account rounding is the cheapest growth an agency has, and it is rarely measured |
| Service workload | Open activities and renewal touches per servicing rep, against book size | Shows where capacity is genuinely constrained rather than where people feel busy |
Commission and producer compensation reporting sits alongside all of this, and we build it — but the KPI set for it belongs on our Power BI insurance agent commission dashboard page, which covers commission earned by agent, overrides, splits, chargebacks on cancelled policies and loss ratio at an agency-wide level, whichever system you bill from. This page is deliberately the technical half: getting Applied Epic data out and modelled correctly. The modelling craft underneath is the same one described on our Power BI data modeling and DAX page.
The model is published to your Power BI workspace and refreshed on a schedule following Microsoft's documented data refresh guidance (opens in new tab). Agencies with a decade of policy and transaction history normally get incremental refresh (opens in new tab) configured, so each nightly run processes a recent window rather than reloading every term the agency has ever written. Where the source is an on-premises Bulk Data Extract server, an on-premises data gateway sits between it and the service.
Producer visibility is handled with row-level security (opens in new tab), driven from a mapping table of users to producer codes, branches and departments — so a producer sees their book, a branch manager sees the branch, and the principal sees everything, all from one report. Our guide to setting up row-level security covers the configuration in detail. We test the roles against real accounts before go-live and document who can see what, which is a useful artifact the first time a producer asks why their numbers differ from the principal's.
Prepaid Flex hours that never expire: Starter is 20 hours at $125/hr ($2,500), Growth is 40 hours at $115/hr ($4,600), and Power is 80 hours at $100/hr ($8,000), with senior hourly work from $100/hour on the largest block. A first build covering book of business, retention, the renewal pipeline and producer views from a single Epic instance usually fits inside a Starter block. Multi-agency or post-acquisition work — two Epic databases to conform, carrier statements and the general ledger joined in, commission rules reconciled — generally moves an agency to Growth, because agreeing the rules takes discussion as well as development.
Most tasks ship within 3–5 business days of scope approval. You get a written scope and hours estimate before anything starts, and hours are deducted only for work you approve. Our guide to Power BI dashboard development cost covers what moves the number, custom dashboard development explains how a build is run end to end, and how prepaid Flex hours work sets out the model in full.
Independent agencies and brokerages running Applied Epic with enough history to trend and no in-house data engineer — typically the point where someone exports an Epic report to Excel every month and nobody entirely trusts the result. Agency principals, operations leaders and finance managers are the people who usually get in touch, often after a producer meeting where two reports disagreed about the same book.
Agencies of roughly ten to two hundred staff are the sweet spot: large enough that producer and branch reporting is a real monthly job, small enough that a dedicated analyst is hard to justify. Agencies that have just acquired a book and inherited a second system are a common case too. If you want the agency-wide metric set rather than the Epic plumbing, start with Power BI for insurance brokers; if you are weighing whether to hire this out at all, Power BI consulting explains how prepaid senior-led hours compare to a retainer or a headcount. Contact is form-only — send your request and we'll reply with a scope estimate.
business days, typical
hours, never expire
own everything
A first Applied Epic build usually fits inside one Flex block, and hours deduct only for work you approve. See Flex pricing →
How it works
We establish which route your Applied agreement actually covers, work out which structure fields and status codes your agency has made load-bearing, agree the retention and renewal definitions with your leadership, and reply with a written scope and hours estimate before any work begins.
A senior US-based engineer pulls clients, policies, transactions, producers and carriers, reconstructs renewal lineage, conforms everything into a star schema with a date table and the agency, branch, department and profit center hierarchy, then writes the DAX — validating totals against reports your agency already trusts.
Published to your workspace with scheduled or incremental refresh, row-level security so each producer or branch sees only its slice, and documentation you keep. We maintain it from your banked hours.
Representative perspective
Once retention was measured against expiring premium instead of total premium, one of our strongest-looking books turned out to be flat and simply riding rate increases.Agency principal, mid-size commercial lines brokerage
Representative perspective
We had the dashboards inside Epic, but half our team could not see the modules they needed and none of it sat next to the rest of our Microsoft reporting.Operations leader, multi-branch independent agency
Tell us which Applied data access your agency has, how you define retention, and the views your principals and producers actually ask for. We'll confirm the route, scope the task, and tell you the hours before anything starts — no project minimums, no obligation.
FAQ
Applied Epic — the agency management system from Applied Systems that independent insurance agencies and brokerages run their book of business on. It is not Epic Systems' electronic health record, not Epicor's ERP, and not Epic Games. If you manage clients, policies, renewals and agency accounting in Epic, this is your page.
No. Applied Epic does not appear in Microsoft's published list of Power BI and Power Query connectors, so there is no "Get Data → Applied Epic" option. Data comes out through Applied's data lake, a Bulk Data Extract copy of your Epic database, the Applied Epic REST APIs, or scheduled exports — whichever your agreement covers.
Use them for in-Epic answers. Epic Dashboards is role-gated across close to forty modules, tied to your Applied package, and built on Applied's own Looker-based analytics platform. A Power BI model earns its place when you are standardized on Microsoft, or when Epic has to sit alongside carrier statements, payroll and the general ledger.
Yes. Row-level security maps each Power BI user to a producer code, branch, department or profit center, and every visual filters to that scope automatically. Principals and operations leaders see the consolidated view from the same report, and adding a producer is a change to a mapping table rather than a rebuild.
No. We are an independent US-based Power BI consultancy with no Applied partnership to claim. We build using whatever documented, supported data access your agency already licenses and owns, and everything we produce is published into your own Power BI workspace and stays yours if we never speak again.
For the agency-wide metric set rather than the source-system plumbing, Power BI dashboards for insurance brokers is the page to read next — it owns commission, override and loss-ratio reporting. Lending and insurance distribution share the same contractual data-access problem, so mortgage lender dashboards is worth a look if you write both. Agencies that need the accounting side of the book joined in should see our QuickBooks and Sage Intacct builds, and agencies wanting reporting delivered inside a client portal rather than the Power BI service should read Power BI embedded analytics. The craft underneath is covered in data modeling and DAX and dashboard development, and Power BI consulting explains the engagement model. Every task is scoped before work begins from your prepaid block of senior-led hours — no project minimums, no change-order surprises, and hours are deducted only for work you approve.
Book a free demo
Tell us the task. We scope it, you approve, and a senior consultant delivers — typically in 3–5 business days. Prepaid hours that never expire.
The fastest way to reach us is the form — tell us the task and we'll reply within one business day.