sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009
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.
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 →
During the month of April, I will be delivering three full-day Power BI hands-On workshops. Each of these events will be the Friday preceding these SQL Saturday events. Seating is limited and many of these workshops tend to book-up. Follow the links to register.
The format will be the same for each event. These are intermediate-level workshops. If you’re new to Power BI, just a little self-study should get you ready to optimize your learning experience.
Event Description:
In this Power BI hands-on Workshop, we will quickly review the essentials and learn some advanced techniques to transform, model and analyze business information with Power BI Desktop. Techniques and best practices presented are from several prior workshops and years of field experience. Learn to use self-service and enterprise-scale Power BI capabilities; gain valuable skills to integrate, wrangle, shape and visualize data for analysis.
If you’re just getting started, please pick-up an intro book or use online resources to familiarize yourself with Power BI Desktop so you can take your skills to an intermediate level in the workshop. If you have the basics, be prepared to take your skills to the next level; learn to address data and reporting challenges with advanced design techniques. At the conclusion of the workshop, you will have a complete solution built from real business data, shaped, cleansed & modeled; with a dashboard and interactive report visuals ready for analysis.
Topics & skills include: Power Query/M, modeling and calculations/DAX, standard & custom visuals, R visuals. Data sources: CSV, JSON, SQL Server; query folding, scheduled refresh & DirectQuery.
Attendees should have at least intro-level experience w/Power BI Desktop or Power Pivot. Bring laptop w/latest Power BI Desktop ver. installed (PowerBI.com), 64-bit Windows & 4 GB of RAM (8 GB recommended), 2 GB free storage. Power BI subscription recommended, not required.
This just in from the Reporting Services product team:
“Power BI reports in SQL Server Reporting Services: January 2017 Technical Preview now available” This feature addition will allow Power BI reports to be published to a local SQL Server Reporting Services server, entirely-on-premises without using the Power BI cloud service.
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.
It’s really exciting to see the first round of entries into the Power BI Best Visuals Contest, which has been running for about two weeks and wraps up on October 1. The first people’s choice award winner is Daniele Perilli from Rome who submitted a nice horizontal bullet chart. The contest is a catalyst to get the community developing custom visuals for Power BI using Java Script libraries and an open development environment. What a great way to get the community involved and to fast track onward development of this great product! See the complete list of entries and more contest information here. Check back to this post for contest updates.
October 6th UPDATE
The second and third People’s Choice Awards go to: “Enlighten” for the Power BI Aquarium and to fredrikheden for the Breakdown Trees visual. This is by far my favorite because it’s essentially the decomposition tree from ProClarity and PerformancePoint.
This style of bullet graph is a good choice for visualizing multiple related values on a common axis scale. Stephen Few promotes the use of this as a simple, clean and comprehensive way to present important metrics in a context that reduces clutter and alerts the reader to take action.
This was demonstrated in James Phillips’ keynote at the Cortana Analytics Workshop. This may not be the most scientific approach but it is a fun way to get aggregated metrics to the viewer in a fun and interesting way. The fish, with different sizes and colors, swim around in random patterns. Imagine this dashboard on a large monitor in a reception area, software development floor or desktop support hall; displaying average wait times, bug backlogs or calls handled.
After seeing Datazen demonstrations, people have asked when Datazen and Power BI will share visuals. Now, I guess we know. These visuals were inspired by Datazen KPI tiles.
What! NO? Get with it, my friend. Power BI is for everyone. All the kids are doing it… not to mention the business owners, marketers, sales campaign planners and scientists, IT Pros and common folk. Power BI is all the rage and can be used to analyze everything under the sun – oh, and that Pluto mission tracking thing that Microsoft did for NASA. Yea, pretty much everybody’s using Power BI!
On the 27th of August, I’ll lead an online virtual workshop “Building Business Intelligence Solutions with Power BI” that will show you how to put Power BI to work. I’ll demonstrate techniques to use with serious business data but we’re also going to have some fun with data from the National UFO Reporting Center. This database contains all of the publically available UFO sighting information, since Thomas Jefferson reported seeing a flying light in the sky up to the latest flying saucer sighting today. You’ll learn to import and mash-up data from different sources, create a data model or analysis and reporting and then create compelling, interactive, highly-visual report and dashboards. You’ll ask you dashboard questions in plain English and she’ll answer you in magically-crafted prose of wisdom and insightful knowledge, investing artistic charts and visualizations while you do nothing but talk to your data. Yep, really.
There’s still time to signup for the training sessions:
In an amazing application of visualization techniques, this production is a depiction of the casualties and affects of war. Starting with World War II, Neil Halloran uses animated charts to measure the number of soldier and civilian deaths and then pivots and refactors these numbers in an impressive and impactful presentation that helped me better appreciate what really happened in history. He goes on to perform deeper analysis; comparing these numbers to other wars in history, and to make adjustments for population and other comparison factors. Really well-done.
This is an 18 minute presentation worth giving your undivided attention to fully appreciate the message. The use of common BI graphics and charting techniques is quite impressive – clustered columns, stacked columns and time-series charts, icons and cloud clusters – with music, narrative & a few pictures, all contribute to a truly enveloping presentation. At one point, you can pause the Flash player and interact with the chart to see more information (if you go to the site choose the Interactive option). View the interactive presentation at: http://www.fallen.io/ww2/
No doubt, this production took a lot of time and energy, and the author provides the option to support his work financially using Amazon; which is a well-deserved donation.
Last week Microsoft announced that they had acquired the DataZen mobile BI platform and were adding it to their enterprise BI product suite. This is very exciting news that rounds out the Microsoft BI capabilities of their entire product portfolio. What’s more, is that they plan to make if free for SQL Server Enterprise customers who have Software Assurance agreements.
I presented a session at the PASS Global Summit in 2013 showcasing DataZen called “New York, London, Paris Munich; Everybody’s Talking About Mobile BI”. A product team leader in the Microsoft BI Visualization team had introduced me to DataZen and recommended I take a look. This led me to recommend that we evaluate the product for one of our enterprise BI clients and to talk about my experience in the conference session. My response to the Microsoft team was to recommend that they acquire DataZen. Whether I actually had anything to do with this product acquisition or not, it does give me a sense of satisfaction to know that it is now part of the Microsoft family.
So, what, exactly is DataZen and what are its capabilities and challenges?
I have built a DataZen server with the latest version and I’m in the early stages of evaluating the product. I’ll update this article with my findings as my investigation continues. I conducted a thorough evaluation of DataZen for a consulting client a little under two years ago and I’ll share that experience. The product has evolved forward since that time.
Until the Microsoft acquisition, DataZen Mobile BI was a division of Component Art, a developer of Application development controls and visualization components. The developer components market has many big players like Infragistics and Telerik but Component Art has been exclusively supporting the .NET development market since the conception of the .NET Framework and they lead the space in providing visually stunning charts and other widgets specifically suited for visualizing data.
The DataZen server and services model is a bit like Reporting Services in that it’s an entirely scalable services layer consisting of Windows services and web services that provide separate authentication, data connectivity, rendering and data caching. You will typically build a dedicated server to host published content. Data results for the dashboard queries are cached and can be refreshed frequently but the platform isn’t really designed for real-time analytics. Queries can be filtered using parameters which utilize slicer-type pick lists and simple drill-through actions. The server currently does not require a SQL Server installation (although this might change as Microsoft integrates the product). DataZen uses IIS and does not require SharePoint or any other products other than Windows Server.
Dashboard & Collaboration Platform
I really like the DataZen approach. It’s more that just a place to host dashboard-style reports. DataZen allows every user to use the device of their choice to view important KPIs at a glance, to drill into detailed and interactive dashboards, discover data insights and collaborate with other team members using threaded discussions in a true social media kind of environment.
Setup is a Pain
I found the server setup to be a bit of a bear and it took a few calls and interactions with DataZen support to get it configured and setup correctly. Errors weren’t easy to understand or resolve. The documentation covered basic features but didn’t address common setup challenges. The unintuitive errors and corresponding work-arounds made me question the stability and maturity of the platform but once they were resolved, it seemed to be reliable. I’m currently contenting with a data source authentication issue which is blocking me from proceeding.
(April 28th update) – Resolved
Thanks to a second pair of eyes barrowed from Davide Mauri, we’ve worked out the data source configuration and I now have three data sources configured and working great. These include: SQL Server 2014 relational, SSAS 2014 multidimensional and SSAS 2014 tabular. The issue was actually quite simple. I had explicitly entered the credentials for a service account and found that if I removed the user name and password and then indicated “Integrated Security”, the connection uses the identity of the Datazen service account. I set all three DataZen services to use this account and then granted Read permission for each database instance. This seems to be the easiest and most reliable method.
Responsive Design & Adaptive Layouts
The dashboard design and usability experience is awesome so from a user perspective, this tool totally rocks. The Component Art folks really get usability. The dashboard designer is a separate app that requires Windows 8 or higher. A couple features I really like are the the drag-to-grid design experience. The dashboard controls sit on a user-configurable grid which can be any size you want. When designing a dashboard, users drag or draw each component to align to the grid cells so it aligns perfectly with adjacent controls. When the dashboard is rendered to a a different layout- like a phone, the controls automatically adapt to the appropriate orientation and layout.
Device support is universal across all platforms. DataZen has client-side apps for the iPad, iPhone, Windows 8+ (Metro) tablet, Windows 8+ Phone, Android and HTML5 for devices connecting directly to the server. The apps are available from the respective app stores and are all free. Each app connects over HTTP or HTTPS to any number of DataZen servers.
SSAS Queries Were Not So Great
At the time I evaluated DataZen, I was able to create dashboards that used an Excel workbook, SQL Server, Analysis Service multidimensional & SSAS tabular. It was a apparent that whoever designed the SSAS data source adapter didn’t know much about MDX or SSAS queries. I was not at all impressed with the limitations for SSAS queries and I sincerely hope this has been improved. The problem was that a dashboard could only consume members returned on the Columns Axis and not on Rows. Although manageable, this required a tedious work-around to define calculated members for every member placed on Rows. When I get connectivity sorted out, this is the first thing I’ll test and update this review accordingly.
(April 28th update) – Resolved
Queries for all three of my data sources are working great. Of course, T-SQL queries are supported as we would expect. To the question of MDX query support… it’s much better but it’s still a little quirky and requires some special handling. After working through the process a few times, the technique is reliable so I’ll downgrade my comment from “quirky” and “not so great” to; Oh, I don’t know, “special”. As I had discovered in my previous experience, members returned on the Rows axis don’t show up in the query results. There are two viable techniques to update a standard MDX query to work with a dashboard. The first is to add the text DIMENSION PROPERTIES MEMBER_CAPTION before the “ON Rows” declaration. This works but the member names can be a bit verbose. Another method that is a little more work but provides a lot more control, is to define a calculated measure/member for each attribute you need to put on Rows and then add these to the Columns axis. Here’s an example of one of my finished Data View queries using this technique:
with
member measures.DateValue as CDATE([Date].[Date].CurrentMember.MEMBER_CAPTION)
member measures.DateName as [Date].[Date].CurrentMember.Name
member measures.MonthName as [Date].[Month of Year].CurrentMember.Name
member measures.CategoryName as [Product].[Category].CurrentMember.Name
member measures.CountryName as [Sales Territory].[Sales Territory Country].CurrentMember.Name
select
{
measures.DateValue,
measures.DateName,
measures.MonthName,
measures.CategoryName,
measures.CountryName,
[Measures].[Reseller Sales Amount],
[Measures].[Reseller Gross Profit]
} on Columns,
(
[Date].[Date].[Date].Members,
[Date].[Month of Year].[Month of Year].Members,
[Product].[Category].[Category].Members,
[Sales Territory].[Sales Territory Country].[Sales Territory Country].Members
)
on Rows
from [Adventure Works]
where
{[Date].[Calendar Year].&[2012], [Date].[Calendar Year].&[2013]}
and here’s a look at the finished dashboard:
A dashboard can be viewed in the DataZen app for Windows 8.x Metro, Windows Phone, iPad, iPhone, Android and through the HTML viewer directly from your server. Every rendering is highly-interactive. Every dashboard can have three different layouts optimized for the desktop, tablet or phone. Once the main dashboard is designed, adding the other layouts it’s a snap.
Here’s the same dashboard on my iPad:
…and on my phone:
Stock and Custom Maps
Map and shape support is quite impressive. They were in the process of extending map support when I originally evaluated the product and have delivered on this promise. Out-of-the-box, DataZen comes with the same basic geographic shapes we’re accustomed to seeing in Reporting Services and other tools. Based on common geographic keys and shape names in your data, a dashboard can render any ERSI shape file (which could include building floorplans, CAD drawings or geographic boundaries) with dynamic hover-over highlighting and tooltips with data details. Very nice.
Bottom Line
Setting up DataZen and getting started with data sources, queries and dashboard design is going to frustrate novice users and keep some BI consultants busy. Of course this is good or bad news depending on which side of the consulting fence you reside. As a BI consultant, I say this a bit tongue-in-cheek but it’s true. Until you work-through this process, it’s not going to be easy. After you’ve done a few of these, it’s actually not so bad. the good news is that the user experience is quite impressive and well worth the investment.
In Review (based on my earlier evaluation):
Good
Bad
Multiplatform support… excellent application support for iPad, iPhone, Windows 8+ (Metro) tablet, Windows 8+ Phone, Android, HTML viewer
No Windows desktop app (but the HTML5 server rendering is a descent work-around)
Dashboard designer snap-to-grid experience is excellent
Designer requires Windows 8+ “Metro” style app.
Responsive rendering adapts to desktop, tablet and phone layouts
Server setup and configuration is unintuitive and cumbersome at first.
General dashboard usability is excellent
Dashboard design is an IT effort or for someone intimately acquainted with the designer tool.
I’ve created a series of five tutorial videos for a set of workshops I’ll be doing at several events this year. The workshop is available from an hour-long quick demo to a full-day, hands-on workshop with a hosted lab environment in the cloud. The series teaches how to create a complete business intelligence solution using the components of Power BI in Excel 2013. These video tutorials, although complete, are a subset of the entire workshop. Each tutorial is about 10 minutes long.
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.
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.
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!
