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
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.
On Friday, James Phillips, Microsoft VP of Business Intelligence, announced the general availability of the new Power BI Business Analytics Service will be July 24th. Also referred to as “Power BI 2.0”, the new service will eventually replace the 1.0 Power BI extension to Office 365. In a nutshell, this means that data transformations, data modeling, dashboard visualization and report design can be performed in the free Power BI Designer stand-alone application rather than requiring you to license Excel 2013 ProPlus and/or Office 365. A Power BI online subscription is free and advanced features are about $10 per month per user. Models and reports created using the Excel add-ins can still be published to the Power BI hosted service but Excel is no longer a requirement. Read the complete announcement here.
There are many fine points to the Power BI equation that will be important factors for companies considering the adoption of Power BI. Interest in the industry is very high and I’ve had several conversations lately with business leaders from companies who want to learn more and and are considering using Power BI to empower analysts and business users instead of relying on traditional IT solution design. To clear the muddy water, here are some popular questions and answers:
To use Power BI, are we required to put our data in the cloud? Technically, Yes; but maybe not. To use all of the latest features of Power BI, including Q&A natural language queries, new visuals, sharing published reports and dashboards; you will need to publish the queries, models & reports you author with the Power BI Designer or Excel 2013 ProPlus add-ins to the cloud-based service at PowerBI.com. The content is secured and encrypted but this is the biggest blocking point for many large businesses who, either for regulatory reasons or just out of fear for losing control of their important data or intellectual property. For other companies who have learned to trust cloud services, this is no big deal. Heck, just about all of us move sensitive data over the Internet every day but it’s still a hang-up for a lot of big companies.
What options are there to use Power BI features on-premises? Quite a few, actually. Most of the features of Power BI were originally created as add-ins for Excel. This isn’t to say that the features in Excel are the same as the latest Power BI service… they’re not but most are either the same or very similar. To use the more recent versions of Power Query, Power Pivot and Power View, you’ll need Office 2013 ProPlus and you should be using the 64 bit flavor rather than 32 bit. This is also a bit of a hang-up in big companies who don’t upgrade software very quickly. I’ve found that even in big, stodging businesses, getting the right version of Office installed isn’t impossible if you can make a case for the business value it brings. If compatibility with old Excel add-ins is a concern, you can use a virtual machine or install new Office on a second computer.
Once you’re using Excel 2013 to author models and reports, you have a few options to share Power Pivot data models, Power View reports and Excel reports with other team members:1. Excel, Power Pivot and Power View integrate with SharePoint 2013 Enterprise edition. This isn’t cheap or real easy to setup but it will allow many users to share and manage content. The latest Power BI cloud features won’t always be supported but we can likely expect occasional updates.
2. Just stick the Excel file on a shared folder so others running the right Excel version can open and view it. This isn’t exactly a collaborative solution but will work with a small group and zero additional effort.
3. Suck it up and use the cloud. This is where everything is going. Microsoft’s directive for all their products is “cloud-first, mobile-first” and most of their competitors are now doing the same – including the major BI vendors. For most companies, adopting the cloud isn’t really a security or regulatory problem so much but it requires a shift in business culture and accepting new options and different challenges.
4. Wait for the on-prem version. Microsoft has promised that most new capabilities introduced in the cloud will eventually make it into installed editions of their software. We probably won’t see on-prem parity with Power BI real soon but individual features will most likely be migrated when they can.
What is the real cost of Power BI? When Microsoft says Power BI is “free”, they mean it – really. According to the pricing information here, at no cost, users can manage 1 GB of compressed data. That’s actually a lot. There are other factors and limitations that won’t affect casual data analysts with moderate needs. Having a development and demo sandbox with no expiration date is pretty cool. For about $10/month, users can collaborate with team members, manage and integrate larger sets of data. That’s pretty cheap.
Can we integrate Power BI with on-prem data and IT systems? Yes. The Power BI REST API lets you programmatically push data into a published model at-will rather than scheduling data refresh or requiring users to do it manually.
The SSAS connector allows report and dashboard visuals to query an on-premises instance of SSAS Tabular rather than pushing the model into the cloud.
The Data Management Gateway allows a data model to refresh data from your on-premises SQL Server databases at scheduled intervals.
Data models can be scheduled to refresh once-per-day for users with the free Power BI subscription and up to once-per-hour with the Pro ($10) subscription.
How does Power BI measure up to competing services like Tabeau and Qlik?
In my opinion, Microsoft is the underdog with the best integration story but they have lost ground to make up. The article from Martin Heller below is a great comparison of features & capabilities. In the past few years, Microsoft has taken it’s BI and reporting platform in all kinds of crazy, confusing directions and have created several interesting but disconnected products. Under new leadership and vision, they’re moving quickly toward a new goal and I’m encouraged by what I see. Power BI development is moving faster than other products at Microsoft can keep-up with (part of the reason they disconnected Power BI from Excel). We’re seeing features added to Power BI just about every month.
As a rule, don’t ever bet on what might be but do pay attention to how quickly Power BI has progressed in the past few months – and what they’re likely accomplish in the near-term future with this product.
How can I see what features are coming, what others are asking for and how can I voice my opinion about new Power BI features?
A new Power BI Community site is managed and monitored by the Power BI leadership and development team. Check out http://community.powerbi.com/
Here are a few good articles & posts related to the new Power BI offering:
I’d like to make you aware of a three-hour training event that I will be presenting, hosted by SQL Server Pro Magazine on Thursday, July 30th beginning at 11 AM Eastern Time. Each one-hour session has a one hour break between and registered attendees can watch the sessions again after the event. You can get details about each session and register using the links in this event summary:
Microsoft DataZen is a new tool from Microsoft built on a mature and time-tested foundation of enterprise services and visualization components. Analytic dashboards created by data professionals are available to users on desktops and practically all modern mobile device platforms. Enabling a stunning user experience requires BI solution developers to set up servers, configure services and data sources, write queries and design dashboards.
This class will guide students through this complex process and show them how to create a comprehensive dashboard solution; beginning with the essentials and then exploring advanced capabilities to integrate dashboards into an enterprise BI ecosystem.
By the end of the course you will learn:
How DataZen fits into an enterprise BI strategy & works with other Microsoft BI & reporting technologies
How to build and configure a secure DataZen Enterprise Server
Dashboard design basics and best-practices
How to apply advanced features like parameters and expressions to create dashboard navigation & drill-through capabilities
In addition to the technical sessions, Paul will be available during each session for LIVE Q&A. PLUS if you miss a live session or want to review the recordings, you will have access to the complete course recordings and materials for 12 months.
In this introductory session, we tour DataZen mobile dashboard solutions, learn about scenarios where the tool is effectively used to provide business value and enable users to visualize data on various mobile devices. Learn how DataZen fits in the Microsoft BI product spectrum and how it can be used to create an integrated BI and reporting solution.
Get started building a solution by installing and configuring DataZen Enterprise Server. Enable email delivery, dashboard hubs, users, branding and caching options. Prepare for dashboard design by creating data sources and data view queries.
Using the DataZen Publisher and integrated dashboard designer, learn to create functional dashboards for tablet, phone and desktop devices. You will learn to use several visualization controls to create an interactive dashboard experience with navigators and data selectors, gauges, charts, maps and data grids.
This session builds on the foundation of server objects created in the first session, using data sources and data views to drive dashboards and KPIs based on server-hosted data in SQL Server and Analysis Services.
We begin with a tour of sophisticated capabilities used to integrated dashboards into a complete Business Intelligence and enterprise reporting solution. You will learn how parameters and expressions are used to pass selections and context to another dashboard, web site or report. Use expressions and variables to customize the user’s navigation experience, providing drill-through paths to dashboards and detail reports.
If you’re considering heading to Seattle to attend the PASS Global Summit on October 27-30, register before July 12 because the cost goes up $400. You can also save an additional $150 by using this discount code, compliments of Oregon SQL PASS Chapter:
Bring your laptop and let’s build a real business intelligence (BI) solution using Power BI! In this deep-dive session, we build a complete solution. You will need the Power BI Designer (available for free at powerbi.com). You will learn how to cleanse and transform data from multiple sources with Power Query, model data for reporting with Power Pivot, and explore the model and build interactive dashboards with Power View. You’ll leave with a working BI solution.
SQL Server Upgrade Preconference Session: October 26
I will also be co-presenting a full-day preconference session on upgrading SQL Server, along with Tim Chapman, Jim Miller, Richard Waymire & Ron Talmage from SolidQ. We will cover upgrading the entire SQL Server feature set from every version since 2005 to newer versions (2012, 2014 & 2016). I be talking about upgrading SSRS, BI design tool and implementing SSAS Tabular.
