Introducing Microsoft SQL Server 2014: Technical Overview, 1st Edition (2014)
PART II. Business intelligence development
CHAPTER 5. Introducing Power BI for Office 365
Power BI for Office 365 is a new business-intelligence (BI) solution that extends the self-service BI capabilities in Excel. Power BI enables users to share their work in the cloud while maintaining a secure connection to on-premises and cloud-based data sources and allows users to interact with reports in a browser or on a mobile device by accessing a common repository hosted in a special SharePoint site dedicated to Power BI. Users can even designate favorite reports to more easily find the reports they need most often and can get answers from their data by asking natural-language questions.
Power BI also provides a support infrastructure for managing self-service BI in the cloud. A Data Management Gateway provides connectivity to data sources. In addition, designated users can serve as data curators by finding and shaping data in Power Query and then sharing and annotating the query through Power BI.
Note To use Power BI, you must have Power BI for Office 365 E3 or E4, Office 365 ProPlus, or Office Professional Plus 2013 and the Power Map and Power Query add-ins. You can learn how to provision Power BI by following the steps in How to set up Power BI, available athttp://go.microsoft.com/fwlink/?LinkId=317870.
Power BI sites
A Power BI site is a special type of SharePoint Online site that serves as a repository for Excel workbooks that implement Power Pivot, Power Query, Power View, or Power Map. The site also provides a link to the My Power BI site and the Power BI Admin Center. After you subscribe to Power BI for Office 365, you can access the Power BI sites app and apply it to a new or existing site in your SharePoint Online Enterprise environment. At present, only users with a current Power BI for Office 365 subscription can access a Power BI site.
When you publish a workbook to a Power BI site, you save it to a SharePoint document library. Use the Save As command on the File tab in Excel, and then select your SharePoint Online account. You might need to sign in to continue. Typically, you can publish your workbook to your team site, and the Power BI site will automatically include the newly published workbook.
Note Objects and data that are not part of a Data Model cannot exceed 10 megabytes (MB) in size. If you have trouble saving a workbook because of its size, consider modifying or removing tables, images, shaded cells, colored worksheets, text boxes, and clip art.
The Power BI site is a view that restricts the display of documents to Excel workbooks. However, workbooks are not automatically enabled for Power BI. For example, if you upload a workbook to a standard SharePoint document library view, you must then navigate to the Power BI site (using the link in the Quick Launch panel on the left of your screen), click the ellipsis in the lower-right corner of the workbook tile, and then click Enable. In the Enable In Power BI For Office 365 dialog box, click Enable. Alternatively, if you navigate first to the Power BI site and click the Add link to upload your workbook directly to Power BI, it is automatically enabled. Either way, a thumbnail image of your workbook is displayed on the Power BI page soon after the workbook is enabled, as shown in Figure 5-1. The Power BI site can render an enabled workbook as large as 250 MB in the Excel Web App, whereas a standard site prevents the rendering of a workbook that is larger than 30 MB.
FIGURE 5-1 Workbooks enabled on a Power BI site.
When you click a workbook, the Excel Web App displays the workbook in the browser and allows you to navigate from sheet to sheet. You can interact with PivotTables and PivotCharts by filtering and sorting, but you cannot change the layout of the pivot at this point. Similarly, a Power View sheet allows you to interact by cross-filtering, sorting, and filtering, but you cannot add or modify fields in the report. To fully interact with the workbook, click the right-most icon in the status bar of your browser window, as shown in Figure 5-2, to view the full-size workbook. When you open the full-size workbook, you have access to the Field List for pivots, which lets you make changes online. However, to make changes to Power View reports, you must edit the workbook in Excel. If the Field List is hidden, right-click the PivotChart or PivotTable, and select Show Field List.
FIGURE 5-2 Workbook status bar from which the full-size workbook is accessible.
Configuring featured workbooks
You can designate a maximum of three workbooks as featured workbooks for a Power BI site. Featured workbooks appear at the top of the site’s page, above the thumbnail images of the complete set of uploaded workbooks. To feature a workbook, click the ellipsis in the lower-right corner of the workbook tile, and then click Feature. If you select this option for a fourth workbook, that workbook is added as a featured workbook and the first workbook you selected in this process is no longer featured.
Opening Power View in HTML5
Regardless of which mode you use to open your workbook, a button icon is displayed at the lower-right corner of your Power View report, as shown in Figure 5-3. Use this button to display your report in HTML5. At the time of this writing, this feature is in preview mode and subject to change. Because Power View has a Silverlight requirement, the types of devices and browsers that can display a report are limited. With HTML5, the range of possibilities for interacting with reports in a browser interface is expanded.
Note The HTML5 preview will continue to evolve after this writing. For a current set of features supported by Power View in HTML5, see http://office.microsoft.com/en-us/excel/features-of-the-power-bi-app-and-power-view-in-html5-preview-HA104168262.aspx?CTT=5&origin=HA104149776.
FIGURE 5-3 A Power View report displayed in Power BI with the HTML5 icon in the lower-right corner.
Adding favorite reports to My Power BI
Each user can mark reports as favorites to have them appear in a private site called My Power BI. To do this, click the ellipsis in the lower-right corner of the workbook tile on a Power BI site, and then click Favorite to add it to My Power BI (or Unfavorite to remove it). A star is displayed in the upper-right corner of the thumbnail image as a cue that the report has been marked as a favorite. The report does not move but is tagged for display when you open the My Power BI site. If you have multiple Power BI sites in the same SharePoint Online tenant, you can consolidate reports from these sites into your own My Power BI site. When you have the Power Bi site open, you can click the My Power BI link at the upper-right corner of your browser window to view your reports, as shown in Figure 5-4.
FIGURE 5-4 A Power BI workbook flagged as a favorite report displayed in the My Power BI site.
Shared queries
Self-service BI is only as good as the information that people can find on their own. The purpose of Power BI is to help users find the data they need, obtain access if necessary, and confirm the validity of the data. Shared queries—resources created by some users for others—can fulfill this purpose, but shared queries are managed by data stewards and administrators to ensure that data is being used correctly. Users can work with shared queries in Power Query and then monitor usage analytics in the Manage Data portal.
Creating a shared query
If your organization is using Power BI for Office 365, you can share a query from Power Query by storing the query in the cloud. When you share a query, you save not only the metadata that describes the data source but also the subsequent transformations, so you or others can reuse the steps from that query later. The data resulting from the query’s execution is not saved in the cloud as part of the shared query.
To share a query, you must first sign in to Power BI, which you do by clicking Sign In on the Power Query tab on the ribbon. Click Workbook on the Power Query ribbon to view a list of queries in the workbook, right-click the query, and then select Share. In the Share Query dialog box, shown in Figure 5-5, you can modify the name and description of the query. You should take time to provide a meaningful and keyword-rich description to help users find the query through a search. You might prefer to use a Word document to provide a longer description of the query. Rather than typing the description, you can use the Document URL field to specify its location.
FIGURE 5-5 The Share Query dialog box displaying query metadata and sharing settings.
The View In Portal link (which opens the Manage Data portal in SharePoint Online) is displayed in the Data Sources section, which also includes a list of all data sources contributing to the query results. You can review usage analytics online to determine whether the addition of a new shared query is helpful or conflicts with existing queries. This analysis may in turn help you determine how to distinguish your query in the name and description fields.
You must set the sharing settings to grant access to yourself only, to everyone, or to specific Windows logins or Windows security groups. If you are the one sharing the query, you are automatically included and do not need to explicitly add your login to this section.
Another decision you make when creating a shared query is whether to upload rows of data for previewing. If the underlying data source contains sensitive data, you should not select the Upload First Few Rows For Preview check box. Even if a user does not have permissions to access the data source, the query preview results are still visible when a user browses shared queries.
If you are a member of the Data Steward group, you have the option to certify a query. The certification check box is displayed only to members of this group. Certification is an indication that a query has been formally reviewed and accepted for general use and is therefore considered a trusted query.
Using a shared query
When working with Power Query, you can use Online Search to find shared queries, or you can click Shared on the Power Query tab on the ribbon to view a complete list of shared queries. When you point to a shared query in the list, a preview flyout screen appears. To use the query, point to the ellipsis at the bottom of the flyout screen and click Add To Worksheet. If you need to modify the query, click Edit Query on the Query tab under the Table Tools tab on the ribbon.
If you do not have permissions to use a shared query, you see a Request Access link. This link sends an email message to a designated recipient or opens a webpage where you can request access. The data steward responsible for the shared query specifies whether the access request is associated with email or a URL.
Managing query metadata
As a data steward, you can review data sources and manage metadata in the My Power BI site. To do this, click the Data link at the top of the page, and then click Data Sources in the navigation pane. Here you have access to the cloud-based metadata repository for Power BI. As data sources are added, the metadata is often missing, and you see a list of untitled data sources. Click the ellipsis to the right of a data source, click Edit, and then type a display name and a description. In addition, you have the option to provide an email address or a URL for users to contact when they request access to this data source.
Reviewing usage analytics
When your My Power BI site is open, you can use the Data link to open the Manage Data portal. Another way to access this information is to sign in to Power BI from the Power Query tab in Excel and then click Shared on the ribbon. Point to a query, click the ellipsis at the right of Edit Settings at the bottom of the preview flyout screen, and then click View Statistics. Both of these methods take you to the same location in your Power BI site, the Manage Data portal.
This portal shows the usage analytics for your queries (shown in Figure 5-6), the shared queries you created by using Power Query, and the data sources you have used. This information can help you measure the usefulness of your queries and see whether others have come to rely on those queries. You can assess this information to determine whether it’s time to formalize a data source as part of your organization’s enterprise information architecture. You can filter the usage analytics to display results for the last day, the last 30 days, or the last year.
FIGURE 5-6 Usage analytics in the Manage Data portal.
Power BI Q&A
Power BI Q&A is a self-service BI feature available only online. First, you upload a workbook to the Power BI site. Then, you can type your own questions or select from a list of suggested questions. These questions do not require you to learn a specialized query or expression language, however. Instead, you ask questions by using your own words. Q&A automatically selects the best type of visual display for the data it returns from the query, which might be an interactive chart, a map, or a graph. You can override this behavior by including the name of the visualization that you prefer in the question or by changing the visualization after viewing the selection from Q&A.
To start Q&A, you must have at least one Power BI–enabled workbook uploaded to your Power BI site. Click the ellipsis in the lower-right corner of the workbook tile, and then click Add To Q&A. Next, click the Search With Power BI Q&A link at the upper-right corner of the page. Start typing your question in the balloon that’s displayed at the top of the page. Q&A can autocomplete words you enter and displays sample questions that you can use to refine your question further. When you finish the question, Q&A displays the results. You might need to adjust the source workbook by clicking Show More in the navigation pane and selecting a different workbook, as shown in Figure 5-7. To change the current visualization, select one of the items in the Show As list in the navigation pane on the left side of the screen.
FIGURE 5-7 A Power BI Q&A question and results.
Here are some examples of the types of phrases that you can use with the report pictured throughout this chapter:
Show total sales by category for Adventure Works
Which subcategory of bikes has the highest sales?
Show a list of customers in Washington
Note Because a common idea can be expressed in many ways, you can add synonyms to a data model to help users find information when they do not know the specific names of objects in that model. For more information about working with synonyms, see http://office.microsoft.com/en-us/excel-help/add-synonyms-to-a-power-pivot-excel-data-model-HA104143188.aspx?CTT=5&origin=HA104149776. Additionally, there are optimizations to your data that can help improve the search results from Q&A. Learn more about these techniques at http://office.microsoft.com/en-us/excel-help/enhancing-and-tuning-excel-power-pivot-workbooks-for-power-bi-q-a-HA104143202.aspx?CTT=5&origin=HA104149776.
Power BI for Mobile
Power BI for Mobile is a touch-optimized Windows 8 app for a tablet. By using this app, you can view any workbook saved to Office 365.
Preparing a workbook for mobile viewing
Power BI for Mobile does not display a workbook in the same way that SharePoint Online does. Instead, it displays a workbook as a set of related pages with one item to a page. An item is a Power View report, a PivotTable, a chart, a PivotChart, or a table. A named range is also an item that is displayed on a separate page, except that other items appearing within the range appear on the same page. You determine which items are displayed online by opening the File tab in Excel, selecting Info, and then clicking Browser View Options. In the Browser View Options dialog box, you can select Sheets or Items in the drop-down list. If you choose Sheets, you can choose to display only Power View reports.
The only control you have over the sequencing of pages is to use a naming convention. Power BI for Mobile displays all items except Power View reports alphabetically by name. Then it displays the Power View reports in the order in which they appear in the workbook.
Important To be visible in the Power BI for Mobile app, your workbook item or sheet must be based on the Excel Data Model in a workbook hosted in Office 365, a Windows Azure SQL Database, or an OData feed. An item or sheet based on any other type of data source will be displayed as a blank page.
Using Power BI for Mobile
When you open the Power BI for Mobile app, swipe up to display the app bar, tap Browse, and then, on the Locations page, swipe up to show the app bar. In the box, type the URL for your SharePoint Online site, and then tap the Go arrow to the right of the URL. You must provide a user name and password to browse your workbooks. Navigate to Shared Documents, and then tap a report. Once a report is open, you can swipe up at any time to access the app bar, where you can tag a report as a favorite or use the Reset To Original button to restore the report to its original state and refresh the data. In addition, you can see thumbnail images of the report, as shown in Figure 5-8, so that you can easily jump to a different page.
FIGURE 5-8 A Power View report and thumbnails of workbook pages in the Power BI for Mobile app.
The Power BI for Mobile app is designed for an interactive touch experience with the following features in mind for Power View:
Highlight In a Power View sheet, tap a chart element such as a bar, column, or legend item to bring the selected data into view. The other data is displayed with a more subdued hue to help you see the ratio between the two subsets of data. Clear the selection by tapping the chart background. You can select multiple items for highlighting by tapping the icon with three bars in the upper-right corner of the chart.
Filter For any item that has a filter defined, tap the filter icon in the upper-right corner to open the filter pane. You can change filter values and clear the filters, but you cannot add or delete a filter.
Zoom You can pinch and expand your fingers on the tablet’s surface to zoom out or in, respectively. However, this technique currently works only on Excel items and not on Power View sheets.
Sort a table column To sort, tap the column heading in an Excel item. Swipe labels on the horizontal axis of a Power View column chart or the vertical axis on a bar chart.
Drill You can double-click a field to drill down to the next level of a hierarchy (if one is defined in the data model). Click the Drill Up arrow to return to the previous level.
As long as you leave the Power BI for Mobile app open, the filter and sort selections you have applied remain intact. However, when you close the app, the filter and sort selections are removed.
Sharing a report from Power BI for Mobile
To share a report that you are viewing in the Power BI for Mobile app, swipe from the right, click Share, and then type the email recipient’s address. The app sends an email message containing a link to the report rather than the page you are viewing. The recipient can use one of two links to view the report, but only if the recipient already has permissions to view the report in SharePoint Online. The MOBILEBI link connects the user to the report in the Power BI for Mobile app, and the HTTPS link opens the report in a browser window using the Excel Web App.
Power BI administration
Power BI for Office 365 includes an administrative infrastructure that you manage through the Power BI Admin Center. To access the Power BI Admin Center if you are a member of the Admin group, click the Power BI Admin Center link on the Tools menu (gear icon) in the upper-right corner of a Power BI site. From a standard SharePoint Online site, click the Power BI link on the Admin menu in the upper-right corner. This centralized portal for administrative tasks allows you to perform the following tasks:
Monitor system health Use the System Health page to access logs. You can search by severity, source, start and end dates and times, and keyword. The logs are displayed in a table format in your browser, or you can download current logs as a CSV file. When you point to a message describing an error in the log, a pop-up window displays a link to a help page with instructions to resolve errors and warnings.
Manage data gateways Use the Gateways page to review a list of existing gateways and their current status. A gateway is the mechanism that securely connects Power BI to an on-premises data source and runs as a client agent on an on-premises computer. When you use this page to set up a new gateway, you download the gateway client, install it locally, and then register the gateway with a key provided in the Power BI Admin Center. You can also use this page to regenerate the key for an existing gateway or to enable the cloud credential store for the gateway so that you can quickly restore the gateway on another computer if the current gateway fails.
Configure data sources Use the Data Sources page to review a list of existing on-premises data sources and their current status. You can edit, delete, or test the connection for an existing data source. When you edit the data source, you specify whether it is enabled for cloud access, which means workbooks with this data source can be refreshed in SharePoint Online, and whether it is enabled as an OData feed that users can access by using Power Query. You also use the Data Sources page to add a new data source, optionally enable it for cloud access or an OData feed, assign it to a gateway, and specify the connection properties or provide a connection string. Currently, only SQL Server and Oracle are supported as data source types.
Define security for administrative roles Use the Role Management page to add members to the Admin group, which grants permission to access the Power BI Admin Center, or to the Data Steward group, which grants permission to certify queries for use in Power Query.
Specify settings for Power BI Use the Settings page to configure general settings such as whether to display top users in the usage analytics dashboard or to enable Windows authentication for OData feeds when the Microsoft Online Directory Synchronization Tool is configured. You can also define the recipients of email notifications and specify whether any of the following events trigger a notification: the expiration of a gateway, the release of a new version of the Data Management Gateway client, or an indexing failure for an OData feed.