Manage Learn to apply best practices and optimize your operations.

Microsoft focuses BI strategy on SQL Server, SharePoint, Excel

Microsoft's BI strategy for 2010 adds new products under the SQL Server business intelligence umbrella and retires some features from the lineup.

Microsoft's BI strategy is shifting, with some business intelligence functionality falling by the wayside and new features and products making an appearance under the vendor's SQL Server 2008 R2 umbrella.

The ProClarity technology Microsoft acquired in 2006 as a BI interface for SQL Server, SharePoint and Office has been dropped in favor of the interfaces users know: Excel and SharePoint. The scorecard and dashboard capabilities in PerformancePoint Server will be embedded in the next version of SharePoint. PerformancePoint Server's financial planning and budgeting capabilities, however, have been cut from the BI product line.

"Microsoft is trying to deepen and broaden its BI strategy, but they no longer have the equivalent of a financial planning and budgeting application, which is a gap," said Rob Helm, an analyst at Directions on Microsoft in Kirkland, Wash. "Also, focusing on SharePoint and Excel as the endpoints will open up BI to more users, but I think Microsoft overestimates the level of expertise users have in Excel."

Microsoft said it plans to rely on partners to develop financial planning applications for its BI platform.

Microsoft's BI strategy, which encompasses SQL Server 2008 R2, SharePoint 2010 and Office [Excel] 2010, is aimed at giving everyday workers the ability to gather data from a multitude of sources, create their own BI applications in Excel and share those applications using SharePoint.

The star of the vendor's BI strategy moving forward is SQL Server PowerPivot for Excel, formerly known as Project Gemini. PowerPivot for Excel and PowerPivot for SharePoint, as well as SQL Server 2008 R2, SharePoint 2010 and Office 2010, are slated for release in the first half of 2010.

More announcements related to Microsoft's business intelligence strategy are coming in the next few weeks, although the company declined to elaborate.

With PowerPivot for Excel, users will be able to conduct complex queries by downloading up to 100 million rows of data to their desktop from many data sources, such as IBM, Oracle, Teradata and SQL Server databases, or data feeds from the Web. The information can then be sliced and diced into reports or users can create BI applications on their desktops and share the applications with colleagues by publishing them on SharePoint.

The technology behind PowerPivot is an in-memory columnar-based database that speeds up the data retrieval process by relying on main memory versus disk storage, compressing data and storing it by columns rather than rows.

PowerPivot for Excel is a free add-on that lets users create an application that has pivot tables and interactive reports. PowerPivot for SharePoint provides the ability to publish BI applications created in Excel to SharePoint. To create the BI applications in Excel, companies will need to have Excel 2010. An investment will also have to be made in SharePoint 2010 to publish the applications from Excel to SharePoint.

SQL Server 2008 R2 must be installed to use the SharePoint PowerPivot capabilities, but it isn't needed for the PowerPivot for Excel add-on.

"It is a significant investment [for midmarket companies] to get on all the right versions of SQL Server, Excel and SharePoint for the [BI] capabilities, and the new versions of SharePoint and SQL Server run on 64-bit hardware, so midmarket companies are looking at a multiyear upgrade cycle," Helm said.

But midmarket companies can get started now with some BI capabilities and no investments, Helm said. SharePoint 2007 has scorecard and dashboard functionality. Excel has had data analysis services in place since early 2000, and users do have some capabilities to push Excel data out to SharePoint today.

Kroll Factual Data, the Loveland, Colo.-based credit report processing arm of risk consultancy Kroll, uses existing Microsoft investments in SharePoint and Vista for BI. About 90 percent of the company's managers today use SharePoint for reporting and data aggregation. "It's an absolutely critical business tool," said Christopher Steffen, principal technical architect at Kroll Factual Data.

The company's developers have also made Vista and Windows 7 gadgets in the OS sidebars that act as BI ticker tapes, displaying real-time corporate performance metrics. PowerPivot, however, is probably not something the company will deploy in the near future.

"We will probably deploy the new SharePoint [as an early adopter and tester] by the end of the year, but Excel for BI? I doubt it. At least not to the degree that Microsoft has planned for it [to be deployed]," Steffen said. "We've already developed so many BI applications because there wasn't anything out there that met our needs when we needed it."

More data on the desktop, more security concerns?

Joe Bugajski, an analyst at Burton Group Inc. in Midvale, Utah, does not shy away from pointing out a flaw in Microsoft's strategy or product line, but he said he thinks the vendor's BI strategy is coming together nicely.

"I'm telling clients to take a look at it because I see the [product] bits being delivered for PowerPivot and I'm impressed by the columnar data store and database compression that gives Excel an extraordinary boost in performance," he said. "Being able to put 100 million rows of data on your desktop really does change the game for BI."

Not to mention that most organizations have Office in place and many have adopted SharePoint, lending additional appeal to the offerings.

But the ability to download 100 million rows of data to the desktop opens up a few security concerns. Microsoft has basic security in place, such as access controls on the server, but IT departments will have to go outside the Microsoft product line for now for more stringent security around what users can do with PowerPivot, he said.

"It's a tedious process to cut and paste [100 million rows] of data and walk away with it, but it can be done," Bugajski said. "Microsoft needs additional security on the server side, like data masking, so that if a company allows more than five values [of data] at a time to be viewed, the software obscures it."

As for SharePoint 2010, Microsoft has built in what it calls "managed self-service BI features" that let IT track what data is being published from Excel to SharePoint.

Let us know what you think about the story; email Christina Torode, Senior News Writer.

Dig Deeper on Small-business IT strategy

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.