Business intelligence (BI) solutions are notoriously time-consuming to build due to the large amount of custom development involved—application databases are optimized for updating, not customized reporting and data-mining. Much of this coding is so specific to individual customers that it cannot be reused in future projects.
Microsoft’s newly released SQL Server Accelerator for Business Intelligence is designed to address these problems.
This SQL Server template is designed to generate code that would otherwise have taken more precious developer resources. The hope is that developers—including consultants who design BI solutions for their clients—will be able to deliver solutions more quickly. It’s also designed to make SQL Server attractive against BI development products from competitors such as IBM and Oracle.
“What we envisioned with the Accelerator is to reduce the development and design time it takes to build a custom analytical application,” said Donald Petersen, SQL Server Product Marketing Manager at Microsoft.
The Microsoft SQL Server Accelerator for Business Intelligence can also help you sell business intelligence projects into smaller and midsize companies that otherwise could not justify the cost. You can use the tool to create a library of BI templates for specific situations that will shorten the time required to create future projects.
Here are the steps you’ll need to follow to get your hands on Accelerator, set up its basic configuration, and use it for custom analytics for your clients.
1. Download the accelerator
The SQL Server Accelerator for Business Intelligence (Accelerator for BI) was designed to be used by Microsoft Certified Partners such as consulting firms, but is available for free download after you fill out a short registration form.
In the download, you’ll find an Excel 2002 workbook called the Analytics Builder Workbook, the Analytics Builder code, and more than 50 pages of "prescriptive guidance" documentation on best practices for BI on the Microsoft platform.
The kit also includes three template workbooks:
- Retail Analytics tracks the kind of data produced by in-store point-of-sale (POS) systems, including store performance and store-to-store comparisons.
- Sales and Marketing Analytics is a more generic sales tool designed for manufacturing companies with a direct sales force. It focuses on product and sales representative performance.
- A blank workbook enables you to create your own solution templates for other vertical markets.
What you'll need
The Accelerator for BI requires the latest versions of several Microsoft tools:
- Microsoft Windows 2000 with Service Pack 2 (SP2) or later, or Windows XP Professional
- Microsoft SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition with SP2 or later
- SQL Server 2000 Analysis Services, installed using SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition with SP2 or later
- Windows Scripting Host version 5.6 or later (Microsoft Internet Explorer 6.0 includes Windows Scripting Host version 5.6)
- Microsoft Office XP with Service Pack 1 (SP1) or later
2. Complete the analytics builder workbook
Information you collect during client interviews about the proposed application is assembled and entered into the Analytics Builder Workbook. The Accelerator uses Excel as the front end to provide an environment with which consultants are already familiar. The code behind the spreadsheet automates the work of generating the rest of the infrastructure.
This combination is meant to help consultants iterate through their design faster and incorporate client feedback more easily. Prototypes can be shown quickly, and the entire back-end management infrastructure can be regenerated based on that feedback.
For example, say you’re helping a clothing company to track sales in its retail stores. To do this, you would interview your contacts about the types of reports they need and work backward from that discovery to define the data that needs to be collected. Returning to the office, you enter the results of your interviews into the Analytics Builder Workbook.
The workbook has tabs for the different types of data you'll need to collect. Some of the major ones are:
Here you'll enter name and path information for the databases the Accelerator will generate. These include a staging database for initial loading of data, a subject matter database (data mart), and Analysis Services databases (cubes) from which reports are generated.
These are the categories of data, such as Products, Customers, and Time that you’ll use in your analysis.
Within each dimension you’ll define a hierarchy of levels. For the store dimension, you might choose Country, Region, City, and finally, individual Store as levels.
These are the quantities you’ll be analyzing, such as sales amount, cost amount, and unit sales. You can also define calculated measures derived from those actually collected.
Acube is a database optimized for dimensional reporting. You can define both physical and virtual cubes in the Accelerator.
A command button on this final tab generates the rest of the BI architecture, including a staging database, subject matter database, analysis cubes, and client views. SQL Server Data Transformation Services (DTS) packages are also defined to load the databases and process the cubes.
3. Generate the application
When you generate the application, the Accelerator creates the following components for you, according to the specifications in the Analytics Builder Workbook:
- A staging database, along with a Data Transformation Services (DTS) package to load it. This allows data to be loaded in batches of manageable size. Each batch can be edited before moving on to the subject matter database. (DTS is a component of SQL Server that allows for the movement of data from disparate sources to any destination.)
- A subject matter database, which contains the central fact table containing your measures plus dimension tables for the hierarchies you defined. This typically includes a time dimension to allow browsing by time period; a customer geography dimension to compare numbers by region or city; and a product dimension to support analysis both by individual product and by product line. Another DTS package is created to move data from the staging database.
- Analysis cubes based on the dimensions you defined and more DTS packages to process them.
- Client views to display the information in the cubes. For the Retail Analytics template, for example, client views would include many reports that compare sales in different stores and by the part of the country in which the store is located. The analyzer supports various client tools: Microsoft Excel, which can access multidimensional data cubes via its Pivot Table feature; Microsoft Data Analyzer, a new member of the Office suite that presents cube data visually as charts; and third-party tools such as ProClarity Analytics for browsing of cube data.
Models included with the Accelerator are meant to be only starting points, says Microsoft’s Petersen. "With our early adopter customers, we either tweaked the model extensively, or we started from a completely new and blank slate,” he said, adding that he expects most projects to feature a similar level of customization.
The SQL Server Accelerator for BI creates a number of opportunities for customization:
- Although the Accelerator creates a DTS job to load the staging database, it assumes the presence of a sequential ("flat") file ready to be loaded. There are significant opportunities for you to customize this front end, accessing the clients existing data in systems of record and mapping it to the staging database.
- You can customize the staging and subject matter databases directly using the standard tools that come with Microsoft SQL Server 2000.
- You can modify the analysis cubes with Analysis Manager and create new virtual cubes as needed by your customer.
- You can provide additional client views or adapt the existing views to new client tools not supported by the Accelerator.