Microsoft Power BI: an overview
Power BI is the collective term from Microsoft for an organization-wide Self-service business intelligence (BI) infrastructure.
Power BI empowers end users and provides tools to take care of an end-to-end business intelligence scenario for Self-service BI solutions:
- Discover data and combine data that is coming from different sources
- Model the data brought-in from different sources.
- Visualize the data from different perspectives with interactive visualization options
- Share those visualizations with your peers
Power BI consists of 2 main parts:
- Self-service BI Features: Excel / Power BI Desktop
- These features extend the data-specific features and capabilities of Excel2013 in a familiar environment.
- The Power BI Desktop is the version 2 of the Self-Service Power BI toolset: all capabilities bundled in one application a dedicated report authoring tool.
- Power BI in the cloud
PowerBI.com amplifies the Self-service BI capabilities created in the Self-service part by making them available in a collaborative online environment.
Figure 1 Overview Power BI
The Self-service Part: Analyzing data with Excel
Excel enables Self-service BI by providing 4 Power BI add-ins. Each add-in has his own focus:
- Power Query
easily discover and connect to data from public and corporate data sources
- Power Pivot
create a sophisticated Data Model directly in Excel
- Power View
create reports and analytical views with interactive data visualizations
- Power Map
explore and navigate geospatial data on a 3D map experience in Excel
You need the right Office version to be able to use these add-ins: Office 2013 / 365 Professional Plus.
(When possible it is recommended to install the streamed version of Office 365 instead of the MSI install the streamed version has faster release cycles and updates are available more quickly)
With the right version the add-ins become available and need to be enabled in Excel via menu File > Options > Add-ins > COM Add-ins.
Figure 2 Excel COM Add-Ins
From now on the Power BI functionalities are available within your Excel workbook. There is an extra ribbon for Power Query and Power Pivot. In the Insertribbon you have the possibility to add a Power View or a Power Map visualization.
Figure 3 Excel Ribbon with Power BI functionality
Microsoft Power Query for Excel enhances Self-service business intelligence (BI) for Excel with an intuitive and consistent experience for discovering, combining, and refining data across a wide variety of sources including relational, structured and semi-structured, OData, Web, Hadoop, Azure Marketplace, and more. Power Query also provides you with the ability to search for public data from sources such as Wikipedia.
Power Query is also called the Self-service ETL toolfrom Microsoft. Basically you search for the data you need to do your analysis either from the web or from another data source
Figure 4 Power Query Discover Menu
Finding your data is one thing, but in most cases your data is not shaped in a way you can use it to do your analysis. In this case you can use the data found and make a re-executable query that shapes the data according to your needs.
Via the Power Query Query Editor you have quite a number of transformation tasks available:
Figure 5 Power Query Query Editor Main Menu
Figure 6 Power Query Query Editor Transform Menu
Figure 7 Power Query Query Editor Add Column Menu
All transformation steps you did on your data are recorded (see applied steps in Figure 8). This query is re-executable actually you can compare this to an Excel macro.
Figure 8 Power Query Query Editor Applied Steps
With Power Pivot you can perform powerful data analysis and create sophisticated data models. You can mash-up large volumes of data from various sources, perform information analysis rapidly, and share insights easily.
The data you work on in Excel and in the Power Pivot window is stored in an analytical database inside the Excel workbook, and a powerful local engine loads, queries, and updates the data in that database. Because the data is in Excel, it is immediately available to PivotTables, Pivot Charts, Power View, and other features in Excel that you use to interact with data.
Create your Power Pivot Data model and click the Manage button in the Power Pivot ribbon to start.
Figure 9 Power Pivot Main Menu
You get an empty Power Pivot window where you can start importing data. Several data sources are supported.
Figure 10 Power Pivot Manage Window Home menu
Once you have imported your data into the Power Pivot data model, you can start optimizing your data model: set datatypes, add calculations (DAX Data Analysis Expressions), add relationships between the different tables, add hierarchies, hide link fields for the end user, add synonyms
Figure 11 Power Pivot Manage Window Design Menu
Figure 12 Power Pivot Manage Window Advanced Menu
An overview of the main capabilities with Power Pivot for Excel and a detailed step-by-step walkthrough:
- Get data from different sources
- Create Relationships between tables
- Add Calculations
- Create Hierarchies
- Add KPIs
- Create Perspectives
Now that you have built your Power Pivot data model, you are ready to make an interactive Power View report using the data model.
Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting.
In Power View, you can quickly create a variety of visualizations, from tables and matrices to pie, bar, and bubble charts and sets of multiple charts. For every visualization you want to create, start with a table, which you then convert easily to other visualizations to find one best illustrates your data.
To create a table, click a table or field in the field list, or drag a field from the field list to the view. Power View draws the table in the view, displaying your actual data and automatically adding column headings.
To convert a table to other visualizations, click a visualization type on the Design tab. Power View only enables the charts and other visualizations that work best for that data in that table. For example, if Power View doesnt detect any aggregated numeric values, then no charts are enabled.
Power View is very easy to use for an end-user, on the other hand the capabilities remain rather basic in comparison with some other interactive data visualization tools. The good news is that Power BI is evolving rapidly and already some extra possibilities are added in the new experience.
See here how to create a Power View Report in Excel.
Figure 13 Power View - Example Report
With Power Map, you can plot geographic and temporal data on a 3-D globe or custom map, show it over time, and create visual tours you can share with other people. Youll want to use Power Map to:
- Map data
Plot more than a million rows of data visually on Bing maps in 3-D format from an Excel table or Data Model in Excel.
- Discover insights
Gain new understandings by viewing your data in geographic space and seeing time-stamped data change over time.
- Share stories
Capture screenshots and build cinematic, guided video tours you can share broadly, engaging audiences like never before. Or export tours to video and share them that way as well.
When you have Excel data that has geographic properties in table format or in a Data Modelfor example, rows and columns that have names of cities, states, counties, zip codes, countries/regions, or longitudes and latitudesyoure ready to get started
See here how to create a Power Map Report in Excel.
Figure 14 Power Map - Example Report
The Self-service Part: Power BI Desktop
The Power BI Desktop Designer is a freedesktop application. In fact all Self-service Excel functionalities described earlier are bundled in this one application.
There are three views in Power BI Desktop: Report view, Data view, and Relationships view. Power BI Desktop also includes Query Editor, which opens in a separate window.
Via the Data view and the Relationship view, advanced data exploration & modeling like you can with Power Pivot are possible.
The data shaping like you do with Power Query is possible with the Query Editor.
The highly interactive visualizations you make with Power View & Power Map can be built in the Report View.
Figure 15 shows the Power BI Desktop Application with the Report View.
Figure 15 Power BI Desktop
Reports made with the Power BI Desktop application can be saved to a Power BI file (*.pbix) or published to your Power BI site to share with others.
Excel with the 4 add-ins vs Power BI Desktop application
How do we position the two client applications for Self-service BI?
Actually - both tools use the same engine: Modeling aka Power Pivot and Query aka Power Query.
The Visualization layers are quite different. Essentially there is zero overlap here.
Excel has the usual suspects pivots and cube formulas, plus a somewhat half-baked version of Power View starting in 2013.
Power BI Desktop "only has the Report pane, which is a complete "reboot of Power View plus a wide range of brand-new visualizations. (ex: Treemap and Funnel)
Why did Microsoft come up with this Power BI Desktop application?
Beside some technical reasons (which would lead us too far for now), the big advantage of a separate Power BI Desktop application is that it can be rapidly improved via weekly releases.
Power BI originally tied itself to Excel for good reasons that remain valid, but that also meant tying itself to the complex and difficult-to-extend Excel codebase AND the Office release cycle has traditionally been measured in years. Power BI needs to improve rapidly, on a weekly basis.
What tool should you use?
If you dont have excel or you dont have the right Office version you can use the free Power BI Desktop application.
Also the visualization type you need - determines the tool: Power BI Desktop will never quite match the network effect of the Excel grid, or the amazing things you can do with cube formulas. But I also dont expect to see Tree decomposition charts in Excel any time soon, so this goes both ways.
Note that the Power BI files can only be shared / published via the PowerBI.com where Excel files can be published to PowerBI.com and SharePoint.
Nice to know is that you can easily import Excel data models (with embedded Power Query / Power Pivot and Power View items) into Power BI Desktop. (see here how to do this)
Share & Collaborate with the Power BI cloud service
PowerBI.com is the Power BI Service resided in the cloud.
After making your own insight in Excel / the Power BI Desktop application, you can use the cloud to share your reports.
After publishing the report, it becomes available for your colleagues in the browser. Users without Excel on their pc can interactively use the report. There is one up-to-date centralized version of the truth. Moreover - you can schedule refreshes against the data sources and have a controlled database load e.g. overnight.
With the Power BI Service a lot of other features become available:
- Q&A - ask a question to your data using natural language.
- Organizational Content Packs - Package up and share your own dashboard, reports, Excel workbooks, and datasets with your coworkers
- Services Content Packs use content packs from existing services such as Salesforce, Microsoft Dynamics, and Google Analytics
- data refresh automation - need to setup a gateway for on premises sources
- (Nice to know is that there is that the SSAS connector enables a live connection to an on premise SSAS source: queries are sent from Power BI directly to the database there is no need to move all the data to the cloud only the dataset needed to render the visualization is sent to the cloud.)
- (Frequency and supported sources depend on the license)
- Mobile: View Power BI dashboards and reports in apps for iOS (iPad and iPhone), Windows, and Android
- Manage Permissions for shared insights (via groups)
When you first open Power BI you will see this:
Figure 16 Power BI Service
- navigation pane
- dashboard with tiles
- Q&A question box
- help and feedback buttons
- dashboard title
- Office 365 app launcher
- Power BI home
The 3 major building blocksof Power BI are: dashboards, reports, and datasets.
A dataset is something that you import or connect to. Use Power BI to bring all of your data together. See it all in one place and access it from all of your devices.
A Power BI report is one or more pages of visualizations (charts and graphs). Reports can be created from scratch within Power BI, can be imported with dashboards that colleagues share with you, or can be created for you when you connect to datasets from Excel, Power BI Desktop, databases,
A dashboard is something you create or something a colleague creates and shares with you. It is a single canvas that contains one or more tiles
ONE report... - can be associated with multiple dashboards (tiles pinned from that one report can appear on multiple dashboards). - can be created using data from one dataset
ONE dashboard... - can display visualizations from many different datasets - can display visualizations from many different reports.
Nice to Know for developers
Power BI offers an open, standards-based REST API to integrate your application or service with Power BI:
- Using any programming language that supports REST calls, you can create a business solution that updates a Power BI dashboard in real-time.
- With Power BI, you can enable application developers to integrate Power BI tiles and reports from a user's power BI account by embedding an Iframe into an app.
- You can create your own visual and add it to the Visuals Gallery for others to use.
Figure 17 Power BI Visuals Gallery
You can get a lot of the Power BI tools for freejust by signing up with your business email. But for $9.99/user/month, you get Power BI Pro, which comes with a higher data capacity limit, more frequent data refreshes, interactive connectivity to live sources, and a bunch of tools for collaboration and sharing. More details can be found here.
Power BI is an all-in solution meaning it does not require other tooling / platforms to unlock the full potential. Power BI isexcellent in providing ways to connectto "BIG data sources like Facebook, Hadoop and many more. However, Power BI is not suitable for actually "housing BIG data.
Using the freely available Excel add-ins or the Power BI Desktop, you can quickly and intuitively create compelling dashboards. You can then upload those to the cloud environment for easy access and for automated data refresh. Microsoft has taken a big step forward on the front-end wise with some new visualizations and the visuals gallery.
With Power BI, Microsoft has unlocked the full potential of Self-service BI.