SQL Server Magazine

An analytic application is an application that lets you monitor key metrics, then analyze those measurements to find out whether your business is moving toward its business objectives. For example, you might have a business objective to increase revenue by 30 percent each year. One metric, or key performance indicator (KPI), that supports this objective is the difference between actual revenue and forecasted revenue. An analytic application lets business decision makers monitor this KPI and analyze it when necessary. If this metric is equal to or greater than zero, your revenue meets or exceeds your forecast and the company is meeting its objectives. But if this KPI becomes negative, you need the ability to determine which factors contributed to the change. Ideally, the analytic application also includes the ability to act on this information (e.g., notify the responsible people).

Let's illustrate this example with a business objective and the analytic application that my company used to monitor and analyze its progress toward that objective. You can use the basics of this application as a model for designing and implementing analytic applications that support your business objectives.

The Business Objective
The business objective that my company wanted to support was to deliver high-quality products on time--the classic objective for a product-development group and an objective that applies to both commercial product development and internal software development. The software development life-cycle phase that we decided to analyze was the quality-assurance, or stabilization, period--the time between the point when the software is feature-complete and the point when the company releases the software to customers.

Most software-development organizations schedule the amount of time for stabilization based on the software's maturity, the duration of code development, and the software's quality objectives. Usually, the scheduled stabilization time isn't sufficient and requires a heroic effort near the end to finish on time. The difficulty in managing the stabilization part of the schedule lies in assessing exact progress, then making intelligent decisions about reallocating resources and changing the schedule in the middle of the stabilization process. For example, as a development manager, you don't want to delay a release date unless you have concrete evidence that you won't make the currently scheduled date.

The traditional approach for tracking progress during the stabilization period is to track the number of outstanding defects. The number of defects should increase for a while, then peak, decrease, and level off at a point of marginal return, as Graph 1 shows. When you reach the point of marginal return (i.e., when great effort is required for minimal progress), the product is presumably ready for release.

I've been in several development groups that monitored the trend of the total number of defects. But without the ability to easily dig more deeply into the trend's contributing factors, charting the trend was only slightly helpful. Herein lies the difference between analysis and reporting: Reporting lets you monitor a KPI but doesn't let you analyze contributing factors or act on them; analysis lets you analyze contributing factors and act on them.

For example, imagine that you're in Week 24 of a project and that the trend line in Graph 1 extends only to Week 24. You examine the defect-count trend and notice a marked change in the slope, suggesting that you're close to the trend's peak. This change in slope could be good news: Perhaps your defect-fix rate is starting to catch up with your defect-find rate. However, the change in slope might simply mean a decrease in test coverage. Upon further analysis, you might find that some of the people who were testing the product in previous weeks have been assigned to another project. So, the lack of incoming defects is really because of a lack of testing. This example shows why analysis is so important: A report usually only creates questions; it doesn't answer them.

An Analysis Solution
How do you determine the cause of a change in trend slope? To answer this question, my company identified the most crucial KPIs for monitoring our product life cycle's stabilization phase (the KPIs we found most important are at callout A in Table 1). A static report on an intranet portal shows some of these KPIs, and a SQL Server 2000 Analysis Services cube supports some of them. We hope to eventually have a cube that supports all KPIs, but the company hasn't finished that project yet. However, the KPIs that the cube supports are available in an interactive view on the same portal. So, when we have a question about a KPI, we can immediately drill down to the contributing factors and determine that KPI's status.

From the basic metrics at callout A in Table 1, you can derive some other interesting metrics, such as those that callout B in Table 1 shows. My company uses a commercial defect-tracking product that stores the underlying defect information in a SQL Server 2000 database. The company then inspects the database's schema and uses a Data Transformation Services (DTS) package to extract this data and transform it into a star schema that supports cubes. The operations staff schedules the DTS package to run nightly to update star-schema tables and refresh OLAP cubes.

   Prev. page   [1] 2 3     next page