SQL 2016 Technical Deep Dive Video – Tooling |
|
Метки: SSDT Video Deep Dive Technical SQL Server 2016 Tooling VS SSMS |
SQL 2016 Technical Deep Dive Video – Tooling |
|
Метки: SSDT Video Deep Dive Technical SQL Server 2016 Tooling VS SSMS |
SQL 2016 Technical Deep Dive Video – Tooling |
|
Метки: SSDT Video Deep Dive Technical SQL Server 2016 Tooling VS SSMS |
SQL 2016 Technical Deep Dive Video – Summary of all Videos |
|
Метки: Video Deep Dive Technical SQL Server 2016 |
SQL 2016 Technical Deep Dive Video – Summary of all Videos |
|
Метки: Video Deep Dive Technical SQL Server 2016 |
SQL 2016 Technical Deep Dive Video – Summary of all Videos |
|
Метки: Video Deep Dive Technical SQL Server 2016 |
Fancy seeing inside of the SQL Server team |
|
|
Awesome series on SQL Server clustered column store (operational analytics) |
|
|
Are you prepared to be blown away |
http://sqlblogcasts.com/blogs/simons/archive/2016/03/08/are-you-prepared-to-be-blown-away.aspx
|
|
Managing multiple accounts in PowerBI |
http://sqlblogcasts.com/blogs/simons/archive/2016/03/08/managing-multiple-acocunts-in-powerbi.aspx
|
Метки: Power BI |
Its not April Fools Day–SQL will run on Linux |
|
|
Microsoft deliver on their promises, handsomely! |
At the SQL PASS Summit in October, James Phillips made a promise that everything he talked about or demonstrated in his keynote would be available by the end of the year. Well they have delivered, handsomely!
Today SQL Server 2016 CTP3.2 has been made available which delivers mobile reporting to SQL Server Reporting Services (SSRS). SSRS has been ignored by Microsoft for years as James Phillips acknowledges in his keynote, but today SSRS gets a complete makeover and lots of new features including the integration of the Datazen mobile technology (screenshot below). About time on-premise reporting got a makeover!
Report Manager’s new web portal also provides support for Key Performance Indicators (KPIs), which starts taking Report Manager into the realms of the dashboard arena.
The December update to Power BI desktop includes the ability to include R visualisations into your dashboards (screenshot below). R is an exciting development for BI professionals as it allows them to deliver practical tools to data scientists who are a demanding and extremely well educated community of end-users, very familiar with the tools and techniques required to big data analysis. SQL Server 2016 includes R services, so Microsoft are only a small step away from delivering an end-to-end R solution with both the massively parallel data querying to the visualisations. If you missed it, it is well worth watching Jason Wilcox PASS Summit presentation on The Future of Analytics.
Power BI’s SSAS multidimensional support just got better to, with the introduction of support for hierarchies. Power BI is really galvanising renewed interest in SSAS multidimensional as it is one of the best ways of implementing a user specific dashboard where different users use the same dashboard, but see their own data. I am working on this scenario at the moment for one client where different manages in the firm get to see their own team’s sales data and statistics. The infrastructure to support this scenario is the newly announced Power BI Enterprise Gateway and the existing Azure Active Directory which provides single sign-on (SSO) access to thousands of cloud SaaS Applications like Office365, Salesforce, DropBox and Google Apps.
Power BI desktop also gets tighter control of formatting and many enhancements to various visualisations. I love the new Format Painter! Take a look at the release video here.
Microsoft also announced the Data Insights Summit will take place in Bellevue, WA in March. Maybe worth a trip!
Right, time to install CTP3.2 and get cracking on that mobile BI demo
|
|
Continuous Integration and the Data Warehouse |
I am excited to say that over the next two weekends I am speaking at PASS SQL Saturday events in Southampton and Slovenia on Continuous Integration and the Data Warehouse. Over the coming weeks I hope to provide some supporting posts outlining exactly how to set up CI in the data warehouse context. However, I am taking a new contract next week, so my time may be sparse!
In my view, the key to implementing Continuous Integration is to move away from the old migration-based development of your SQL database to the declarative model approach offered by SQL Server Data Tools (SSDT). In my opinion, without this transition you will be fighting an up-hill battle!
I recently attended a SQL Supper session by Alex Yates of RedGate where he discussed the pros and cons of state-based declarative model versus migration-based database development strategies. Although he outlines the problems inherent in both approaches, he did not outline how SSDT and good source code management overcomes all these issues as his talk was tool-neutral. He is also speaking at Southampton, so please go along.
With the declarative model approach, problems typically arise when you:
I am sure there are a few other scenarios, but none immediately come to mine on this a cold November morning.
SQL Server Data Tools overcomes these issues by the following specific functionalities:
So please come along to one of my PASS SQL Saturday presentations or watch this space for more on Continuous Integration and the Data Warehouse
|
|
Another one bites the dust – cross database queries now possible in SQL Azure |
|
|
Using SQL Server on high resolution monitors |
|
|
SQL Saturday 467 - Precon - Query processing and internals |
Im not doing a tremendous amount of public speaking this year and concentrating on more professional matters, however im pleased to say that on Friday 5th December i will be presenting a day long pre-con on Query Processing and internals at SQL Saturday 467 in Southampton.
We will be spending the whole day looking at the optimizer and picking apart its internals to show how it generates a plan from your given sql. This will cover a wide range of topics, starting from execution plan basics and working ourselves through to demonstrating internals with undocumented traceflags.
Other precons are available if this doesn't float your boat. Full details, pricing and hotel discount codes are available from
http://www.sqlsaturday.com/467/eventhome.aspx
|
|
You are using Office 365 (Business) and you want Office 2016 this is what you need to do |
|
|
Upgraded to Excel 2016 and can’t find PowerView you aren’t alone |
|
|
What is Devops? The power of feedback |
http://sqlblogcasts.com/blogs/simons/archive/2015/07/15/what-is-devops-the-power-of-feedback.aspx
|
|
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.
|
Метки: C# T-SQL SQL Server 2012 T4 Templates |
