A useful business intelligence model starts well before tables are connected or visuals are created. In project-costing work, I have found it important to understand what users need, where the information comes from, what it means, and how the solution will be maintained and secured.

A significant project in my work has involved building and maintaining a Power BI data model for project costing, margins, and operational KPI reporting. I can’t share the internal model or its data, so this article focuses on the general workflow and lessons rather than its confidential design.

Start with business-user requirements—and expect them to evolve

First, clarify what business users need to understand or do with the reporting. Which questions should it answer? What measures and level of detail are useful? Who needs access, and how often does the information need to be updated?

Requirements should remain open to refinement. New requests can emerge, and the available data or its structure may change. Keeping track of requests and their effect on definitions, sources, and the model helps the solution adapt without losing sight of its purpose.

Identify and inventory the data sources

Business information may be spread across many files and tables. Depending on the organization, inputs might include downloaded CSV files, documents in SharePoint folders, individual SharePoint files, existing spreadsheets, or data made available through a database or platform.

Before building the model, make an inventory: where each source lives, what it contains, who maintains it, how it is updated, and whether it is expected to change. A data catalogue, where available, can help explain fields, identifiers, relationships, and business meaning. It is a useful reference to compare with what the data actually contains, not a substitute for checking the data.

Explore the data before loading it into the model

Exploration helps reveal the real shape and quality of the sources. Depending on access and the task, an initial review might use Excel, database queries, or a Python notebook. Look at the available fields, sample values, missing information, key consistency, row grain, and how records relate.

Compare observations with the data catalogue and business context. For example, check whether a field described as an identifier behaves consistently, and whether dates or categories mean what users expect. This early discovery can surface assumptions and questions before they are built into transformations or measures.

Choose the transformation approach to fit the environment

Architecture depends on the organization’s existing data platform, budget, resources, and access. Some teams work with cloud data platforms such as Databricks or Snowflake; others may have an on-premises warehouse or relational databases such as SQL Server or MySQL. The available tools and platform determine where transformations can sensibly be maintained.

In the work described here, I carried out data transformations and cleanup in Power BI Desktop, then built the model, security, and dashboard there. Power Query in Power BI can be a practical transformation layer when a separate warehouse or data engineering platform is not available to the reporting work. That choice still calls for clear steps, checks, and a plan for refreshing and maintaining the solution.

Model for clear measures and maintainable change

Once the sources are understood, the model should represent meaningful relationships and support clearly defined measures. Establishing the level of detail in each dataset helps prevent joins or aggregations from duplicating values. Definitions should remain understandable to the people using the report.

Because requirements and source structures can change, a model also needs ongoing maintenance. A new file, field, or business request can affect transformations, relationships, measures, and reports. Rechecking those dependencies helps keep changes from silently altering existing outputs.

Include security in the model design

Security is part of a reporting solution, especially when different users should see different rows. In this project, I configured dynamic row-level security (RLS) using USERPRINCIPALNAME() to apply user-specific filters. The function provides the signed-in user identity for the security logic; the role and its access mapping still need to be configured and tested against the intended permissions.

Security should be considered alongside ongoing maintenance: changes to users, access requirements, or model structure may require the rules to be reviewed and retested. The internal role definitions and access mapping are not included here.

Validate before sharing

Validation runs throughout the work: compare results with appropriate source information, review how records combine, check that measures match their definitions, and test how filters affect the report. For a secured model, also verify that representative users see only the information they are meant to access.

This connects my accounting background with data modelling: understanding how a number was produced is essential before interpreting what it means.

Key takeaways

  • Clarify user needs and keep requirements open to change.
  • Catalogue and explore sources before designing the model.
  • Choose the data platform and transformation layer to fit available resources.
  • Plan for maintenance, validation, and access security from the start.