1197
Chapter 53: Building Multidimensional Cubes in Analysis Services with MDX
53
Unifi ed Dimensional Model
The Unifi ed Dimensional Model (UDM) defi nes the structure of the multidimensional data-
base including attributes presented to the client for query and how data relates and is
stored, partitioned, calculated, and extracted from the source databases.At the foundation of the UDM is a data source view that identifi es which relational tables
provide data to Analysis Services and the relations between those tables. Based on the
data source view, measure groups (facts) and dimensions are defi ned. Cubes then defi ne the
relations between dimensions and measure groups, forming the basis for multidimensional
queries.Server
The UDM, or database defi nition, is hosted by the Analysis Services server. Data can be kept
in a Multidimensional OLAP (MOLAP) store, which generally results in the fastest query
times, but it requires preprocessing of source data. Processing normally takes the form of
SQL queries derived from the UDM and sent to the relational database to retrieve underly-
ing data. Alternatively, you can send data directly from the Integration Services pipeline to
the MOLAP store.In addition to storing measures at the detail level, Analysis Services can store precalculated
summary data called aggregations. For example, if aggregations by month and product line
are created as part of the processing cycle, queries that require that combination of values
do not need to read and summarize the detailed data but can use the aggregations instead.Data can also be left in the relational database, or ROLAP store, which generally results in
the fastest processing times at the expense of query times. Without aggregations, queries
against a ROLAP store cause the equivalent SQL to be executed as needed. Aggregations can
be precalculated for ROLAP, but doing so requires processing all the detailed data, so MOLAP
is the preferred option. A compromise between the speed of MOLAP storage and the need
for preprocessing, called proactive caching, serves queries out of MOLAP storage when pos-
sible, but queries the relational database to retrieve the latest data not yet processed into
the MOLAP store.Client
Clients communicate with Analysis Services, like any other web service, via the Simple
Object Access Protocol (SOAP) using an XML format called XMLA or XML for Analysis.
Client applications can hide XMLA and SOAP details by using the provided data access
interfaces to access Analysis Services:■ (^) All .NET languages can use ADOMD.NET.
■ Win32 applications (such as C++) can use the OLE DB for OLAP driver.
■ (^) Other COM-based applications (such as VB6, VBA, and scripting) can use ADOMD.
c53.indd 1197c53.indd 1197 7/31/2012 10:30:22 AM7/31/2012 10:30:22 AM
http://www.it-ebooks.info