• Illustration: Accelerating Business Intelligence Application Development

The new SQL Server Accelerator for Business Intelligence (SSABI) gives Microsoft customers and partners a basic tool for parlaying their industry expertise into business intelligence (BI) solutions, which extract high-level decision-making data from an organization’s raw transaction data. SSABI can be used for rapidly building prototypes, or for training in BI development. Building on Microsoft’s successful efforts to gain market share for SQL Server in the BI or "analytics" market, SSABI lowers the threshold for starting a BI project by configuring both a SQL Server back end and an Office front end so that customers can more easily identify business trends and relationships from their desktop PCs.

Designing BI Applications

One of the few software sectors that was relatively unscathed by the economic woes of the last year (IDC estimates that it grew about 20% in 2001), BI is a growing focus for Microsoft. As organizations increasingly store information in digital form, BI is emerging as a popular way for organizations to reduce costs and gain business advantages by making better use of data that they already own.

A typical BI application is built on top of an online analytical processing (OLAP) "cube" that views data in terms of various "dimensions," such as time, product, site, customer, and revenue. In turn, these views are created from back-end databases, such as a data warehouse (which typically stores raw transaction data) or a data mart or operational data store (a subset or duplicate of the raw data that can be used for analysis without interfering with data warehouse transactions). If the back end uses more than one database, special "extract, transform, and load" (ETL) middleware is used to move data between databases. (The ETL functions in SQL Server are called Data Transformation Services, or DTS.)

For example, a grocery chain manager might query an OLAP cube to begin answering a question such as "What were the unit sales and revenues of each brand of paper towels in 2001?" to help decide which brands to stock the following year. A BI application would help the manager further refine the data, using the filtering, sorting, and pivot table features of Excel to rank the data by various parameters. (For more about SQL Server and OLAP, see "OLAP Breaks Ground for SQL Server" on page 6 of the May 2001 Update.)

Such applications are difficult to build, however. Data must be stored in tables that make it possible to extract the desired results, and client tools used to view the data must know how the data is stored. A change in the data might require changes to the ETL packages and to the client tools; a change in the desired views or queries might require a change in how the data is stored.

SSABI solves part of the problem by giving customers a desktop tool to design and prototype the type of data they want to view, and which dimensions they want to view it in. SSABI can then set up the back-end databases to store and organize the data appropriately, while simultaneously creating a client tool for viewing it.

By making BI applications easier to build on the Microsoft platform, the company can sell more copies of SQL Server Enterprise Edition and associated Client Access Licenses. SSABI also helps cement the role of Excel and the new Data Analyzer as desktop tools that can be optimized to put BI at the fingertips of business decision-makers.

Components of SSABI

The main components of SSABI are Excel worksheets, documentation, and Analysis Builder, which is a tool for configuring SQL Server databases and creating client-side templates for viewing OLAP cubes.

SSABI is designed to be used with desktop analysis tools, such as Excel and Data Analyzer, and with SQL Server Enterprise Edition. SQL Server features such as DTS and Analysis Services (Microsoft’s name for OLAP functions in SQL Server) play essential roles in applications built with SSABI.

A special Excel workbook, the Analytics Builder Workbook, is used to map out the requirements for the back-end data storage and the dimensions of the OLAP cube. This template is then sent to a software tool called the Analytics Builder, which provisions the back-end databases according to the model, configures the OLAP cube, and creates a client worksheet for Excel or Data Analyzer that can be used to view the data. (For more information, see the illustration "Accelerating Business Intelligence Application Development".)

SSABI also creates many DTS packages required to move the data among the databases, but customers or partners must develop their own tools for populating the database from external sources, such as an enterprise resource planning (ERP) system or a manufacturing control system.

Just the Beginning

Consultants can use SSABI to rapidly create a proof-of-concept or prototype on an offline system, or a basic BI solution that they can later modify to suit more specific requirements. SSABI is not suitable for tweaking production BI systems because the development process is one-way: any change in the desired views, or in the way data is stored, requires restarting the process from scratch.

SSABI competes with some partner offerings, particularly third-party BI modeling tools or "workbenches" for building BI applications on SQL Server. However, those are generally more sophisticated tools, and in its present form SSABI is not a full-featured do-it-yourself toolkit for BI. A finished solution is likely to require additional customization to fit a particular customer’s requirements.

Furthermore, Microsoft has minimized the pain for partners by giving them a prominent role in distributing SSABI; requests for SSABI are potential sales leads that partners can pursue to assist customers with using the tool or creating more advanced BI applications.

SSABI does provide templates for creating two specific BI applications: one template is designed for "retail analytics," including sales representative performance, fraud detection, and marketing campaign analysis; and the other template is for sales and marketing, including orders, shipments, and returns analysis.

These templates demonstrate how SSABI can be used to build a working BI solution and can be modified for production use, should a customer have a need for these particular types of BI applications.

Resources

SSABI can be downloaded at www.microsoft.com/solutions/BI/register.asp. Microsoft advises that using SSABI requires skills in modeling and application design, and recommends that those without this background work with a partner to develop their first project.

More information about the Accelerator for Business Intelligence, and links to partners who can provide it, are available at www.microsoft.com/solutions/bi/.

For a review of Data Analyzer, see "New Client to Extend OLAP Reach" on page 27 of the Aug. 2001 Update.