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
- 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.
- Browse to the Data Warehouse via the Browse > Data Warehouse menu option.
- 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.
- 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.
- 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.
- If required, the Data Store Object 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 Data Store Object is simple (e.g. one source table) then it is possible to create, load and update the Data Store Object in a couple of steps.
- If you want 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 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.
- 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 Data Store 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 Data Store Object displayed in the middle pane, this pane is considered a drop target for additional columns.
- It is a simple matter to select 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 was dragged from.
- 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, description could become group_description, and so forth.
- Several WhereScape RED ancillary columns 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 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_date | Used 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_flag | Used for Data Store 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 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_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 Data Store Object has been defined in the metadata, we need to physically create the table in the database.
- This is done by right-clicking on the Data Store Object in the left pane and selecting Create (ReCreate) from the pop-up menu.
- 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.
- 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.
- Another double-click on the heading sorts the columns back into their create order.
The next section covers Generating the Data Store Update Procedure.