BI in SQL Server 2008 R2: Empowering Business Users

Microsoft SQL Server has been a favorite for years for organizations that want to implement business intelligence (BI) functionality - even in traditionally non-Microsoft shops. Especially since the SQL Server 2005 release, the ROI of a Microsoft solution coupled with the ease of implementation has driven healthy adoption of the DBMS for BI. And the integration of SQL Server 2008 with Microsoft Office, SharePoint Server, and PerformancePoint Services for delivering BI to end users has created an even stronger end-to-end platform.

Now, the SQL Server community is awaiting the release of new BI and management features in what's being billed as the next "mini-release" of SQL Server: SQL Server 2008 R2, formerly code-named Kilimanjaro. However, if you scan R2's published feature list, you'll realize that it's nowhere near a "mini-release." In fact, this release introduces compelling new functionality that promises to bring the power of BI deeper into every organization by empowering business users.

With R2 Community Technology Preview (CTP) 2 available now, let's take a look at three exciting new BI features guaranteed to make companies sit up and take notice: self-service analysis, self-service reporting, and Master Data Services.

Self-Service Analysis

"Gemini" is the code name for an innovative feature that puts the power of SQL Server Analysis Services (SSAS) on a user's desktop, letting information workers (IWs) access, manipulate, and report against managed and unmanaged data sets, including large data sets. Users can also access data from existing reports for analysis.

In one of his presentations, Donald Farmer, principal program manager at Microsoft, said he has never been more excited about a feature than he is about Gemini-high praise coming from the person who worked on the Data-Mining Add-ins for Excel as well as SQL Server Integration Services (SSIS). Farmer's excitement is well placed.

Having shown this feature to a number of potential clients, I can easily see why self-service analysis is a game-changer in many ways. Gemini introduces an in-memory BI engine that can take advantage of hardware advancements and increased memory capacity on client desktops and laptops. More important, it lets IWs fulfill their analytical requirements iteratively, freeing them from the constraints of a predefined model. And Gemini models can be easily shared with others for collaboration via SharePoint.

A huge advantage of Gemini is that it promotes better data security inside organizations. Because Gemini allows flexible and complex analysis against the managed data sets and can be shared through SharePoint, it eliminates the need to store vital organizational data on spreadsheets that are then distributed via email. In addition, data in the Gemini models can be regularly refreshed so that users aren't just working with old copies of data from when the model was first built.

For IT, Gemini helps keep core data secure, while freeing IT from having to spend a lot of time addressing ad hoc user requests. IT pros have the tools to manage shared Gemini solutions-including data refresh, model security, and resource utilization-and can enhance the model as more and more workers use it.

Self-Service Reporting

Self-service reporting follows logically on the heels of self-service analysis. Microsoft initially delivered self-service reporting features with Report Builder 1.0 and released significantly enhanced features and functionality for IWs in Report Builder 2.0. In R2, Microsoft further improves the self-service user experience and overall reporting experience with managed "Grab-n-Go" reusable report components.

The libraries of reusable components can include queries, tables, charts, maps, gauges, and logos. The ability to use data already contained in existing reports is another benefit. This way, users don't always have to be aware of the data sources or data queries that generate the data they're interested in.

R2 also provides an easy-to-use drag-and-drop interface that lets IWs design queries, reports, and charts and delivers new support for geospatial visualization such as mapping and routing along with the ability to combine spatial and business data. Again, these capabilities allow IWs to be less dependent on IT for many ad hoc data needs and reduce the pressure on IT to support these requests.

Master Data Services

Master Data Management (MDM) is a hot topic in organizations these days, largely due to the advent of decision support systems that need to report against integrated data sets. Such needs have highlighted issues around data integration, data quality, compliance, and poor data entry and management practices. Companies that grow through acquisitions face even bigger data management problems as they try to seamlessly integrate heterogeneous and previously unrelated systems.

Poor data entry and management systems remind me of a line of business (LOB) application I worked on many years ago for which a customer data input screen had to be built with no constraints. The data entry person could easily get away with not only entering bad data on the customer, but also entering virtually no data at all. The only place personnel were required to enter good data was in the key transactional screens. You can imagine the difficulty of reconciling unique customers in this system or even searching for an existing customer-especially repeat customers. I guess I should mention that this was for a law enforcement intake system, where the "customers" were offenders being processed into the system.

Sound judgment eventually prevailed, and we put in better data-quality controls. But it's common to find bad or inconsistent data inside organizations. Even in a scenario as simple as an order-processing system and a financial system that keep different records of the same customer, records can quickly get out of sync. Thus, the need for well managed data for the "nouns" in your organization-people, places, or things-is critical.

Enter R2's Master Data Services (MDS) feature, which provides a centralized approach to defining, deploying, and managing master data as well as a centralized portal for creating, editing, and updating that data. MDS enhances your ability to implement MDM solutions, enabling the data's business owners to also be data stewards and allowing IT to still exercise appropriate security and control. The strength of MDS is that it doesn't need to be the entry point for master data or the final destination for managed data. Rather, it can act as a hub that provides sound data management and stewardship services to other systems in your organization, letting companies take an iterative approach to implementing MDM solutions.

As part of its long-term vision and continuing push for data excellence, Microsoft last year acquired Zoomix and its Accelerators, which combine advanced semantic and linguistic analysis with machine learning to automatically and accurately classify, match, and standardize complex corporate data. Although this functionality won't be part of R2, Microsoft plans to integrate it inside the SQL Server platform in the future. At the 2008 Microsoft BI Conference, Tom Casey, General Manager for SQL Server, encouraged me to see the Accelerators in action, and I was impressed with the ease in which complex corporate data could now be standardized. I'll leave your imagination to process the power of an integrated stack that includes MDS and the automated data standardization functionality of Zoomix.

Get Your Hands on It

Microsoft has made a strong commitment to BI excellence and, in R2, is taking valuable steps to bridging the gap between the real-world needs of IT and IWs. But R2 delivers much more than these features. For large data warehouses ranging from tens to hundreds of TB, R2 delivers "Madison," which integrates the massive parallel processing innovations of DATAllegro with SQL Server. The upcoming release also delivers enhanced manageability features and improved scalability.

I encourage you to sign up for R2 CTP2 and check out these powerful technologies for yourself. And make plans to see R2 in action at the Professional Association for SQL Server's (PASS's) PASS Summit Unite 2009 conference ( in Seattle Nov. 2-5. As the largest gathering of SQL Server professionals in the world, PASS Summit will put you in touch with the leading experts inside and outside of Microsoft who can equip you with the knowledge and expertise to take advantage of R2. In the meantime, sign up for the PASS newsletter at to stay up-to-date with the latest R2 announcements, including R2 Webcasts by PASS and Microsoft.