Manage Learn to apply best practices and optimize your operations.

SQL Server business intelligence and self-service BI in the midmarket

Could your users benefit from SQL Server business intelligence? SQL Server 2008 R2 introduces valuable self-service BI tools that may be worth a look for the midmarket.

Organizations maintain databases mainly because they expect to obtain business intelligence (BI) data from them. But in some cases, obtaining this business intelligence -- the vital information about your organization that can be used to achieve goals -- is like pulling teeth. However, for those organizations using Microsoft SQL Server and willing to upgrade to SQL Server 2008 R2, this is about to change with self-service BI.

Currently, a big problem with BI is that it doesn't focus on the end user. The purpose of a database is to allow users to extract and analyze data trends and patterns, but it doesn't tend to work out that way. Users must rely on developers and database administrators to extract data or take the time to learn the ins and outs and become database experts on their own. Because of this, BI is often a hit-or-miss affair.

That's why it makes sense to turn data extraction into a self-service. SQL Server 2008 R2 includes powerful self-service business intelligence tools. These tools allow your end users to get what they need from your data while keeping IT administrators in control of it, ensuring security.

How does it all work? Microsoft relies on familiar tools such as Microsoft Excel and Office SharePoint Server to provide this new type of self-service BI. Excel allows users to extract data -- an extraction that can be quite efficient even against very large databases, thanks to SQL Server 2008 R2's new StreamInsight data-streaming service -- and SharePoint lets them expose the results to collaborators, usually in the form of easy-to-understand reports. The entire process can be tracked and reviewed by IT administrators, keeping the data under organizational management.

SQL Server 2008 R2 offers three new features in support of self-service business intelligence:

Master data hub: The new SQL Server master data hub creates a central portal that can provide end users with a single point of entry for access to all master data. Because all master data is located in one hub, IT administrators can also track it at all times. A special stewardship portal displays all master data configurations with members and hierarchies. This allows your data administrators to validate the quality of the data through standardized model development, review and management.

PowerPivot for Excel: End users can now use Microsoft Excel to perform their own data extractions. This self-service BI solution gives users the ability to blend data from various internal and external sources to create more powerful insights into business events and trends. By giving users access to the data they need, when they need it, vital business decisions are better made and supported.

PowerPivot for SharePoint: End users are now able to publish extracted data to SharePoint in one simple step, allowing them to share and collaborate with colleagues. Report Builder 3.0 generates reports that display the information in an easily consumable format. Because the data is published in SharePoint, your IT administrators can both version it for consistency and ensure that only authorized personnel can access it.

These tools allow your end users to get what they need from the data while keeping IT administrators in control of it, ensuring security.

These three new features make it easier for your end users to access and work with stored data. Your IT administrators will also benefit -- as they discover who needs access to which data, they will gain a better understanding of data flows within your organization.

Of course, you must migrate to SQL Server 2008 R2 to gain access to these key features, but it's much simpler than you may think: You only need to update your database engine and related services, and you don't necessarily need to update the databases they contain. In other words, you can either update all your servers to SQL Server 2008 R2 or install new SQL Server 2008 R2 machines and move existing databases to the new engine.

Once you're running the new engine, you can implement a better self-service end-user data extraction and manipulation process. This will also help when it comes time to upgrade your databases, because you will gain more insight into which data elements users need most. You can also discover in which order your databases need to be upgraded, and go for the critical databases first. Now that's business intelligence.

Danielle and Nelson Ruest are IT experts focused on virtualization, continuous service availability and infrastructure optimization. They have written multiple books, including Virtualization: A Beginner's Guide for McGraw-Hill Osborne, and MCTS Self-Paced Training Kit (Exam 70-652): Configuring Windows Server Virtualization with Hyper-V for Microsoft Press. Contact them at [email protected] or [email protected].

Next Steps

Shining the light on PowerPivot for SQL Server 2008 R2

Is self-service business intelligence the answer?

Dig Deeper on Small-business infrastructure and operations