Developing Large Power BI Datasets – Part 3 – Detail Tables

Power BI is architected to consume data in a dimensional model, with narrow fact tables and related dimensions. Introducing a big, wide table in a tabular model is extremely inefficient. It takes up space and memory resources, impacts performance, and complicates measure coding. Flattening records into a flat table is one of the worst things you can do in Power BI and a common mistake made by novice Power BI users.

Developing Large Power BI Datasets – Part 2 – Partitioning

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.

Developing Large Power BI Datasets – Part 1

When planning a Power BI solution, how can we plan for scale and growth? Like any technology, Power BI has limits but Power BI can manage a surprising large value of analytic data. We also have tools and companion technologies capable of handling workloads of data that alongside Power BI.

The Right Tool for the Job
You wouldn’t use a screwdriver to hammer a nail and you wouldn’t use a chainsaw to assemble an Ikea cabinet; so why would you use Power BI Desktop to create a detailed shipping manifest? It’s not the right tool for the job. The Power BI platform contains tools that are appropriate to use for different reporting scenarios and using them together will yield far better results than forcing tools to behave differently than they were intended.

Power BI Object Names – Why Standards Are Important

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

Getting Data Into Shape for Reporting with Power BI

Even for small, informal BI projects, shaping the data into a dimensional model alleviates complexity, speeds up slow calculations and reduces the data model storage size. I conclude this post by reviewing seven data architectures and the data shaping methods with different degrees of scale.

Use Paginated Reports to Create a Gantt Chart

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.

Doing Power BI The Right Way – for Enterprise Reporting

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 to Use Paginated Reports vs Interactive Power BI reports

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.

Hybrid Tables, Incremental Refresh and Table Partitioning in Power BI

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.

Are We There Yet? …Composite Models Using DirectQuery Over Power BI Datasets

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

Microsoft Data Community Interviews and Advice about Working Remotely

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

Learn Databricks, ADF & Paginated Reports at the PASS Community Summit

Seven days and counting… For the past eighteen years, thousands of data tech professionals would travel to the PASS Summit…

Guy In A Cube Power BI Livestream

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!)

Power BI Live Stream this Saturday

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

A Developer’s Guide to Creating Bad Power BI Projects – Part 1

It occurred to me that we have put so much effort into promoting best practices and proper design that there is far less information about how to create bad reports and data models. In that light, the purpose of this article is to talk about what to do if you want things to go poorly and make sure your projects fail – if not immediately, then sometime in the future – and if not for you then for whoever inherits the work that you have done.

How to Name Worksheets in a Paginated Report Exported to Excel

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

New Blog: Data On The Road

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

Doing Power BI the Right Way: 10. Designing and Managing Large Datasets

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

Power BI Data Modeling Sessions

This is going to be a very busy week for presentations Iam presenting with five sessions scheduled on the topic…

Drill-through from Power BI to Paginated Report – Report Recipe #4

Navigation between reports is the hallmark of an interactive reporting solution, enabling the ability to drill-through and see relevant details…

Paginated Reports Recipe eBook – first two recipes

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

Paginated Reports eBook Finally Released

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

Doing Power BI the Right Way: 4. Power Query design best practices

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

Doing Power BI the Right Way: 4. Power Query in Dataflows or Power BI Desktop

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

A First Look at Gen 2 Composite Models with Live Power BI Datasets

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

Power BI: The Week in Graphics

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

Doing Power BI the Right Way: 5. Data Modeling Essentials & Best Practices (1 of 2)

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

Composite Models Gen 2 and DirectQuery over Power BI Datasets

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

Power BI Expert Resources

Where to go for best practice advice for Power BI: The Data Gods One of the most common questions I…

Doing Power BI the Right Way: 7. Planning for separation – data models and reports

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.

Doing Power BI the Right Way: 6. Validating data model results – Part 2

Moving important business data into a data model for analytic reporting can often be a two-edge sword. Data retrieval is fast and can support all kinds of analytic trending and comparisons. But, data in the model may be one or two layers away from the original source data, making it more challenging to compare with familiar user reports. Often the first validation effort after transforming and loading data into the model and then visualizing the initial results is having a business user say “yeah, that looks about right.” Then, sometime later after more development and extensive user testing, the feedback might be “hmm, that looks a bit off.” …not exactly scientific.

I have been doing a lot of data validation work lately – both formally and informally. Informally: Validating calculation results from a Power BI data model is just a normal part of the development process. Formally: After developing and delivering an entire business dashboard solution, a formal validation process is used to validate the ongoing results after future data refresh cycles and to certify reports so that business leaders know they can trust them.

Doing Power BI the Right Way: 6. Validating data model results – Part 1

Moving important business data into a data model for analytic reporting can often be a two-edge sword. Data retrieval is fast and can support all kinds of analytic trending and comparisons. But, data in the model may be one or two layers away from the original source data, making it more challenging to compare with familiar user reports. Often the first validation effort after transforming and loading data into the model and then visualizing the initial results is having a business user say “yeah, that looks about right.” Then, sometime later after more development and extensive user testing, the feedback might be “hmm, that looks a bit off.” …not exactly scientific.

I have been doing a lot of data validation work lately – both formally and informally. Informally: Validating calculation results from a Power BI data model is just a normal part of the development process. Formally: After developing and delivering an entire business dashboard solution, a formal validation process is used to validate the ongoing results after future data refresh cycles and to certify reports so that business leaders know they can trust them.

Doing Power BI the Right Way: 9. Choosing the right report type: analytic or paginated

Writing this blog series while juggling other commitments has brought me to two realizations: 1. Posting articles in the series will be sporadic, and 2. The topics will be in fairly random order based on the projects I’m working on and the topics that are most top-of-mind at the time.

This brings me to the subject of this post: Paginated and Analytic reports.

Doing Power BI the Right Way: 2. Preparing, shaping & transforming source data

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.

Doing Power BI the Right Way: 1. Futureproofing Power BI solutions

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.

Power BI External Tools: Reading the Tea Leaves

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.

Doing Power BI the Right Way

This is an introduction to a series of posts and pages that will provide a comprehensive set of best practices for successful Power BI solutions. In previous posts. Let’s start with a simplified flowchart and condensed decision tree. This first whiteboard drawing is the first half of the Power BI design process, ending with the data model, before measures, visualization and solution delivery. There is a lot more but I think this is a good starting point. Let’s start the conversation here and then I will enhance this post with a more complete list of topics.

I Am Done Using Visual Studio …for BI data model development

…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.

Demystifying the Power BI XMLA Endpoint

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.

Countries with Improved COVID-19 Recovery Rates

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.

I Need Your Questions about Power BI and Paginated Reports

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.

New Coronavirus (COVID-19) Daily Updates Report

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

COVID-19 Coronavirus Daily Updates in Power BI

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.

Correlating Corona Virus Outbreaks with Business Data using the ESRI Map in Power BI

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.

Power BI Query Performance & Query Diagnostics

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.

Getting Started with the New Power BI Activity Log API

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.

Curator’s Corner: December 2019

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

A Conversation with Ásgeir Gunnarsson about Power BI in the Enterprise

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.

Setting up Power BI project Team Collaboration & Version Control

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.

Next Level Power BI – M Language Basics

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.

Chat with Donald Farmer at PASS Summit 2019

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.

Next Level Power BI – SVG Databars

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.

Next Level Power BI – SVG Symbols

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.

So, I wrote a bedtime book now?!

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

Power Platform World Tour Vancouver Presentations

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

Power BI Project Good and Best Practices

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

Power BI Product Suggestion: Show Measure Dependencies

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

Power BI Licensing

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

Power Platform Virtual Conference

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.

Drillthrough Navigation Between Power BI and SSRS Paginated Reports

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!

Drillthrough from Paginated Report to Power BI

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

Drillthrough from Power BI to an SSRS Paginated Report

Navigating from a Power BI report to an SSRS Paginated report with filters and parameters.
This recipe primarily involves Power BI report design techniques. I’m not going to get into the details of Power BI report design but will cover the basics with a partially-completed report to get you started. If you are less-experienced with Power BI you can use this as an example for future report projects.

Power BI Performance Analysis

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

Advanced DAX Training from Marco Russo in Portland: June 2019

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

Getting Started with Power BI Report Design level 101 – What Every New User Should Know

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

Data Geeks and Data Jocks Meetup in Vancouver, Washington

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

Azure Everyday Roundup

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

Power BI at Enterprise Scale

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

It’s about solving business problems & meeting people… Allen White interview

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.

Interviews from PASS Summit 2018: Matthew Roche, Matt Masson, Adam Saxton & Patrick LeBlanc

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

Data Model Options for Power BI Solutions

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.

Interviews with Microsoft Data Community Speakers and Leaders

What is the key to advancing your career in the Microsoft data platform? Here is some advice from some of the most successful people in the industry…
Every year we have some big community events that bring together community leaders from all over. These are international user group and community leaders who write books and speak at conferences. we had our local Oregon SQL Saturday “SQL Train” which is a chartered train – some coaches that one of our speaker’s chartered to bring all of our speakers and attendees up to the PASS summit after Oregon SQL Saturday, and then the big PASS summit (the big conference up in Seattle). I had a chance to sit down with a number of our speakers, community leaders and attendees and just ask questions about what brought them there, and advice that they would give people in the industry about how to get the most value out of that experience …and this is what they said:

How to Assign Pro Licenses to a Power BI Tenant

This is a question that comes up all the time. Power BI licensing is not complicated but a common challenge is that the person who sets up a new Power BI subscription and tenant within an organization is often not the same person who manages Office 365 or Azure service licensing for the organization. I’ve consulted on projects for several organizations where folks just didn’t know who to talk to or how to proceed after testing the water with Power BI. After setting up a new subscription, IT professionals and business data analysist often don’t know how to license Power BI for company use and share reports and datasets with others in the organization.
This post will show you how licenses are assigned to users and, more importantly, what to request from your support desk or administrators who may be unfamiliar with Power BI and Office 365 user licensing. Keep reading for background information about why this is important and necessary.

Full-day Power BI for Enterprise Solutions Workshop in Chicago, Sept 12

If you are in the Chicago area and haven’t already registered for the Data Insights Summit, please join me. I…

Doing Power BI the Right Way: 8. Delivery options

Part of the the series: Doing Power BI the Right Way When you sign-up for the Power BI service at PowerBI.com…

%d bloggers like this: