SQL Server Accelerator for Business Intelligence (SSABI) - often unheard of and sometimes underestimated

SQL Server Accelerator for Business Intelligence (SSABI) is one of the best and most exciting tools I've ever used.  Alas too many people haven't heard about it and sometimes people underestimate it because of its appearance – an Excel spreadsheet.  It’s a shame to say but I’m quite convinced it would have greater recognition if it wasn’t free.  At one point there was talk of charging thousands of dollars for it and then suddenly it was posted to the web for nothing (assuming you have a SQL Server license). 

 

SSABI is a fantastic tool that encapsulates much of Microsoft’s best practice for data warehouse/mart design and Analysis Services cube generation.  A simple series of Excel worksheets gathers the information (dimensions, measures etc) required to design cubes that will support the implied analysis.  The last worksheet has a wonderful ‘Generate Application’ button.  This button builds an almost complete data warehouse solution with associated cubes.  The solution includes a staging and subject matter databases with all the necessary DTS (Data Transformation Services) packages to maintain the data (facts and dimensions) with updates from source systems.  Rather conveniently it does this in about the same time it takes to make a nice cup of tea.

 

All you have to do is connect the solution to your data – this can easily be done by supplying data (csv) files that conform to the definitions (format fmt files) pumped out by the accelerator.  More adventurous folk can connect the DTS packages directly to their source systems.  The cubes can be viewed from Excel’s pivot tables, Excel with the new add-in for Analysis Services, or Data Analyzer.  Or if you like you can use an array of third party products.

 

SSABI has some excellent documentation that describes the whole process and all the objects it creates, as well as the best practice.  It comes with two templates and associated dummy data for creating cubes for sales marketing and retail analytics.  These two templates supply around 80 KPIs (Key Performance Indicators), they make up a very comprehensive set for people in these areas.  So once installed you can simply press the ‘Generate Application’, stand back, then have a look at a really solid data warehouse/mart design.

 

I introduced SSABI to Harrods almost on the day it released.  The team there had just been charged with creating a substantial data mart (with associated support for analysis and training for users) in around 4 months – they looked like dear paralysed by car headlights.  It was a tall order.  I persuaded them that SSABI could save them potentially 16 weeks of their time by enabling rapid, iterative prototyping.  SSABI also allowed them to use the technology before they fully understood it.

 

If this hasn’t wet your appetite for a look at the product – I doubt it’s for you.  If it has you can find it at https://www.microsoft.com/sql/ssabi/default.asp