Multidimensional Models and Relational Database Schemas - Microsoft SQL Server 2008 Analysis Services - page 327

define a useful set of aggregations for your cube. Given that, you will see improved
performance in your cube.

. Processing the dimension is faster because Analysis Services processes each attribute

independently from others and sends a query for data required by the attribute. This
data often belongs to a single table, which is smaller than a single, large (non-
normalized) table.

. The relational schema enforces quality of data, and therefore there is a smaller prob-

ability of referential integrity problems.

. Smaller data warehouse size because records for attribute members are stored just

once.

Disadvantages

. Relational schema is relatively more complex.

. It is harder to perform incremental updates to the dimension. The ProcessAdd com-

mand becomes more complex because you need to specify separate DSV in out-of-
line binding of the command.

Optimizing Relational Schemas

Relational schemas used in a data warehouse differ from schemas used in OLPT systems.
The pattern of their access also differs, and therefore you need to create different sets of
indexes.

Microsoft SQL Server provides an easy-to-use and powerful tool: Database Engine Tuning
Advisor. The purpose of this tool to help you to create a set of data structures (such as
different types of indexes, partitions, and others) to increase query performance of rela-
tional databases.

Before we discuss the Database Engine Tuning Advisor tool, it is useful to go over reason-
ing and tradeoffs associated with creating indexes for relational database objects.

First of all, you can create several types of indexes in relational databases. You can read the
“Index Design Basics” topic in the SQL Server Books Online to get a better idea about how
indexes affect relational database performance. As a gross generalization, one of the major
benefits of creating an index for a relational database table is the increase in performance
of SELECT relational queries. These are the same type of queries Analysis Services sends to
the relational database to retrieve the data during the processing operation.

In many cases, the disadvantage of having an index is that it slows down table updates.
INSERT , UPDATE , DELETE , and MERGE statements become slower because the relational
engine has to adjust all indexes as data in the table changes.

One of the strategies you can implement to work around this problem is to create a new
set of tables each time you push updates from the OLTP system into your data warehouse