A Data Store Object is a data warehouse object used to store any type of data for later processing. In WhereScape RED, Data Store objects have many of the code-generating attributes of Stage, Dimension, and Fact tables. Data Store objects can be thought of as a source system for the data warehouse. Alternatively, they may be reported directly by users and reporting tools. Data Store Objects can be considered either reference or transactional.
A Data Store Object is built from the Data Warehouse connection. Unless you are retrofitting an existing system, Data Store Objects are typically built from one or more Load or Stage tables. The Data Store model may be retrofitted from an enterprise modeling tool. Refer to Importing a Data Model for details.
The usual steps for creating a Data Store 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 Data Store Object. 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 Data Store Object into a Data Store target. Refer to Building a Data Store Object for details.
  3. If there's only one source table and all of 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 a Data Store Object for details.
  4. Add columns from other Load and/or Stage tables if required. Refer to Building a Data Store Object for details.
  5. Create the Data Store Object in the database. Refer to Building a Data Store Object for details.
  6. Build the update procedure. Refer to Generating the Data Store Update Procedure.
  7. Run the update procedure and analyze the results.

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

Data Store Object Keys

Data Store Objects have Business Keys, they do not usually have Artificial Keys.

Business Key

The business key is the column or columns that uniquely identify a record within a Data Store Object.
If the Data Store Object 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:

  • The product SKU in a product table
  • The customer code in a customer table
  • The IATA airport code in an airport table.

It is assumed that business keys will never be NULL. If a NULL value is possible in a business key then the generated code needs to be modified to handle the NULL value by assigning some default value. In the following examples, the business key column is modified by using a database function and default value:

  • DB2: COALESCE(business_key,'N/A')
  • SQL Server: ISNULL(business_key,'N/A')

Business keys are assumed to never be NULL. If they can be null it is best to transform them to some value before the Data Store or Stage table update. If this is not done, an unmodified update procedure will probably fail with a duplicate key error on the business key index.


  • No labels