The Microsoft® Data Warehouse Toolkit: With SQL Server™ 2005 and the Microsoft® Business Intelligence Toolset

17.4. Using Analysis Services to Deliver Real-Time Data

In this book, we've encouraged an architecture that uses Analysis Services as the primary query engine for your DW/BI system. The advantages of user-oriented metadata, support for complex analytic expressions, and generally excellent query performance are appealing to consumers of real-time data, too. Many DW/BI users also need to access purely operational data and would like the same rich tools in the real-time environment.

Microsoft provides some interesting functionality that you can use to deliver real-time data in Analysis Services. The two most important features are:

• The ability to build an Analysis Services database directly from a transactional (normalized) data structure, without first putting that data in the data warehouse database. You may have seen this functionality described as the Unified Dimensional Model, or UDM. The UDM simply refers to the Analysis Services database definition metadata.

• The ability to populate an Analysis Services database automatically, as data flows into the relational source, known as Proactive Caching.

17.4.1. Building Cubes from Normalized Data

Just for kicks, start up BI Studio and run the Analysis Services cube designer wizard against the AdventureWorks database, rather than the AdventureWorksDW or MDWT_AdventureWorksDW databases. As you will see, you can build a cube against a normalized database, and the design works surprisingly well.

You could create an Analysis Services layer ...