Supercharge your SSDT database projects using T4 to auto-generate T-SQL code! |
There are many techniques that data warehouse developers could learn from other parts of the software industry and automated code generation is one of them. Most database developers have at some point in their careers written T-SQL code to query the SQL Server catalog views to automate the generation of some SQL code. However, such approaches always suffer from the same drawback: they are working with a materialized copy of the database you are developing, not the up-to-the-minute actual code you are working with within SQL Server Data Tools (SSDT).
The Text Template Transformation Toolkit (T4) is easy to overlook because it is baked into the heart of your favourite development tool SQL Server Data Tools (SSDT). First released in Visual Studio 2005, the Text Template Transformation Toolkit has been the standard code-generation technique for all application developers. One of the key reasons T4 has not been widely adopted by the database community is that there are precious few examples out there about using T4 to generate T-SQL code and the official Microsoft documentation uses HTML or C# code generation in all their examples (yuk!). Also when you open Visual Studio, T4 does not jump out and grab you. You need to know where to look.
Essentially T4 Templates can be used anywhere in your SSDT project where you need to generate multiple database objects based on other database objects such as tables. In the context of data warehouse development, T4 Templates are particularly useful for generating MERGE statements, triggers and history tables (i.e. tables to record changes to data in other tables).
As an example of their practical use, let’s take a real-world scenario. Many financial institutions have a regulatory requirement to record every change to data that appears in the data warehouse so they know how a report looked like at any point in time. A simple but effective solution to this requirement is to create a set of history tables which have exactly the same structure as the data warehouse dimension or fact table but with an extra column which records the date the data was changed (i.e. the effective date). An update trigger is then created on the source table which populates the history table every time the source table is updated. In this situation two new database objects need to be created for each source table – a history table and a matching update trigger. Of course, these two objects can be created manually, but imagine doing that for 200 source tables; that is 400 objects you need to code by hand! This is where T4 Templates come in. Using a T4 Template containing only a few lines of code, you can easily generate all the history tables or update triggers which are automatically added to your SSDT project as a SQL file. Even better, when you add another source table, you simply run the T4 Template again and it will generate the matching history table and update trigger. This is precisely the scenario I have implemented in the code example provided with this blog.
There are three ways you could implement T4 Templates:
The problem with the first two approaches is that the schema is out-of-date, albeit by minutes. You want to be dealing with the code you are developing now. This is why I chose the final method for coding up my T4 Templates provided alongside this post. It gives you access to the in-memory model of your database, not some old out-of-date copy.
The following screenshot of a SSDT solution shows that each T4 Template consists of two objects. The T4 Template itself which has a .tt suffix and the corresponding generated .sql code file which is presented below. The .sql code file generated by the T4 Template becomes part of the solution and will be compiled into your DACPAC when you build. During the build process, the .tt file is ignored – only the generated .sql code file is compiled.
In the example solution, I have placed all the T4 Templates within one folder. The structure is entirely up to you. However, when referencing include files, keeping everything in one folder makes life much easier.
So, for each of the 26 dimension and fact tables in the Adventure Works DW database, as per the requirements outlined above, I want to create a set of history tables and matching update triggers using a single T4 Template. So let’s review the code in the HistoryTables.tt T4 Template file.
As you can see, the T4 Template mostly consists of T-SQL code with which you are very familiar. The code between the angle brackets is C# code handling the iteration over the database model and returning the names of the objects found. It is a bit like old ASP or modern day BIML. Let’s look at each line of code to see what they are doing.
This is a T4 include directive which includes the InMemoryDatabaseModelInclude.tt file which holds the library of common functions I have written which make working with the Data-tier Application Framework (DACFx) much easier.
The first thing we do is get a handle on the in-memory database model (i.e. the content of your SSDT project) by calling GetInMemoryDatabaseModel() which is one of the functions I have written as part of the common function library in the include file. This line obtains a reference to the in-memory database model (i.e. the content of your SSDT project) which is held by an instance of the TSqlModel class which is part of the Data-tier Application Framework (DACFx). Note that you should always use a using statement when dealing with a DACFx TSqlModel class as this will automatically dispose of the model and release the resources used once your template has run.
The call to GetAllTablesInSchemaWithPrefixes() requests all the tables within the dbo schema which have either the prefix Fact or Dim. In the case of the AdventureWorksDW database, this function returns all 24 dimension and fact tables.
This is the first line of T-SQL where we dynamically create the name of the new table based on the name of the source table, but in the History schema.
Next we iterate through the list of columns in the source table which we obtain by a call to the DACFx function GetReferenced() and we then add them into the new table definition by calling the GetColumnDefinition() function which returns a string containing the T-SQL for the column definition.
We finish our T-SQL statement by adding a standard column to the table which records when the history table record was written and completing the CREATE TABLE statement by providing a FILEGROUP name upon which all the new history tables will be stored. And that is it! Pretty straight forward and easy to follow. But that is mostly because I have done all the hard work in the InMemoryDatabaseModelInclude.tt include file which contains my set of common functions which hide the complexity of DACFx. So when we save our new template or select Run Custom Tool from the Solution Explorer window, the HistoryTables T4 Template will generate a .sql file containing all the history tables. For example, here is part of the generated file:
Rather than provide the code sample as part of this post, I have published the code on CodePlex so that I can document and extend the library of functions properly. So download the sample code from here: T4 Template to auto-generate T-SQL from SSDT database model using DacFx. To use these samples, you will need Visual Studio 2013 which has SQL Server Data Tools built-in, or Visual Studio 2012 with the latest version of SSDT which can be downloaded from here.
By default, T4 Templates are treated by Visual Studio as normal text files, with no syntax highlighting. If you don’t like this black and white world, you can download one of the many free editors. Two I have tried are:
However, be warned that both editors provide erratic syntax highlighting when the syntax is fundamentally wrong, so if your world goes black and white, check your syntax!
To add a T4 Template to a SSDT solution, select a location within your solution within Solution Explorer in simply click Add Item and select Text Template under Visual Studio Templates.
As you can see in the above dialog, you have two Text Template options. The first gives you pretty much a blank screen which is the best option to select when using my include file. The second option provides a sample T4 Template which uses the content of the DACPAC generated by SSDT on your last build (i.e. option 2 of three ways you could implement T4 Templates listed above). In either case, the first thing you need to do is delete everything and add the following to your template:
Certainly I would recommend having a play with the standard template, but unless you are a strong C# programmer, you will soon get lost in the DacFx model as it is loosely typed and does not give you any guidance as to whether the object is a Table, View, Index, Tigger or anything else as they’re all represented by the one TSqlObject class. Ultimately this makes it hard to navigate the database model in a natural way. In particular I struggled for hours before I figured out how to obtain the details of the foreign key relationships for a table; and even then some of the information is missing (i.e. the name of the source column in the relationship).
By default, T4 templates do not get regenerated the code prior to a build. Microsoft have documented that auto-regeneration can be configured on their page outlining Design-Time Code Generation by using T4 Text Templates which requires the installation of the Modeling SDK for Microsoft Visual Studio 2013 to work. However, in my experience the transformation fails with the following error:
------ Build started: Project: AdventureWorksDW2014, Configuration: Debug Any CPU ------
Transforming template T4_Templates\DACPAC_Example.tt...
C:\Program Files (x86)\MSBuild\Microsoft\VisualStudio\v12.0\TextTemplating\Microsoft.TextTemplating.targets(396,5): Error: : A processor named 'SqlModelDirectiveProcessor' could not be found for the directive named 'SqlModelDirective'. The transformation will not be run. . Line=3, Column=4
Done building project "AdventureWorksDW2014.sqlproj" -- FAILED.
Build FAILED.
========== Build: 0 succeeded or up-to-date, 1 failed, 0 skipped ==========
Searching the forums, I find evidence to suggest that T4 templates using DacFx will not run from the command-line as there is some unique code run which the directive processor requires to access the database model. The same problem seems to occur when you select Transform All T4 Templates from the Build menu in Visual Studio. However, this method claims all is well, yet does not actually transform the templates.
Although SQL Server Data Tools (SSDT) was originally released with SQL Server 2012, it also supports SQL Server versions down to SQL Server 2005, so if you are not already using SQL Server Data Tools, then I would strongly encourage you to do so. I key strength of the new tool is that it allows more effective use of source code control and makes it far easier to implement continuous integration as noted by Keith Schreiner in his blog Auto-deploy and version your SQL Server database with SSDT.
Upgrading is straight forward and lots of ‘project’ artefacts can be removed as outlined here. Given it supports all SQL Server versions back to 2005, moving to the latest desktop development tools is much easier than transitioning your servers and ultimately will allow you to upgrade your code base and servers more efficiently when the time comes. Note that SQL Server Data Tools (SSDT) is built into Visual Studio 2013, so no separate install required. However, always ensure you have the latest version of SQL Server Data Tools for Visual Studio 2012.
Here are a few interesting resources out there which you will find useful when using T4 Templates to generate T-SQL code.
This blog entry forms part of the backdrop for my presentation at SQL Saturday Cambridge on 12th September 2015 where I will be talking about continuous integration and the data warehouse. Of course, T4 templates will be covered amongst numerous other techniques.
| Комментировать | « Пред. запись — К дневнику — След. запись » | Страницы: [1] [Новые] |
