Business Intelligence Development Best Practices

01Purpose and scope

This manual supports the course Business Intelligence Development. It is written for developers, analysts who build their own reports, and team leads who review and release that work. It describes how the job is actually done in most organizations: turning business questions into governed data models, pipelines, reports and dashboards that people can trust and use.
The scope covers the full build lifecycle. That includes gathering requirements, identifying source systems, designing a dimensional or semantic model, writing extraction and transformation logic, building visuals, testing numbers against a known baseline, releasing to users and supporting the content afterward. It does not cover the deeper statistical work of data science, the administration of database servers, or the purchasing of software licenses, although a BI developer often needs to understand each of those areas well enough to work with the people who own them.
Practice varies widely by context. A small finance team with one spreadsheet export and a single monthly review needs far less structure than a retail group with dozens of sources, hourly refreshes and regulatory reporting. This manual describes the common core and notes where the trade-offs change.

02Core principles

Start from the decision the report supports. A dashboard that shows many metrics but does not tell a manager what to adjust this week is decoration. Before building anything, write down who will use the output, what action they might take, and how often they will look at it.
Keep one definition for each measure. If revenue means booked revenue in the sales report and recognized revenue in the finance report, users will spend meetings arguing about the difference rather than acting on it. Record the definition, the filters, the time basis and the source of truth in one place that the whole team can reach.
Separate storage, logic and presentation. Raw data should be loaded with minimal change, transformation should be visible and testable, and visuals should read from a clean model rather than from raw tables. This separation makes it possible to fix a calculation once and have every report inherit the fix.
Prefer simple, reproducible logic over clever shortcuts. A calculated column that a colleague can read in two minutes is worth more than a single expression that only its author understands. Build for the person who inherits the work, not only for the person who wrote it.

03Planning and preparation

Begin with a requirements conversation that produces a short written brief. The brief should name the business owner, the intended users, the questions the content must answer, the required refresh frequency, and the acceptable latency between an event happening and it appearing on screen. When owners cannot agree on a definition, record the disagreement as an open item instead of silently picking one.
Inventory the sources. For each source, note the system, the owner, the access method, the grain of the data (for example one row per order line or one row per customer per day), the expected volume, and known quality issues. Ask the owner whether the source has an audit trail or a change log, because late corrections are a common cause of numbers that move after a report has been shared.
Agree on the environment and the tools before writing much logic. Most teams separate development, test and production spaces, and use version control for queries, model files and report definitions. Confirm who can publish to production, how credentials are stored, and whether personal or confidential fields may be copied out of the source system at all.
Set the acceptance criteria early. Good criteria are specific: the total of order value for the prior closed month must match the finance ledger to the cent, or the dashboard must load within a stated number of seconds for the typical user. Agreeing on these before the build prevents later debates about whether the work is finished.

04Recommended workflow

1
: Write the business question and the metric definitions in plain language, then get written sign off from the business owner. Attach a sample of the expected output, such as a mock screen or a table with example rows, so that the owner and the developer picture the same result.
2
: Profile the source data. Check row counts, null rates, duplicate keys, date ranges and the distribution of key categorical fields. Record anything that does not match what the owner described, because those gaps determine most of the rework later.
3
: Design the model. For reporting workloads, a star schema with fact tables at a clear grain and dimension tables for customers, products, dates and locations is a common and well understood pattern. Document the grain of each fact table in the model itself so that a reviewer can check it without reading the query.
4
: Build the extraction and transformation logic. Load raw data into a staging area first, then apply cleansing and conformance rules in separate, named steps. Handle late arriving records, deleted records and changed keys explicitly rather than hoping they will not occur.
5
: Create the measures and visuals. Put calculations in the model layer so that every visual uses the same logic. Keep each dashboard page focused on one decision area, label units and time periods on every chart, and avoid truncated axes or colors that convey meaning only to people with typical color vision.
6
: Test against a baseline. Choose a closed period where an independent figure exists, such as a ledger total or an approved report from the previous system, and reconcile the new output to it. Investigate every difference until it is explained. Explained differences can be accepted with sign off, unexplained ones cannot.
7
: Release, announce and monitor. Publish to the production space, set the refresh schedule, confirm that the refresh completes and that failure alerts reach a person who can act on them, and tell users what changed, what the known limits are and where to send questions.

05Quality checks, risk controls and compliance

Quality checks should run at several points: on load, after transformation, and on the final measures. Typical checks include row count comparisons against the source, uniqueness of primary keys, referential integrity between facts and dimensions, reasonable ranges for amounts and dates, and reconciliation totals. Automate the checks that run on every refresh and keep their results visible to the owner.
Access control is a risk control. Apply row level or object level security where users should see only part of the data, and test the security with accounts that represent each role. A report that shows the right numbers to the wrong audience is a breach, even if the arithmetic is correct.
Personal data needs particular care. Many jurisdictions regulate how personal information is collected, stored, combined and shared. Examples include the General Data Protection Regulation in the European Union and the Health Insurance Portability and Accountability Act rules in the United States for health information. Confirm current obligations with your privacy office or legal counsel, because the rules change and depend on where the data subjects live.
Financial, health, legal, tax and safety reporting is regulated or high risk. This guidance does not replace qualified professional judgment, approved procedures or required authorization. When a figure will be used for a filing, a clinical decision, a legal position or a safety action, the owner of that process must approve the definition and the method, and the BI output should be treated as one input to their controlled process.
Keep an audit trail of changes to logic, data sources and security settings. Version control, release notes and a record of who approved each publication are usually enough for internal reporting, but ask your governance team what they require for regulated content.

06Collaboration and documentation

Work closely with the business owner, a data steward who understands the source systems, and the people who will support the content after release. Hold short review sessions at the end of each build stage rather than one large review at the end. Early feedback on a mock layout or a single reconciled measure costs far less than redesigning a finished dashboard.
Document the model, not just the report. A useful data dictionary lists each table, column, measure and filter with its business meaning, owner, source, refresh time and known limitations. Add a short lineage note that shows how a headline figure is derived from source fields, because that is the first thing an auditor or a skeptical manager will ask about.
Write release notes in plain language. State what changed, which users are affected, whether any historical figures were restated, and who to contact. Restated history is a frequent source of friction, so flag it clearly and give the reason.
Agree on a support model before release. Decide who answers questions about a number, who fixes a broken refresh on a weekend, and how change requests are logged and prioritized. Without this, a successful launch often turns into a backlog of unanswered messages.

07Measures and continuous improvement

Track indicators for the content and for the process. Useful content measures include the number of active users, the share of users who return within a set period, the time between a data event and its appearance on screen, and the count of open data quality issues. Useful process measures include the number of defects found after release, the share of refreshes that complete without manual intervention, and the elapsed time from a signed off requirement to a published version.
Interpret these measures with care. High usage does not prove that a report is correct, and low usage can mean the audience is small but important. Use the numbers to start conversations with the owner, not to rank developers.
Review each published item on a regular cycle. Ask whether the questions it answers still matter, whether the definitions still match current policy, and whether any source has changed. Retire reports that nobody uses, because each stale report adds maintenance load and a chance of confusion.
Build a backlog of improvements from the defects, the support requests and the user feedback. Group them into fixes, enhancements and structural changes, because structural changes such as remodeling a fact table affect many reports and need a planned release.

08Common mistakes and how to prevent them

A frequent mistake is building visuals before agreeing on definitions. The result is a polished dashboard that gets rebuilt after the first meeting. Prevent it by writing the metric definitions and getting sign off before opening the report designer.
Another mistake is mixing grains in one model. A table that holds order lines and order headers in the same fact table will double count totals when someone sums the order value. Prevent it by declaring the grain of every fact table and reviewing any join that changes it.
Teams often skip reconciliation because the new numbers look plausible. Plausible is not the same as correct. Prevent it by reconciling to an independent total for at least one closed period before release, and keeping the reconciliation workbook with the release record.
Hard coded values cause quiet errors. A filter that excludes a region by name will not update when the region is renamed or split. Prevent it by keeping business rules in maintained reference tables with an owner and a change date recorded in the table itself.
Security is often added at the end and tested only with the developer account. Prevent it by testing each role with a representative user before release and by reviewing the security model whenever a new audience is added.
Finally, many teams treat release as the finish line. Without monitoring and a support owner, errors surface through executive complaints rather than alerts. Prevent it by assigning the support role and the refresh alerts as part of the build, not as an afterthought.

09Practical checklist

Confirm the business owner, the intended users and the decision the output supports, and record them in the brief.
Write each metric definition with its filters, time basis and source of truth, and obtain written sign off.
Inventory each source with its owner, grain, volume, access method and known quality issues.
Profile the source data and record every gap between what the owner described and what the data shows.
Build the model with a documented grain for every fact table and a clear set of dimensions.
Separate staging, transformation and presentation logic, and name each transformation step.
Run automated quality checks on every refresh, including row counts, key uniqueness and reconciliation totals.
Test security with representative accounts for each role before publication.
Reconcile at least one closed period to an independent figure and explain every difference.
Publish release notes, update the data dictionary, confirm the refresh schedule and name the support owner.

★Learning sources

Micro Degree: Business Intelligence Development
Micro Degree: Data Analytics Applications
Micro Degree: Azure Cloud Fundamentals
Micro Degree: Google Cloud Platform Basics

?Frequently asked questions

How much technical depth does a BI developer need in SQL and modeling?

You need enough SQL to join, filter, aggregate and test results reliably, and enough modeling knowledge to choose a grain and build clear dimensions. Many teams expect developers to read query plans when reports are slow, but the exact expectations depend on the organization. Ask your team lead which skills the role is measured on before planning your learning.

Should measures live in the report or in the data model?

For anything that more than one report uses, put the measure in the shared model so every visual reads the same logic. Report-only calculations are acceptable for layout helpers or one-off exploration, but they should be labeled as such. Moving a measure into the model later is usually easier than untangling conflicting copies across many reports.

What should I do when two business owners give conflicting definitions of the same metric?

Record both definitions, the reasons each owner gives, and the reports that depend on each one. Escalate the decision to the person who has authority over the metric, such as a finance or operations leader, and do not choose silently. Until the decision is made, label the affected figures with the definition they use.

Do the same practices apply to a small team with spreadsheet data?

The core steps still apply, but the controls can be lighter. A single analyst may skip formal release management, yet should still document definitions, reconcile to a known total and keep a copy of the previous version. The main risk in small settings is an undocumented spreadsheet that becomes a critical report, so review that possibility regularly.