The Future of Power Query – Interviews with Chris Webb at PASS Summit 2017

In a series of interviews during PASS Summit 2017 in Seattle, the week of October 30 through November 3, I caught up with Chris Webb, who is an international BI community thought-leader and product team advisor for Microsoft.  We chatted about his love affair with Power Query, lessons learned from many years of expert Business Intelligence project work and his thoughts about the future of Power Query and other Microsoft tools.  Chris had just finished an all day preconference presentation the day before our interview and his experience was fresh in-mind.  He has a long history of deep expertise with SQL Server, Analysis Services multidimensional and tabular technologies, MDX and DAX.  His focus lately has been teaching, writing and using Power Query in practice.

He was so interesting and insightful that I honestly couldn’t edit anything out of the final recording, so I split it into three parts.

Chris Webb Interview Part 1 of 3

In the first installment, Chris talks about how he got started with Power Query and why he thought it was worth his investment of time and energy.  He hadn’t planned to bet his career on Power Query.  He said “this tool was cool!  I liked playing with it…”  He carried on playing with Power Query until he fell in love with the product and its awesome ability to transform data in ways that weren’t possible before with the same easy and elegance.  Maybe the long-term success and pervasive integration of Power Query into more products will be bolstered by the enthusiasm of Chris and others in the community who just love to use it.

Chris Webb Interview Part 2 of 3

We began with the question: “Do you ever foresee Power Query being used as an enterprise ETL tool in lieu of something like SSIS?”  Chris shares his thoughts about Power Query as a self-service, desktop data transformation tool and its juxtaposition with other with older, more complicated data tools.  He says it is “a universal query generator”, capable of performing “query folding” (translating and pushing  queries back to the data source engine for each data provider).  We discussed how Microsoft is committed to supporting and enhancing Power Query, not only for desktop analysis but to perform tasks partitioning in Analysis Services tabular, with many other possible scenarios.  His passion for this tool is so apparent in our discussion.

Chris Webb Interview Part 3 of 3

How can you get started with Power Query and where is the best place to go for expert advice?  Chris wrote one of the best books on the topic that, in my opinion, is very relevant today.  I gave him the bait and he wouldn’t take it when I asked: “can you recommend any good books on Power Query?”  In his usual, humble, fashion he tells me how difficult it is to write a good book about a product that changes so much; and goes on to recommend several online learning resources.

We conclude with Chris’ advice about how to get started and where to go for best practices, to develop expertise and advanced knowledge.

You can follow Chris Webb’s frequent posts on his blog, at:
Thank you, Chris, for your time and willingness to share your thoughts and expertise.

Resources for Power Query Chris mentioned:

Implementing Row-Level Security in Power BI

The best method to implement row-level security in a published Power BI model or SSAS Tabular model consumed from the Power BI service will depend to some degree on your data structure and requirements.  The method I demonstrate here is one of the most flexible approaches and one that I commonly use in my projects. Continue reading

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

Reporting Services 2016, Power BI and PASS Summit 2016

What am I most excited about as I prepare for the PASS Summit here in Seattle this week?  A lot of things.  Preparing for my session, which will be on Thursday at 1:30, by far the most popular and interesting topics are about integration and tool choice.  Today’s public announcement on the SSRS product team blog about on-premises Power BI integration with Reporting Services is really big news.  It’s great to see two of the technologies I love working together.  Whether in the cloud or on-premises, Power BI and Reporting Services can be used together.

Thursday’s session is titled “Reporting Services 2016, the Force Awakens”.  The consensus among industry users is that Reporting Services was stalled and left alone for far too long.  Technology and industry trends have moved forward over the past five years or so but Microsoft have invested in other tools and products, aside from Reporting Services.  The SSRS server architecture is capable but the rendering and delivery components needed to be modernized.

The good news is that the modernization effort has been underway for quite some time.  Resources that were directed to other investments have been recommitted, not only to the Reporting Services product, but to integration across the product stack.  It’s really hard to build a reporting platform that addresses every need.  I think Microsoft have a history of creating false expectations by putting experimental and incomplete features  out in the industry that weren’t on the committed roadmap.  I know better than to believe that the pattern won’t ever be repeated but I firmly believe that there is a well-defined reporting and BI roadmap and product teams are making rapid progress against it.  It is once again an exciting time to invest in the Microsoft analytics, reporting and BI tool set.

I’ll publish the entire deck after the 2016 Summit.  Until then, here are some selected slides…

Comparing the progress of new features added to SSRS over the release cycles, the product really slept from 2010 until 2016.

2016-10-25 (1)

Microsoft’s reporting & analytic platform has shifted significant as they moved from products to services, and from servers to the cloud.  The dust is settling on this major refocusing effort.  Capabilities that were introduced in Azure, Cortana Analytics and Power BI are now becoming available to “the box”, on-premises or in hybrid cloud scenarios.  We can expect more announcements on this as the roadmap is played-out and gaps are filled-in.

2016-10-25 (2)

Reporting Services is a central player in the on-prem delivery of the BI, analytics and reporting tools offered by Microsoft.  These include Excel, Paginated reports created with SSDT/report designer or Report Builder, Mobile Reports and Power BI Desktop.

2016-10-25 (3)

…more to come!

check back for updates.

Power BI Dashboard In A Day Training in Richmond and Pittsburgh

I’m pleased to announce that I will be presenting two more Dashboard-In-A-Day training sessions in October.  These free, full day training sessions are co-sponsored by Microsoft and SolidQ.  I’ve had a blast teaching Power BI in lots of other locations.  What’s not to like?  It’s Power BI and it’s free.  That’s a pretty good combination.

If you’re in the area, please plan to take and day and get some great training.  Here are the dates and registration links:

October 11th
Richmond Microsoft Office in Glen Allen, VA

October 13th
Microsoft office in Pittsburgh, PA

What’s Dashboard-In-A-Day?

DIAD is designed to accelerate your Power BI experience with a comprehensive training program in a single day. A trained Power BI specialist partner will host you for the entire day – all you have to do is bring your Windows-based notebook and we will supply the rest – even lunch!* After registering, you will receive an email confirmation that includes a link to download the training materials you’ll need for the day.
With DIAD you get practical hands-on experience with SolidQ who specializes in the art and science of bringing data to life in a full-day of instructor lead report building. You will learn how to connect to, import & transform data from a variety of sources, build real data models, as well as author and publish Business Intelligence reports. You will learn how to customize and share your creations for collaboration with other groups securely inside your organization including mobile device sharing.

Power BI and Excel–the Dynamic Duo

Attending the PASS Business Analytics Conference last week reminded me once again that the most-used data analysis tool in the world is Excel.  This isn’t startling news but it is an important reality that can be easy to ignore until being reminded that users usually want to WORK with their data rather then just seeing their data.

Following are some take-aways and thoughts from the PASS BAC event and recent news on the Excel and Power BI front…

Power BI – Analyze in Excel

– this new capability in Power BI and Excel for many users completes the cycle and brings the product full circle.  I’ve built a lot of Power BI solutions in the past year or so, and the most recognized request from users and stakeholders was that they wanted to know how to work with the data in Excel.  There are a few related features that make this a reality today.

Analyze in Excel is a feature of the Power BI service that allows data in a Power BI model to be (you guessed it…) analyzed in Excel.  More specifically, choosing this option (by clicking a link) opens Excel on your desktop, creates an ODC file containing connection information for the Power BI  as a data source and creates a Pivot Table in the new Excel workbook – all automagically and without user interaction.

At this point, Excel has a live connection to the Power BI model just like any data source (cube or tabular model).  Just add fields to the pivot table… use normal Excel features; add charts, filters, slicers, you name it.  Just do what you do with Excel and it works with your Power BI model data.

This a great for consuming a published Power BI model as a single source of truth in a tool that users know and love to use.

Here’s the cool thing… Power BI can use Excel visuals as dashboard content.  You can pin Pivot Tables, Pivot Charts and entire worksheet selections to a Power BI dashboard tile.  To do this, save the Excel workbook to the OneDrive associated with the Power BI subscription.  Now that Excel can consume the Power BI model as a client, using these features together makes Power BI and Excel part of the same ecosystem and extends the power of the platform rather than just more features of the individual tools.

A companion feature of Excel 2016, Publish to Power BI, makes it much easier to push content to a Power BI subscription.

Tutorial: Use Excel Reports in Published Power BI Subscription

Third Party Power BI Vendor Support

As a fairly new entry in self-service BI and analytics space, we’re all watching Power BI.  Adoption has been impressive as has been Microsoft’s commitment and investment into the platform.  Another heathy sign of any new platform is the investment of vendors.  I’m not endorsing anything but just making some observations.  At the conference, I saw several new third-party additions for Power BI and I’ll share a few as  examples:

Pyramid Analytics – Publish PBIX content on-premises

Pyramid Analytics has one of the most comprehensive add-on products for SQL Server Analysis Services.  It’s an entire suite of server-based visual and reporting tools, along with narrative report authoring and self-service tools that leverage the depth of SSAS and MDX functionality.   Pyramid announced their intent to provide on-premises Power BI publishing in their product last summer and have been working to deliver on what should be available in the next couple of weeks, based on internal build demos I saw at the conference.  This will the first offering for an on-prem collaborative Power BI solution.

PowerON BI – Write-back to Power BI and SSAS tabular for forecasting and planning

I’ve used the Power Update tool from Power On in the past.  When I saw the Power Planner demonstrated, the simplicity and ease-of-use just knocked my socks off.   The ability to write-back forecast data and report on real-time “what-if” scenarios has been a difficult to achieve holy grail in the Microsoft BI space but these guys have it right.  The concept is amazingly simple: in a PivotTable, change the total of any calculated or aggregated measure.  The contributing details and subtotals are proportionally recalculated and written back into the model’s data source and then then refreshed.  Large models using SSAS tabular use small partitions to isolate changes.  We’ve built custom solutions to address specific user requirements using a similar architectural approach (with considerable effort) but this tool seems to be flexible enough for different use cases.  They’ve also created custom visuals that allow data changes to be applied using Power BI visual charts and tables.  Quite nice.

Adam Saxton (aka Guy In A Cube) reviews Power ON BI during his coverage at the PASS Business Analytics Conference.

Custom Visuals – from the community contributors or roll-your-own visuals

In the Fall, the Power BI product team opened the visuals API and provided a development toolkit with sample code in GitHub projects.  Several individuals and companies responded, along with Microsoft, to create 48 (so far) new custom visuals that can be used by any Power BI user to extend their solutions.  A recent entry is the Long Text Viewer that describes a Power BI query in verbose text.  This is a useful for documenting the context of reports and visuals.


Additionally, a new set of Excel-based custom app visuals are in the works from the Excel teams and community.