Recently I had the pleasure of deploying the entire Microsoft SQL Server 2005 Business Intelligence "stack" at a client. In the course of reviewing the successes and challenges of this engagement, I realized that in many ways this was an ideal implementation:
1) Except for a few SSRS 2000 reports, it was mostly "green field", meaning there were no existing BI technologies deployed
2) The client had intentionally scoped this first project to a very specific single subject area
3) Project planning called for multiple iterations, including a very early prototype as well as iterative development as the system grew in features and scope.
4) To the credit of the Business Stakeholders and IT Management, this project was conceived from the start to adhere to the Kimball/Microsoft design methodology. In other words, once business requirements were gathered, a dimensional design was created in the form of a snowflake schema. Business Intelligence and security were realized in the form of the Universal Dimensional Model
For this (my first PointBridge blog posting), I wanted to highlight the characteristics that made this project both beneficial to the client, and fun to deliver:
1) Being "green field" allowed us to deploy the BI SQL Server products without worrying about integrating with existing reporting systems, although we were able port the existing SSRS 2000 reports up to SSRS 2005.
2) Scoping to a single subject area allowed us to build out the entire BI lifecycle system:
a. SQL Server Integration Services for ETL
b. SQL Server Database Engine for the Star/Snowflake database
c. SQL Server Analysis Services for Business Intelligence metrics like Current Period/Prior Period
d. SQL Server Reporting Services for presentation/delivery)
in a relatively short amount of time, about 6 weeks. This provided immediate value to the client (in this case creating something they could sell to their clients), and it also put their Information Systems in a good state for subsequent building out of additional subject areas or enhancing the first one.
3) The early functional prototype allowed me to put a working example in front of the business stakeholders. Giving the business stakeholders a concrete example affords the opportunity to gather more specific or – gasp – additional requirements from them.
4) By adhering to a best practice methodologies, we were able to
a. Meet the essential requirements of the project
b. Include “low hanging fruit” that add value to the deployed solution, For example:
i. In the UDM we were able to design multiple date hierarchies even though only one was featured on the deployed reports.
ii. Add attributes to the cube that would be valuable during ad hoc analysis, even though these were not called for in the original requirements.
iii. Correct modeling and design of the MDX allowed creation of measures that provide year-over-year comparisons no matter what date hierarchy the end user selected.
Business Intelligence implementations come in all shapes and sizes, but the common denominators I have found that make them a success are:
· Early business stakeholder involvement
· Iterative approach to keep deadlines manageable and provide real value in short time frames
· Adhere to industry standard best practices for modeling and architecture
In addition to the above attributes, one notable characteristic of this engagement was that we used Reporting Services to deliver a high degree of flexibility and interactivity for the end-use reports. My next blog posting will examine the techniques I used in more detail.
