[et_pb_section][et_pb_row][et_pb_column type=”1_4″][et_pb_sidebar admin_label=”Sidebar” orientation=”left” area=”et_pb_widget_area_1″ background_layout=”light” /][/et_pb_column][et_pb_column type=”3_4″][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]
Creating a Derived Staging table
[/et_pb_text][et_pb_text admin_label=”Text” background_layout=”light” text_orientation=”left”]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. It’s 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:
- 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.
- Create a Source System for your Staging Database. The Source System Abbreviation must be Derived. The Source system name can be anything but ‘Staging’.
- 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. Tip. Give the Extract an Extract Name prefixed with Derived. When defining the ETL Batch Workflow later you can create a phase that executes after all the regular Staging table Extracts have been executed, and processes all extracts that start with the name Derived.
- When defining the Staging table tick the Derived tick box on the Staging table tab of the Staging table editor. This ensures the Staging table is generated with the Derived prefix in the Staging database. Also the SSIS package is prefixed with Extract_Derived instead of Extract.