Advanced Modeling - Microsoft SQL Server 2008 Analysis Services - page 95

These two properties, which distinguish a referenced dimension, make it possible to bind
the dimension to the fact even though the granularity attribute is not bound to the fact.

In addition to indirect dimensions that are referenced, there are also direct referenced
dimensions. A referenced dimension is one that has a value of Regular for its
Materialization property. In the model, therefore, the data from the intermediate granu-
larity attribute is loaded into the fact, thus extending the fact data. Analysis Services mate-
rializes the granularity attribute of the referenced dimension during measure group
processing. In our example, the data for the ZIP Code attribute is added to the fact,
making the Geography dimension bound to the fact just like any regular dimension (see
Figure 8.5).

Time

Year

Geography

Quarter

Country

Customer

Month

Province

ZIP Code

Customer Date

ZIP Code

Sales Count

ZIP Code Customer Day

Measure Group Sales

FIGURE 8.5 The Geography dimension is a materialized referenced dimension to the Sales
measure group.

When the value of the Materialization property is Indirect , data for the ZIP Code
attribute isn't loaded into the fact. Every time we need data about the geographical loca-
tion of sales, the system searches the Customer dimension and maps the data in the
Geography dimension to the value of the ZIP Code attribute of the Customer dimension.
Now information about sales can be enriched by the information about country and
province where the sale occurred.

In Listing 8.2, we define a referenced dimension from the HR cube in our FoodMart 2008
database. The HR cube contains data about employees and their salaries. This cube has a
Salary measure group that contains information about the salaries of employees by time.
However, we also want to find data about salaries paid to employees according to the store
where they work.

To serve both these needs, the system uses the Store dimension as a referenced dimen-
sion. The Employees dimension has data for each employee describing the store each