Export (0) Print
Expand All

Business intelligence capabilities in Excel, SharePoint Online, and Power BI for Office 365

 

Applies to: SharePoint Online Enterprise (E3 and E4), Power BI for Office 365, Office 365 ProPlus, Excel Online, Excel 2013

Topic Last Modified: 2014-10-15

Summary:  Understand business intelligence (BI) capabilities in Excel, SharePoint Online, and Power BI for Office 365.

To download a poster that summarizes BI tools, visit http://go.microsoft.com/fwlink/?LinkId=394694.

Download this SharePoint BI poster

Office 365 offers lots of business intelligence (BI) capabilities that your organization can use to gather data and turn it into meaningful information that you can use to make better business decisions. Excel 2013, SharePoint Online, and Power BI for Office 365 together offer you a full range of capabilities to view, explore, analyze, and share data.

The sections below explain the BI capabilities that you can use in Office 365.

Download a poster here that summarizes BI capabilities in SharePoint—whether your environment is on-premises or in the cloud.

You can create powerful reports, scorecards, and dashboards by using Excel 2013, which is available in Office 365 ProPlus. You can bring data into Excel, sort, and organize the data, and use it to create reports and scorecards. Review the following table to understand the BI capabilities that are available in Excel.

 

Task Capability Description

Get data

Native data

Native data is data that has been imported or entered into Excel and resides in Excel. Native data does not require an external data connection. This is useful when you’re working with static data, with data that is manually updated, and when you’re creating workbooks that are published where external data connections aren’t supported.

Get data

External data

External data is data that resides on another computer, such as a server. It is accessed in Excel through one or more external data connections. Excel 2013 can connect to many kinds of data sources.

Get data

Power Query

Power Query is a new add-in for Excel that you can use to find and connect to lots of data sources. These include private and public data sources. You can combine data from multiple sources, shape the data to suit your needs, and create custom views of data. For more information, see Introduction to Microsoft Power Query for Excel.

Get and work with data

Power Pivot and Data Models

Power Pivot for Excel is an add-in that enables you to create a Data Model in Excel. A Data Model is a powerful dataset that consist of one or more tables of data. A Data Model in Excel can support much more data than a basic Excel worksheet can. And, you can use a Data Model to bring together data from different databases and create relationships among tables of data. A Data Model serves as a data source for reports, scorecards, and dashboards. For more information, see Power Pivot: Powerful data analysis and data modeling in Excel.

Work with data

Flash Fill

By using Flash Fill, you can quickly and easily format data that’s displayed in a table in Excel. For example, suppose you’ve imported data into Excel. The worksheet includes a column that lists dates and times, and you want to use only dates. You can insert a new column, type the date that corresponds to the first item in the format that you want to use, and then use Flash Fill to automatically do this for all the rows in your table. To learn how to turn on this feature, see Flash Fill.

Create reports

Quick Analysis

By using Quick Analysis, you can select a range of data and choose from recommended ways to visually depict that information. When you use Quick Analysis, you can see what your chart or table would resemble before you select which type of visual display to use. For more information, see What's new in Excel.

Create reports

Reports

In Excel, you can create tables, line charts, bar charts, scatter plots, radar charts, and many other kinds of reports to visually display your data. You can use Excel to create powerful, interactive PivotChart and PivotTable reports to display relevant information by using lots of different data sources. For more information, see Available chart types.

Create reports

Power Map

Power Map is a new add-in for Excel that you can use to create interactive views on a three-dimensional globe. You can map data by using Bing maps, and see data trends over time. For more information, see Get started with Power Map.

Create reports

Scorecards

Scorecards are a special kind of report that shows at a glance whether performance is on- or off-target for one or more metrics. Scorecards contain one or more key performance indicators (KPIs) that compare actual values to target values and use a graphical indicator, such as colors or symbols.

In Excel, you can use KPIs that were defined in an external database, or you can use Power Pivot to create your own KPIs. For more information, see KPIs in Power Pivot.

Create reports

Power View

Power View is an add-in for Excel that you can use to create highly interactive views. By using Power View, you can quickly and easily create reports, scorecards, and dashboards. You can configure connections between items in a view so that values in one report can be used as parameters for other reports in the view. For more information, see Power View: Explore, visualize, and present your data.

Create or edit reports

Field List and Field Well

When you create a PivotChart or PivotTable report, you use the Fields section to specify the information to display in the reports. The Fields section contains the Field List and Field Well.

  • The Field List lists items such as dimensions and members from the data source that you’re using for the PivotChart or PivotTable report. You can also use the Field List to determine whether you are using native data or external data.

  • The Field Well shows which items from the Field List are displayed in the report. You can change the information displayed in a PivotChart or PivotTable report by selecting or clearing different items and measures.

Apply filters

Timeline control

The Timeline control is a special kind of time filter that you can use in Excel. To add a timeline control to a worksheet, at least one report on the worksheet must use data that contains a calendar date/time hierarchy. You can use the timeline control to select a single time period or a range of time, and any reports that are connected to the timeline are automatically updated to show information for that time period. For more information, see Create a PivotTable timeline to filter dates.

Apply filters

Slicers

Slicers are a kind of filter that you can use to select one or more items to use as a filter for reports and scorecards in a worksheet. For example, suppose you want to see sales information for different colors of items, such as shirts that your company carries. You can create a slicer that lists shirt colors, connect it to a sales report, and then use the slicer to view sales information for the colors that are selected in the slicer. For more information, see Filter data in a PivotTable.

Explore and analyze data

Quick Explore

With Quick Explore, you can click a value in a PivotChart or PivotTable report and then see additional information about that value displayed as a chart type. The PivotChart or PivotTable report must use that uses SQL Server Analysis Services data or data in a Data Model for Quick Explore to work. You can also use Quick Explore to drill up (or down) to view higher or lower levels of detail in a PivotChart or PivotTable report. For more information, see Drill into PivotTable data.

Use advanced analytic capabilities

Calculated Members and Calculated Measures

Calculated Members and Calculated Measures are items that you can define by using Multidimensional Expressions (MDX) queries in Excel 2013. You can create calculated members and calculated measures for PivotChart or PivotTable reports that use multidimensional data that is stored in Analysis Services. For more information, see Calculated items in Excel and Excel Services.

Use advanced analytic capabilities

Calculated Fields

Use Calculated Fields to change a Data Model that was created by using Power Pivot. When you create Calculated Fields, custom columns are added to the Data Model. You can then use those columns in reports that you create using that Data Model. For more information, see Calculated items in Excel and Excel Services.

These features are currently in Excel 2013, but not all these features are supported for workbooks that are viewed in a browser window. See the following sections for more information about what’s supported in SharePoint Online and Power BI for Office 365.

SharePoint Online (Plan 2) enables you to view and interact with a workbook in a browser window similarly to how you would do it in Excel. You can use filters, slicers, and timeline controls to focus on specific information in charts and tables. In a PivotChart or a PivotTable report, you can use Quick Explore to view additional information about a particular value, and you can open the Field list to temporarily change the information that’s displayed. Workbooks that contain Power View sheets are supported.

A few things are currently not supported in SharePoint Online. These include views that are created by using Power Map, Flash Fill, and Quick Analysis. To use those features, you must open the workbook in Excel. Depending on the data sources that are used, you might be unable to refresh data in a workbook in a browser window. For more information, see Data sources supported for workbooks in SharePoint Online.

To share a workbook in SharePoint Online, you have a few options:

  • Use a BI Center site. A BI Center site template is available so that you can create a basic site to store and manage content. Prebuilt lists and libraries are available to store and manage data connections, reports, scorecards, and dashboards. For more information, see Set up a BI Center site in SharePoint Online.

  • Use a library in a site, such as a team site. A team site typically includes a Shared Documents library where people can store and share files, such as workbooks, with others. Only people who have permission to view the team site can access library. This can be a good way to share content by using built-in SharePoint permissions. For more information, see Share workbooks using Excel Services.

  • Use the Excel Web Access Web Part. Once a workbook is uploaded to trusted location in SharePoint, you can display it in a SharePoint Web Part called the Excel Web Access Web Part. By using this Web Part, you can display all or part of an Excel workbook on a SharePoint site. For more information, see Display Excel content in an Excel Web Access Web Part.

Power BI for Office 365 supports self-service BI capabilities beyond what’s available in Excel. By using Power BI for Office 365, people can easily discover, analyze, and share data through Excel and SharePoint Online and across different devices. Key capabilities in Power BI for Office 365 include the following:

 

Feature name Description

Support for larger workbooks

Certain file size limits apply to workbooks in Office 365 that affect whether a workbook can be viewed in a browser window. Power BI for Office 365 supports larger workbooks than what’s available in SharePoint Online alone. For more information, see File size limits for workbooks in SharePoint Online.

Power BI for Office 365 sites

This is an application that transforms a basic SharePoint site into a robust, dynamic way to view and share Excel workbooks. Power BI sites on Power BI for Office 365 also provides people with an easy way to access all the BI capabilities that are included in Power BI for Office 365.

For more information, see Power BI for Office 365 sites.

Power BI Q&A

This functionality enables people to search for information by using natural language queries. A workbook author uses Power Pivot to add synonyms to a workbook, and after the workbook is uploaded to Power BI sites on Power BI for Office 365, others can discover and interact with the data more easily.

For more information, see Introduction to Power BI Q&A.

Power BI for Windows application

This is an application that lets people view and interact with Excel workbooks on a Windows tablet.

For more information, see Power BI for Windows application.

These are just some of the great new features in Power BI for Office 365. Additional BI capabilities are included. See Getting Started with Power BI for Office 365.

To download a white paper that discusses Power BI in a hybrid environment, see Using Power BI in a Hybrid Environment

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft