Guide to SQL Server data management and data quality
A comprehensive collection of articles, videos and more, hand-picked by our editors
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.
“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:
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.
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.
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:
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.
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.
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.