“New” Power BI

Microsoft Power BI offering is going through another evolution. New tools and services, available only as preview, have introduced significant changes as compared to Microsoft’s current approach to providing self-service BI capabilities.

In this post,  I will briefly talk about new tools/services and see how this will impact Microsoft BI community, both users and developers.

Power BI Dashboard

Power BI Dashboard is a cloud based service to create dashboards. You can create datasets, reports and dashboards using a web interface. It has capability to connect to lot of data sources including on-premise SSAS as well as couple of SAAS based applications like Salesforces.com, Microsoft Dynamics CRM etc. This eliminates the need to install any tool on your desktop. All you need is web browser to access Power BI service.


Here is a screen shot of Power BI dashboard. It provides better experience for creating dashboard layout as compared to Excel. However it is not functionally rich at this stage.

New Power BI 01

Power BI Designer

Power BI Designer (PBID) decouples certain capabilities from Excel and offers them in a standalone tool. PBID combines the capabilities of Power Query and Power View along with some additional features for dashboards.  Again, this eliminates the need for Excel as a desktop tool to explore data and create reports. However, currently PBID lacks certain features available in Excel.

Here is screen shot of Query View.

New Power BI 02

Here is screen shot of Power View.

New Power BI 03

Consultant’s Point of View

  • Key point here is to decouple Power BI from Office so that customers who do not have latest version of Office and SharePoint can use Power BI. Of course, it offers some new features for dashboards.
  • Deciding which tool to use in a given context is becoming even more difficult. Historically, Microsoft has been integrating BI capabilities in existing products (Office, SQL Server, SharePoint), however adoption has been challenging due to complexity of end to end technology stack. Now cloud and mobile further add to this complexity.
  • Limited support to connect to on-premise data sources.
  • PBID is being introduced with some of the capabilities available in Excel. Power Pivot data models,  Power maps, some features from Power View are missing in PBID. Again, too many options creates confusion for everyone.
  • Per user cost for Power BI is too much for lot of customers.

Self Service BI in Microsoft Ecosystem – Part 02

This is second blog about Self Service BI and it focuses on delivering self-service BI using Microsoft technologies.

Microsoft BI capabilities are embedded in three different products: SQL Server, SharePoint and Office. In this article, we will focus on Self Service BI capabilities in Office (Excel) and SharePoint.

Excel has always been primary choice for number crunching.  Previously, almost every major BI vendor had extensions available for Excel.  Microsoft finally realized to use Excel as a primary tool for delivering BI as well as enabling power users to perform ad-hoc data analysis.  Following diagram shows various components in Excel which enable self Service BI.

Power BI Excel

Here is brief description of each tool.

Power Query

Power Query is a mini ETL tool. It enables to discover public as well as enterprise data sources, and then connect, extract, and transform data from these sources.  You can perform certain data cleansing operations as well as define new calculated columns.   You can also augment enterprise data with public data sources e.g. an insurance company can use car crash data available publically along with their own statistics to see patterns or identify an anomaly. Following screenshot shows options available in Power Query tab.

Power Query

Power Pivot

Once data has been sourced and transformed by Power query, it can be added in Power Pivot data model. Power Pivot enables to combine data from various sources and build data model within Excel. You can define relationships in tables, define new columns, calculate measures using data analysis expressions (DAX).   This becomes very handy when you have to combine enterprise data sources with public data sources like data from Azure Data Marketplace.  Following screen shot shows Power Pivot designer.

Power Pivot

Power View

Once data model has been designed, you can create visualizations using Power View. Power View is built into Excel to create dashboards, containing multiple charts, graphs, tables and filters. User can interact with views to sort, filter and slice and dice information. It eliminates the need to talk to IT to design basic ad-hoc reports for business. Power user can create powerful visualization themselves and share it on the web. Below screen shot shows a power view screen.

Power View

Power Map

Power Map provides geospatial and 3D mapping capabilities in Excel. Users can plot data on the map as well as create convincing story boards for presentations.

Power Map

Power BI

All of the components discussed earlier are built into Excel. Once you have design reports in workbooks, you can use Power BI to share these reports on the web. Power BI also enables to generate natural language queries.  Excel Services in SharePoint Online can automatically refresh data by connecting to enterprise data sources. Power BI also provision of My Power BI sites to aggregate reports from various sites as favorites so that you have all the information available at one place.

Power BI

Windows Power BI App delivers same BI capabilities on mobile devices. Here is how Power BI app delivers BI on mobile devices.

Power BI App


In next part, we can talk about a real life business scenario in retail and see how it can be solved by using self-service BI components.

Note: Few images are taken from publicly available sample workbooks from Microsoft.

For discussion about Self Service BI, click Self Service BI in Microsoft Ecosystem – Part 01.

Self Service BI in Microsoft Ecosystem – Part 01

This post,  about Self-Service BI, is three parts post.  In first part, we will discuss what Self Service BI is,  and then in subsequent parts,  we will talk about capabilities in Microsoft technology stack as well as  a specific business scenario where self-service BI capabilities were deployed .

Among various definitions of the term, I find following one by Gartner, most relevant.

Self-service business intelligence is defined as end users designing and deploying their own reports and analysis within an approved and supported architecture and tools portfolio.

Key words here are “end users” and “approved architecture and tools portfolio”.

Historically, BI/Data related investments have been on the top in CIO’s agenda. These investments may be in traditional data management technologies like DBMS, ETLs, data quality, data warehousing, data marts, MDDBs, OLAP,  reporting or in latest capabilities like  predictive analytics, data discovery, self-service BI and big data.  However, even with so much technology available, when you look on business side, there is not much excitement about data/insights coming out of these investments. Putting aside large companies,  small to medium companies still rely on basic static reports being delivered from monolithic reporting systems without much flexibility. Such reports provide minimal value to business users.

If you ask business about how their information needs are being met, they would typically raise following issues:

  • Long cycle time to build new report
  • Limited to non-existent data analysis capability
  • Non-standard definitions/Inconsistent KPIs

A traditional BI environment is centrally managed by IT. IT takes care of everything from data capture to final report delivery.  Overall architecture and tools require significant effort to fulfill new data and analysis requirements.  Additionally, tools have significant learning curve and make it heavily dependent on developers.

Typically, reports have static content and structure with limited capabilities of searching and sorting. Another issue is non-standard definition of KPIs. Usually, there is lot of inconsistency in terms of how KPIs are defined across various departments and their reports. This makes reports-reusability very difficult.

So how Self Service BI promises to counter these problems? Self Service promises more power for end users to design ad-hoc data views and analyze data with minimal day to day support from IT.  This power comes through various sources namely:

  • Easy to use and familiar tools like Excel. Almost any computer literate person can use Excel for data manipulation. These tools have been enhanced with advanced capabilities to facilitate data analysis. Barriers to learning become very low once you have such tools available.
  • Well defined and governed infrastructure to provide enterprise data sources as well as ability to integrate external data sources. These data sources (data warehouse, data marts, cubes, ) are still centrally managed and have consistent definitions across departments.
  • Web based access to such tools makes it very easy to access and analysis information without creating siloes in Excel or other systems.

So Self Service BI focus more on the business users to deliver value out of data related investments. IT is still responsible for overall architecture, governance, performance, and  security. It is delivery part that moves towards power users.

In next post, we will discuss how self-service capabilities are available in Microsoft ecosystem