SQL Server Insider

Master data services: A work in progress


Manage Learn to apply best practices and optimize your operations.

What every company should do with Business Intelligence

When used properly, the robust business intelligence stack in SQL Server 2008 R2 helps companies run more efficiently. Are you getting the most out of your BI?

Microsoft’s SQL Server 2008 R2 business intelligence stack is probably one of the most flexible and easy to use that’s available today. While older versions of the BI stack were adequate, new versions are better—allowing the business unit to be more agile and responsive with a lower operational cost. 

With this major business intelligence release, the SQL Server BI stack is poised to become a major player in the business space as well as the IT space. The changes can take the BI stack from being a database application that’s used to host business-based data to an application that the business unit can use to help reduce overhead, increase business response time, reduce report creation time and increase business agility. There are five things that every smart company is doing with the BI stack. How smart is your company? Are you doing all of these things?

1) Allow users to construct reports with Report Builder 3.0

One major advantage of the Microsoft business intelligence stack is that business users have the ability to create their own reports based on information that the IT group publishes to Report Builder. Reports allow users to analyze information in several ways without having to wait days, weeks or months for a report writer or developer to build the report and schedule it for delivery. The more you empower users to create their own reports, the more value they will derive from BI. As users associate more value to their own jobs from the BI software, they will drive more of the budget toward keeping the product well-maintained and upgraded to the newest version. With Microsoft SQL Server 2008R2’s reusable report Web parts, users can include IT-developed Web parts within their SQL Service Reporting Services reports—accelerating report development, which also ensures that report data that users develop will match reports that IT releases.

2) Schedule reports for delivery so business users have the information they need when they walk in the front door

One of the great features of SQL Server Reporting Services is that reports don’t have to wait for a user to load them. Reports can be generated automatically before business users arrive at work so the data will be waiting for them when they arrive. This potentially can save users hours each day, especially if they’re running long reports. By scheduling reports to run at specific times, administrators can determine the order in which reports start running—keeping the amount of load on the source systems to a minimum at busy reporting times.

3) Use PowerPivot to slice and dice data

PowerPivot, a feature released as part of SQL Server 2008 R2, is a plug-in for Excel 2010 that allows an end user to load millions of pieces of data into an Excel sheet from a variety of disconnected sources. Users can even type in their own data for a dimension. PowerPivot then gives the business user the power of the SQL Server Analysis Services on their desktop—without an actual connection to a SQL Server Analysis Services instance. When the PowerPivot Excel workbook is then uploaded to a SharePoint2010 website, it can be configured to update automatically so that it contains the newest information. Data as well as all graphs and icons update automatically. PowerPivot can also be used for database administrator functions via the PowerPivot plug-in, which makes analyzing the output of the sys.dm_db_index_usage_stats easier and faster. By dropping the data from the Dynamic Management View into PowerPivot, you can quickly slice and dice it to locate frequently updated data using unused indexes.

4) Turn data into information

One of the biggest problems in the business world is that there is simply too much data available to business users and not enough knowledge about how to use this raw data. The SQL Server Business Intelligence stack lets users take this raw data and turn it into useful information that can be properly analyzed and used to drive intelligent business decisions. The SQL Server BI stack helps both IT users and business users turn data into useful information. This freedom to work independently from the IT department allows end users to be more flexible when making intelligent decisions.

On the IT side, end users are able to turn their raw data into valuable information without the use of valuable IT resources, i.e., time. In turn, the IT department is free to manage the environment and develop new applications to help drive additional revenue for the company.

5) Make business decisions based on information—not on what your gut tells you

Once business users have collected all available data and turned it into information using Microsoft PowerPivot and Excel 2010, their job is only half done. Those same business users then need to learn a new skill—how to analyze the information and use it to drive business decisions.

Bringing gut reactions into play is no way to run a business, and business users need to have the most current and most relevant information in order to quickly make decisions.

About the Author

Denny Cherry has more than a decade of experience managing SQL Server, including’s over 175-million-user installation, one of the largest in the world. His areas of expertise include system architecture, performance tuning, replication and troubleshooting. Check out his blog at SQL Server with Mr. Denny

Article 2 of 3

Dig Deeper on Microsoft SQL Server 2008 R2

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.