Power BI, Self-service and Governed Data Solutions

Power BI is due to be released to the masses this week and I’m very excited to see this powerful product made available to so many for so little.  We’ve had some of the capabilities of Power BI in other Microsoft products for a few years so why is this this different and what does it really mean for most business users and IT shops?  I think there are enormous possibilities in this new frontier of easy-access, self-service analytics but there are also big challenges ahead to manage systems with a different style of discipline.

As I visit organizations interested in Business Intelligence and data analytics, and talk to people in the industry about self-service tools; I hear some common and often conflicting themes:

“Business knowledge workers want self-service tools to get answers from reliable data”

“CIO/IT leadership: let’s enable the business users to do their own reporting and analysis so our IT staff can do other things”

“Data should be centrally-managed by IT and DBAs so there is one version of the truth and the data can be secured and maintained”

“Self-service BI empowers everyone in the business to be self-sufficient and more productive”

Isn’t self-service BI easy?

On the surface, yes.  It sounds easy:  Import some data, clean it up a bit, join some tables together, write a few simple calculation formulas and throw it into a chart… and viola!  A dashboard.  With some practice and a little training, some parts will be easy but the challenges of data integration, conformity, quality and master data management are ago-old problems that will always be present when data is sourced from different systems and is handled and manipulated by different people.  This is where the democracy of self-service BI and the rigidity of governed solutions can collide; and BI projects of any scale will get complicated… regardless of the tool.

The good news is that we’ve been doing this a long time and have developed disciplines and best practices to manage blended solutions.  With careful management and diligence, ad-hoc solutions can be made to scale and endure the transition.  In the article I wrote last year, titled “Business Intelligence Roles and Team Composition”, I discuss lesson learned about balancing business-owned BI with IT-managed BI and how team members assume roles to balance solution objectives.

Another very important consideration is that the objective for most self-service analysis projects to create “good enough” models and reports for a specific purpose.  When a project graduates to a different form, intended for more users to answer more questions or for leaders to make informed decisions; different rules of data quality and reliability apply.

Back to Power BI

One of the most significant reasons I’m optimistic about Power BI as a serious business tool is that it is comprised of technologies I’ve learned to use in serious business scenarios over the past several years.  Although the new user experience is simple, the product heritage is quite extensive.  Let’s do a quick review of the products and components that have led to the current Power BI service offering from Microsoft…


The lion’s share of the work on Power Pivot and Power View begin back around 2008 and the first versions of these tools were release in 2010.  This included the Power Pivot add-in for Excel 2010 followed by integration with SharePoint, along with Power View.  Power Query was released in public preview shortly afterward and then all these tools were integrated into the first official Power BI subscription for Office 365.

This is where things got complicated for enterprise customers… Many enterprise-scale organizations don’t upgrade Microsoft Office on an aggressive schedule so business users don’t have access to these wonderful self-service BI tools in the corporate licensed copies of their desktop software.  The transition from 32-bit to 64-bit Office has been a tough nut to crack as well.  Enter the Power BI Designer (soon to be called “Power BI Desktop”)…  With the new designer, there are no Office/Excel version compatibility problems but if Excel users want to continue to use Excel, they still have that option.

Enterprise-Class BI

There are several things to consider when scaling a solution but in brief, Power BI currently offers a few options for managing data centrally or migrating datasets to managed servers.

  • The Data Management Gateway enables data in a published model to be refreshed at regular scheduled intervals with on-premises or cloud-based data sources.
  • The Analysis Services Connector enables reporting & visualization from an on-prem instance of SSAS (currently Tabular only) without copying the data to the cloud.
  • The Power BI REST API gives us the ability programmatically push data into, and refresh, the model at will.

We work with some organizations who, for various reasons, can’t utilize cloud services for some of their reporting scenarios.  Most data professionals I talk to don’t know where this boundary lies for them and it’s an important discussion to have with strategic leaders and decision makers.  Many businesses are adopting cloud services for strategic use and I’m learning to approach this topic using appropriate language to discover where those opportunities are. For example; if I were to ask a business leader if it would be OK to move their precious data to a data center offsite and outside of their IT control, she will probably say “no”.  But, if I were to ask if we could consider using an encrypted cloud service from a trusted service provider that is likely more secure than your online banking site – at a fraction of the cost and in less time, she is likely to say “let’s investigate that option”.  Sometimes, it’s a matter of presentation.  A healthcare company may not be able to allow patient  personal information to be used in a cloud-hosted data model but may have no issues publishing facility data in a securely-managed solution.

The good news is that a lot of Power BI capabilities are available for on-prem solutions with cross-compatibility between Microsoft BI products.  The path to move between cloud-based Power BI and the confines of the corporate firewall isn’t entirely concrete and is a moving target on a few fronts, as the products continue to evolve.  The two points I think about in this regard are that: one, development of these tool is moving forward at a rapid pace, giving us new options all the time.  The second point is that we’ve had a lot of experience testing the water and learning what options are supported and actually work well.

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

SSAS Tabular Modeling Article Series on SQL Server Pro Magazine

I’ve just finished a series of four articles for SQL Server Pro Magazine, along with sample projects and hands-on exercises. The series will take you through SSAS Tabular model design from start to finish, using the Adventure Works sample data in SQL Server 2012 or 2014.  Here are links to all four articles followed by an excerpt from each.

Part 1 – Getting Started with SSAS Tabular
Part 2 – Easy DAX – Getting Started with Data Analysis Expressions
Part 3 – Tabular Model Administration
Part 4 – Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions

Download the sample projects here:  Part 1, Part 2, Part 3, Part 4

Starting Your Modeling Career with Analysis Services Tabular Models Part 1

Four-part series

This is the first of a four-part series about getting started with Tabular model design using SQL Server Analysis Services in SQL Server 2012 and 2014.  You will learn the basics from start to finish and build a complete solution.  A sample project is provided for for each stage of the solution so you can follow-along with each article. Continue reading

Glossary of Terms Used in Microsoft BI Projects

At the beginning of many new BI projects it can be challenging to get everyone using common terminology and language.  This is especially true when people have biases with different vendor products or may have learned to use terms with different meanings.  Miscommunication can be costly on a project so getting everyone on the same page is important.  This list is intended bridge the gap for technical and non-technical members of the project effort. Continue reading