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

real column or a column calculated using an SQL expression. The general
structure of the physical schema of the Northwind cube is given next:

< PhysicalSchema >

1

< Table name= ' Employee ' keyColumn= ' EmployeeKey ' >

2

< ColumnDefs >

3

< ColumnDef name= ' EmployeeKey ' type= ' Integer ' / >

4

< ColumnDef name= ' FirstName ' type= ' String ' / >

5

< ColumnDef name= ' LastName ' type= ' String ' / >

6

...

7

< CalculatedColumnDef name= ' FullName ' type= ' String ' >

8

< ExpressionView >

9

< SQL dialect= ' generic ' >

10

< Column name= ' FirstName ' / > || '' ||

11

< Column name= ' LastName ' / >

12

< /SQL >

13

< SQL dialect= ' SQL Server ' >

14

< Column name= ' FirstName ' / > + '' +

15

< Column name= ' LastName ' / >

16

< /SQL >

17

< /ExpressionView >

18

< /CalculatedColumnDef >

19

< /ColumnDefs >

20

< /Table >

21

...

22

< Link source= ' City ' target= ' Employee ' foreignKeyColumn= ' CityKey ' / >

23

...

24

< /PhysicalSchema >

25

The Table element defines the table Employee (lines 2-21). The columns
of the table are defined within the ColumnDefs element, and each column
is defined using the ColumnDef element. The definition of the calculated
column FullName is given in line 8 using the CalculatedColumnDef element.
The column will be populated using the values of the columns FirstName and
LastName in the underlying database. The ExpressionView element is used
to handle the various SQL dialects, which depend on the database system.
As can be seen, concatenation of strings is expressed in standard SQL using
' ', while it is expressed in SQL Server using ' + '.Inthecaseof snowflake

schemas , the physical schema also declares the foreign key links between
the tables using the Link element. In the example above, the link between the
tables Employee and City is defined in line 23.

||

5.10.2 Cubes, Dimensions, Attributes, and
Hierarchies

A cube is defined by a Cube element and is a container for a set of dimensions
and measure groups, as shown in the schema definition at the beginning of
this section (lines 9-23).