EDW 3NF tables are often sourced from one table in the base application. The process for building an EDW 3NF table begins with the drag and drop of the Load or Stage table that contains the bulk of the EDW 3NF table's information.
Drag and Drop
- Create an EDW 3NF table target by double-clicking the EDW 3NF object group in the left pane. The middle pane displays a list of all existing EDW 3NF tables in the current project. When this list is displayed in the middle pane, the pane is identified as a Drop Target for new EDW 3NF tables.
- Browse to the Data Warehouse via the Browse > Data Warehouse menu option.
- Drag the Load or Stage table, which contains the bulk of the EDW 3NF table columns, into the middle pane. Drop the table anywhere in the pane.
- The new object window appears and identifies the new object as a EDW 3NF table and provides a default name, based on the name of the selected Load or Stage table.
- Either accept this name or type in the name of the EDW 3NF table and click ADD to proceed:
EDW 3NF Table Properties
The table Properties window for the new table is displayed.
- If required, the EDW 3NF table can be changed to be a history table by choosing History from the table type drop-down list on the right side of the dialog. History tables are like slowly changing dimensions in dimensional data warehouses. Refer to Building a Dimension for details. Change the storage options if desired.
- If prototyping and the EDW 3NF table are simple (e.g. one source table) then it is possible to create, load, and update the EDW 3NF table in a couple of steps.
- If you wish to do this, select the (Build Procedure...) option from the Update Procedure drop-down, and answer Create and Load to the next question.
Create and Load
If you choose to build the update procedure, the following window appears after clicking OK in the Properties window. It asks if you want to create the EDW 3NF table in the database and execute the update procedure.
If you are satisfied with the columns that will be used and do not wish to add any additional columns you can select the Create and Load button. Alternatively, the Create button creates the table in the repository but does not execute an update, enabling you to change columns before loading data into the table.
If Create or Create and Load is selected and a new procedure creation is chosen, proceed directly to the Generating the EDW 3NF Update Procedure.
It is possible to create and load the table via the Scheduler; by selecting this option from the drop-down list on the Create and Load button:
If you have additional columns to add or columns to delete then select Close and proceed as follows below.
Deleting and Changing columns
The columns defined for the EDW 3NF table are displayed in the middle pane.
- It is possible to delete any unwanted columns by highlighting a column name or a group of names and clicking Delete.
- The name of a column can also be changed by right-clicking the column and choosing Properties to edit its properties.
- Any new name must conform to the database naming standards. Good practice is to use alphanumerics and the underscore character.
Refer to EDW 3NF Column Properties for more details.
When prototyping, and in the initial stages of an analysis area build, it is best not to remove columns, nor to change their names to any great extent. This type of activity is best left, and done after end users have used the data and provided feedback.
Adding columns
With the columns of the EDW 3NF table displayed in the middle pane, this pane is considered a Drop Target for additional columns.
- Select the columns from other Load and/or Stage tables and drag these columns into the middle pane. The source table column in the middle pane shows where each column originated.
- The column description could be acquired from three different tables. Best practice is to rename at least two of the columns, perhaps also adding context to the column name. For example, the description could become group_description, and so forth.
- There are a number of WhereScape RED ancillary columns that do not have a source table. These columns have been added by WhereScape RED and are added depending on earlier choices.
A description of these columns follows:
| Column name | Description |
|---|---|
| dss_start_date | Used for history tables. This column provides a date time stamp when the EDW 3NF table record came into existence. It is used to ascertain which EDW 3NF table record should be used when multiple tables are available. |
| dss_end_date | Used for history tables. This column provides a date time stamp when the EDW 3NF table record ceased to be the current record. It is used to ascertain which EDW 3NF table record should be used when multiple tables are available. |
| dss_current_flag | Used for EDW 3NF history tables. This flag identifies the current record where multiple versions exist. |
| dss_source_system_key | Added to support history tables that cannot be fully conformed, and the inclusion of subsequent source systems. See the ancillary settings section for more details. |
| dss_version | Used for EDW 3NF history tables. This column contains the version number of an EDW 3NF history tables record. Numbered from 1 upwards with the highest number being the latest or current version. It forms part of the unique constraint for the business key of an EDW 3NF history tables. |
| dss_update_time | Indicates when the record was last updated in the data warehouse. |
| dss_create_time | Indicates when the record was first created in the data warehouse |
Creating the table
Once the EDW 3NF table has been defined in the metadata, we need to physically create the table in the database.
- This is done by right-clicking on the EDW 3NF table in the left pane and selecting Create (ReCreate).
- The Results pane appears to show the results of the creation. The message confirms that the EDW 3NF table was created. A copy of the actual database-created statement and if defined, the results of any index create statements is listed. For the initial create, no indexes will be defined.
- If the table was not created, then ascertain and fix the problem. A common problem is a 'Duplicate column' where a column has the same name in two of the source tables. The best way to find such a column is to double-click the list heading Col name; which sorts the column names into alphabetical order.
- Another double-click on the heading sorts the columns back into their create order.