Advice on Prototype for a Business Intelligence System

Our organisation lacks any data mining or analytical tools, so I'm trying to persuade them to implement a Business Intelligence solution using Microsoft SQL Server 2008 R2. They've asked for a prototype, so they can get a better idea of what Business Intelligence can do for them.

I'm assuming that the prototype will consist of -

• A subset of data from a critical application
• Integration Services (SSIS): Used to clean the data subset?
• Analysis Services (SSAS): Used to create and maintain a dimensional model based on that data subset?开发者_如何学C Data Mining?
• Reporting Services (SSRS): Used to create, maintain and update a 'dashboard' of reports.

I want to show how a Business Intelligence solution with data mining and analytic capabilities can help their organisation perform better.

As this is the first time I've done this, I'd value advice from other people on whether this prototype is realistic or not. And does anyone know of any easily-accessible real-life examples that I can show them?

my thoughts …

• Don’t overestimate the size (in terms of time) of a new DWH project.
• Start with something not very complex and well understood in terms of business rules.

The biggest problem we have had with new DWH projects/pilots (we are a DWH consultancy so have a number of clients) is getting management support of it. Often the DWH will be sponsored by someone in IT and there is no real board level support, so the project takes a long time to progress and it is difficult to get resources for it.

The best projects we have found are ones that have levels of support in three areas: Management (board level), IT and Business (preferably someone with good understanding of the business rules involved).

Have a look at Ralph Kimball’s Data Warehouse Toolkit which goes through different styles of DWH design for different industries. It is very good!

The tools I expect you would use (I’ve added a couple of technologies here)

• SSIS (ETL tool) is used to Extract (from source systems) Transform (data into appropriate form the load) and Load (into Dim and Fact tables)
• SSAS (OLAP tool) is used to create/process an OLAP cube. Warning: there is quite a large learning curve on this tool!!
• SSRS (reporting tool) is used to create static and dynamic reports/dashboards.
• MS Excel. There are free data mining models that can be added in and when connected to an OLAP cube which will allow very interesting DM to be performed.
• Windows Sharepoint Services (WSS) (comes free with a Windows Server operating systems) to deploy your SSRS reports onto.

This is a good prototype scope in terms of technologies (if you are from the MS background), but the spread of technologies is very large and for one person coming in cold to them, this is perhaps unrealistic.

The most critical thing is to get the DWH star schema design correct (and there will be a number of solutions that are “correct” for any set of data), otherwise your OLAP cube design will be flawed and very difficult to build.

I would get a local DWH consultant to validate your design before you have built it. Make sure you give them a very tight scope of not changing much otherwise most consultants will “tinker” with things to make them “better”.

Good Luck!

It's been 2 years since the question was posted and of course, there has been updates in the world of business Intelligence. We now have couple of great tools for prototyping in the Microsoft Business Intelligence World: - Power Query (Self Service ETL) - Power Pivot.

Hope this helps someone just getting started w/ building prototypes.