Maximizing the benefits of Excel add-ins: PowerPivot and Power View
A comprehensive collection of articles, videos and more, hand-picked by our editors
More on SQL Server BI best practices
Count ’em: Three ways to scale SQL Server business intelligence
Pry open the SQL Server business intelligence toolbox
Some top reasons to get started with SQL Server 2012
If much of SQL Server 2008 R2’s focus was on making business intelligence (BI) more accessible to users as a self-service, then SQL Server 2012 is about building on that vision while making the concept of self-service BI easier for IT to manage, industry watchers say.
Indeed, part of the charter of SQL Server 2008 R2 was the democratization of BI—that is, simplifying moderate to advanced BI capabilities like the ability to mash up data and create relationships and dimensions so the process was comfortable and familiar to any Excel user, according to Herain Oberoi, director of product management at Microsoft.
“If you look at [SQL Server] R2 and the theme around managed, self-service BI … this is a continuation of that strategy,” Oberoi said. “It’s all about what we can do to help end users and what can we do to help IT.”
In that vein, Microsoft is plying SQL Server 2012, released in a “virtual launch” early this month, with capabilities to meet both ends of the spectrum. For end users, experts say the highlight of this release is the visualization tool Power View (formerly code named Project Crescent), while new capabilities in data quality services, analysis services and master data management are designed to give IT more control over the management and maintenance of self-service BI and deliver overall better performance, Oberoi said.
While observers said there were no glaring BI holes in SQL Server 2008 R2, many contend that the additional capabilities in SQL Server 2012 make it a more compelling and integrated BI platform. Because of Microsoft’s strength in development languages like .Net and Transact-SQL, SQL Server developers were able to create their own set of functionality in key areas like data quality or master data management, yet they required expertise and the skill set to do so, said Mark Tabladillo, a Microsoft Most Valuable Professional and president of MarkTab Consulting, which specializes in data mining and predictive analytics.
“To say these capabilities were missing is too strong because it was always possible for someone to build solutions by writing them on their own,” Tabladillo said. That might work for larger companies with many SQL Server developers on staff, he explained, but it’s not the best option for smaller companies that have one or two people wearing both the database developer and database administrator hats. “They’re providing more of a packaged application and not offering the capabilities at a tool kit level,” he added.
Power View’s self-discovery
Much of the BI focus in SQL Server 2012 will be on Power View, which brings highly touted visualization and data exploration capabilities to the Microsoft platform. While PowerPivot, released in SQL Server 2008 R2 and based on Excel, is all about letting users easily connect to different data sources and mash up data, Power View builds on that premise by allowing users to explore the data using a drag-and-drop interface to discover things they weren’t necessarily looking for.
“This is the tool you use if you’re looking to do self-discovery and want to discover patterns and trends in data,” said Mark Kromer, a SQL Server technical specialist at Microsoft. Say, for example, a user was tracking sales for a company and found one particular region wasn’t hitting his targets. “Instead of just seeing it at a top level and not knowing why, Power View lets users slice and dice the data to find out things like which products are selling or the demographics of customers so they can determine whether they might be selling the wrong products to the wrong customers in that particular region,” Kromer explained.
SQL Server 2012’s new Data Quality Services (DQS) is another feature targeting both business users and IT administrators. While previous versions of SQL Server had some data quality capabilities as part of Integration Services, the forthcoming DQS is a dedicated data quality server and client that can run either in standalone mode for individual users or can be used with SQL Server Integration Services (SSIS) at an enterprise level for profiling, cleansing and matching data. “Data Quality Services will allow you to control the quality of data from several sources instead of writing custom code,” said Marco Russo, a consultant, writer and trainer specializing in BI and Microsoft technologies. He runs a site dedicated to BI based on SQL Server, SQLBI.com. “It enables a set of standard tools to apply on data to get the job done faster.”
Master Data Services, which was retooled for SQL Server 2012, works with SSIS and DQS to help IT get a handle on master data structures used for providing object mapping, reference data, metadata management and dimension and hierarchy management. Another enhancement designed for IT and end users is the BI Semantic Model, or BISM, a common metadata layer that Microsoft says spans all BI user experiences. It lets the traditional multidimensional model coexist with the relational model in a format called the tabular mode.
“Now IT can take [BI reports and dashboards] created by end users and seamlessly transition them to Analysis Services,” Oberoi said. “You have all these end users building data models in Excel and publishing them in SharePoint. This helps formalize and manage the maintenance of those solutions.”
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.