Connecting your CRM to Power BI without the Monday export

The report is automated. Every step of it, except the first one, which is a person exporting a CSV from the CRM on Monday morning and dropping it into a folder so the refresh has something to read.

That export is not a small remaining piece of manual work. It is the part that determines whether the number can be trusted, because it is the step where someone chooses a date range, a filter and a set of columns — and those choices are not written down anywhere.

There are three ways to remove it. They fail differently, and picking between them is really a decision about where the business logic is allowed to live.

The three routes in

The native connector is the correct starting point and is frequently dismissed too early. Power BI ships connectors for the major CRMs; authenticate, choose entities, and you have live data. For a single CRM and a handful of straightforward questions, this is the right answer and anything more is over-engineering.

Its limit is that all the modelling ends up inside the report file. That works until the second report needs the same definition of a qualified opportunity, and now the definition exists twice.

Direct API queries buy control. You choose exactly which objects and fields come across, you can filter server-side to keep volumes sane, and you can reach things the connector does not expose. The cost is that you now own pagination, authentication refresh and error handling — and CRM APIs are rate limited, so a query that works against a sandbox with two thousand records can fail against production with four hundred thousand.

A warehouse in between is the option people resist because it sounds disproportionate. It is the only one of the three that solves the problem the other two work around: CRMs store current state, not history. When a deal moves from £40,000 to £25,000, the CRM shows £25,000 and the previous value is gone. Any question of the form "what did the pipeline look like at the end of last quarter" is unanswerable without somewhere that kept the record.

If you are unsure whether that applies to you, the warehouse question is usually a definitions question first.

The refresh is the part that actually breaks

Reports rarely fail at build time. They fail three months later, quietly, and the failure looks like a number that is simply out of date.

The recurring causes are worth knowing in advance:

• Credentials expire. An account's password change or an expired OAuth token stops the refresh. If the connection was authenticated as a named individual, it also breaks permanently when that person leaves — a genuinely common cause of a dashboard going stale with no obvious explanation.
• The gateway. On-premises or gateway-routed sources depend on a service running on a machine somebody has to maintain. Machines get rebooted, patched and decommissioned.
• Rate limits. A full reload of every record each morning will eventually collide with the CRM's published API limits as volumes grow. Incremental refresh — pulling only what changed since the last run — is the fix, and it needs a reliable modified-timestamp field.
• Schema drift. Someone adds a required field, renames a picklist value, or deletes a custom field that a query depended on. Nobody tells the report.

The mitigations are unexciting and effective: authenticate as a service account rather than a person, turn on refresh failure notifications to a shared mailbox rather than one inbox, use incremental refresh once volumes justify it, and check the refresh history occasionally rather than waiting for someone to notice a wrong figure in a meeting.

Where the logic should live

This is the decision that outlasts the connector choice.

When definitions live inside a report — what counts as qualified, which stages are open pipeline, how currency is converted — they are invisible to everyone who reads the output and impossible to reuse. The second report gets built by copying the first, and the copy is subtly different. Within a year there are four dashboards answering the same question four ways, and meetings begin with reconciling them.

When definitions live upstream — in the warehouse, or in a shared semantic model — there is one place to read them, one place to change them, and every report inherits the change. That is the actual argument for the warehouse route, and it is a governance argument rather than a technical one.

The practical rule: anything another report might need belongs upstream of the report. Formatting and layout stay in Power BI. What a qualified opportunity means does not.

What automated should mean

A useful test, because "automated" is doing a lot of work in most descriptions.

A report is automated when:

1. 01Nobody touches it between the CRM and the dashboard. No export, no manual upload, no monthly re-pointing at a new file.
2. 02A failure announces itself. Someone is told the refresh failed, before a reader finds a stale number in a meeting.
3. 03It survives its author. The connection is not tied to one person's credentials, and the definitions inside it are written down somewhere other than that person's memory.
4. 04A definition changes in one place. Changing what counts as qualified pipeline is one edit, not six.

Most reporting described as automated satisfies the first and fails at least one of the rest. The third is the one that quietly matters most, because it is the difference between reporting infrastructure and someone's personal file that the business has come to depend on.

Starting sensibly

Start with the native connector and one report. If the questions stay simple and singular, that is genuinely where it should end.

Move to a warehouse when one of three things is true: you need history the CRM does not keep, more than one source has to agree on the same figure, or several reports have started disagreeing because each carries its own copy of a definition. Those are the signals — not the size of the company and not the data volume.

Building the dashboards themselves is the last step and the easiest one. Reporting dashboard design covers what belongs on the surface, and automated reporting covers the pipeline underneath it.