Logical Data Warehouse Design - Data Warehouse Systems: Design and Implementation - page 150

￿ In the Time dimension table, the named calculations FullMonth , FullQuar-
ter ,and FullSemester , are defined, respectively, by the expressions
MonthName + '' + CONVERT(CHAR(4),Year)
' Q ' + CONVERT(CHAR(1), Quarter) + '' + CONVERT(CHAR(4), Year)
' S ' + CONVERT(CHAR(1), Semester) + '' + CONVERT(CHAR(4), Year)
These calculations combine the month, quarter, or semester with the year.
￿ In the Sales fact table, the named calculation OrderLineDesc combines the
order number and the order line using the expression
CONVERT(CHAR(5),OrderNo) + ' - ' + CONVERT(CHAR(1),OrderLineNo)
5.9.3 Dimensions
Analysis Services supports several types of dimensions as follows:
￿ A regular dimension has a direct one-to-many link between a fact table
and a dimension table. An example is the dimension Product .
￿ A reference dimension is indirectly related to the fact table through
another dimension. An example is the Geography dimension, which is
related to the Sales fact table through the Customer and Supplier dimen-
sions. In this case, Geography may be defined as a reference dimension
for the Sales fact table. Reference dimensions can be chained together, for
instance, one can define another reference dimension from the Geography
dimension.
￿ In a role-playing dimension , a single fact table is related to a
dimension table more than once, as studied in Chap. 4 . Examples are the
dimensions OrderDate , DueDate ,and ShippedDate , which all refer to the
Time dimension. A role-playing dimension is stored once and used multiple
times.
￿ A fact dimension , also referred to as degenerate dimension , is similar
to a regular dimension, but the dimension data are stored in the fact table.
An example is the dimension Order .
￿ In a many-to-many dimension , a fact is related to multiple dimension
members and a member is related to multiple facts. In the Northwind data
warehouse, there is a many-to-many relationship between Employees and
Cities , which is represented in the bridge table Territories .Thistablemust
be defined as a fact table in Analysis Services, as we will see later.
Dimensions must be defined either from a DSV, which provides data for
the dimension, or from preexisting templates provided by Analysis Services.
A typical example of the latter is the time dimension, which does not need
to be defined from a data source. Dimensions can be built from one or more
tables.
In order to define dimensions, we need to discuss how hierarchies are
handled in Analysis Services. In the next section, we provide a more