SQL Server business intelligence and self-service BI in 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

    Requires Free Membership to View

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 infos@reso-net.com or editor@searchcio-midmarket.com.

This was first published in June 2010

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.