Excel, SharePoint 2013 Getting BI Features
By Kurt Mackie
Self-service BI capabilities will be part of the next versions of Excel and SharePoint. Microsoft recently released previews of its 2013 products expected to be released this fall, including Microsoft Office 2013 and SharePoint 2013. One component needed to test the new BI tools in these product previews is Service Pack 1 Community Technology Preview 3 of SQL Server 2012 (downloadable here).
Microsoft released SQL Server 2012 SP1 CTP3 so users can test the new BI capabilities but warns that it's designed for testing purposes only, not not for production environments. Furthermore, it doesn't include any advanced bug fixes.
Microsoft says users can perform self-service BI by leveraging SQL Server 2012, which was released in April. However, Microsoft's BI vision depends on bringing together several of the company's products and specific features, such as Excel's PowerPivot and the Power View add-in, which is part of SharePoint Reporting Services.
Power View lets users visualize data using queries. PowerPivot is for structuring multidimensional data using Excel's workbooks. Both features can be used to create interactive charts from data with drill-down capabilities.
From the previews, it it appears that Microsoft is improving BI's integration within Excel.
Power View Changes
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. The new Power View does not support its use on an Excel Web App, which is a browser-based version of Excel. Users must use Excel Services instead for those BI capabilities.
Power View also works differently for users working with 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, which is part of Office 365, must be used to support Power View on the new Office 365 preview. Furthermore, SkyDrive, Microsoft's online storage service, can't be used to view Power View sheets, according to Microsoft.
PowerPivot Changes
PowerPivot in the Excel 2013 preview lets users "import millions of rows from multiple data sources," according to a Microsoft article. The data can be processed quickly using SQL Server 2012's "xVelocity in-memory analytics engine." Data compressed via xVelocity is saved inside an Excel workbook. Additional new PowerPivot features include filtering data during import, definition of calculated fields and key performance indicators, and advanced formula writing using an expression language, "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.