News

Microsoft Adds BI Capabilities to Excel and SharePoint 2013 Previews

• By Kurt Mackie
• 07/23/2012

Microsoft today outlined some finer points for organizations wanting to test business intelligence (BI) capabilities enabled by the latest Excel 2013 and SharePoint 2013 previews.

Last week, Microsoft released previews of its flagship 2013 products to come, including Microsoft Office 2013 and SharePoint 2013. One essential component needed to test Microsoft's BI tools supported by those previews is the installation of Service Pack 1 Community Technology Preview 3 of SQL Server 2012, which can be downloaded here.

SQL Server 2012 SP1 CTP3 was specifically designed by Microsoft to let users test the BI capabilities of these emerging products. It was originally released on July 5 and is just designed for testing purposes. It's not for production environments and doesn't contain any advanced bug fixes.

Microsoft has touted the ability for knowledge workers to perform "self-service BI" by leveraging SQL Server 2012, which was released in April. However, this Microsoft BI vision depends on bringing a number of Microsoft products together, including the Excel PowerPivot addition found in Office and the Power View add-in, which is part of SharePoint Reporting Services.

Power View allows users to visualize data on an ad hoc query basis. PowerPivot is for structuring multidimensional data using Excel's workbooks. Both can be used to create interactive charts from data with drill-down capabilities. With the new 2013 previews, it appears Microsoft has been improving its BI integration within Excel.

Power View Changes
One new change associated with Power View and Microsoft's 2013 preview releases is that Power View's features now are available via the Excel 2013 preview, as well as SharePoint 2013, according to a Microsoft article. "Many of the features now in Power View in Excel 2013 were in Power View in SharePoint 2010, and new features have been added to both," the article explains. That change now makes it possible to perform data modeling from within an Excel 2013 workbook.

What's not supported with the new Power View is its use on an Excel Web App, which is a browser-based version of Excel. Users have to tap Excel Services instead to get the BI capabilities.

Power View also works a little differently for those using Excel in the Office 365 preview, which is Microsoft's subscription-based, cloud-enabled version of Office 2013. The Excel Web App Data Center in SharePoint Online, part of Office 365, needs to be used to support Power View on the new Office 365 preview.

Also out of the picture right now is the use of SkyDrive, Microsoft's online storage service. It can't be used to view Power View sheets, Microsoft's article indicated.

PowerPivot Changes
PowerPivot in the Excel 2013 preview now lets users "import millions of rows from multiple data sources," according to a Microsoft article. The data can be processed quickly via the use of SQL Server 2012's "xVelocity in-memory analytics engine." Data compressed via xVelocity get saved inside an Excel workbook.

Other new PowerPivot features include the ability to filter data when importing it, the ability to define calculated fields and key performance indicators, and advanced formula writing using an expression language called "Data Analysis Expressions."

With all of that data crunching, IT management may need some support tools. A Microsoft blog suggested that new IT management capabilities are being added to SharePoint 2013 for that purpose. The SharePoint 2013 preview has the ability to discover user-created spreadsheets. It also has spreadsheet analysis capabilities with interactive diagnostics, according to the blog post. Microsoft lists the software requirements for running BI capabilities with the SharePoint 2013 preview at this TechNet library page.

Self-service BI is a major push for Microsoft with SQL Server 2012, which comes in Enterprise, Business Intelligence and Standard editions. A recent Forrester Wave report (PDF) ranked Microsoft in the "leaders" category, along with IBM, SAP and SAS, in terms of enabling BI solutions. One perk on the Microsoft side is the licensing, which organizations may already have in place.

"Microsoft may only offer around 80% of advanced BI functionality as compared with other leading vendors, but what it lacks in features it more than makes up for in cost/benefits ratios," according to the report, "Self-Service Business Intelligence Platforms, Q2 2012," which examined 11 BI software vendors.