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.
End of Article
