- A report is built in six phases: brief, data, build, checking the numbers, handover and upkeep. Each phase has its own output, which the next one builds on.
- The brief produces a list of metrics with business definitions. The build turns those definitions into DAX calculations over the semantic model.
- In our experience, first reports usually run on exports from the source systems. A data warehouse comes later, as sources and history grow.
Contents 8 sections
- 01How long does a report take?
- 02Brief
- 03Data
- 04Build
- 05How do you check the numbers in a report?
- 06Handover
- 07Upkeep
- 08FAQ
How long does a report take?
Microsoft splits building a Power BI solution into five steps: gathering requirements, planning the deployment, a proof of concept, cycles of development and validation, and deployment with support (BI solution planning). For a single report, the steps fit into six phases. The times in the table are our rough estimate from our own projects.
Where the time on a report goes, our rough estimate
| Stage | What happens in it | How long it takes |
|---|---|---|
| Brief | The questions the report should answer, definitions of the numbers, the people who will use and check it | Hours to days, depending on how fast people agree |
| Data | Access to sources, exports, cleaning and joining | A few hours on a good data warehouse or small data, days when starting from scratch on larger data |
| Build | Power Query, semantic model, DAX calculations and visuals | Hours to several days |
| Checking the numbers | Comparison with what the company calculates today, and an explanation of every difference | Hours to days, depending on the number of metrics |
| Handover | Publishing, access, scheduled refresh and documentation | Minutes to hours |
| Upkeep | New requirements, changes in sources, more metrics | Ongoing |
Microsoft lists the output of gathering requirements as a list of business metrics with definitions and the expected aggregation, and a solution design in the form of a mockup or prototype.
Brief
For the brief, the people who will use the report get together and show how they work with the numbers today. Microsoft recommends also going through the existing solutions, that is, the Excel files and reports the company takes its numbers from today (gathering requirements). The brief covers:
- The questions the report should answer
- The value it brings the company
- Definitions of KPIs and metrics in business terms
- A person who will help check that the results are correct
- A person who will actually use the report
- And of course the data sources
Definitions are the most work in the brief. Revenue, margin or a completed order often mean different things in sales, in finance and in the warehouse. The report calculates by one definition, and the company chooses which one.
The output of the brief is a list of metrics with definitions, a list of people for checking, and a mockup of the report. The technical design then maps each metric to columns in the source and to the calculation logic.
Data
Power Query connects to Excel files, SharePoint, Dynamics 365, databases and other sources through connectors (Power Query connectors). A source in your network needs an on-premises data gateway for scheduled refresh (refresh through a gateway). The guide Power BI and data on your own servers describes how to reach data on your own server.
In our experience, first reports usually run on regular or one-off exports. Data flows that bring in data without manual work are built step by step. A data warehouse with documented tables and columns shortens the data work, as the table above shows.
The output of this phase is access to every source in the brief and a mapping of metrics to specific tables and columns.
Build
The build has three layers, each building on the one before. Power Query loads and transforms the data and saves the transformation steps as a query that runs again at every refresh (what is Power Query). Typical steps are combining exports, filtering out blank rows and setting data types.
The semantic model organizes the data into dimension and fact tables. Relationships carry filters from dimensions to facts (star schema). DAX measures in the model capture the agreed metric definitions (DAX). On top of the model, the report takes shape with pages, visuals and filters. The parts of Power BI are described in the guide What Power BI is and who it is for.
Margin by region needs a region on every sales row. In a model with a customer table, a relationship carries the region to sales, and the margin measure stays the same. A flat export from the ERP has to include region as its own column.
The output of the build is a Power BI file with the queries, model, measures and report pages.
How do you check the numbers in a report?
Checking compares the values in the report with a known baseline, usually the numbers the company works with today. Microsoft also counts functionality, performance and security as part of validation, for example a successful scheduled refresh and row-level security (content validation).
Checking goes metric by metric. A difference between the report and Excel usually comes from the same number having been calculated in two places in two ways. For each difference, the cause is written down and the company decides which definition holds.
The output of checking is a list of metrics, each marked "matches" or with an explanation of the difference, and a sign-off from the person who knows the current numbers.
Handover
The report is published from Power BI Desktop to a workspace in the Power BI Service (publish from Desktop). Access is controlled by workspace roles, sharing by link, or an app for a wider group of people (sharing and collaboration). People open the report in a browser or in the Power BI mobile app.
Scheduled refresh sets how often and at what times the semantic model refreshes, up to eight times a day on shared capacity. When a refresh fails, an email goes to the model owner and any other addresses you enter (data refresh). After four failures in a row, Power BI turns off the refresh schedule.
The documentation describes where the data comes from, how each metric is calculated, who has access and who handles a failed refresh.
A report's documentation fits on one page: data sources, how each metric is calculated, a list of who has access, and a contact for when the morning refresh fails.
Upkeep
Upkeep handles new requirements, changes in sources and more metrics. A refresh in the Power BI Service fails when a column or table is renamed or removed in the source (data refresh). The fix happens in Power BI Desktop, and the model is published again.
As things grow, so do the number of reports and the people who build them. Microsoft describes how less experienced authors often build a new semantic model for every report, so the same calculation ends up existing in several versions (managed self-service). A certified shared model marks data that meets the company's quality standards (content certification). New reports then connect to the shared model.
FAQ
How long does the first report take?
In our experience, the build itself takes hours to several days. The length of the whole job depends mainly on the brief and the state of the data; times per phase are in the table above.
Do we need a data warehouse?
No. In our experience, first reports usually run on regular or one-off exports. A data warehouse or data platform comes in when sources multiply, exports take up time, or you need to keep history.
What do we need to prepare?
The questions the report should answer and the value it brings the company. Then access to the data or exports, a person who knows the current numbers and will help check them, and a person who will use the report.
Why does the report show different numbers than our Excel?
Usually because the same number has so far been calculated differently in several places. During checking, every difference is explained and the company decides which definition holds.
Where do you stand?
Data Probe
€840excl. VAT
One day with the people who work with the numbers at your company. Within three working days you get one page: which systems and numbers you have, the three biggest findings and one recommended next step with an estimate in days and in euros.
