Power BI with Me

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!

If you’re diving into the world of data analysis and visualization with Power BI, you’ve probably come across terms like “data types” and “data formats.” At first glance, they might seem like technical jargon, but understanding these concepts is key to making the most out of your data.

In this blog post, we’re going to break down the difference between data types and data formats in Power BI. We’ll explore why they’re important, how they impact your reports, and share some practical tips to help you get it right.

1. What are Data Types? Let’s start with data types. In simple terms, data types define the kind of data you are working with. Power BI supports various data types to help you categorize and manage your data effectively. Here are some common data types you’ll encounter:

• Number types: These include Decimal number, Fixed decimal number, and Whole number. They are used for numerical data, such as sales figures or quantities.
• Date/time types: These include Date, Time, Date/Time, Date/Time/Timezone, and Duration. They are used for time-series data, such as transaction dates or event durations.
• Text type: This is used for textual data, such as names or descriptions (keep in mind, Power BI is case insensitive, but Power Query is case sensitive).

Power BI automatically converts data types from source columns to optimize storage and calculations, ensuring your data is handled efficiently.

2. What are Data Formats? Now, let’s talk about data formats. While data types define the kind of data, data formats determine how that data is displayed. Think of data formats as the presentation layer that makes your data more readable and meaningful. Here are some common data formats in Power BI:

• Currency: Displays numerical data as currency, with appropriate symbols and decimal places.
• Percentage: Displays numerical data as a percentage, making it easier to understand proportions.
• Scientific notation: Displays large or small numbers in scientific notation for clarity.
• Custom formats: Allows you to define specific formats, such as date formats like MM/DD/YYYY or DD-MM-YYYY.

Data formats help you present your data in a way that makes sense to your audience, enhancing the overall readability of your reports.

3. Importance of Correct Data Types and Formats Choosing the right data types and formats is crucial for accurate and efficient data analysis. Here’s why:

• Data accuracy: Correct data types ensure that your data is interpreted correctly, preventing calculation errors and data inconsistencies.
• Performance: Proper data types and formats optimize storage and processing, improving the performance of your Power BI reports.
• Visualization: Appropriate data formats enhance the readability and visual appeal of your reports, making it easier for your audience to understand the data.

Incorrect data types or formats can lead to issues such as calculation errors, visualization problems, and overall confusion. So, it’s essential to get them right!

4. How to Specify Data Types and Formats in Power BI Now that we understand the importance of data types and formats, let’s look at how to specify them in Power BI:

• Determining data types: In Power BI Desktop, you can determine a column’s data type using the Power Query Editor or the Table view/Report view. Simply select the column and choose the appropriate data type from the dropdown menu.
• Applying data formats: To apply data formats, select the column or measure you want to format, and use the formatting options available in the Modeling tab. You can choose from predefined formats or create custom formats to suit your needs.

These steps will help you ensure that your data is correctly typed and formatted, leading to more accurate and visually appealing reports.

5. Practical Examples and Use Cases Let’s bring it all together with some practical examples and use cases:

• Sales report: Imagine you have a sales report with columns for sales figures, transaction dates, and product names. Using the correct data types (e.g., Decimal number for sales figures, Date for transaction dates, and Text for product names) ensures accurate calculations and sorting. Applying appropriate data formats (e.g., Currency for sales figures, custom date format for transaction dates) enhances readability.
• Customer analysis: In a customer analysis report, you might have columns for customer names, ages, and purchase amounts. Using the correct data types (e.g., Text for customer names, Whole number for ages, Decimal number for purchase amounts) ensures accurate data representation. Applying appropriate data formats (e.g., Currency for purchase amounts) makes the report more understandable.

These examples demonstrate how using the right data types and formats can improve the accuracy and clarity of your Power BI reports.

Understanding and correctly using data types and data formats in Power BI is essential for accurate and efficient data analysis. By paying attention to these elements, you can ensure that your reports are not only accurate but also visually appealing and easy to understand.

So, the next time you’re working on a Power BI project, remember to choose the right data types and formats. Your data (and your audience) will thank you!