Power BI for Microsoft Office 365
.png "patterns & practices Developer Center")
From: Developing big data solutions on Microsoft Azure HDInsight
As businesses increasingly move IT services to the cloud, many organizations are taking advantage of Office 365 online services for productivity application deployment and management, email and communications, and document sharing. Power BI is an additional service for Office 365 that enables business users to publish and share queries, data models, and reports in Excel workbooks across the enterprise; and to engage in rich, cloud-based data exploration and visualization. Power BI is available as a standalone Office 365 service or as an add-on for existing Office 365 enterprise-level online service plans.
Power BI for Office 365 builds on the capabilities of Power Query, Power View, PowerPivot, and Power Map for Excel to enable self-service reporting and data visualization in Power BI sites. The reports and visualizations can be accessed in a web browser, or through a Windows Store app on Window 8 and Windows RT devices. Business users can use Power BI to take on data stewardship responsibilities for the data they share, managing access to the data and providing appropriate documentation for other users in the organization who discover it. Additionally, administrators can use the Data Management Gateway feature in Power BI to securely publish on-premises data sources to Power BI, enabling automated refresh of data models in shared workbooks that have been published in Power BI sites.
Sharing Power Query queries with Power BI
One of the key foundations of a self-service BI solution is discoverable data. Not all business users have the expertise to construct queries against Hive tables or the Azure blob store, but most are already familiar with searching for data on the web. Power BI enables business analysts to define queries that return data (including the results of big data processing in HDInsight) and share them with the organization to make the data discoverable. Business analysts take on the role of data steward for the queries they publish. They can provide documentation about the data returned by the query, and how to request access to the underlying data sources. Figure 1 shows a query published by a Power BI user.
.png "Figure 1 - Sharing a Query")
Figure 1 - Sharing a Query
After a query has been shared, other users in the organization can discover it by using the Online Search feature of Power Query, as shown in Figure 2. This ability to share a query for discovery through search makes it easier for users to incorporate the results of big data processing into their self-service data models and reports, even if they lack the expertise to build their own queries.
.png "Figure 2 - Discovering shared data with Online Search")
Figure 2 - Discovering shared data with Online Search
After discovering data that has been exposed through shared queries, business users can use PowerPivot to create their own data models that combine data from multiple sources, and use Power Map and Power View to visualize that data in Excel workbooks.
Publishing reports in Power BI sites
Organizations that have a Power BI for Office 365 subscription can share workbooks containing data models, Power View reports, and Power Map tours in special Power BI sites that are hosted in SharePoint Online. Business users can view these workbooks as interactive reports in a web browser, or in the Power BI app available from Windows Store. Power BI sites enable administrators to promote specific workbooks as featured reports, and users can add their own favourite reports to their My Power BI site. Figure 3 shows a report in a Power BI site.
.png "Figure 3 - Viewing a report in a Power BI site")
Figure 3 - Viewing a report in a Power BI site
In addition to publishing Power View visualizations as reports, administrators can add workbooks to the Power BI Q&A feature, which enables users to query the data models in the workbook by submitting natural language expressions. For example, a data model containing meteorological data could be queried using expressions such as “Which regions have the highest average temperature?” “Show maximum temperature by month” or “Show average wind speed by country as a pie chart.” Power BI interprets these expressions and applies an appropriate query to retrieve and visualize the required data. Figure 4 shows a report generated by Power BI Q&A.
.png "Figure 4 - Using Power BI Q&A to query a data model using natural language")
Figure 4 - Using Power BI Q&A to query a data model using natural language
Power BI for Office 365 is a great choice when you want to empower business users to create and share their own queries, data models, and reports. Users with the necessary skills can use HDInsight to process data (for example, by using Pig or Hive scripts), and then import the data directly into Excel data models using the Hive ODBC Driver or Power Query. The reports generated from these data models can then be published in a Power BI site where other business users can view them.
Alternatively, you can publish the results of big data processing to the general business user population through shared queries that you have created with Power Query. Business users can then engage in self-service data modeling and analysis simply by discovering and consuming the big data processing results you have shared—without requiring any knowledge of how the results were generated, or even where the results are stored.
The following table describes specific considerations for using Power BI in the HDInsight use cases and models described in this guide.
Use case |
Considerations |
|---|---|
Iterative data exploration |
In an iterative data exploration scenario, users can use Power Query or the Hive ODBC Driver to consume the results of each data processing iteration in Excel, and then use native Excel charting, Power View, or Power Map to visualize the data. Power BI makes it easier for multiple analysts to collaborate by sharing queries and reports in a Power BI site. |
Data warehouse on demand |
When HDInsight is used to implement a basic data warehouse, it usually includes a schema of Hive tables that are queried over ODBC connections. Data analysts can use the Hive ODBC Driver to import the data from Hive into a PowerPivot data model and create Power View reports. They can then share the workbook in a Power BI site so that users can view and interact with the reports, or query the data model using Q&A. |
ETL automation |
Most ETL scenarios are designed to transform big data into a suitable structure and volume for storage in a relational data source for further analysis and querying. In most cases, the ETL process loads the data into a relational data store for analysis. However, it would be possible to use HDInsight to filter and shape data into tabular formats, and then use Power Query or the Hive ODBC Driver to import the data into a PowerPivot data model that can provide a source for reports and interactive analysis in a Power BI site. |
BI integration |
Power BI provides a platform for sharing queries, data models, and reports. By sharing queries that obtain HDInsight output files from Azure storage, organizations can use Power BI to make big data processing results discoverable for self-service BI. |
Guidelines for using Power BI with HDInsight
When using Power BI for big data analysis with HDInsight, consider the following guidelines:
- Use Power Query to share queries that retrieve data from HDInsight output files. This makes big data processing results discoverable by business users who may have difficulty creating their own queries.
- Foster a culture of data stewardship in which users take responsibility for the queries they define and share. Encourage users to document their queries and to monitor their usage in their My Power BI site.
- Use the Synonyms feature in PowerPivot to specify alternative terms for tables and columns in your data model. This will improve the ability of the Power BI Q&A feature to interpret natural language queries.