Livadata

As Fabric adoption blurs the traditional lines of BI, I’m seeing a rising need for BI developers to sharpen their data modelling skills. Designing a good star schema has always been central to successful Power BI delivery, but as we become key players in delivering end-to-end solutions, our ability to shape the data model matters more than ever. I’ve always pushed for early BI involvement, as early influence = less rework later. Historically, entire platforms were delivered by architects and engineers before anyone asked the BI team how they’d use it. Fabric changes that. With Power BI and Fabric finally aligned, we’re not on the side-lines anymore. So how do we step up and hold the pen?

The first in my new Data Modelling for BI Teams series starts with my high level thoughts on the 7 key steps which are, as ever, rooted in business value. 🪄

Step 1 – The Spell of Intent: Understand the Why 🧙‍♂️

A key principle of Kimball modelling is Business-Driven Design – model the business story, not the data. When tackling a new data model design, I encourage teams to first walk away from the data all together. Understand why the model is needed.

• Who is going to use it? What is their job? What’s hard about their day-to-day? What does success look like and how do they measure it? How do they interact with data? Who are the customers of their insight?
• Always ask this key question “If you had a magic wand, and waving it would fix anything – what would it fix?” Prompting discussion in this way allows your stakeholders to remove barriers they assume exist, remove all cloudy context of project scope for just a moment, to give you the real clarity about what they need. I’ve seen some really fantastic requirements (that were actually deliverable!) come from this line of questioning.

Step 2 – Create your Spellbook: Define Metrics & Attributes 📜

Now that you understand the business context, it’s time to get specific. Document your metrics (KPIs, calculations, numbers) and attributes (dimensions you filter or group by). Start with what exists: legacy reports, Excel sheets, dashboards, and validate with stakeholders. Make sure each metric has a natural language definition, not just a formula.

• Use real language for your definition. If you had to explain this metric in a meeting, how would you say it?
• This is where legacy migrations and optimisations can get real interesting. This is your chance to flag discrepancies and facilitate business alignment before building anything where metric definitions or attribute groupings are not consistent.
• If you’re starting from scratch, pull these definitions out through conversation and wireframes. Ask how they measure success, then pin down logic together.
• Don’t forget attributes: product names, regions, dates – anything you slice or filter by. “Sales by Product” is not a metric. Sales is the metric, and Product is simply an attribute we splice by. Time intelligence? Same rules. “Last Year” isn’t a metric, it’s a way of slicing time. Use calculation groups where possible to keep things clean and consistent.

Finally, get your work validated. Let stakeholders sign off on what’s been defined—and where possible, assign owners to your most important metrics. A little governance here saves a lot of pain later.

Step 3: Follow the Magic: Map your Data Sources 🧭

At this stage, it’s finally time to start getting into the weeds of the actual data we will be working with, and not just what we want it to end up like. I like to stick to the Metric & Attribute Catalogue I have just created. Trace the data source, table, fields required for each metric and attribute. Some will be calculations from other metrics or groupings of other attributes – in this scenario I like to note a “Base Measure” or “Base Attribute”, and only document the lineage of these, leaving a streamlined document that isn’t bloated by repeating information.

In legacy migrations, or optimisations / rationalisations of current reporting, this stage can be a time sink. Unpicking daisy chained logic with no documentation, unpicking stored procs in SQL databases nobody has dared to touch since Dave the contractor built it in 2017… it’s scary business!

Depending on your timeline and priorities, time-box this step. Set a clear limit on how long you spend mapping each source. You don’t need to unravel every last join in legacy code. Be ruthless with prioritisation. Focus your efforts where it matters most to your delivery. I often see at this stage an almost masochistic need to pour over the sheer wonder of bad code someone else has put together can be. But it’s not a horror movie we can’t look away from – we have to keep coming back to why it’s relevant, and if it really matters. How things were transformed in the old world doesn’t matter all that much if we are delivering anew.

Step 4: Conjure the Structure: Define Conceptual & Logical Model 🔮

As you have completed Steps 1, 2 and 3, many Power BI pros will probably already start to have seen a star schema take shape in their brain. Our metric catalogue and the process of understanding the data that drives them may have prompted assumptions of the fact tables we need, and the attribute catalogue will have given you a first draft of dimensions we need.

This stage, is where we formalise that design. Our conceptual model is where we agree the “things” that will exist in our model. Transactions, Events – Orders, Leads, Shipments, and the things we describe them by – Customers, Products, Categories, Dates.

We then define our logical model – which is where we define what is actually in the “things” and how they relate to each other. I often find at this stage the conceptual model can change. Where you identified Products and Categories as 2 “things”, looking at grain and relationships in a Power BI context may push the decision to flatten these into one table. The trade-off of repeating Category against each Product and creating some redundant data is one worth making when weighing against 2 keys in your fact table where you can have 1, and against a Snowflake Schema with bidirectional relationships. I often find this is where having BI people involved in the data modelling process can save miles of rework as the nuance of Power BI needs versus traditional data modelling best practice can sway these sorts of decisions.

This is the stage where we determine naming conventions, and define keys. There is a lot more to say on Conceptual and Logical Data Modelling in a Fabric world, but we shall park that for later in the series.

Step 5: Forge the Alchemy: Build your Physical Data Model ⚒️

Now it’s time to turn your design into reality. Build the tables, columns, and relationships in your Lakehouse, Warehouse, or Power BI model. Whether using Direct Lake, import mode, or composite models, focus on making it performant, scalable, and easy to use.

Keep these in mind:

• Multiple Fact Tables = Waterfall Schemas: The old “one fact table to rule them all” idea comes from early cube designs, where each added fact made models complex and slow. But today, especially with Fabric and Power BI, you can have multiple fact tables flowing like a waterfall as long as you share common dimensions. This flexibility helps avoid conflicting grains and messy designs.
• Know When to Split Models: Trying to cram every metric and attribute into one “Enterprise Model” often leads to bloated, hard-to-manage datasets. Use composite models to create focused, purpose-built datasets that can work together in reports.
• Be Mindful of Usability: Facts should have clear, discrete purposes. Overlapping metrics across many facts can overwhelm users. Shared, conformed dimensions keep slicing consistent and user-friendly.
• Prep for AI: Clean, well-structured models make Copilot and AI features work smoothly. Prepare your data to support smarter insights. Prepare your data for AI – Power BI | Microsoft Learn

Build models that fit the business, keep things clean and performant, and don’t be afraid to challenge old rules when it means a better outcome.

Step 6: The Trial of Truth: Test & Validate your Model 🛡️

Now, for the moment of truth. You’ve built your model – can you actually use it? For many BI professionals, our test of a model is when we begin building reporting. An ideal flow would see Power BI model builds roughly 1-2 weeks ahead of a report build, whether you are taking on both tasks yourself, or working in a team. You can create some great collaborative rhythms by pairing up visualisation focussed developers with BI engineers – testing models in real time against visual requirements, and developing the model iteratively.

Checklist of truths to test:

• Metric Accuracy: Do calculated values reconcile against current version of the truth? If someone has a trusted spreadsheet, you better match it, or have a compelling reason why you don’t. The latter being a common occurrence where a platform like Fabric has been introduced and improves data quality and reporting accuracy.
• Dimensional Completeness: Are all expected categories, hierarchies, and filters working as users expect them to? Are slicers giving the right breakdowns? This is where to ensure to test against features like Auto-Exist.
• Row Count Reconciliation: Do facts match expected volumes? Are any rows mysteriously dropping off due to filters, joins, or transformations?
• Time Intelligence: Test date-based filters and calculations thoroughly. If you’ve implemented calculation groups for “Last Year”, “YTD”, and so on, verify that they’re rolling correctly across different metrics.

In a self-service model scenario, testing may look a bit different. Inviting some business champions to workshop report builds in a real time UAT workshop can drive effective feedback loops. I’ll share more on collaborative validation and testing playbooks later in the series.

Step 7: Casting your Spell: Launch your Model & Drive Adoption ✨

And so we close with the bit few take time to account for – now to embed your model in the fabric of the organisation, and secure the adoption your hard work deserves. This is the bit where good models go to die if we’re not intentional. You don’t want to spend weeks crafting a clean, beautiful semantic layer only for it to gather dust while everyone clings to that one Excel sheet Janet in Finance built in 2019. How do we bring it to life?

Document it (yes, actually do it): I’m not talking about a 30-page Word doc that no one reads. Keep it accessible. Data dictionary, metric definitions, logic explained in plain language. Bonus points if it’s linked directly from reporting or an app!

Run launch workshops: Take stakeholders through what you’ve built. Show them how to self-serve. Talk through use cases. This isn’t training – it’s adoption. If people see value, they’ll use it. Help people get comfy with Copilot.

Make it visible: Certified datasets, naming conventions, and workspace structure all help here. Don’t make users guess which model to use. Label it clearly, share it widely, and get champions across the business talking about it.

Feedback loops: Encourage it, welcome it, build it in. Even if the feedback is just “can we get this slightly differently?”, that’s gold. It means they’re in there, trying things, and starting to trust it.

Support, not control: We want self-service, not chaos. Lock down what needs protecting, but let analysts build on top. Provide golden KPIs and dimensions they can drag into their own reports without needing to rebuild the wheel, striking the right balance between Controls and Innovation.

And finally, keep talking about it. Keep showing people how the model solves real business problems. Keep sharing wins. Because the launch isn’t a one-off moment – it’s the start of the model living in your organisation.

🧙 Summary: The Model is the Magic

Data modelling doesn’t need to feel like wizardry. It’s just about asking the right questions, getting under the skin of what people actually need, and building something that works in practice – not just on paper. With Fabric pulling BI teams closer to the centre of delivery, we’ve got a real chance to influence the data model from day one.

These seven steps aren’t a rulebook – they’re just a solid place to start. If you stay rooted in the business need, keep it clean, and work with your users, you’ll avoid a lot of painful rework down the line.

This is the first in my “Data Modelling for BI Teams” series, and I’ll be digging deeper into each step in future posts. Next up: getting more from “Understanding the Why” and running a proper stakeholder workshop.

Until then, happy modelling. And if it ever does feel like wizardry? Just remember: the real ✨magic✨ is making it all feel simple.