Export (0) Print
Expand All
1 out of 1 rated this helpful - Rate this topic

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-04-10

Summary:  Get an overview of business intelligence capabilities in Excel, SharePoint Online, and Power BI for Office 365.

To download this poster, visit http://go.microsoft.com/fwlink/?LinkId=394694.

Download this SharePoint BI poster

Excel 2013 offers lots of business intelligence (BI) capabilities to help you explore and analyze data. SharePoint Online enables you to share, view, and explore workbooks in a browser window. Power BI for Office 365 supports more advanced self-service capabilities. To download a poster summarizing BI capabilities in SharePoint—whether your environment is on-premises or in the cloud—click here.

Read this article for an overview of the BI features that you can use in Office 365.

Using Excel 2013 (available in Office 365 ProPlus), you can create powerful reports, scorecards, and dashboards. You can bring data into Excel, sort, and organize data, and use it to create reports and scorecards. The following table summarizes BI capabilities that are available in Excel.

 

Task area Capability Description

Get data

Native data

Native data is data that was imported or entered into Excel and lives in Excel without maintaining an external data connection. This is useful for working with static data, working with data that is updated manually, and creating workbooks that are published in locations where external data connections are not supported.

Get data

External data

External data is data that resides on another computer and 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 can be used to find and connect to lots of data sources. These include private and public data sources. You can combine data from multiple sources, clean and 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

A Data Model is a powerful dataset that consists 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 between tables of data. A Data Model serves as a data source for reports, scorecards, and dashboards. For more information, see PowerPivot: Powerful data analysis and data modeling in Excel.

Work with data

Flash Fill

Flash Fill is functionality that enables you to quickly and easily format data that is displayed in a table in Excel. For example, suppose that you have imported data into Excel and you have a column that lists dates and times, and you only want to use 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. For more information, see Flash Fill.

Create reports

Quick Analysis

Quick Analysis is functionality that enables you to select a range of data and see recommended ways to visualize that information. When you use Quick Analysis, you can see what your chart or table will resemble before actually selecting which chart type 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 charts. You can use Excel to create powerful, interactive PivotChart reports and PivotTable reports to display relevant information by using lots of 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 (3D) globe. You can map data by using Bing maps, and see how data trends over time. For more information, see Get started with Power Map.

Create reports

Scorecards

Scorecards are a special kind of report designed to show whether performance is on- or off-target for one or more metrics at a glance. Scorecards typically 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, to show performance at a glance.

In Excel, you can use KPIs that were defined in an external database or 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. Power View enables you to 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 what 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 are 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 what information is 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 that 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

Quick Explore is functionality that enables you to 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

Calculated Fields enable you 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 users to view and interact with a workbook in a browser window similar to how it works in Excel. You can typically use filters, slicers, and timeline controls to focus on more specific information in charts and tables. In a PivotChart report 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 what information is displayed. Workbooks that contain Power View sheets are supported.

A few things are currently not supported in SharePoint Online. These include views that were created by using Power Map, Flash Fill, and Quick Analysis. To use those features, users must open the workbook in Excel. Depending on the data sources that are used, users 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 several options:

  • Use a Business Intelligence 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 that people can use to 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 or Excel Online.

  • Use the Excel Web Access Web Part. Once a workbook is uploaded to trusted location in SharePoint, it can be displayed in a SharePoint Web Part called the Excel Web Access Web Part. This Web Part enables you to 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, people 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.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.