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

It has been a few weeks since I last posted, and a while since I started the promised blog series “Doing Power BI the Right Way”. I’ve received a lot of positive comments on the posts in the series this far, and people wondering why it’s taking so long. Well, I am busy working on a book about Paginated Reports and SQL Server Reporting Services. This will be the second edition of a book I co-authored ten years ago and this edition is also a collaborative effort with multiple contributors. We have quite a lot of excellent material that is just starting to go through the process of editorial and technical review. Writing a technical book always takes more time and effort than imagined, and working on this one in my “spare time” is a laughable notion but this is going to be a great book.

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.

Before we had Power BI, we had Reporting Services. When the focus of my career and consulting practice became Business Intelligence, starting about fifteen years ago, most of the “BI reports” I created were in SQL Server Reporting Services (SSRS). We could create a variety of charts and reports that allowed users to drill-down from summary to details. We could create reports that allowed a user to drill-through, from one record or report item to another report that presented more details. With a little creativity, we could create reports that allowed users to click items that would filter other items on the same report. It took some work and time to create these interactive “drill-through-to-self” reports. Today, after transforming data into the right format for a tabular data model, this type of interactive functionality just magically happens in Power BI with very little report design effort. But, Power BI is primarily a tool for analyst users to create their own reports and to get answers to business questions rather than for IT developers to create reports that print or export perfectly to a PDF or Excel. Although it is possible to create fairly advanced paginated reports, most bread-and-butter operational reports are really just what I call “lists of stuff”… the results of SQL queries presented as a table or matrix; perhaps with a few groupings and subtotals. Simple business processes and task often require simple reports to help manage them.

Paginated reports, by definition, are reports that allow a lot of data to be presented on multiple pages. Paginated reports can be viewed online, printed or exported to a variety of file formats. Becasue there is so much overlap in the capabilities of SSRS/Paginated Reports and Power BI, the choice between these two report tools is not always clear. I can cite many cases when using Power BI Desktop is clearly the right choice to create an analytic report, and cases where it would make more sense to create an operational report using Paginated Report Builder. I think these are the easier decision points but I would like to explore those cases where we could argue either way. What are the pros and cons of using Power BI to create traditional reports? When would it make more sense to replace a static, paginated report with an interactive report created with Power BI?

The integration of Paginated Reports into the Power BI cloud service continues to march forward at a rapid pace. Just yesterday, Chris Finlan teased a blog post announcing new Power Automate integrations that will allow report to be distributed and exported using flows. Listen up for some big announcements at Microsoft Ignite about Paginated Reports in the Power BI service. Whether you host operational reports on your own report server or in the cloud, Paginated Reports are an important part of most business reporting portfolios.

When do you choose to use Paginated Reports or SSRS rather than Power BI? Do you design reports differently than you would have 5 years ago? Do your users prefer interactive, self-service reports now or do they still want made-to-order static reports?

What are the deciding factors between creating a paginated report or an interactive report?

Do you create paginated reports with a Power BI dataset or SSAS model as the data source or do you only use SQL queries for those reports?

I’m interested in your ideas and questions. Please post them in the comments below this post and then I will extend this post with your input and my thoughts.

Can’t We Just Get Along? Making SSRS, Power BI and Excel Play Well Together

Please join me and other 2017 PASS Summit speakers for 24 Hours of PASS: Summit Preview on July 19th and 20th.  24HOP is a series of 60 minute on-line sessions presented back-to-back for 24 hours, from the same professionals who will deliver preconference and main conference sessions during the Summit the first week of November this year.  These online sessions are free of charge and normally attended by thousands of individuals to gain insight and knowledge about the topics.  24 Hours of PASS features free educational webinars delivered over 24 hours. Topics covered in this edition include Performance Tuning, SQL Server 2017, Linux, DevOps, Azure, PowerShell, SSRS, Power BI and much more. Browse all sessions. These webinars provide a sneak peek at some of the best practices, expert tips and demos you’ll find at this year’s PASS Summit in Seattle. Continue reading

Animated Visual: A Day in the Life of Americans

One of the more effective data visualizations I’ve seen in a while, this animated visual is a time-varying Markov chain developed by Nathan Yau of FlowingData.  This is a simulation of 1,000 people’s average day. It’s based on 2014 data from the American Time Use Survey, made way more accessible by the ATUS Extract Builder.

image

Real-time Wind, Weather & Climate on Animated Earth Projection

When I got this link in my daily feed from FlowingData this morning, I thought this would just be yet another nifty map graphic but it’s not.  It’s a very sophisticated body of work – not only cool but “earth” is a very sophisticated projection of real-time weather and climate data projected over the entire planet.

Author, Cameron Beccario, describes his work as “a visualization of global weather conditions forecast by supercomputers updated every three hours.  Ocean surface current estimates updated every five days; ocean surface temperatures and anomaly from daily average (1981-2011) updated daily.”  Data is aggregated from NCEP, the National Weather Service & NOAA.  Graphics were created with JavaScript libraries: D3.js, backbone.js and node.js.

image

You can see here typhoon Matmo which is moving over Taiwan at the time of this posting.  The menu options allow you to select wind speeds at different elevations, actual and perceived temperatures, clouds, humidity and precipitation.

This is really nice work!

image

Cameron shares his work at github.com/cambecc/earth.

Data Visualization – World Cup Team Players & Pro Teams

Check out this data visualization from the New York Times that shows the team make-up of 2014 World Cup teams and the teams these players play for during the regular season.  In many cases, teammates are playing with members of rival teams and players from other countries.  Very interesting!

image

 

shared by FlowingData.com