Paul Turley's SQL Server BI Blog

My new book “Microsoft Power BI Data Analyst Associate Study Guide” from O’Reilly Press is really two books in one. When O’Reilly approached me about writing a study guide for the PL-300 exam, one of the most popular Microsoft certification exams, I said that I wanted to be a two-part book to help exam candidates join the thriving Power BI community followed by a guide for using Power BI in real-world applications in accordance with industry best practices. The second half of the book is focused on how to apply best-practice design after learning the Power BI building blocks. Some of the content in the second half of the book is updated and elaborated material from earlier blog posts and 2025 posts I will reference in this blog series. As of this post in January 2026, the online book is available as an early release through the O’Reilly subscription service and scheduled to be delivered through booksellers like Amazon early this year.

I had the pleasure of hosting eight hours of two-hour-long study group sessions for the Microsoft-sponsored Fabric Data Days in November and December. The event was coordinated and promoted by the Fabric and Power BI Community. As a group, we reviewed live demonstrations and preparation materials for the DP-600 Data Analyst Engineer exam. I’m proud to say that all of the attendees who participated in the four sessions have since passed the exam!
Whether you or your employer are interested in certification, Microsoft have done a remarkable job identifying three important roles related to BI and analytical reporting projects. There are three certification exams that effectively define the roles associated Power BI and the Fabric data platform.
Enterprise BI Solutions Need Analytics Engineers…

How do Copilot and Agentic AI change the way business users will interact with organizational data? Is the platform ready and are we ready to embrace it? AI agents and assistants are part of our daily lives. LLM-driven agents like Alexa, Siri, Gemini and Grok provide entertainment, perform requested tasks and answer questions with varying degrees of accuracy and reliability. Sometimes they misunderstand and provide out-of-left-field responses for simple questions. I might ask: “Show me the best practices for my model.” and get: “Of course! Here’s a detailed guide on runway walking techniques and how to maintain a confident posture during a fashion show.”

This series of posts will focus on how to prepare Microsoft Fabric and Power BI semantic models to support effective self-service reporting and analysis using Copilot.

It is no secret that the AI revolution has started. Most every modern business application has an agent, a copilot or assistant asking to help write a document or take meeting notes. Nearly every banking or telecom customer service site greets us with a chatbot. Microsoft Teams meetings are concluded with Copilot summarizing the discussion and talking points. Like Chat GTP, Copilot serves as a next-level search engine that can spit-out code snippets and summaries from blog posts and product documentation, but what’s next? When can I say “hey, Copilot, gather up data from our company line of business databases and forecast next year’s profitability by month and region.” Will AI reach that level of sophistication? Before we get into AI, let’s cover a topic that is super basic and super important in this context.

Fabric Solution Architecture and Power BI…

Both Azure DevOps and GitHub are supported Git hosts for Power BI and Fabric workspace integration. I will demonstrate using GitHub rather than Azure DevOps because, it is free for personal use. Getting started with GitHub is quite easy, and anyone can have a free GitHub account. Simply navigate to http://GitHub.com and sign-up for an account. You may create a personal or organizational account, and your personal account can be associated with multiple organizations.

After you have an account, sign-in and create a new repository (repo). A repo may be public or private and can, optionally, be part of an organization. This is the Create a new repository page in GitHub.

Each repo generates a URL with the owner or organization name and the name of the repo. Since I created this as a public repo, you can access it by navigating to http://GitHub.com/paulturley/AirlinePerformance. It’s as easy as that!

GitHub provides excellent documentation and support resources on the website and I recommend that you familiarize yourself with these resources to learn about basic functionality and site navigation. We only need basic functionality so don’t get lost in the details.

For the business intelligence professional, DevOps can be a confusing topic because it intersects with many disciplines and philosophies. I’m hopeful that a bit of reflection on my own experience over the years provides some valuable insight. I have seen DevOps and version control implemented on dozens and perhaps hundreds of projects, with different degrees of sophistication and control.

If you work in a software development environment where DevOps is practiced as part of your team’s development culture, you know that DevOps truly is a holistic methodology, including practices can be very extensive and regimented. Software development tends to be a linear process, whereas data and BI projects are more iterative in nature. Frankly, DevOps purists can be down-right militant about enforcing all the rules, which many BI specialists tend to skirt, in an effort to move quickly.

If you are a business intelligence analyst or developer and suddenly find yourself working on a team where DevOps is practiced, you will likely find the process to be more strict and less flexible than typical ad hoc BI development. The key is to strike the right balance for your organization’s needs.

There is certainly a flavor of DevOps that seem to be over-engineered and counter-productive. DevOps practices exist because they address critical needs in a software development environment, but you should find the right balance for your organization’s project needs. Be mindful that the very purpose for business intelligence is to deliver insights and reporting insights directly to business users, which entails quick iterations through the entire process – from requirement refinement to report delivery, with several steps in-between.

I just read that the Miss Universe contestant from Panama was evicted from the Miss Universe pageant. I don’t know…

I work with hundreds of consulting clients who go through the same cycles, having the same experiences, facing the same challenges, many making the same mistakes, and many learning some of the same lessons. The purpose of this series is to share those lessons with you.

Universal Best Practices

If we sum up Power BI best practices at the highest level, it is that all projects of any scale and size should adhere to the same general set of guidelines, categorically. Your Power BI project no matter how small or large should address every category in the following list. The specific design patterns and practices will vary significantly depending on scale and purpose. The following guideline categories will frame this and related future posts in this series:

In my view, projects and teams of different sizes have different needs. I described DevOps maturity as a pyramid, where most projects don’t require a sophisticated DevOps implementation, and the most complex solutions do. The DevOps maturity is a progression, but only for projects of a certain scale. One of the following options might simply be the best fit for a particular project.
Unless you are throwing together a simple Power BI report that you don’t plan to maintain and add features to, the first and most basic managed project should start with a PBIX file or Power BI Project folder stored in a shared and cloud-backed storage location.
DevOps isn’t a requirement for all projects, but version control and shared file storage definitely is.

Please come to the Atlanta SQL Saturday BI Edition in February, and join me for a full-day preconference event: Designing and Developing Enterprise-scale Solutions with Power BI

Are you in the California Bay area? Do you want to learn to do Power BI and Fabric the right…

The enterprise Business Intelligence and IT community have been anxiously awaiting this capability for many years and it has finally arrived – at least in Preview form for testing and prototyping real Power BI CI/CD DevOps solutions.

Microsoft Fabric (#MicrosoftFabric), the new Unified Analytics platform, was announced this morning, literally moments before the Build Conference. Satya made the official announcement in the keynote address. Why is this big news and why is it important? In a few words, this is the most significant expansion to the Power BI and the analytics platform since the product was introduced. Satya said that Fabric is the most significant data platform innovation since SQL Server.

There is no secret about this. If you do any legitimate research about Power BI (reading blogs, books or training from reliable sources), you will quickly learn that a lot of basic functionality requires a dimensional model, aka “Star Schema”. This is a hard fact that every expert promotes, and self-taught data analysts either have learned or will learn through experience. So, if everyone agrees on this point, why do so many resist this advice?

Perspective is everything. I didn’t understand why getting to the star schema was so out of reach so often until I was able to see it from another perspective. There are a few common scenarios that draw source data into different directions than an ideal dimensional model.

Table partitioning has long been an important task in semantic model design using SQL Server Analysis Services. Since SSAS Tabular and Power BI models are built on top of the SSAS architecture, the pattern of partitioning remains the same as it has been for twenty years. However, the specific methods for implementation have been fine-tuned and improved. The reasons for partitioning large fact tables mainly include:

Improve refresh speed,
Prevent reloading historical records,
Capture updated history,
Reduce database resource load

You don’t have to have massive tables to benefit from partitioning. Even tables with a few hundred thousand records can benefit from partitioning, to improve data refresh performance and to detect source data changes. There is little maintenance overhead, so the benefits usually outweigh the cost, in terms of effort and management.

Colleagues: if I have sent you to this post, it is because I respect you and want to make sure…

A Gantt chart is an excellent example of where Paginated Report & SSRS were an ideal choice for the purpose. It is a running list of activities with the duration for each displayed as a horizontal bar depicting the beginning and ending day along a horizontal scale. The challenge is that this is not a standard chart type in either Power BI or SSRS/Paginated Reports. Furthermore, project planners may prefer to see activities as rows in the format of a printed page.

I started a series of blog posts back in 2020 about best-practice guidelines for planning and designing enterprise reporting solutions with Power BI. To make the topics covered in this series of posts easier to find and follow, they are listed on this page: Doing Power BI The Right Way – for Enterprise Reporting | Paul Turley’s SQL Server BI Blog which you can access from the main menu on the blog. We have a few more topics to go so check back and subscribe for notifications.

When should we use Power BI vs Paginated Reports for our reporting needs? You can use either tool to create different styles of reports for different purposes, but should you? Just because you CAN doesn’t mean you SHOULD.

• Can we use Power BI to create operational or transactional style reports?
• Can we create scorecards and dashboard-style reports with SSRS/Paginated Reports?
• Should we use a Power BI dataset as a data source for a Paginated report?

The following diagram depicts the relative capabilities and design purpose for these two reporting tools. See the overlap? On the left, you see that Power BI is optimized and best used to create interactive, visual reports, scorecards and dashboard-style reports. Contrast this with Paginated Reports on the right side, which is best used for “list-type” reports that continuously flow across multiple pages. SSRS/Paginated Reports was architected and works ideally with SQL queries, where filter values are passed into the query using parameters.

The December 2021 Power BI Desktop update introduced a long-awaited upgrade to the partitioning and Incremental Refresh feature set. The update introduces Hybrid Tables, a new Premium feature that combines the advantages of in-memory Import Mode storage with real-time DirectQuery data access; this is a big step forward for large model management and real-time analytic reporting.

Last year I wrote this post about the new composite model feature in Power BI that enables datasets to be…

You may not know that I have been travelling the country interviewing community leaders from the Microsoft data platform, on…

I just wanted to give a big big Thank You to Adam Saxton and Patrick Leblanc at GuyInACube.com for inviting me to participate in the Power BI Livestream yesterday. It was a blast and an honor to be hang with you the Saxton clan (even the sheep and the great dane!)

Only the most dedicated data professionals are willing to invest their personal time and spend any part of a weekend…

This question comes up every few years in SQL Server Reporting Services. Of course, in Power BI Paginated Reports, we…

That’s right! I’m starting a new blog site today. Just to be clear, it will not replace this one. I…

Part of the the series: Doing Power BI the Right Way Later in this post, you will find a 27 minute…

First and most importantly, I want to acknowledge and thank these contributing authors who wrote report recipes and working samples…

At last, the second edition of “SQL Server Report Recipes” written by Paul Turley, Robert Bruckner and a host of…

Part of the the series: Doing Power BI the Right Way (link) Although my professional focus is building enterprise-scale BI solutions,…

Part of the the series: Doing Power BI the Right Way (link) Power BI, more than any other Microsoft product in…

About three years ago when the data model development engineers from the Power BI product team told me they were…

If you’re like me right now, you need a distraction. I got started on a new blog and tweet about…

Part of the the series:  Doing Power BI the Right Way (link) Data Modeling 101: part 1 (more advanced techniques…

My wife bought me a nice telescope for Christmas. I’ve wanted one since I was a kid. We opened gifts…

Back in the day, when we created BI solutions, reports and data models were separate. If you created a cube or Tabular model with Analysis Services, it was developed with Visual Studio and deployed to a server. Reports could be authored and deployed to the report server, separately. Now, with Power BI Desktop, you author your data model and report in the same development space and then deploy the whole kit and kaboodle to the service at once.

Second post in the “Doing Power BI the Right Way” series. In a business intelligence solution, data must be shaped and transformed. Your data is rarely, if ever, going to be in the right format for analytic reporting.

When starting a Power BI project, you have many choices to make. Decisions like how to source your data, where and how to create queries to cleanse, transform and reshape the data; where and how to create calculations and the nuances of modeling are just the tip of the iceberg.

This week, Christian Wade from Microsoft announced on the Microsoft Power BI blog that a new ribbon “External Tools” was added to the preview features of Power BI Desktop in the July update. In earlier posts, Christian has promoted community developed tools such as Tabular Editor, DAX Studio and the ALM Toolkit that can be used as replacements or extensions to the Power BI development experience. These three tools are autmatically added to the ribbon by default if they are installed, but you can add your own applications by following the instructions in the this Microsoft document titled Using external tools in Power BI.

…for BI data model development.
For several years, Visual Studio has been my go-to tool for designing semantic data models used for Business Intelligent reporting. Back in 2005, I used the Business Intelligence Development Studio (BIDS) Visual Studio add-in for SSIS, SSRS and SSAS projects to develop BI solutions with multidimensional cubes. In 2012 when Microsoft began the transition from on-disk cubes to in-memory SSAS Tabular models, I used SQL Server Data Tools (SSDT) to create tabular models. It was a rocky road at first. The Tabular design was fragile to put it mildly.

Using the XMLA endpoint, the Power BI Premium service now includes the capabilities of SQL Server Analysis & Azure Analysis Services combined with newer data modeling capabilities of Power BI. Data models published to the Power BI service now support version control, scripted builds and team application life cycle management, enterprise IT tooling and scripted object management.

Is there any good news in the COVID-19 Coronavirus data? Everyone is familiar with the tragic growing number cases and fatalities globally and in most countries that have high populations. However, several countries have increasing recovery rates. You can click here to view the new report shown in the image below.

The experts keep taking about looking for “the peak”, when the number of people recovering from infection increases faster than new cases. We’re not there yet but where is the recovery rate increasing? The “Recovery Rate (3 Day Change)” measure is the Recovery Rate (e.g. The number of confirmed cases divided by the number of recovered cases) from three days prior to the most recent report date. This report ranks countries by this measure.

For a new series of posts to include tutorials and “how to” demonstrations for Power BI, SQL Server Reporting Services (SSRS) and Paginated Reports, I need your help to know what questions are most compelling and top of mind.

Please scroll to the Comments section at the bottom of this post and help me create a list with your questions and suggested topics.

There are now three different ways to access the COVID-19 Daily Updates Power BI dataset & report:

• Use this link to view the report published using Power BI “publish to web”
• Add the complete COVID-19 app to your Power BI tenant from Microsoft AppSource (see instructions below)
• Download the .PBIX file from this GitHub repository and make your own enhancements

This is a Power BI report that I have developed and published with public access to the web to be shared with anyone who needs access to this information. It contains daily updates from the Center for Disease Control using data curated by the John Hopkins University Center for Systems Science & Engineering. To the best of our collective ability, the data is accurate but I cannot make any guarantees. Please validate with other sources before making any decisions with this information.

I’m very hopeful that the this report will be a valuable resource. It’s been a labor of love and as usual, considerably more work that I envisioned. But, I will continue to work on enhancements and corrections as I am able based on feedback. Please post comments here or contact me through Twitter, if you have feedback, comments and questions.

At the time of this post, the world is dealing with a serious health and economic crisis. The COVID-19 Corona Virus is impacting the lives of people around the world and in turn, it is affecting world markets and industries and many different ways. This demonstration shows you how to create a multi-layer map to correlate current outbreak case locations with your own data, using Power BI and the ESRI map visual.

This post and the video walk-through demonstrates how the order of steps added to a query can make a big performance difference and drastically effect the number of steps generated by the designer.

Activity monitoring and troubleshooting with the new logging API is focused entirely on Power BI tenant events like dashboard, interactive and paginated reports views, deployments, errors and data refresh events. This should be easier than before, enabling admins to be more proactive by track usage patterns. In a short series of blog posts, I’ll demonstrate how to build a complete activity logging and reporting solution for your entire Power BI tenant.

As I read the many posts from those in the community who I follow, I am reminded that the community…

As I continue to explore and develop best practices for managing serious business-scale Power BI solutions, I’m having conversations with recognized community leaders. Last month I chatted with Ásgeir Gunnarsson on the SQL Train ride from Portland to PASS Summit in Seattle. Ásgeir is a data platform MVP and seasoned Business Intelligence expert from Reykjavik, Iceland who works as Chief Consultant for Datheos, a Microsoft-focused BI and Analytics consultancy in Copenhagen. He leads the Icelandic Power BI User Group and PASS Chapter.

Team file sharing and version control may be managed through Power BI workspace integration with OneDrive and SharePoint team sites. In this post, I will show you how to get started and setup a new project.

This is a series of posts and exercises from a recent full-day workshop called Next Level Power BI. Check back for additional lessons and freely post your questions and feedback in the comments section after this post.

Power Query is a modern data acquisition and transformation technology and it is the “Get Data” component of Power BI and many other Microsoft tools. The query script language of Power Query is known as “M” or the Microsoft Data Mashup language. The menu options and selections in the Power Query Editor interface build this “M” script for you but to use Power Query at an expert level, you must understand fundamental concepts of the language. The post will step you through some essential mechanics of the language to build the necessary fundamental skills.

Enabling the modern data analyst by becoming shopkeepers rather than gatekeepers of information

I really appreciated the opportunity to sit down and talk with one of my heros in the industry. Donald Farmer has been recognized leader in the Business Intelligence and Analytics industry for the past two decades and his thought leadership is even more relevant today. He played a key role in shaping the Microsoft BI toolset as a Principal Program Manager from 2001 when the products we use today were in their infancy.

Power BI table and matrix visuals can render SVG object elements in a column or measure to display in-line web content. You can build dynamic content like KPI indicators, databars and sparklines by altering SVG element content using DAX expressions. In this post, I will show you how to create databars that show negative and positive values with different color fills based on category values. Of course, Power BI has built-in databars in the conditional formatting options for numeric columns but you cannot alter the color and other attributes dynamically – at least not yet. For the time being, this technique provides more flexibility than the built-in databar feature.

In this first post in a series called “Next Level Power BI”, I demonstrate how to create dynamic databars using a Scalable Vector Graphic. A dynamic SVG databar, icon, sparkline or practically any imaginable graphic can be produced with calculated column or measure.

Is the 2010, first edition of my SQL Server Reporting Services Recipe Book so snooze-worthy that it is now being…

These are copies of my presentation decks from the sessions today at the Power Platform World Tour event in Vancouver,…

The purpose of this article is to outline a set of guidelines and recommended practices for managing Microsoft Power BI projects. This guide is primarily focused on the work performed by the IT-managed BI Solution Developers for formally-managed BI projects.

Again, this guide is primarily focused on the work performed by the IT-managed BI Solution Developers for formally-managed BI projects. Topics included in this guide:

• Solution Architecture
• Managing Power BI Desktop Files
• Datasets and Reports
• Version Control & Lifecycle Management
• Workspace and App Management
• Data Model and Power Query Design Guidelines
• Dimensional Design
• Query Optimization
• Managing Dataset Size with Parameters
• Implicit and Explicit Measures
• If Users Need Excel, Give them Excel
• Certified & Shared Datasets
• Enterprise Scale Options

I was just working on a client project where we have several complex measures that reference other measures. Sometimes it…

Power BI licensing options are a mystery to many. It’s not that it is complicated; in fact, on the contrary,…

I am happy to join several world-class speakers on July 23 & 24 for the Power Platform Virtual Conference. I will be speaking about how to create “no-code” Power BI custom visuals along with Mike Carlo from PowerBI.tips.

Report navigation is, in my opinion, the essence of true data exploration and discovery. It lets business users see and understand important information in different forms; in summary and in detail, within context. SSRS and Power BI are truly a dynamic duo for reporting and data discovery. I wanted to post a follow up to these two articles because they are apparently very popular topics. Posted two days apart on my blog, they received 2,744 and 3,356 views. Woo!

— report recipealso see: Drillthrough from Power BI to an SSRS Paginated Report In the recipe titled “Drillthrough from Power…

How can you find the cause of slow performance issues in Power BI? This post demonstrates some of the advanced…

It’s very rare to have the opportunity to learn DAX from the world’s leading expert but we will have that…

Let’s step through a few very simple things that every Power BI new user needs to know. Power BI is…

Our little “Data Geeks” Meetup group in Vancouver, Washington is growing with a line-up of heavy-hitters and bigger crowds. This…

Short (2-4 minute) video posts to help you learn about the Microsoft Azure analytics and data platform. In November of…

Last night I was scheduled to present this topic at the Power BI User Group in Portland except the meeting…

I’ve been remiss about blogging for a little while and have some great stuff to share, starting with this interview with Allen White at the PASS Summit conference in November. Allen is a 15-year PASS veteran and one of the most consistent SQL Server experts in the industry. He’s a powerhouse of knowledge, experience and wisdom. He’s a long-time SQL Server and Microsoft Data Platform MVP and owner of DataPerf Professionals Consulting, mentor, coach, trainer, co-founder and leader of the Ohio North SQL Server user group. He’s served on the PASS Board of Directors as the Program Director.

I had a chance to catch up with two of the three Matts from the Power BI product team at…

At the heart of every a Business Intelligence reporting solution is a data model, to optimize queries and enable ad hoc report interactions. Data modeling technology has evolved quite a lot over the past 20 years or so. You have several choices and options depending on the simplicity or formality of your project, and factors like data size and security. In the past, choices were simpler. Power BI was the choice for smallish, “good enough” projects and when data quality, high volume and exactness were the priority, SSAS was the better choice. Now, using Power BI for modelling larger data sets is even advantageous with new features like hybrid models, aggregations and incremental data refresh.
I’ve been a quest to find the best medium to break these choices down into simple terms but it truly is a journey and not a destination.