Data Source View definition

While the Data Source connection is required to tell us from where data is supposed to be retrieved, the Data Source View is required to tell us what data should be used and how these data are related to each other.

In other words, the Data Source View is where we define the relationship between the tables that we want to access via the Data Source. We will also be setting logical keys for the tables as we define their relationship.

Start out by right-clicking the Data Source Views folder to create a new Data Source View.

The first step in the Data Source View Wizard tells us that none of our source tables in the Stage database has any foreign keys (we will get back to the definition of foreign keys later in this chapter), and therefore we are offered this option to let the wizard start guessing relationships between our source tables.

We do not recommend this, but would rather have full control over table relationships ourselves, so we will disable this option.

In the next step we will select from a list of available objects (tables in our case) to be included in the Data Source View. During the Stage database import we did in a previous lesson we already carefully selected the few tables we needed at this point, so in this case we will simply them all to be included in the Data Source View.

Move all the imported tables from Available objects to Included objects.

Finally, give the Data Source View a proper name:

When we finish the Data Source View Wizard, all the included tables will be placed in the main section of the Data Tools window.

However, initially they may not all be visible. Some of the tables may reside outside the borders of the window.

To change this, simply right-click in this section to change the zoom scale.

Use the "To Fit" zoom option to see all included tables.

Fact tables and Dimension tables

Some tables contain the actual data or the facts that we would like to analyze such as Amount, Quantity etc. These tables are often referred to as Fact tables.

Each post in the fact tables also contains information about e.g. which item has been sold, but the item information in the fact table may be kept short, often limited to the item number only.

Further information about an item number such as name and description may then be found in another table, e.g. the Item table. Such a table is then called a Dimension table.