EDW 3NF/Normalized Table rename

  • Former WhereScape RED Normalized Tables have been renamed to EDW 3NF from RED version 6.8.4.3. However, this change applies only to new metadata repositories, existing metadata repositories will not be affected and will not have their table's naming modified. All references to Normalized tables have been updated in the RED documentation from version 6.8.4.3 onwards. However, some screenshots of the RED left pane browser might still show instances of the Normalized object type instead of the new EDW 3NF type.
  • To modify table naming from Normalized to EDW 3NF in existing repositories see Object Type Names and Global Naming Conventions. Please note that short name and table prefixes can be overwritten by the Local Naming conventions setting in the User Preferences. If this is the case, you can disable this option here: Local Naming conventions.

An EDW 3NF Table is a data warehouse object used to build third-normal form enterprise data warehouses.

In WhereScape RED, EDW 3NF objects have many of the code-generating attributes of Stage, Dimension, and Fact tables.

Third normal form enterprise data warehouses can be thought of as a source system for star schema data marts. Alternatively, they may be reported directly by users and reporting tools.

EDW 3NF tables can be considered either reference or transactional.

An EDW 3NF table is built from the Data Warehouse connection. Unless you are retrofitting an existing system, EDW 3NF Tables are typically built from one or more load or stage tables.

The EDW 3NF model may be retrofitted from an enterprise modeling tool. Refer to Importing a Data Model for details.

The usual steps for creating an EDW 3NF model are defined below and are covered in this chapter. The steps are:

  1. Identify the source reference or transactional data that will constitute the EDW 3NF Table. If the data is sourced from multiple tables ascertain if a join between the source tables is possible, or if one or more intermediate stage (work) tables would be a better option.
  2. Using the 'drag and drop' functionality drag the load or stage table that is the primary source of information for the EDW 3NF Table into an EDW 3NF target. Refer to Building an EDW 3NF Table for details.
  3. If there's only one source table and all the columns from it are being used, you can select the auto-create option to build and load the table. This automatically completes the next four steps. Refer to Building an EDW 3NF Table for details.
  4. Add columns from other load and/or stage tables if required. Refer to Building an EDW 3NF Table for details.
  5. Create the EDW 3NF Table in the database. Refer to Building an EDW 3NF Table for details.
  6. Build the update procedure. Refer to Generating the EDW 3NF Update Procedure for details.
  7. Run the update procedure and analyze the results.

If necessary, modify the update procedure or create a custom procedure.

EDW 3NF Table Keys

EDW 3NF Tables have two types of keys that we will refer to frequently. These are the Business Key and the Artificial Key. A definition of these two key types follows:

Business Key

The business key is the column or columns that uniquely identify a record within an EDW 3NF Table. Where the EDW 3NF Table maps back to a single or a main table in the source system, it is usually possible to ascertain the business key by looking at the unique keys for that source table. The business key is sometimes referred to as the 'natural' key. Examples of business keys are:

It is assumed that business keys will never be NULL. If a null value is possible in a business key then the generated code will need to be modified to handle the null value by assigning some default value.

Business keys are assumed to never be Null. If they can be null it is best to transform them to some value before the EDW 3NF or Stage table update.

If this is not done, an un-modified update procedure will probably fail with a duplicate key error on the business key index.

Artificial Key

By default, EDW 3NF Tables in WhereScape RED do not have an artificial key (artificial keys can be added manually, refer to EDW 3NF Table Artificial Keys for details.

An artificial key is a unique identifier that can be used to join an EDW 3NF Table record to other EDW 3NF Tables.

When joining EDW 3NF Tables it would be possible to perform the join using the business key. For EDW 3NF Tables that satisfy one of more of the following conditions, joining with business keys could result in slow query times and excessive use of database storage:

As query time is one of our key drivers in data warehouse implementations the best answer is often to use some form of artificial key.

A price is paid in the additional processing required to do key lookups, but this is offset by the reduced query times and reduced complexity.

The artificial key is an integer and is built sequentially from 1 upwards.

Refer to the section on artificial keys for a more detailed explanation. An artificial key is sometimes referred to as a "surrogate" key.