Paul Turley's SQL Server BI Blog

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.