Essential Guide

Guide to SQL business intelligence, analytics and data visualization

A comprehensive collection of articles, videos and more, hand-picked by our editors

With familiar tools, new toys, Microsoft BI stack casts wide net

The Microsoft BI stack gains a few additions with release of SQL Server 2012, with new Excel-based Power View for business users and old standbys like Reporting Services.

This article can also be found in the Premium Editorial Download: SQL Server Insider: SQL, NoSQL technologies coming together in new Microsoft project:

More on the Microsoft BI stack

Microsoft BI stack gets performance management addition

Get tips on how to get the Microsoft BI stack work to your advantage

Find out whether your Microsoft BI stack is compatible with virtualized SQL Server

As part of its strategic mission to “democratize” business intelligence (BI), Microsoft has been steadily building out a sizable BI lineup for its SQL Server platform, beginning with SQL Server 2008 R2 and continuing with SQL Server 2012, due for “virtual launch” March 7.

A common theme for both SQL Server releases in terms of the Microsoft BI stack is delivering much of the functionality through Excel.

“Excel is the linchpin in our BI story,” noted Herain Oberoi, Microsoft’s director of product management. “It goes back to the original vision that the best way to make BI pervasive is to put in tools people know how to use and are comfortable with. We continue to add more capabilities to Excel to democratize [BI] usage. The goal is that Excel users don’t think of the capabilities as BI, rather that they’re just using Excel.”

As part of its BI stack, Microsoft has been packing SQL Server with new capabilities to serve a range of user types, including information workers, end users and IT and database administrators charged with supporting self-service BI systems. Here is a rundown of SQL Server’s core set of BI capabilities:

For IT and DBAs:

Reporting Services

As part of the core SQL Server engine, Reporting Services is a server-based platform used to create, deploy and manage reports across the enterprise and across a variety of data sources. With this foundational element, developers can create interactive, tabular, graphical or free-form reports from relational, multidimensional or XML-based data sources.

Analysis Services

This comprehensive set of analytical tools supports several BI functions, from multidimensional analysis and reporting to predictive analysis and data mining. The newest SQL Server 2012 release extends the core Analysis Services foundation with the Business Intelligence Semantic Model (BISM), which delivers a single model for all BI applications whether they be reporting and analysis tools or dashboards and scorecards. This is intended to give users a consistent view of data across various analysis functions while providing a single business view for tabular and multidimensional data.

Integration Services

SQL Server Integration Services, a platform for building enterprise-level data integration and data transformations systems, delivers data-cleansing and data-mining services to help upkeep regular BI applications. Developers can leverage Integration Services to extract and transform data from many sources and load the data into multiple destinations.

Master Data Services

Introduced in earlier SQL Server releases, Master Data Services is Microsoft’s master data management (MDM) offering. The purpose is to maintain object definitions across different systems by enforcing processes, validations and rules to promote data accuracy. With this module, developers can perform object mapping, reference data, metadata management and hierarchy management. In SQL Server 2012, Microsoft adds new capabilities that enable business users to perform MDM tasks on their own, working with familiar tools like Excel, as opposed to having to rely on IT and database professionals.

Data Quality Services

New to SQL Server 2012 is Data Quality Services (DQS), a tool designed to ensure data quality through profiling, cleansing and data matching. DQS can run as a standalone tool, allowing individual information workers and business users to take charge of their own data, or it can be leveraged on an enterprise scale as part of SSIS’ data quality functionality.

For end users and information workers:

Report Builder
Unlike Reporting Services, which is aimed at the DBA, Report Builder is an ad hoc report development tool designed for power users. Using a drag-and-drop interface, users can build and modify reports without help from database professionals and without having in-depth knowledge of things like SQL queries or database schema.

PowerPivot

Leveraging two of Microsoft’s most widely used and familiar technologies—Excel and SharePoint—PowerPivot is a self-service BI tool designed specifically for business users. Operating as an add-in to Excel 2010, PowerPivot lets users take advantage of a drag-and-drop environment to connect to and mash up data sources. The SharePoint connection lets users share their PowerPivot applications with others.

Power View

SQL Server 2012 extends the PowerPivot vision with Power View, a complementary data visualization and reporting tool that lets users drill down and explore their data to come up with insights they didn’t necessarily set out to discover. The query tool, built on Microsoft’s Silverlight Web browser media plug-in, offers interactive storyboarding capabilities, which allow users to share visualizations of really large data sets.

Beth Stackpole is a freelance writer who has been covering the intersection of technology and business for 25-plus years for a variety of trade and business publications and websites.

This was first published in March 2012

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Essential Guide

Guide to SQL business intelligence, analytics and data visualization

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close