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

5.9 Definition of the Northwind Cube in Analysis
Services

We introduce next the main concepts of Analysis Services using as example
the Northwind cube. In this section, we consider a simplified version of the
Northwind cube where the ragged geography hierarchy was transformed into
a regular one. The reason for this was to simplify both the schema definition
and the associated MDX and SQL queries that we will show in the next
chapter. More precisely, we did not include sales data about cities that roll
up to the country level, such as Singapore. Therefore, we dropped the foreign
key CountryKey in table City . Moreover, we did not consider the Region level.
As a result, the hierarchy City → State → Country → Continent becomes

balanced.
To define a cube in Analysis Services, we use SQL Server Data Tools
introduced in Chap. 3 . The various kinds of objects to be created are described
in detail in the remainder of this section.

5.9.1 Data Sources

A data warehouse retrieves its data from one or several data stores. A data
source contains connection information to a data store, which includes the
location of the server, a login and password, a method to retrieve the data,
and security permissions. Analysis Services supports data sources that have
a connectivity interface through OLE DB or .NET Managed Provider. If the
source is a relational database, then SQL is used by default to query the
database. In our example, there is a single data source that connects to the
Northwind data warehouse.

5.9.2 Data Source Views

A data source view (DSV) defines the relational schema that is used for
populating an Analysis Services database. This schema is derived from the
schemas of the various data sources. Indeed, some transformations are often
needed in order to load data from sources into the warehouse. For example,
common requirements are to select some columns from a table, to add a new
derived column to a table, to restrict table rows on the basis of some specific
criteria, and to merge several columns into a single one. These operations can
be performed in the DSV by replacing a source table with a named query
written in SQL or by defining a named calculation , which adds a derived
column defined by an SQL expression. Further, if the source systems do not
specify the primary keys and the relationships between tables using foreign
keys, these can be defined in the DSV.