By all accounts, the introduction of SQL Server Data Tools (SSDT) in SQL Server 2012 was a watershed moment for many SQL Server developers. For better or for worse, SSDT as an IDE for business intelligence development changed – amongst other things – the way we deployed our SSIS packages (i.e. package vs project deployments), simplified Tabular Model development, and also introduced us to the SSISDB. Likewise the replacement of Business Intelligence Development Studio (BIDS) with SSDT had its detractors who were noticeably not very happy that in addition to installing SQL Server 2012 you still had to do a separate download and installation of BI templates for SSDT (previously, BI templates in BIDS were available as soon as you installed SQL Server 2005/2008). Although SSDT-BI is still being offered as a separate installation, subsequent releases of SSDT have included several enhancements changes that should go a long way to winning the hearts of its critics. In this article we conduct a comparative analysis of all versions (up until 16.5) of SSDT and identify all the major improvements that have been introduced in the BI templates.
SSDT for Visual Studio 2010
The focus of the initial release of SSDT was designed to replace BIDS as well as VSDB, as shown in Table 1.
As shown in Figure 1, the initial release of SSDT ran off a Visual Studio 2010 Shell and there were no major changes to the templates that were last released as part of BIDS 2008 except for the inclusion of Analysis Services Tabular Project template.
Although the initial release of SSDT contained almost similar templates to BIDS 2008, it did however provide several enhancements to those templates. For instance, for the first time ever we were introduced to the SSIS Toolbox window, and the SSIS (Integration Services) templates were enhanced to:
- Allow redo and undo of package changes
- In addition to variables, you could configure package parameters
- Define project-wide connections that would be visible to all packages within a given project
SSDT for Visual Studio 2012
SQL Server 2012 was released ahead of Visual Studio 2012 which explains why the initial release of SSDT ran off Visual Studio 2010. Later on, when Visual Studio 2012 was released, another version of SSDT that ran off Visual Studio 2012 was released. Except for changes in template icons shown in Figure 2, no new enhancements were done in the BI templates of SSDT 2012.
SSDT for Visual Studio 2013
A year after Visual Studio 2012 was released, Visual Studio 2013 was released yet at that time SQL Server 2012 was still the latest RTM version of SQL Server. Not surprisingly, as shown in Figure 3, the BI templates contained in SSDT 2013 were similar to its predecessor, SSDT 2012.
SSDT for Visual Studio 2015
The release of SQL Server 2016 introduced several enhancements relating to business intelligence templates, particularly the overwhelming changes in Reporting Services. As a result, the release of SSDT 2015 was expected to contain several enhancements to support SQL Server 2016 BI features.
1. SSAS Tabular Model Explorer
A noticeable improvement in the Analysis Services Tabular Project template was the inclusion of the Tabular Model Explorer, shown in Figure 4. Although, compared to multidimensional cubes, SSAS Tabular Models were easier to design and work with, configuration of the model.bim file was such that you had to navigate to different areas of Visual Studio to, i.e. identify total number of measures in the model, identify a list of existing configured data sources, etc. Fortunately, Tabular Model Explorer, reduces some of that navigation exercise by providing a collapsible tree structure of model.bim properties such as data sources, KPIs, list of tables, roles and measures.
Similarly to other Visual Studio windows such as Solution Explorer, Tabular Model Explorer has the following built-in functionality:
- Sort contents of the model – by default, the explorer is sorted alphabetically
- Search within the model
- Reposition the window i.e. float, dock, hide etc.
Finally, you can only use Tabular Model Explorer against SQL Server 2016 Tabular Models or higher. That, means if you change your solution compatibility level to a level lower than 1200 as shown in Figure 5, you will run into an error returned in Figure 6.
2. SSRS Adjustable Parameters Pane
Perhaps one of the frustrating issues with SSRS parameters was its inability to reposition them according to your preference. Luckily, SQL Server 2016 introduced the ability to customise the position of report parameters. SSDT 2015 introduces a parameters grid that can be used to adjust the position and appearance of report parameters, as shown in Figure 7.
The easiest way to access the parameters grid is to right click on the report body region, select the View option, and then click on Parameters.
3. SSIS Single Package Deployment & new Hadoop Tasks
In spite of the benefits, one limitation of project deployment in SSIS was that the Deploy option was only available at the project level. That meant even if you made changes to a single package in a project, you still had to redeploy the entire project. Luckily, SQL Server 2016 supports deployment of a single package and SSDT 2015 provides support for single package deployment through the Deploy Package option shown in Figure 8.
The Integration Services Project template in SSDT 2015 further includes components that can be used to configure tasks relating to Hadoop. The 3 Hadoop-related tasks are shown in Figure 9.
One of the improvements in SSDT 2015 was its support for Azure ETL development. As a result, Microsoft released a separate installation file, Azure Feature Pack for Integration Services (SSIS) that makes it possible for SSIS to connect, process and transfer data between Azure and on-premises data sources. A list of available Azure ETL tasks are shown in Figure 10.
We conclude the review of SSDT 2015 by mentioning another exciting feature introduced in the Integrations Services Project template, the Package Parts. Package Parts allows developers to setup package template that can be executed within a control flow area.
References
- How to use Package Parts to develop easily maintainable Data Warehouse solutions
- Previous releases of SQL Server Data Tools (SSDT and SSDT-BI)
- Business Intelligence Development Studio
- SQL Server Data Tools: A Work in Progress
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu