In previous blog posts I described the basics of working with the DWH Deck. In this post I’ll dive a bit deeper into the templates.
Importance of templates
The template concept is the basis of the DWH Deck:
The DWH Deck combines the information from the source system with the selected templates to create the target data warehouse and the necessary ETL.
In the template tab of the DWH Deck there are 3 areas, indicated above:
- The area that describes the naming conventions you want to use for your target schema, target table and job.
- The area where you can define additional (housekeeping) columns that you want to add to the target table:
- Start date
- End date
- Record source
- Surrogate key
- The template statement area. In this part you can edit the selected template statement. You can choose between:
- Create target objects,
to create target tables, indexes and views
- Create load procedure,
to create the stored procedure to load the data
- Create BIML Package,
to create the BIML files that you can use to create SSIS Packages
- Create target objects,
At several places you can use placeholders that are replaced with the actual value when a job is created.
In the next image of the dimension template the placeholder @ObjectName is marked. When you create a job for the Customer dimension this placeholder is replaced with the name of the source object. (Customer) Which will result in:
- Load_dimCustomer for the job name
- Customer for the target table name (in schema dim)
- Additional surrogate key column with the name: dimCustomerKey
In the template statement 2 types of placeholders are used:
- Type A starts with one @ character (e.g.: @SourceConnection)
- Type B representing a column list that starts with two @@ characters (e.g.: @@SourceColumns)
The current Type A placeholders are:
- @CreatedOn, the date the job was created
- @TemplateID, the id of the template used
- @TemplateName, the name of the template used
- @MetaConnection, the name of the connection with the solution (meta) tables
- @MetaServer, the name and instance of the server for the meta connection
- @MetaDatabase, the name of the database for the meta connection
- @SourceConnection, the name of the source connection
- @SourceServer, the name of the database for the source connection
- @SourceDatabase, the name of the source database
- @SourceObject, the source object e.g: [dbo].[vw_Customers]
- @ObjectName, the name of the source object e.g.: Customers
- @JobID, the id of the job
- @JobName, the name of the job
- @TargetConnection, the name of the target connection
- @TargetServer, the name and instance of the server for the target connection
- @TargetDatabase, the name of the target database
- @TargetSchema, the name of the target schema
- @TargetTable, the name of the target table
The current Type B placeholders are:
... [col1], [col2]..
… [col1] INT IDENTiTY(1,1), [col2] varchar(50) ..
… src.[col1], src.[col2]
… tgt.[col1], tgt.[col2]
… src.[col1]=tgt.[col1] AND src.[col2]=tgt.[col2]
… his.[col1]=cur.[col1] AND his.[col2]=cur.[col2]
… <Column SourceColumn=col1 TargetColumn=col1 />
… <Column SourceColumn=col1 TargetColumn=col1 MappingType=Key />
… cur.[col1], his.[col1] as col1_his
The Type B placeholders are defined in a separate stored procedure: meta.usp_ColumnLists.
You can view and add definitions that are used for these placeholders.
In this blog post I described the templates.
This concludes my first series of post on this version of the DWH Deck.