Thomas Williams

Review: February SQL SIG - Overview of SQL Server 2005 Business Intelligence

Last night (Tuesday February 15th) the Melbourne SQL Special Interest Group (website at http://www.sqlserver.org.au/) kicked off again for the year. Peter Myers from Tenix Connections presented specifically on SQL Server 2005 Business Intelligence which is right in my line of business; the rest of the year promises a couple more topics on BI in SQL Server at the SIG, which is good news as it gives me a head start on the upcoming, yet-to-be-released version of SQL Server.

Peter presented really well - he obviously knew his stuff - and it was pointed out that he had “nearly every Microsoft qualification there is” including the Microsoft Certified Trainer, so he was well qualified to speak. He spoke on Microsoft's new BI model called the UDM or “Universal Dimensional Model” which replaces the concept of “cubes” under Analysis Services 2000 (although physical cubes still exist in the new Analysis Services, they are just far more feature rich and flexible).

I have had the dubious pleasure of using Analysis Services under SQL Server 2000 in the past and we nearly built a web-based reporting system using XML and ASP on it. After Peter's slides and demo (which can eventually be found at http://www.sqlserver.org.au/meetings/melb/), I feel like the new Analysis Services is comparable to watching color TV after only having known books. Like DTS, the development tools have been re-written from the ground up and are developed from within a Visual Studio-like shell. Other improvements that Peter mentioned were the ability to incorporate business rules and KPI's into cubes, the ability to use multiple fact tables (no more virtual cubes), the ability to use the same dimension on two different axes (no more virtual dimensions), better authorisation options including Windows groups and more processing options, including scheduled & automatic MOLAP, right down to the basic ROLAP where data is kept in the relational tables.

Peter started with one of the better overviews of Business Intelligence I have seen, covering some of the key concepts like data warehouses, data marts, and cubes and the problems that BI needs to address like multiple data sources, data integrity and the desire to often not mess with existing systems. He also showed how SQL Server 2005's improved toolset handles the different stages of BI, from integration of data sources (called Integration Services, the old DTS), to analysis (Analysis Services) to finally reporting using Reporting Services.

I reckon the presentation was aimed at more the DBA, which is my part in BI at work. It will be interesting to see what sort of client tools & controls to help home-grow user interfaces are available when SQL Server 2005 ships. I know in my situation, users are very familiar with Excel and want Excel-like powers from my reports. Some users have even mastered pivot tables (which I personally think are great for their flexibility and analysis power) and I'll be interested to see how Microsoft plans to get tools into the hands of the masses - will it just be through Reporting Services, or will Excel get better as well?

Overall a good evening and presentation. I got to meet a few new people like Chad the ColdFusion lad and someone even admitted to reading my blog (thanks Grant!)