A dashboard earns its keep when it answers real decisions with reliable data and clear owners. Power BI won't fix contradictory definitions or messy master data: before you design a single chart, agree on your KPIs, build a reusable semantic model, control permissions, and decide who certifies, publishes and maintains each piece of content.
Start with decisions
For every page of the dashboard, define:
- Who uses it.
- What decision it drives.
- How often.
- Which metric it needs.
- What threshold triggers action.
- Who is accountable.
If a chart doesn't change any decision, it's probably not needed.
Define KPIs
Each metric needs its own definition sheet:
| Field | Example |
|---|---|
| Name | Gross margin |
| Formula | Sales − attributable cost |
| Source | ERP |
| Period | Monthly |
| Owner | Finance |
| Breakdowns | Customer, product and channel |
| Threshold | Variance against target |
| Limitation | Cost pending close-out |
This stops every report from inventing its own formula.
Semantic model
SMEs should centralise shared dimensions and measures: calendar, customer, product, company, salesperson, sales and costs. A star schema usually simplifies both performance and consistency.
Critical transformations get documented. Power Query should never become an invisible chain that only one person understands.
Data quality
Before publishing, check:
- Row counts against source.
- Accounting totals.
- Duplicates.
- Dates and time zones.
- Null values.
- Units and currencies.
- Unmatched master records.
- Closed periods.
The dashboard shows its last refresh date and its known limitations. Incomplete data doesn't get dressed up with design.
Architecture and refresh
Choose between import, DirectQuery or a mixed approach depending on volume, latency, source and capacity. The on-premises gateway needs an owner, high availability where relevant, managed credentials and monitoring.
Refresh frequency should match the decision it supports. Refreshing every five minutes adds no value to a monthly KPI, and it raises the cost.
Workspaces and lifecycle
Keep development, test and production separate. Workspaces need a purpose, an owner and groups, not uncontrolled individual permissions.
The usual flow is:
- Development.
- Technical review.
- Business validation.
- Publication.
- Monitoring.
- Retirement.
Changes to measures go through regression testing.
Security
Microsoft notes that row-level security (RLS) restricts rows for Viewer users, but it doesn't apply the same way to workspace Admin, Member or Contributor roles. That's why report consumers should never get edit roles just to view content.
Controls to apply:
- Identity groups.
- Least privilege.
- RLS tested with "Test as role".
- Sensitivity labels and DLP.
- Export controls.
- Auditing.
- Periodic review.
- Automatic offboarding.
RLS can't fix an architecture where sensitive data gets duplicated into local files.
Governed self-service
Self-service works within limits:
- Certified models.
- A catalogue.
- Shared definitions.
- Training.
- A sandbox.
- A process to promote content.
- Community support.
Not everyone who builds a chart should publish it company-wide.
Design
- One question per visual.
- Clear hierarchy.
- Colour with meaning.
- Visible units.
- Comparisons and context.
- Accessibility.
- Mobile performance.
- Tables for detail.
Avoid gauges, 3D and decorative colours. Accuracy matters more than visual impact.
Adoption
Worth tracking:
- Active users.
- Return visits.
- Load time.
- Unused reports.
- Questions answered.
- Decisions and actions.
- Support tickets.
- Excel exports.
A spike in exports can signal that the report doesn't really cover the process.
Performance
- Trim columns.
- Avoid needless cardinality.
- Efficient measures.
- Aggregations.
- Incremental refresh.
- Limit visuals per page.
- Review queries and gateway.
Set a load-time target and measure the 95th percentile, not just one local test.
Minimum governance
| Role | Responsibility |
|---|---|
| Business owner | Definition and sign-off |
| Data owner | Quality and access |
| BI developer | Model and report |
| Administrator | Tenant and capacity |
| Security/DPO | Permissions and privacy |
| Support | Incidents and operations |
90-day plan
Days 1 to 30
Decisions, KPIs, sources and quality.
Days 31 to 60
Model, security, prototype and validation.
Days 61 to 90
Production, training, metrics and retiring parallel spreadsheets.
Common mistakes
- Starting with the charts.
- Redefining the KPI in every report.
- Giving consumers the Member role.
- Not reconciling with the ERP.
- Relying on a single person.
- Publishing from Desktop with no lifecycle.
- Refreshing too often.
- Ignoring the gateway.
- Creating hundreds of reports.
- Measuring views, not decisions.
Checklist
- Decisions and audience defined.
- KPIs defined.
- Shared model built.
- Quality reconciled.
- Workspaces and lifecycle established.
- RLS and groups configured.
- Gateway and refresh reviewed.
- Accessible design.
- Adoption and performance measured.
- Owners and retirement defined.
FAQ
Does Power BI replace the ERP?
No. It consumes and models data; the ERP remains the operational source of truth.
Does RLS protect every user?
It must be tested by role. Microsoft clarifies that RLS applies to Viewers, not to workspace edit roles.
How many reports does an SME need?
The minimum that covers its decisions. A shared model with a few well-maintained reports beats many.
Official sources consulted
- Microsoft: implementation planning.
- Microsoft: BI strategy.
- Microsoft: RLS.
- Microsoft Fabric Adoption Roadmap.
Summum Sistemas can help define KPIs, the model, governance and rollout of Power BI.