page static_html.xsl b2005_01_29

Building cubes with the SQL Server Accelerator for Business Intelligence

For the last couple of years, my approach to cube development has been to use the SQL Server Accelerator for Business Intelligence (SSABI). Rather than clicking screen after wizard screen in the Analysis Manager, I create a single file which declaratively specifies the cube schema. SSABI takes that file and creates a whole infrastructure for creating and updating the cube from source data.

SSABI is a free and supported Microsoft accelerator. You can find it at at:

http://www.microsoft.com/sql/ssabi/default.asp

What's included

1. a staging database where you load data from your datawarehouse
2. a subject matter database which uses integer foreign keys for joins
3. format files and file processing scripts for doing flat text file data import
4. an empty analysis services cube
5. lots of DTS (with extensibility hooks) to orchestrate things
6. command-line invocation scripts which run the process

SSABI is a big and complex accelerator, and explaining all of its features and capabilities is beyond the scope of this article. The included documentation is very extensive. In this article I will explain how I use SSABI to very quickly get a new cube built and to take advantage of its support for text-only, declarative development to enable revision control.

 

From the documentation

BI Accelerator Components

"The analytical application consists of a staging database, a subject matter database, an Analysis database, DTS packages, and preconfigured client views. Operational data flows into the customized analytical application beginning with the Staging database. The data flows through to the Analysis database where KPIs are exposed through collections of preconfigured client views that are tailored to meet the needs of various business users."

Analytical Application Development Process

"A roadmap of the analytical application development process is shown the figure. This roadmap diagrams the process of using the BI Accelerator software to develop a customized analytical application. It is not intended to delineate all of the steps involved in creating and deploying the analytical application."

I included this diagram from the documentation because I think it does a nice job of conveying the overall analytics development process.

Prerequisites

The computer on which you do SSABI development must have the following software installed.

• Windows 2000, any edition, with SP3 or later, or Windows XP Professional
• SQL Server 2000 Enterprise Edition, SQL Server 2000 Developer Edition, or SQL Server 2000 Standard Edition (note that the Standard Edition does not support partitioning and calculated cell functionality)
• SQL Server 2000 SP3 or later
• SQL Server 2000 Analysis Services, installed using SQL Server 2000 Enterprise Edition or SQL Server 2000 Developer Edition
• SQL Server 2000 Analysis Services SP3 or later
• Windows Scripting Host version 5.6 or later (Microsoft Internet Explorer 6.0 includes Windows Scripting Host version 5.6)
• Microsoft Excel 2002
• Microsoft Word 2002
• Office XP SP1 or later

I used Office 2003 and it worked fine. The Developers Guide has a step-by-step run book for the installation process.

BI Accelerator Software

"In a single-computer development environment, you must install the BI Accelerator software on the all-in-one development computer. In multiple-computer development environment, you must install the BI Accelerator software on both the development client and the primary development server. The BI Accelerator software setup program adds the following components: " The Analytics Builder utility " Analytics Builder Workbook " The SQL Server catalog (the BisoCatalog database) " Various code components used by the analytical application " Utilities to assist developers in customizing the analytical application and database administrators in deploying and operating the analytical application " Data model templates, client views, and sample datasets " Help documents These components are explained in detail later in the SSABI Development Guide."

My Experience with the new SSABI

The SSABI installation includes two sample analytics applications. The first, RA, is a retail analytics cube. The second, SMA is a sales and marketing cube. The Excel workbooks are located in the Applications folder. Each sample has its own folder (RA and SMA). Within these folders are the Excel work books (AnalyticBuilderWB_RA.xls and AnalyticBuilderWB_SMA.xls). Also found here is a docs folder and a sample folder. The docs folder for each sample contains a Visio document for the logical model of the analytics application. I would install SSABI if for no other reason than to get these Visio documents because they are excellent examples of documented cubes.

The sample folder contains flat-file format import files for building the sample cube. The RA sample includes about 40MB of data; the SMA's is about 3MB.

Cube as a spreadsheet

SSABI supports declarative cube definition with two mechanisms. The one you would likely turn to from reading the users' guide is the Excel approach. The included Excel workbook is a macro-driven template for a cube. There are sheets for specifying things like dimensions, cubes, calculations, and actions.

The SSABI installation includes two sample cube workbooks. The first, RA, is a retail analytics cube. The second, SMA is a sales and marketing cube. The Excel workbooks are located in the Applications folder. Each sample has its own folder (RA and SMA). Within these folders are the Excel work books (AnalyticBuilderWB_RA.xls and AnalyticBuilderWB_SMA.xls). Also found here is a docs folder and a sample folder. The docs folder for each sample contains a Visio document for the logical model of the analytics application. I would install SSABI if for no other reason than to get these Visio documents because they are excellent examples of documented cubes.

The sample folder contains flat-file format import files for building the sample cube. The RA sample includes about 40MB of data; the SMA's is about 3MB. In the tools folder is an empty workbook named AnalyticsBuilderWB_Empty.xls. Since this workbook contains embedded macros, you must begin your cube definition with this template. This, by the way, is a noted flaw in the SSABI architecture, as is evidence by my project team's following experience. About half-way through our project, the version 1.1 of SSABI came out. We figured that we would easily change to using this new and improved version but were mistaken. Since the password-protected macros are embedded into the template, the only way to upgrade is by going through the tedious and error-prone process of manually cutting and pasting information from the old workbook in the new template. Since the payback of upgrading was ill-defined but the cost was clear, we decided not to upgrade. And in fact we abandoned SSABI as part of our process after getting the initial cube schema complete.

When we abandoned SSABI in our project, we switched to using a modified version of the Microsoft sample XML cube metadata scripter. I wrote a replacement for SSABI which used this file to orchestrate a cube build process. In a nutshell, I used XSLT to generate SQL DDL and DML to create and populate a staging and subject matter database. And I used WSH to execute the DSO commands to populate, process, and aggregate the cube. This new infrastructure gave our DBAs single-click build of production cubes. The scripts ran on 32bit machines but could automate our big 64bit Itanium cube build machine.

If it wasn't for a new feature of the version 1.2 SSABI, I perhaps would have relegated it to the developer dustbins of history.

XML Cube Definition

I tool a closer look at SSABI for a new project when I saw that they had added support for XML cube definition in version 1.2. I was at a crossroads in terms of completely replacing SSABI with my own toolkit (but giving up some of its advanced cube update and partitioning features) or building upon SSABI version 1.2.

I decided to go with SSABI for my new project. After testing the install by running one of the sample cube builds, I read the section of the developers guide which explained the XML schema for cubes and the COM component used to process an XML cube definition. Unfortunately, the same attention to detail apparent in the rest of the documentation was not present in this section - it was both incomplete and incorrect and my attempts to build a cube were rewarded by cryptic error messages. Unfortunately, the install does not include a sample XML file.

But since I knew this thing must work, I called Microsoft for assistance. They said that while there is not a sample XML file, you can generate the XML file for one of the samples by running the application generation within Excel. That should have been documented somewhere - even better a cleckbox on the Processing sheet could have said "generate XML cube definition file". But regardless, I now had a working sample which got me over the hump.

I coded up my new cube definition and wrote a WSH script to process it. And I was still confronted with cryptic error messages - the kind that indicate that a novice programmer wrote that part of SSABI. I was shocked to find, for example, that having any XML comments in the file could cause it to blow up. Not wanting to give up the ability to put comments in my code, I wrote an XSLT pre-processor as part of my automation script which removed comments and also my annotation elements.

Build automation

I create the file BuildCube.wsf to automate SSABI and build a cube. For the time being, I am using the flat-file format for data import instead of the SSABI DTS hooks. The script uses the provided COM component (progid= "MSBISOAnalyticsBuilder.AnalyticsBuilder") to process the file. The first time you invoke the Generate method on this object, it creates the import format files which SSABI uses to process you data files. We wrote queries in PL/SQL for Oracle to extract data from our application's database into files conforming to these format files.

Other pitfalls and gotchas

At some point, I accidentally ran msbiso_update.bat instead of <app>_Load_<cube>.vbs. In a very elegant example of self-modifying code, the script deleted itself - which necessitated the re-installation of SSABI. You have been warned.

Conclusion

In the download for this article I have included my cube build automation script and also a bare-bones XML cube definition file. The file create_ssabi.xsl is an XSLT transformation which a) updates the cube XML file with build-time parameters given to BuildCube.wsf (cube name, sever name, etc.) and b) does an identity copy rule which removes XML comments and my Description elements.

This approach to cube definition and creation is in my opinion far superior to using dialogs and wizards within Analysis Manager. You can manage change. All files are text and so can be versioned in Visual Source Safe. You can automate the process as much as you wish. While I have not tried it, I would hope that the server specified in the build script could be a 64bit Itanium box without SSABI present.

My incentive to invest further in this development path is diminished by the fact that it will soon be obsoleted by the arrival of SQL 2005 and its new BI Studio, which formally separates cube definition from cube instantiation. Moving forward, therefore, I will be creating tools and samples which further automate that new OLAP platform. But those of you still on SQL 2000 can still get the benefits of automation and the separation of cube definition from instance by using the approach outlined in this article.

downloads: b2005_1_29.zip (4K)