IN2BI, Microsoft Business Intelligence
data, stories & insights
rss | email | twitter

DWH Deck 2.0: How to change or add templates

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.

image

In the template tab of the DWH Deck there are 3 areas, indicated above:

  1. The area that describes the naming conventions you want to use for your target schema, target table and job.
  2. The area where you can define additional (housekeeping) columns that you want to add to the target table:
    • AuditID
    • Start date
    • End date
    • Record source
    • Surrogate key
  3. The template statement area. In this part you can edit the selected template statement. You can choose between:

 

Placeholder tokens

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

image 

 

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:

  • @@SourceColumns
    ... [col1], [col2]..
  • @@NotSelectedSourceColumns
  • @@TargetColumns
  • @@DefintionTargetColumns
    … [col1] INT IDENTiTY(1,1), [col2] varchar(50) ..
  • @@PKColumns
  • @@BKColumns
  • @@srcColumns
    … src.[col1], src.[col2]
  • @@tgtColumns
    … tgt.[col1], tgt.[col2]
  • @@WhereList
    … src.[col1]=tgt.[col1] AND src.[col2]=tgt.[col2]
  • @@DimWhereList
    … his.[col1]=cur.[col1] AND his.[col2]=cur.[col2]
  • @@LKPColumns
    … <Column SourceColumn=col1 TargetColumn=col1 />
  • @@SCDColumns
    … <Column SourceColumn=col1 TargetColumn=col1  MappingType=Key />
  • @@DimViewColumns
    … 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.

» Similar Posts

  1. Create a LightSwitch Application to Maintain Metadata
  2. Making your Biml files less complex
  3. Building a data warehouse while the source systems are still in development

» Trackbacks & Pingbacks

    No trackbacks yet.

» Comments

    There are no comments.

Comments are closed