Introducing Power BI for Office 365 – SQL Server

Power BI for Office 365 is a new self-service business intelligence (BI) solution delivered through Excel and Office 365 which provides information workers with data analysis and visualization capabilities to identify deeper business insights from their on-premises and cloud data.

Businesses today are feeling the impact of some important trends that are converging in the enterprise: the growth of cloud services, a marked increase in data volume and processing needs, and employee demand for more simplified, intuitive connections with that data. In response to these trends, Power BI for Office 365, was created which acts as a solution as it dramatically reduces the barriers for businesses of all sizes to use and deploy self-service BI tools.

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. Customers now have the ability to easily search, discover and access data inside and outside of their organization and, with just a few clicks, shape and transform that data. They can also analyze and create stunning interactive visualizations that uncover hidden insights to share and collaborate from virtually anywhere, on nearly any device.

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 at

Learn how to use SQL Server, from beginner basics to advanced techniques, with online video tutorials taught by industry experts. Enroll in the Free SQL Server Training Demo!

Power BI Sites

A Power BI site is a special type of SharePoint Online site that serves as a repository for Excel workbooks that implements 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.

 MindMajix YouTube Channel

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.

Related Page: How To Create A Power View Report In Excel 2013

Workbooks enabled on a Power BI site
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.

Workbook status
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.

Frequently Asked SQL Server Interview Questions & Answers

Opening Power View in HTML5

Regardless of which model 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 can be subjected 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

Power View report
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 on 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.

Power BI workbook
FIGURE 5.4: A Power BI workbook flagged as a favorite report displayed on the My Power BI site.

Explore SQL Server Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download Now!

List of Related Microsoft Certification Courses:

 SSIS SQL Server
 SSAS Team Foundation Server
 SCCM BizTalk Server
 SharePoint BizTalk Server Administrator
Course Schedule
SQL Server TrainingMay 28 to Jun 12View Details
SQL Server TrainingJun 01 to Jun 16View Details
SQL Server TrainingJun 04 to Jun 19View Details
SQL Server TrainingJun 08 to Jun 23View Details
Last updated: 04 Apr 2023
About Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.

read less