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

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.

Power BI Hands-on Workshops

Chicago – full-day: March 4, 2016
San Jose, CA – 2 hour session at the PASS BAC, May 3-4, 2016

Will you be in the Chicago area on March 4th?  So will I and you can learn to use Power BI to design real business solutions at my full-day Power BI Hands-On Workshop one day before the Chicago SQL Saturday #484.  I’ve delivered this at other preconference events with sell-out attendance.  Thank you to all who have attended in other locations.  An abbreviated version was delivered at the PASS Global Summit this past November and I’ll be doing a quick two-hour abbreviated session at the PASS Business Analytics Conference this coming Spring in San Jose, CA.

Here’s a copy of the entire event description page below.  You can register here.

This engaging workshop will teach attendees how to use Power BI Desktop to create a complete solution delivering dashboards, self-service and mobile BI.  Attendees will learn to analyze information using impactful and interactive visualizations.  We will create a solution using a variety of data sources that can be used on the desktop by Data Analysts or for group collaboration in the Power BI secure cloud service.

Attendees should have a basic knowledge of database concepts and should know how to use Excel 2013 or newer.

Computer Requirements

Bring your laptop running Windows 7 or newer (4 GB of RAM is required, more is better, 64 bit OS recommended).  Before the session, attendees should download and install the Power BI Desktop and create a free account at (90% of the exercises can be completed in Power BI Desktop without a subscription.)  Note that to create a subscription you must use a work email address rather than an address from a free mail provider such as Hotmail, GMail or Yahoo!.  If you have any difficulty getting signed up, follow the directions at this post.

At the conclusion, attendees will know how to use Power Query to combine and transform data from different sources; Power Pivot to model data for reporting and discovery, and write calculations, KPIs and complex metrics. They will learn to use Power BI report visuals to create interactive visualizations and dashboards, and Excel PivotTables, PivotCharts, slicers, conditional formating and advanced features to create collaborative dashboards and reports. Finally, attendees will learn to deliver a collaborative BI solution in Power BI online enabling natural language queries with Power BI Q&A, scheduled data refresh and queries using live, on-premises data.  We will also introduce enterprise capabilities which include connectivity to live, on-premises data sources through DirectQuery and Analysis Services.

BI Solution Components

Begin with a brief and high-level introduction to the components of traditional BI solutions to help attendees appreciate the usual scale and cost.  Discuss the options to scale these items for small and large projects.  Discuss the trade-offs for each and the value of centralized solutions, data quality and master data management.  Discuss the challenges of designing, building and maintaining large-scale BI solutions.

Topics covered:

  • Introducing the Power BI Dashboard designer
  • Universal principles of data behavior
  • Universal data problems & visualization principles
  • Interactive visuals
  • Project types & scale
  • Self-service BI & enterprise scale projects
  • Power BI project stages
  • Managing data transformations with Power Query
  • Introduction to advanced “M” script
  • Data modeling with Power Pivot
  • Introducing DAX Calculations
  • Visualizing & exploring with Power BI report visuals
  • Building live, interactive dashboards
  • Publishing and collaborating with
  • Integrating Excel pivot tables & reports
  • Introducing management groups & content packs
  • On-premises and large-scale solution options
  • Data Management Gateway & scheduled refresh

Registration includes lunch and beverages

When: Friday, March 4, 2016 from 8:30 AM to 4:30 PM (CST) – Add to Calendar

Where: DeVry University Addison Campus – 1221 N. Swift Road Chicago, IL 60160 – View Map

How to Get SSRS Reports in Excel Without Exporting from Reporting Services

I’ve recently seen a wave of questions from clients and peers about difficulties exporting reports to Excel lately.  Every few weeks I get a call or question about this.  This topic has been a recurring theme for a very long time and one that I have encountered many times over the past – oh, eleven years or more – using SSRS.  Business users like Excel because it’s what they know and they can reformat and manipulate data in a workbook.  People like Reporting Services because all the hard work of connecting to data sources, writing queries, totaling, grouping and formatting the results gets done once and then all they need to do is run the report.  Users want the best of both worlds and they expect that when they export a report to Excel that they should have their cake and eat it.  In other words; they should be able to get a report, with all the goodness of headers, scrolling regions, pagination, interactive sorting – you name it – to work exactly the same way in Excel.  Many improvements have been made to all of the report rendering extensions over the years and the Excel export story is better than ever but here’s the deal – bottom line – the more rich formatting and advanced functionality that gets designed into an SSRS report, the less likely it is to export all that functionality to Excel or any other rendering format. Continue reading