Power BI with Me

A Cautionary Rhyme for the Analytically Inclined

On gold layers, dimensional modeling, and why your AI agent cannot squint

KIMBALL · FABRIC · POWER BI · COPILOT · DATA AGENTS

A Note Before We Begin
 Though Wex works in sales — his pipeline, his reps, his regional woes — the principles that broke his world are not a sales problem. They are a data problem. And data problems are universal.
 
 Kimball’s dimensional modeling framework, first published in The Data Warehouse Toolkit in 1996 and refined across three decades of real-world practice, was never industry-specific. Its examples span retail, healthcare, education, finance, manufacturing, insurance, government, and beyond. The methodology is not a pattern for one domain — it is a pattern for how humans ask questions of data, and humans ask questions the same way regardless of what business they’re in.
 
 Microsoft Fabric’s gold layer guidance, Power BI’s semantic model best practices, and the design expectations that Copilot and AI agents depend on are all deeply aligned with Kimball principles. Surrogate keys, declared grain, separated facts and dimensions, conformed dimensions, and model-level security are not Power BI opinions — they are thirty-year-old industry standards that Microsoft’s modern AI stack has made non-negotiable in a way they never were before.
 
 You can survive bad modeling in a static report. A human can squint and compensate.
 
 An AI agent cannot squint. It reads what is there. And it answers accordingly.
 
 So whether your data is about students, patients, donors, claims, grants, products, or parcels — Wex’s story is your story too, if you let it be.
 
 Don’t let it be.
 

Prologue

The Wumble and His Dream

In the land of the Dashboards, where the metrics all gleam,

Lived a Wumble named Wex with a big data dream.

He had pipelines! And lakehouses! And Fabric in tow!

He had data from everywhere, raring to flow.

“I’ll skip all the modeling,” said Wex with a grin,

“I’ll just dump it all in and let Copilot win!

Who needs Kimball? Who needs grain? Who needs all that fuss?

I’ll call everything a gold layer — that’s good enough for us!”

And that, my dear friends, is where trouble begins.

For the gold that’s not structured is tinsel — not wins.

Part One

The Gold That Was Not Gold

Now gold, in the land of the data design,

Is meant to be clean and semantically fine.

It’s the last transformation before meaning is made —

The layer where modeling discipline must not be afraid.

But Wex took his silver (which wasn’t great either)

And dumped it in gold and created a mirror.

He called it a gold layer. He stamped it “production.”

But naming things gold doesn’t change their construction.

His gold had no surrogate keys — just strings and GUIDs mixed.

His gold had no grain declared — every row was unfixed.

His gold had dimensions and facts in one table —

A stew so confused it was barely a fable.

“I’ll sort it in Power BI!” Wex proudly declared.

But the mess from the gold layer couldn’t be repaired

By a calculated column or a measure or two —

When the foundation is broken, the whole building goes through.

Part Two

The Downstream Disaster Begins

The data flowed onward from gold into BI,

And the problems all followed — they don’t wave goodbye.

You see, what you model (or don’t) in the gold

Is exactly the chaos your reports will hold.

Mixed grain tables made aggregations absurd —

Summing a header row with its lines? That’s the word.

Copilot said “Total Sales” — but the number was wrong,

Because grain had been muddled the whole way along.

Text-field relationship keys — “East,” “West,” “Unknown” —

Could not be relied on to join on their own.

A region spelled “EAST” and a region spelled “East”

Are not the same thing to a model, at least.

The agent tried joining. The agent fell down.

It returned “null” where revenue should have been found.

Part Three

No Surrogate Keys, No Sleep

Now Kimball will tell you — and Kimball was right —

That natural keys shift in the dark of the night.

A customer number gets reused by accounting.

A product code changes — the mismatches mounting.

Surrogate keys are integers, dumb and divine,

A stable, synthetic, reliable spine.

They don’t carry meaning — that’s why they work well.

They join without breaking. They don’t have a smell.

“My natural keys are just fine!

I’ve used them for years and they’ve always aligned!”

Until the day came when a vendor reused

An ID from ’09 — and the data got confused.

Two vendors. One key. One semantic model.

Copilot said “revenue” — it went off like a bottle.

The number it gave was the sum of them both —

A fictitious figure that nobody knows.

The board saw the chart. The board liked the chart.

The chart was completely wrong from the start.

Part Four

The Dim That Was Not a Dim

Now a dimension, dear Wumble, is not just a lookup.

It’s not a list of values you glance at and hook up.

A dimension has attributes — descriptors that tell

The who, what, and where of the thing very well.

But Wex had no dims — just flat columns instead,

With customer names typed in the fact table’s bed.

No hierarchy. No conformed. No shared definition.

Every table had “Region” with its own disposition.

“East” in one table. “Eastern” in two.

“Great Lakes Region” wherever Wex threw.

The agent tried filtering by region with care

And returned seven answers — none matching a prayer.

Conformed dimensions mean one definition shared.

One Region. One Customer. Consistently declared.

Across every fact, the same dim is applied.

That’s how drill-through works. That’s the Kimball divide.

Part Five

The DateDim He Refused to Build

Of all Wex’s sins — and the list was long-form —

The worst was the DateDim he never could form.

“I have a date column!” he said with great pride.

“It’s a text field — but dates are inside!”

“Jan-2024.” “2024 Q1.” “March.”

“3/4/24” and “04-03-2024” — what a march

Of inconsistent formats that no engine could parse

Into time intelligence. This was a farce.

No DateDim means no time intelligence at all.

No year-to-date. No rolling. No prior-period call.

Copilot asked, “What are sales compared to last year?”

The model said nothing. The silence was clear.

“But my fiscal calendar is special!” cried Wex.

“It runs on a 4-5-4 and it’s technically complex!

My periods don’t match the calendar grid —

A DateDim can’t handle the things that mine did!”

But oh, Wumble Wex — your excuse has no teeth.

The DateDim handles your fiscal calendar beneath

Its calm, structured columns, with every week named.

Your weird fiscal year is completely tamed.

A 4-5-4 is not magic. It’s math.

A DateDim will walk it right down the same path.

Healthcare plan years. Academic fall starts.

Broadcast weeks. Government quarters. All parts.

Your special calendar is not an excuse.

It’s a column in a DateDim. Now put it to use.

*(That last line was a declaration. Not a suggestion.)*

Part Six

Copilot Arrives (And Immediately Regrets It)

When Copilot came to the Wumble’s domain,

It opened the semantic model and felt the great pain.

It saw undefined measures with names like these treasures:

Sales_East_Adj_v2_FINAL

Revenue_Calc_DO_NOT_USE

Margin_OLD_keepforref

TotalThingActual_Fixed

It saw 47 measures and not one description.

It saw no synonyms to help with prescription.

It saw columns marked “sales” in six different tables

With six different definitions — all wearing the labels.

“Which sales?” asked the Copilot. “I cannot be sure.”

“The real one!” cried Wex. “It’s the one I use more!”

But “the one I use more” is not metadata, friend.

Copilot needs what you wrote down, not what’s in your head.

Part Seven

The Agent Who Could Not Be Grounded

Now agents — the AI kind, the agentic new breed —

Don’t guess at your meaning; they follow your lead.

They read your semantic model like a map through the wood.

If the map has no labels, they won’t find what’s good.

An agent needs measure descriptions to know what to pick.

It needs column synonyms so language can stick.

It needs relationships declared — direction and all.

Without these, the agent just hits a brick wall.

Wex said, “I’ll explain it each time that I ask!”

But an agent that needs re-explaining each task

Is not automation — it’s just a slow search,

A very expensive, AI-flavored lurch.

The semantic layer is the agent’s ground truth.

It’s the contract. The covenant. The permanent proof.

If your model is broken, your agent is blind,

And all the AI in the world won’t help you find

A trustworthy answer to even one question.

The garbage comes out with the greatest expression.

Part Eight

The Reckoning

So Wex sat alone in his dashboard’s dim glow,

With a Copilot that guessed and an agent that’d go

In circles and spirals and null-value dreams,

Returning wrong numbers and inconsistent themes.

His gold layer wasn’t gold — it was painted-on shine.

His dims weren’t dims — they were columns in line.

His facts held no grain — they held everything, friend.

His natural keys shifted. His measures were lost.

His time intelligence had never been crossed.

His synonyms: zero. His descriptions: none.

His agent: confused. His Copilot: done.

The business asked questions. The data said lies.

Not intentional lies — just unstructured surprise.

The most dangerous data is data that looks right

But breaks at the grain, in the join, in the night.

Part Nine

The Rebuilding

A consultant arrived — she had seen this before.

(She had, in fact, seen it at least five dozen times or more.)

She opened the model. She closed it. She sighed.

She took a deep breath, then she went back inside.

“We’ll start with the gold layer,” she said with great care.

“We’ll declare every grain before putting things there.

We’ll build surrogate keys — integers, clean.

We’ll separate facts from the dims in between.”

She built a DateDim — with fiscal columns aligned.

She conformed the dims — one Region, defined.

She gave every measure a name and description that’s new.

She declared the grain. She documented it too.

She added synonyms so Copilot could hear

When a human said “revenue” or “income” or “clear

quarterly performance adjusted for returns” —

The model would know. That’s the thing that she earns.

And slowly — oh slowly — the agents came back.

The Copilot answered and stayed on the track.

“What were our sales in Period 7 by rep and by zone?”

The answer came back — and it stood all alone

As trustworthy. Accurate. Properly grained.

A number you’d bet on. A number explained

By a model that meant what it said from the start.

That’s the Kimball way. That is also the art.

The Final Moral

Because You Deserve One

So listen up, Wumbles, wherever you dwell,

In your lakehouses, Fabric workspaces, and cells

Of Azure and Synapse and pipelines galore —

The modeling discipline is not a chore.

It is the thing that makes everything work.

From gold layer onward, the structure must hold.

Bad decisions upstream become the mold

That shapes every report, every agent, every ask —

The garbage will surface. It’s only a mask

Of time before Copilot says something wrong,

Before an agent returns a misaligned song,

Before a stakeholder trusts a number that’s false

And makes a real decision with fictitious results.

Kimball wrote this in 1996.

Microsoft Fabric arrived — and nothing got fixed

By the new shiny tooling if the thinking stays old.

The principles travel. The principles hold.

The Checklist of Things the Wumble Skipped

☐  Surrogate keys on every dimension — integers, stable, never reused

☐  Grain declared in writing at the top of every fact table

☐  Facts contain only foreign keys and additive measures — nothing else

☐  Dimensions contain only surrogate key + natural key + descriptive attributes

☐  No mixed fact/dimension tables — ever, under any circumstances

☐  A proper DateDim with fiscal columns — your special calendar is not an excuse

☐  Conformed dimensions shared across all fact tables — one Region means one Region

☐  Consistent text values — “East” is “East” everywhere, always

☐  Measure names that a human AND an AI can understand

☐  Measure descriptions filled in — Copilot and agents read these to decide

☐  Column synonyms defined for natural language queries

☐  Gold layer treated as a semantic contract — not a dump zone with a pretty name

☐  Relationship direction declared correctly in the semantic model

☐  Dead measures removed — DO_NOT_USE means delete, not rename

The End.

Kimball was right. He was right the whole time. He is still right.

Your agent is not magic. It is a mirror of your model.

The mirror doesn’t lie. Your model might.

Based on 30+ years of Kimball dimensional modeling methodology · Aligned with Microsoft Fabric gold layer guidance · Power BI semantic model best practices · Copilot + AI agent grounding requirements

Just Because Power Query Can Doesn’t Mean It Should

Power Query is incredible.

It’s one of the most powerful, flexible, and misunderstood tools in the Power BI ecosystem. It can join data, reshape it, cleanse it, deduplicate it, apply business logic, detect types, generate calendars, and perform transformations that rival full ETL pipelines.

And yet — the goal should be to use as little of it as possible.

If you open a Power BI model and see dozens of Power Query steps—or worse, complex chains of logic buried in M—it’s not impressive. It’s a red flag.

Power Query Is Not Your Data Platform

Let’s say the quiet part out loud:

Power Query is not where enterprise ETL belongs.

Especially when:

• Your data already lives in a data warehouse or lakehouse
• You have SQL, Spark, or dbt available
• The data is reused across multiple models, reports, or tools

If your organization has invested in a centralized analytics platform, then Power BI is a consumer, not a transformer.

Using Power Query as your primary ETL layer:

• Hides logic inside Power BI files
• Breaks reuse across semantic models, data agents, etc.
• Encourages one-off transformations
• Makes testing, versioning, and governance harder
• Forces logic to run per model instead of once, centrally

That’s not architecture. That’s convenience.

The Ideal Power Query: Boring and Small

My ideal Power Query looks like this:

1. Source
2. Navigation
3. Optional row limiting
   • For development, performance testing, or incremental refresh

That’s it.

If I see:

• Merging queries
• Extensive column logic
• Conditional business rules
• De-duplication logic
• Type coercion across dozens of columns
• Complex step chains with unclear intent

…I immediately ask:
Why is this not in the source?

“But Power Query Can Do That”

Yes. It absolutely can.

Power Query is astonishingly capable. I am impressed every time I see what it can do.

But architecture isn’t about capability—it’s about placement of responsibility.

Just because Power Query can:

• Define business logic
• Shape conformed dimensions
• Apply semantic rules
• Resolve grain mismatches

Doesn’t mean it should.

Those steps belong where they can be:

• Tested
• Reused
• Documented
• Versioned
• Shared

And that place is:

• SQL views
• Warehouse tables
• Lakehouse tables
• dbt models
• Medallion layers (Bronze / Silver / Gold)

Not buried inside a Power BI file.

Reusability Is the North Star

Every transformation you put in Power Query is:

• Locked to that dataset
• Rebuilt for every refresh
• Invisible to other tools
• Harder to validate

When logic lives upstream:

• Multiple Power BI models can use it
• Other BI tools can consume it
• Data science and ML can rely on it
• Governance becomes possible

If you care about scale, AI readiness, and enterprise analytics, reuse is non‑negotiable.

Complex DAX Isn’t a Flex Either

This philosophy doesn’t stop at Power Query.

I’m equally unimpressed by:

• Overly complex DAX
• Measures compensating for poorly shaped data
• Logic that exists only because the model wasn’t designed correctly

Good semantic models make DAX simple.

If you need heroic DAX to calculate something basic, that’s often a modeling or upstream data problem—not a DAX skill problem.

Power BI’s Job Is Modeling and Experience

Power BI shines when it focuses on:

• Semantic modeling
• Measures
• Relationships
• Calculation groups
• UX and storytelling

When Power BI does everything, it becomes:

• Slower
• Harder to govern
• Harder to scale
• More fragile

When Power BI does what it does best, it becomes:

• Fast
• Trusted
• Reusable
• AI‑ready

The Discipline Is the Point

Using minimal Power Query steps isn’t a limitation—it’s discipline.

It forces you to ask:

• Is this logic reusable?
• Does this belong in the source?
• Should this logic exist once, centrally?
• Is Power BI the right layer for this responsibility?

Power Query is a phenomenal tool.

But great architecture is knowing when not to use it.

If your Power Query pane is boring, simple, and almost empty—
you’re probably doing something right.

Why “Why?” is the most important question

When people hear the word why, they often brace themselves.

It can feel combative.
Invasive.
Judgmental.

As if the person asking is about to clutch their pearls and say, “I’m sorry… you want to do what with the data?”

But in data, analytics, and AI—especially when designing solutions in Microsoft Fabric and Power BI—“why” is not an attack. It’s the most important tool we have.

The Real Job of a Data Platform

There’s a common misconception that the purpose of a reporting platform is to answer questions like:

• What happened?
• How many?
• What’s the total?

Those answers are necessary—but they’re not the point.

The real purpose of your data and reporting platform is to help people understand, decide, and act. To support decisions you know you need to make today—and the ones you don’t know you’ll need tomorrow.

You can’t design for that without asking why.

“Can I Export This to Excel?”

Let’s talk about the most famous Power BI question of all time:

“Can I export this to Excel?”

Of course you can.
Power BI is a Microsoft tool—Excel is practically a sibling.

But here’s the thing: when I ask why, I’m not offended. I’m not disappointed. I’m not silently judging your life choices.

I’m curious.

Because there is always a reason.

The Many Reasons Behind “Excel”

When someone wants to export to Excel, it could be because:

• They’re simply more comfortable there
• Their team is resistant to change
• They need to merge this data with additional data
• They want to create a visualization that doesn’t exist in the report
• They need to print it
• They’ve “always done it that way”
• They’re asking for… a friend

Each of these reasons points to a different solution.

If I don’t ask why, I’m forced to design for the perceived requirement, not the actual need.

And that’s how we end up with technically correct solutions that still fail to deliver value.

Designing for the Questions You Don’t Know Yet

One of the most powerful outcomes of asking “why” is that it pushes us beyond today’s questions.

Not just:

• The questions you know you need to answer
• The questions you think you might need

But the questions you don’t even know you’ll need to ask yet.

This is where thoughtful data modeling and platform design quietly do the heavy lifting.

A Brief (non-why ask why) Side Quest: Star Schema and Intentional Design

The need/ask of tomorrow is one of the many reasons I’m such a strong advocate for dimensional modeling and star schema.

Not because they’re old.
Not because they’re academic.

But because they force intentionality.

When you take the time to define:

• Facts (what happened, at what grain, and why it matters)
• Dimensions (how the business actually wants to slice, filter, and reason about those facts)

You create a structure that is:

• Understandable to humans
• Predictable for analytics
• Scalable over time

In Fabric terms, this often shows up as well‑intentioned Gold views—not just “cleaned up tables,” but views that are purpose-built to serve a semantic model designed around a star schema.

Those Gold views aren’t for everyone to query directly.
They exist to serve the model, and the model exists to serve the user.

That chain of intent—from Bronze to Silver to Gold to semantic model—is much harder to achieve if you never stop to ask why the data is being used in the first place.

And when you do ask why, you give yourself the freedom to add new facts, new dimensions, and new business questions later—without rebuilding everything from scratch.

Why Asking “Why” Reduces Waste

Let’s be honest: there’s a lot of waste in analytics.

• Models that never quite get used
• Reports that look great but don’t drive action
• Dashboards that answer questions no one is asking
• People using a visualization or BI tool only to export the data and do their “real” analysis somewhere else

Asking why early—and often—helps surface these issues before they’re locked into the solution.

Why build a model and report that still doesn’t provide what is actually needed?

Why optimize visuals when the real problem is confidence, trust, or usability?

Why This Matters Even More in the Age of AI

As we move further into AI-enabled analytics, semantic models, and natural language querying, the importance of why increases—not decreases.

AI is very good at:

• Aggregating data
• Finding patterns
• Answering well-formed questions against a well-defined semantic model

What it cannot do on its own is:

• Understand organizational context
• Infer business intent
• Distinguish between an interesting question and a meaningful one
• Know which metrics actually drive decisions versus which are just “nice to know”

AI amplifies whatever structure and assumptions you give it.

If your model reflects unclear intent, unchallenged assumptions, or legacy “we’ve always done it this way” logic, AI will happily scale that confusion—faster and more confidently than ever before.

Asking why is how we:

• Clarify intent
• Define meaning
• Encode business logic on purpose into the semantic layer

That’s what makes AI useful instead of noisy.

Final Thought: “Why” Is an Act of Respect

When I ask why, I’m not questioning your intelligence or your intent.

I’m saying:

• Your time matters
• Your decisions matter
• Your outcomes matter

And if we take the time to understand the real need—not just the request—we can build solutions that last longer, scale further, and actually deliver value.

So yes.
You can export it to Excel. Yes, you can insert other request here.

But let’s talk about why first.

After a very long day yesterday I was thinking about thigs I wanted to do with my daughter over Thanksgiving break and it reminded me of the year that Frozen came out and I took her to the movies for the very first time the day before Thanksgiving. What does this have to do with Microsoft Fabric and Power BI, you might ask? Well… when you have AuDHD a random memory can lead to an equally random need to go down the rabbit hole on some other seemingly un-related side-quest.

I invite you all to sing along with me – I bet you know the tune…

Do you want to build a model?

A semantic one that slays?

It starts with bronze, then silver shines,

Gold refines—

Dimensional all the way!

We used to wrangle flat files,

Now we’ve got Lakehouse dreams—

With medallions stacked and clean!

Do you want to build a model?

Let’s optimize that schema throttle—

Come on, let’s stream!

(Click… click… click…)

Do you want to build a model?

With star joins that perform?

We’ll follow Microsoft’s best advice—

Kimball’s spice—

And keep that logic warm!

I think some facts need bridging—

Slowly changing types—

Let’s version Type 2 right!

(Oh, surrogate keys…)

🎶 *Bridge:*

Fundamentals aren’t optional—

No duct-tape DAX allowed!

You need clean grain, conformed dimensions,

Not just vibes—

Or clouds that make you proud!

We build with purpose, not just pipelines—

Each table tells a tale—

Of insights we unveil…

Do you want to build a model?

Let’s go from raw to gold—

And make it bold!

By: Audrey Gerred

Why I’m a Broken Record About Star Schema in Power BI

The Repetition Is Intentional

I talk about star schema again and again because it’s foundational. It’s not just a best practice — it’s the difference between scalable, performant, governable models and ones that become brittle, slow, and hard to trust. Power BI may not require it, but that doesn’t mean it’s optional.

---

Why Star Schema Is So Important

• Performance: Power BI’s VertiPaq engine uses columnar storage, which means data is stored and compressed by columns rather than rows. This allows for highly efficient scanning, filtering, and aggregation — especially when fact tables are narrow and well-structured. Star schema supports this by keeping fact tables slim and focused, while dimension tables tend to be much wider, which maximizes compression and speeds up query execution.
• Simplicity: Star schema simplifies the model by clearly separating facts from dimensions. But simplicity doesn’t mean fewer tables. If your model represents ten distinct business entities, it’s not simpler to put them into one ‘dimension’ table — it’s confusing.
• Semantic Clarity: Dimensions in a star schema represent real-world business concepts/entities (think customer information, product information, associate information, etc.). This makes it easier for users to understand the model, write DAX, and build reports. It also improves discoverability and trust — users can see what each table represents without guessing. As Mr. Kimball taught us, ‘the data warehouse is only as good as the dimension attributes’. Each entity (e.g., customer, product, profit center) deserves its own dimension to preserve semantic clarity and avoid tangled logic. For example, we shouldn’t find the name of a product that was bought in the same dimension table as the name of who it was sold to and the name of the sales-person that gets commission – these are three distinct business entities.
• Copilot & AI-readiness: Tools like Copilot rely on semantic structure to interpret natural language queries. A well-formed star schema gives Copilot the context it needs to generate accurate measures, filters, and insights. Without it, AI features become less reliable and harder to govern.

---

Why You Shouldn’t Ignore It — Even If Power BI Lets You

Power BI is flexible — but that flexibility can be dangerous. You can build snowflake schemas, flat tables, or spaghetti joins. But here’s what you risk:

• Slow performance due to inefficient joins and poor compression.
• Confusing relationships that make DAX harder to write and debug.
• Poor AI experiences — Copilot struggles with ambiguous or overly complex models.
• Certification risk — Models that don’t follow star schema are harder to endorse confidently.
• Semantic ambiguity — Mixing different business entities (e.g., customer name and profit center) into a single dimension table creates confusion. These entities often belong to different domains, have different grain, and serve different analytical purposes. When they’re lumped together, it becomes unclear what the table represents, which leads to modeling errors, misleading visuals, and broken trust.
• Storage inefficiency — Star schema supports columnar compression, which is most effective when columns contain repetitive, low-cardinality values. When models deviate from star schema — for example, by embedding dimension attributes in fact tables or creating wide, flat structures — compression suffers. This leads to larger memory footprints, slower refreshes, and degraded performance.

---

Is It Still a Star Schema If Only Part of Model Is Set Up With Star Schema?

Not really. A partial star schema is like a half-built bridge — it might work for now, but it’s not structurally sound. If some dimensions are snowflaked, if facts are mixed with dimensions, if many business entities are mixed in one dimension table or if relationships are ambiguous, you lose the benefits of clarity, performance, and semantic alignment.

---

Should a Model Be Certified If It Doesn’t Follow Star Schema?

This is where governance comes in. A certified model should meet a minimum standard of:

• Semantic clarity
• Performance
• Governability
• AI-readiness

If a model doesn’t follow star schema, it should be reviewed carefully. Exceptions might exist, but they should be rare and well-documented.

---

Should Copilot Be Used on Models That Don’t Follow Star Schema?

Technically, it can be used — but practically, it’s risky. Copilot relies on semantic structure to interpret user queries. Without a star schema:

• It may misinterpret relationships.
• It may suggest incorrect measures or filters.
• It may frustrate users with inconsistent results.

If you want Copilot to shine, give it a model that’s built to be understood.

---

Final Thought: Star Schema Is Not Just an Option — It’s a Mindset

Being a broken record about star schema means you’re advocating for clarity, performance, and trust. It’s not dogma — it’s discipline. And in a world of AI-powered analytics, that discipline matters more than ever.

By: Audrey Gerred

After almost a decade of working with Power BI and now Microsoft Fabric —earning certifications, community recognition, and the “Super User” rank in the Fabric Community forums — I’ve seen a pattern emerge (#AuDHD #iykyk): the tools are powerful, flexible, and forgiving, but¹ that flexibility often tempts users into practices that feel convenient or familiar² in the moment but sabotage scalability, performance, and clarity in the long run (you know… in production).

So, let’s talk about the difference between can and should³.

1. Denormalized Wide Tables: Yes, You Can. No, You Shouldn’t

Power BI will not stop you from building a single flat table with all your facts and dimensions crammed together. It’ll even render visuals. But ignoring star schema design is like wiring a house without a circuit breaker—works until something overloads. Just wait until you want or need to scale, optimize, or troubleshoot…

• Why it matters: Star schema supports efficient relationships, better compression⁴, and clearer semantic modeling.
• What to do instead: Normalize your data⁵. Use dimension tables⁶. Respect granularity⁷.

2. One Model Per Report (1:1): Tempting, But Misguided

Sure, you can build a separate model for each report. Power BI will not even speak up and object. But this leads to duplication, inconsistent logic, and a maintenance nightmare.

• Why it matters: Centralized models promote reuse, governance, and consistency. Who doesn’t want that?
• What to do instead: Build reusable semantic models and connect multiple reports to them.

3. Implicit Measures: Easy Doesn’t Equate to Right

Power BI lets you drag and drop fields into visuals and auto-aggregates them. That’s implicit measures. But they’re fragile, opaque, and often misleading.

• Why it matters: Explicit measures are transparent, reusable, and easier to debug.
• What to do instead: Define your measures in DAX. Name them clearly. Own your logic.

4. Measures Table: Optional, But Essential

Technically, you don’t need a dedicated measures table⁸. But skipping it is like tossing all your spices into one drawer with no labels.

• Why it matters: A measures table improves discoverability, organization, and user experience.
• What to do instead: Create a measures table. Use folders if desired. Include descriptions (HINT: Generating descriptions is a great use of Copilot).

5. Renaming Fields in Power BI: You Can, But It’s the Wrong Layer

Renaming columns in Power BI is allowed in the sense that it won’t stop you from doing it — but it’s a semantic patch over a structural problem.

• Why it matters: Renaming at the lakehouse or warehouse level ensures consistency across tools and teams and minimizes repetitive work downstream.
• What to do instead: Clean and rename upstream in views. Let Power BI reflect the source, not override it.

6. Direct Query ≠ Live Data

It is a very common misconception that Direct Query equates to real-time or streaming data. Direct Query feels “live,” but it’s only as fresh as the source.

• Why it matters: If your source updates once a day, your Direct Query visuals show stale data until the next load.
• What to do instead: Know your refresh cadence. Use Direct Query when true real-time/streaming is needed.

7. Direct Query for Everything? Please Don’t.

Power BI won’t stop you from using Direct Query for every model. But performance will. And then the complaints from users will.

• Why it matters: Direct Query introduces latency, limits DAX functionality, and depends heavily on source performance⁹.
• What to do instead: Define clear guidelines. Use Direct Query only when:
  • Data must be real-time and source supports it
  • Security requires source-level control¹⁰
  • Dataset size exceeds import limits¹¹

Even then, be sure to add manual aggregate tables and/or create hybrid tables whereby part of the table is in archive, part is import, and part is hybrid.

Final Thought: Tools Don’t Impose Wisdom—You Do

Power BI and Fabric are generous. They’ll let you build almost anything. But they won’t stop you from building it badly. That’s where experience, governance, and architectural discipline come in.

So next time you’re tempted to take the shortcut the tool allows, ask yourself: Just because I can… should I?

1. But, with great power comes great responsibility! In this case, responsible, sustainable, optimized, re-useable, and scalable semantic modeling ↩︎
2. We’ve always done it that way ↩︎
3. I say ‘can vs should’ because as a neurodivergent woman, I know saying ‘dos and don’ts’ tends to ruffle feathers ↩︎
4. A star schema model can get compression rates of up to 10x, compared to flat/denormalized tables which only yield 2-4x compression ↩︎
5. Exclamation point! ↩︎
6. Exclamation point! ↩︎
7. Exclamation point! ↩︎
8. Ditto for parameter table for dimension fields, parameter table for measures, and calculation groups (especially for time intelligence) ↩︎
9. And, still following all other Power BI best practices ↩︎
10. In other words, not because you don’t want to re-define it again in Power BI ↩︎
11. And, ensuring you are following star schema – if your model size exceeds the SKU size limitations and does not follow star schema, switching to Direct Query is not a solution. Star schema is. ↩︎

By Audrey Gerred

As a Data Engineer/Analytics Engineer, one of the most common questions I get asked is, “How do you decide what data goes into the warehouse/model when you’re starting out?” It’s a great question, and the answer is both simple and complex: I go as granular and broad as I can.

Why, you ask? Oh boy, let me tell you!

When I say I go as granular (or atomic) as possible, I mean that I dive to the most granular level of the data that I can. This approach allows me to not only meet the immediate needs and answer the specific questions that were asked of me, but also to anticipate and prepare for additional questions that might come up later. It’s all about being ready for the things we don’t know we don’t know.

By capturing data at the most detailed level, I can ensure that my model is flexible and scalable. This means that as new questions arise, I (or self-service users) can easily drill down into the data to find the answers without having to go back and rework the model. It’s like having a Swiss Army knife of data – ready for any situation!

Dimensional models are highly scalable, and the devil is truly in the details. And, you know where the details are… in your data! The more detailed your data, the more powerful your analysis can be. Think of it like building a house: the stronger and more detailed the foundation, the more robust and versatile the house will be.

But why is dimensional modeling the most viable and accepted technique for delivering data for data warehouses and business intelligence? Let’s dive into that.

• Ease of Use: Dimensional models are designed to be user-friendly, making it easier for business users to understand and navigate the data 
• Efficient Query Performance: They are optimized for high-performance querying, which is crucial for business intelligence applications that require quick and efficient data retrieval 
• Conformed Dimensions: These allow for drilling across different business processes, providing a unified view of the data 
• Scalability and Performance: Dimensional models support high concurrency and scalability, making them suitable for large-scale data warehousing and business intelligence applications 
• Flexibility: The structure of dimensional models allows for easy extension and adaptation as business needs change, ensuring that the data warehouse can grow and evolve over time 
• Proven Methodology: Dimensional modeling has been a settled debate in the industry, with established best practices and methodologies that have been refined over decades 

Using separate fact tables for data at different levels of granularity is generally the best practice in dimensional modeling. This approach helps maintain a clear and accurate data model, making it easier to manage and analyze your data effectively.

So, next time you’re starting a new Power BI project, remember to go granular and broad (but do not shove all levels of granularity into one table – I beg you!). You’ll thank yourself later when you’re able to answer not just the questions you have now, but also the ones you haven’t even thought of yet.

Happy data modeling!

By Audrey Gerred (and Doughlene)

As you may know, I am speaking at FabCon with the talented Tanvi Jaikamal about the Realities of AI in Microsoft Fabric and the ways you can leverage it to make your day more efficient. And, I am bringing dehydrated packets of my sourdough starter, Doughlene, to give away to so you can get your sourdough journey started quickly- with a copilot or accelerator. So, if you are reading this because you got some dehydrated Doughlene, here is how to rehydrate her.

Day 1: Find a glass container with a lid (like a mason jar). Weigh the jar and write that number down somewhere (don’t forget that step – it’ll be very handy to know in couple of days and going forward). Empty the packet of Doughlene into the glass container and put it on your kitchen scale, then zero it out. Now, add 30g of warm water to the container and stir together. Let this sit for about an hour or until whenever you see that the dehydrated starter has dissolved. Add the container back to your scale and zero it out. Stir in 30g bread flour (organic or not, bleached or unbleached, AP is fine too). It’ll look thick and clumpy – perfection. Grab a rubber band and place it around the jar and line it up with where the top of the mixture is (if you don’t have a rubber band, a dry erase marker will work). This will allow us to see if there is any growth (you may not have any the first day or two). Now, set the lid on it (doesn’t need to be tightened – you just want it on there so it doesn’t get dried out). Leave it on your counter and come back tomorrow.

Day 2: Is there any growth? If yes – YAY!! If not, no biggie – we still proceed! Put your container on the scale, zero it out. Add 30g of flour. Zero out the scale. Add 30g of warm water. Mix well – thick and clumpy looking is the goal. Line up your rubber band or make marker line, place lid on and leave on counter. See you again tomorrow!

Now, I have to get ready to head to Vegas for the show and finish packaging up Doughlene so I will update with the remaining day(s) when I get back. I’ll also make a video showing the steps. PROMISE!

I can’t wait to meet all of you!