Data Store Objects are often sourced from one table in the base application.
The process of building a Data Store Object begins with the drag and drop of the load or stage table that contains the bulk of the Data Store Object's information.

Drag and Drop

  1. Create a Data Store Object target by double-clicking on the Data Store group in the left pane. The middle pane will display a list of all existing Data Store Objects in the current project. When such a list is displayed in the middle pane, the pane is identified as a Drop Target for new Data Store Objects.
  2. Browse to the Data Warehouse via the Browse > Data Warehouse menu option.
  3. Drag the Load or Stage table that contains the bulk of the Data Store Object columns into the middle pane. Drop the table anywhere in the pane.
  4. The new object window appears and identifies the new object as a Data Store Object and provides a default name, based on the name of the selected Load or Stage table.
  5. Either accept this name or type in the name of the Data Store Object and click ADD to proceed:

Data Store Object Properties

The table Properties window for the new table is displayed.

Create and Load

If you chose to build the update procedure the following window appears, after clicking OK on the Properties window. It asks if you want to create the Data Store table in the database and execute the update procedure.

If Create or Create and Load is selected and a new procedure creation is chosen, proceed directly to the Generating the Data Store Update Procedure.
If you have additional columns to add or columns to delete then select Close and proceed as follows below.

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:

Deleting and Changing columns

The columns defined for the Data Store Object are displayed in the middle pane.

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 Data Store Object displayed in the middle pane, this pane is considered a drop target for additional columns.

A description of these columns follows:

Column nameDescription
dss_start_dateUsed for history tables. This column provides a date time stamp when the Data Store Object record came into existence. It is used to ascertain which Data Store Object record should be used when multiple are available.
dss_end_dateUsed for history tables. This column provides a date time stamp when the Data Store Object record ceased to be the current record. It is used to ascertain which Data Store Object record should be used when multiple are available.
dss_current_flagUsed for Data Store history tables. This flag identifies the current record where multiple versions exist.
dss_source_system_keyAdded 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_versionUsed for Data Store history tables. This column contains the version number of a Data Store history table 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 a Data Store history table.
dss_update_timeIndicates when the record was last updated in the data warehouse.
dss_create_timeIndicates when the record was first created in the data warehouse

Creating the table

Once the Data Store Object has been defined in the metadata, we need to physically create the table in the database.

  1. This is done by right-clicking on the Data Store Object in the left pane and selecting Create (ReCreate) from the pop-up menu.
  2. The Results pane displays the results of the creation. A message confirms that the Data Store Object was created. A copy of the actual database create statement and if defined, the results of any index create statements is listed. For the initial create, no indexes will be defined.
  3. 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 Column Name, which sorts the column names into alphabetical order.
  4. Another double-click on the heading sorts the columns back into their create order. 

The next section covers Generating the Data Store Update Procedure.