Reasons Why You Might Think You Should Build a Cube on a Transactional Data Model

Some of the cool new capabilities in SQL Server 2005 Analysis Services (SSAS) let you create OLAP cubes based on relational data models (i.e., normalized transactional or OLTP databases) that aren't designed for business intelligence (BI). SSAS supports many-to-many relationships, the ability to design cubes spanning many tables containing measures (even across multiple data sources), near realtime data access with proactive caching, and the ability to build more complex calculated members with robust MDX syntax. Those features help build better cubes on complex dimensional data models, but they open up possibilities - such as building OLAP cubes on OLTP databases - that aren't best practices.

The ability to build a cube on a transactional data model can make sense in some scenarios. For instance, it's better to build a cube based on a transactional data model than to do reporting and analysis on a mission-critical OLTP system. At least the SSAS cube buffers the underlying system from the performance impact of BI, buffers the end-user from schema changes, and adds SSAS programmability, security, and performance. If you need to get BI moving in your organization, building a data source view over OLTP and adding SSAS could be a valid approach (e.g., as proof of concept or a Phase 1 effort). Some situations do require near real-time data, and building an OLTP-based cube and using proactive caching meets that requirement. However, such situations are rare; if needed, real time date can usually be married with a data warehouse-based solution.