OLAP cubes are a great enabler of many typical business analyses, analyses that deal with large quantities of data, and use basic mathematical operations mostly (pivoting, drilling down and up and similar). However, traditional OLAP systems have some common problems:
- comparing actual and plan data,
- different representations in different client tools,
- pivoting enabled only for hierarchies (like customer-city-region-country, day-month-quarter-year) but not for arbitrary attributes (like color and size),
- somehow stale data (usually the data is refreshed over the night),
- and more.
The main issue with traditional OLAP systems (to name just one: Analysis Services 2000) is poor metadata. A cube was limited on a single star schema, i.e. on a single fact table. In addition, a join between a fact table and a dimension was possible on the lowest level of granularity only. In AS 2000, a cube actually inherited the schema from a single star schema inside a data warehouse, with just couple of additional properties.
The Unified Dimensional Model (UDM) in AS 2005 is much, much richer. A single OLAP cube supports now multiple fact tables, and they you can connect with dimensions on different levels of granularity. This way you can have, for example, actual and planned sales data together in the same cube, and actual data on day level while plan data on quarter level. UDM is attribute- and not hierarchy-based, so you can use any attribute for cross tabulations. It has enormously reach metadata. It is still possible to inherit the schema from the data warehouse, but because of this rich metadata, you can also create the cube first, and then generate the relational data warehouse schema from the cube. This is especially useful for proof-of-concept projects, when you have to show some results very quickly.
The rich UDM schema includes Key Performance Indicators (KPIs), which are useful for different scorecards. Because all of this metadata is stored inside a cube, all client tools can now show the same presentation of the information. Thereby you can easily achieve a single version of truth in your enterprise.
Physically, you can implement low-latency MOLAP (Multi-dimensional OLAP, where data and aggregations are stored in an Analysis Services database) solutions with proactive caching. This way you can get nearly on-line data together with OLAP lightning speed of reports. However, I agree with Teo Lachev (see his excellent book Applied Microsoft Analysis Services 2005, ISBN 0-9766-3530-5) that although the proactive caching could be very useful in some scenarios, it is probably the most oversold AS 2005 feature.
I heard some OLAP developers complaining about the complicated process of development of an UDM (OLAP) cube with AS 2005. Well, you can still develop it in classical way, with metadata inherited from a data warehouse only. Nevertheless, I think it is worth investing your time in investigating the richness of the UDM. You can push your analytical system to a completely new level.
Besides projects, I spend about half of the time on training and mentoring. I am the founder of the Slovenian SQL Server and .NET Users Group. and the main author or coauthor of eleven books about databases and SQL Server. I also developed many courses and seminars for SolidQ.
Latest posts by Dejan Sarka (see all)
- Python for SQL Server Specialists Part 4: Python and SQL Server - April 24, 2018
- Python for SQL Server Specialists Part 3: Graphs and Machine Learning - April 11, 2018
- Python for SQL Server Specialists Part 2: Working with Data - March 22, 2018