Continue in 2 seconds

SQL Server Executive - Business Intelligence Takes Center Stage

  • April 01 2007, 1:00am EDT

The proliferation of business intelligence (BI) capabilities across an ever-greater number of departments and functions within the enterprise has been the source of much discussion recently. It's no surprise, then, that with increased competition and the rate of change accelerating across numerous industries, decision-making cycles are shortening. Not just major strategic decisions, either, but tactical and operational decisions made by line-of-business owners, managers and directors deep within the organization.

In the past, BI solutions were used by a very limited number of business analysts largely to develop strategic business plans and forecasts, address competitive threats and set policy on long-term strategic issues. Reporting and analysis tools were used mostly to analyze historical data about market size and demand, financial models and national or global market strategies on issues such as where to build the next shipping warehouse or retail store. With today's organizations looking to act on opportunities faster than ever, BI tools have become too valuable not to be used wherever they can deliver insight for day-to-day operational decisions. The result is an increased demand for "live" or real-time BI and reporting, and this places an additional burden on the IT infrastructure group to deliver systems that can provide these capabilities.

Today, BI applications are as likely to be used by midlevel managers as they are by a small number of business analysts. As such, the questions these applications need to answer tend to be much more tactical and operational in nature and pull from data that resides in multiple systems within the enterprise. Indeed, the ability to pull from data throughout the organization and rapidly analyze and act on that information is key to making quick decisions and staying competitive in today's market. The more tightly the enterprise can integrate operations, marketing, sales, IT, finance and other departmental data, the more successful it is likely to be.

Speed and integration of information across functions or departments is critical. Yet, for the marketing team to know whom to target with an effective sales pitch, they must have access to transactional data. In years past, when BI applications were usually limited to a small set of business analysts, the demands for real-time transactional data were relatively modest and could be accommodated without too much trouble. But now, with BI applications more commonly used in operational settings, it is essential to have up-to-the-minute data available in the data warehouse. Since even hours-old data is of little use in many situations today, demand is very strong for data warehouse (DW) solutions and BI applications to collect and evaluate real-time information. How strong is this demand exactly?

Recently, Unisphere Media conducted a survey of members of the Professional Association for SQL Server (PASS) to gauge the level of adoption, planning and platforms from databases to operating systems.1 The survey tracked the adoption of the new SQL Server 2005 database platform and its implications for enterprise-scale and BI applications. What the survey found was that 95 percent of respondents either run SQL Server 2005 today or plan to adopt the database platform within two years, citing performance and extended BI capabilities as the top drivers. In fact, close to half (45 percent) said they installed or upgraded to SQL 2005 to take advantage of its "enhanced reporting services." Moreover, the top two applications run within SQL environments among companies participating in the survey were reporting and BI/analytics (see Figure 1).

Clearly, the tactical ability to execute on business strategy has never been more tightly aligned with the capacity to share and leverage knowledge within the enterprise — without interruption. The challenge is how to provide BI applications and their end users with the timely data they require. For the IT department, the technical question is how to deliver real-time data acquisition and integration across a variety of systems without degrading the performance of source transaction systems or clogging the enterprise network with enormous flows of data.

Companies may first look to extract, transform and load (ETL) products or homegrown scripts, which periodically move large volumes of data in batches to target systems. There are several drawbacks to these methods, however. First, periodic means intermittent - data is not available in real time. Moreover, both custom scripts and ETL require batch processing or outage windows, and they impose significant overhead on IT systems. Alternatively, enterprise application integration (EAI) solutions may satisfy the need for low latency, but they are expensive to implement and manage over time. In addition, EAI is not effective for situations in which high or even moderate data volumes are at issue.

Real-Time Access to Real-Time Information

A growing number of organizations are finding that an effective way to accomplish high-volume data acquisition and integration in real time is with change data capture technology. Change data capture solutions provide continuous, real-time and low-impact capture and delivery of data between source and target systems. As soon as new transactions are committed on the source system, that data is immediately moved to the target where it can be used to feed analytics applications or for real-time reporting on the business.

In a SQL Server environment, organizations can use this technology to move data from source to target, in real time, with no requirement for a middle tier. In effect, the change data capture solution reads the SQL Server database log, applies basic transformations as needed and moves the changed data across the network to one or many target systems, encrypting or compressing the data on the fly as needed. In this way, it provides an up-to-date source of data for BI and reporting applications with minimal impact to the source operational systems and the network.

Although change data capture solutions are asynchronous by architecture, they provide synchronous-like behavior, delivering virtually all of the benefits of EAI, even with high-volume enterprise data loads that have conventionally only been addressable with ETL. Change data capture, however, eliminates dependencies on batch windows. As such, it combines many of the most highly prized capabilities of both ETL and EAI. With EAI and change data capture, only data changes and updates are moved, rather than entire data sets. This is a key reason why solutions using this technology operate with subsecond latency while maintaining the integrity of the data transactions.

Change data capture also resolves many of the business continuity issues that can plague real-time BI environments. Systems that support operational BI can be critical to the bottom line. In fact, once an organization begins to actively depend on BI capabilities to drive operations, those capabilities tend to assume an ever-larger role in the business. As dependence on the system grows, downtime becomes more problematic and expensive - the PASS survey also revealed that 28 percent of respondents tolerate "less than one to four hours" of downtime per year. Change data capture has unique capabilities that make it ideal for guaranteeing continuous uptime for critical BI systems. Because it moves data bidirectionally while maintaining transaction integrity, in the event of a primary system outage, users can be immediately pointed to a second fully synchronized system.

As more and more organizations come to depend on the DW for tactical decision-making, it is critical that the freshest data possible is continuously available. Deploying change data capture solutions in a heterogeneous database environment for both real-time data acquisition as well as availability can be an effective way to power operational BI activities.


  1. Unisphere Media. "Survey: SQL Server and Microsoft Technologies Reach Across the Enterprise." September 2006.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access