Create a Staging Table

You must define a Staging table for Source data before the data can be incorporated into the Data Warehouse. Dimodelo Data Warehouse Studio allows you to import the metadata about columns and data types of a source table to quickly define a Staging table. When defining a Staging table extract, you have the choice to either:

  1. Map Staging columns to a Source table column.
  2. Map Staging Columns to the columns of query against the Source System.
  3. Create a Derived Staging table, by uses other Staging tables as the Source. For more information about Derived Staging tables see the ‘Creating a Derived Staging table’ section.

DA also implements two Patterns for Extracts. The two patterns are either Full Extract, or a Date Range Extract. More information can be found in the Patterns Guide. To create a Staging table, you must first define a Connection Manager and Source System of the Source table(s) in the DA project. This makes it easier to define Staging tables as you can import the columns and data types of the Staging table from the Source. A Staging table can only stage data from a single Source System, but can stage data from multiple tables in that Source system, although this is not a recommended approach (according to Kimball et al)  in most cases. If you do need to combine data from more than one Source System in the Staging database, to perhaps aggregate or allocate, then you can use a Derived Staging table.

Create a new Staging Table

  • Right click the Staging folder in the Solution Explorer tool window, and click ‘Create New’, a new Staging editor panel appears.
  • Select the Source System of the Staging table in the Source System field. The Source System is used to define the name of the Staging table in the Staging database. The table name in the Staging database will be Source System Abbreviation_Staging Table Name.

The quickest way to define a Staging table is to import its column definition from a Source table. If there isn’t a matching Source table, then you can manually define the columns. To import a Source table do the following:

  • On the Staging Columns tab Click the Import Schema button.

  • Select desired Connection Manager in the Connection dropdown.
  • Select Source table or view in the Import Table field.
  • Click Ok. The columns and Data types of the Source table in the source system are imported. You can see the columns on the Staging Columns tab.

Return to the Staging Table tab:

  • Enter a Staging Table Name. The name is used to define the name of the table in the Staging database. The Staging Table Name is used to define the name of the Staging table in the Staging database. The table name in the Staging database will be Source System Abbreviation_Staging Table Name.
  • The Derived check box must be ticked for a derived staging table. This affects the Name of the table in the Staging database. The database table name is prefixed with Derived rather than the Source System abbreviation. For more information about Derived staging tables read the ‘Creating a Derived Staging table’ section.
  • Enter a Description for the Staging table. This is optional. The Description is used as an overview for the table in the generated Documentation.

Staging Columns

Click the Staging Columns tab to view the columns of the Staging table. If you imported from a Source table, the names and data types of columns has been populated with the from the Source table. You can:

  • Modify a Column Name. Tip… Only modify the column names after you have defined an Extract. That way DA will automatically match the columns in the Source table with columns in the Staging table based on name, when you edit the mapping in the Extract. After that, you can come back and change the name, DA recognizes the name change. The Column Name becomes the name of the column in the Staging Database.
  • Modify a Column Data Type. This may mean you need to convert the data type of the Source column in the Extract definition using an Expression.
  • Add a Description. This is optional. The Description is used in the generated Documentation.
  • Delete a Column. Just click the Delete link at the end of the row.
  • Add a Column. You will need to add a mapping in the Extract if you add a column.

Dimodelo Data Warehouse Studio will Sync the columns when the Data Warehouse is deployed.

Advanced Tab

  • Staging Tags. Some Extract patterns require custom design data to operate correctly. See the patterns guide for more information on applicable custom design/meta data.