While the value of BI has been well-demonstrated for quite some time, that value has seemed hypothetical to many, because of the difficulty in keeping OLAP cubes up-to-date. Compared to an OLTP database, OLAP cubes have, for many, projected an image of inferiority because their data has for the most part been reflective of historical facts rather than of current transactions.
Historical data, and analysis of it, is very important, but appreciating that importance takes a certain vision and leap-of-faith. In most over-stressed IT organizations, dealing with historical data feels less urgent than dealing with current data. This has pushed BI down on the list of priorities…far enough down, in fact, that it has never gotten off the backlog list in many organizations. And while techniques have always existed for keeping cubes reasonably up–to-date, doing can involve a lot of work and require a lot of resources (of both the hardware and human variety), pushing BI projects further onto the back burner
SQL Server 2005’s Integration Services and Analysis Services 2005 change the landscape of the BI market by making real-time BI easy. New advances in these components allow you to load data directly into your cubes bypassing your data warehouse and/or allow Analysis Services to process cubes automatically in the background, in response to changes in your data warehouse.
I believe these advances will eventually change the whole dynamic of the BI market. People at all levels of management and even non-management will now be able to perform sophisticated analysis on current data, and they’ll be able to do so much faster than they could with a relational database.
As groundbreaking as these advances are, for people new to SQL 2005, it will not be immediately obvious how to take advantage of them and thus it will not be obvious that they even exist: the direct data push capabilities of SSIS are stashed away in the Visual Studio toolbox and the proactive caching features of Analysis Services are buried deep inside a wizard and some dialog boxes.
Take a look. You can adjust proactive caching settings directly from Management Studio or from Analysis Services projects in Visual Studio:
You can also do so from the Aggregation Design Wizard (available in both Visual Studio and Management Studio):
And the SSIS data push tasks are highlighted below:
I am posting these screen captures to make it clear how easy these tools are to get at, if you know where to look. In many cases, using them requires only a modicum of effort. I will try to coverage some basic usage scenarios in another post.