Creating and Using a Derived Staging Table

What is a derived staging table?

It’s a table created in your Staging database that derives its data from other already existing Staging tables. Its useful for creating aggregations, or allocations of data prior to the transform and load process into your Data Warehouse. It can also be useful for tables that don’t have any other source other than an SQL Query which creates the data.

To create a Derived Staging table in Dimodelo Data Warehouse Studio, do the following:

1. Create a Connection manager to your Staging Database. Name the Connection Manager anything other than ‘Staging’ as this will conflict with the standard Staging Connection added to SSIS packages.
2. Create a Source System for your Staging Database. The Source System Abbreviation must be Derived.
3. Create your Staging table. In the ETL Extract, set the source to a query, rather than a table. The query contains the logic of aggregation, allocation etc.

Thats it !