UDM: The Best of Both Worlds

The next release of Analysis Services, coming in SQL Server Yukon, will combine the best aspects of traditional OLAP-based analysis and relational reporting into one dimensional model—the Unified Dimensional Model (UDM)—that covers both sets of needs. Compared to direct access of a relational database, OLAP technology provides many benefits to analytic users. OLAP's dimensional data model makes it easy to understand, navigate, and explore the data. And OLAP's precalculation of aggregate data enables fast response time to ad hoc queries, even over large data volumes. An analytic engine, supporting the Multidimensional Expression (MDX) query language, lets you perform analytic calculations. And OLAP's data model includes rich metadata that lets you employ user-friendly, business-oriented names, for example.

However, reporting directly from the underlying relational database still has its advantages. OLAP, traditionally oriented around star or snowflake schemas, doesn't handle the arbitrary, complex relationships that can exist between tables. Reporting on the underlying database lets you handle flexible schema. OLAP cubes also expose data in predetermined hierarchies, making it unfeasible to provide true ad hoc query capability over tables that have hundreds of columns. Directly accessing the relational store means that results are realtime, immediately reflecting changes as they're made, and you can drill down to the full level of detail. In addition, by not introducing a separate OLAP store, you have less management and lower total cost of ownership (TCO). Table 1 compares the advantages of relational versus OLAP-based reporting.

Many relational-based reporting tools try to gain some of OLAP's advantages by providing a user-oriented data model on top of the relational database and routing reporting access through that model. So, the many enterprises that need to provide both OLAP and relational reporting commonly end up with multiple reporting tools, each with different proprietary models, APIs, and end-user tools. This duplication of models results in a complex, disjointed architecture. Analysis Services' new UDM, however, combines the best of OLAP and relational approaches to enhance reporting functionality and flexibility.

The UDM Architecture

You define a UDM over a set of data sources, providing an integrated view of data that end users access. Client tools—including OLAP, reporting, and custom business intelligence (BI) applications—access the data through the UDM's industry-standard APIs, as the diagram in Figure 1 shows. A UDM has four key elements: heterogeneous data access, a rich end-user model, advanced analytics, and proactive caching. In tandem, these elements transform sometimes difficult-to-understand data into a coherent, integrated model. Although the UDM enables a range of new data-access scenarios, it builds on SQL Server 2000 Analysis Services, allowing easy migration from Analysis Services 2000 and backward compatibility for clients. Let's look at the UDM's key components in more detail.

Heterogeneous data access. You can build a UDM over a diverse range of data sources, not just star or snowflake data warehouses. By default, you can expose every column in a table as a separate attribute of a dimension, enabling exposure of potentially hundreds of dimension-table columns that users can drill down on. In addition, a cube can contain measures drawn from multiple fact tables, letting one cube encompass an entire relational database. The model also lets different kinds of relationships exist between measures and their dimensions, enabling complex relational schemas. This structure supports degenerate dimensions, letting users drill down to the lowest level of transaction data. You can also build a UDM over multiple heterogeneous data sources, using information integrated from different back-end data sources to answer a single end-user query. These capabilities, combined with unlimited dimension size, let the UDM act as a data-access layer over heterogeneous sources, providing full access to the underlying data.

Rich end-user model. The UDM lets you define an end-user model over this base data-access layer, adding the semantics commonly lacking in the underlying sources and providing a comprehensible view of the data that lets users quickly understand, analyze, and act on business information. The core of a UDM is a set of cubes containing measures (e.g., sales amount, inventory level, order count) that users can analyze by the details of one or more dimensions (e.g., customer, product). The UDM builds on Analysis Services 2000's end-user model, providing significant extensions. For example, the UDM lets you define Key Performance Indicators (KPIs), important metrics for measuring your business's health. Figure 2 shows how a client tool might display three sample KPIs, organized into display folders. The sidebar "The UDM End-User Model," page 35, describes the full set of end-user capabilities the UDM supports.

Advanced analytics. You can augment the end-user model by using a comprehensive, script-based calculation model to incorporate complex business logic into UDM cubes. The UDM's model for defining calculations provides something akin to a multidimensional spreadsheet. For example, the UDM can calculate the value of a cell—say, AverageSales for the category Bike in the year 2003—based on the values in other cells. In addition, the UDM might calculate a cell's value based not only on the current value of another cell but also on the previous value of that cell. Thus, the UDM supports simultaneous equations. For example, the UDM might derive profit from revenue minus expense but derive bonuses (included in expenses) from profit. In addition to providing the powerful MDX language for authoring such calculations, the UDM integrates with Microsoft .NET, letting you write stored procedures and functions in a .NET language, such as C# .NET or Visual Basic .NET, then invoke those objects from MDX for use in calculations.

Proactive caching. The UDM provides caching services that you can configure to reflect business and technical requirements—including realtime, or near realtime, access to data while maintaining high performance. The goal of proactive caching is to provide the performance of traditional OLAP stores while retaining the immediacy and ease of management of direct access to underlying data sources. Various UDM policy settings control the caching behavior, balancing the business needs for performance with an acceptable degree of latency. Examples of possible caching policies might be

The UDM also provides a flexible, role-based security model, letting you secure data down to a fine level of granularity. And Yukon will include a full set of enterprise-class tools for developing and managing UDMs. The development tools, including an MDX query editor and an MDX debugger, are integrated with other SQL Server tools for building reports and Data Transformation Services (DTS) packages as well as with Visual Studio .NET.

One Model for Reporting and Analysis

The UDM combines the best of traditional OLAP and relational reporting, providing a single model that you can use as the basis for all your reporting and analysis needs. This flexible model allows data access across multiple heterogeneous data sources, including OLTP databases and data warehouses. And through the UDM, users can access all data, including the lowest level of transaction detail. With the UDM's proactive caching, you can define policies to balance performance versus the need for realtime, or near realtime, information—without having to explicitly manage a separate Multidimensional OLAP (MOLAP) store. In addition, you can define a rich end-user model, including complex analytic calculations, to support interactive and managed reporting. Watch SQL Server Magazine for more details about the UDM and other Yukon Analysis Services enhancements.