Tag Archives: SSDT

New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

With the General Availability (GA) release of SSDT 17.0, the modern Get Data experience in Analysis Service Tabular projects comes with several exciting improvements, including DirectQuery support (see the blog article “Introducing DirectQuery Support for Tabular 1400”), additional data sources (particularly file-based), and support for data access options that control how the mashup engine handles privacy levels, redirects, and null values. Moreover, the GA release coincides with the CTP 2.0 release of SQL Server 2017, so the modern Get Data experience benefits from significant performance improvements when importing data. Thanks to the tireless effort of the Mashup engine team, data import performance over structured data sources is now at par with legacy provider data sources. Internal testing shows that importing data from a SQL Server database through the Mashup engine is in fact faster than importing the same data by using SQL Server Native Client directly!

Last month, the blog article “What makes a Data Source a Data Source?” previewed context expressions for structured data sources—and the file-based data sources that SSDT Tabular 17.0 GA adds to the portfolio of available data sources make use of context expressions to define a generic file-based source as an Access Database, an Excel workbook, or as a CSV, XML, or JSON file. The following screenshot shows a structured data source with a context expression that SSDT Tabular created for importing an XML file.

XMLFileImport 1024x561 New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

Note that file-based data sources are still a work in progress. Specifically, the Navigator window that Power BI Desktop shows for importing multiple tables from a source is not yet enabled so you end up immediately in the Query Editor in SSDT. This is not ideal because it makes it hard to import multiple tables. A forthcoming SSDT release is going to address this issue. Also, when trying to import from an Access database, note that SSDT Tabular in Integrated Workspace mode would require both the 32-bit and 64-bit ACE provider, but both cannot be installed on the same computer. This issue requires you to use a remote workspace server running SQL Server 2017 CTP 2.0, so that you can install the 32-bit driver on the SSDT workstation and the 64-bit driver on the server running Analysis Services CTP 2.0.

Keep in mind that SSDT Tabular 17.0 GA uses the Analysis Services CTP 2.0 database schema for Tabular 1400 models. This schema is incompatible with CTPs of SQL vNext Analysis Services. You cannot open Tabular 1400 models with previous schemas and you cannot deploy Tabular 1400 models with a CTP 2.0 database schema to a server running a previous CTP version.

Another great data source that you can find for the first time in SSDT Tabular is Azure Blob Storage, which will be particularly interesting when Azure Analysis Services provides support for the 1400 compatibility level. When connecting to Azure Blob Storage, make sure you provide the account name or URL without any containers in the data source definition, such as https://myblobdata.blob.core.windows.net. If you appended a container name to the URL, SSDT Tabular would fail to generate the full set of data source settings. Instead, select the desired contain in the Navigator window, as illustrated in the following screenshot.

AzureBlobs 1024x663 New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

As mentioned above, SSDT Tabular 17.0 GA uses the Analysis Services CTP 2.0 database schema for Tabular 1400 models. This database schema is more complete than any previous schema version. Specifically, you can find additional Data Access Options in the Properties window when selecting the Model.bim file in Solution Explorer (see the following screenshot). These data access options correspond to those options in Power BI Desktop that are applicable to Tabular 1400 models hosted on an Analysis Services server, including:

  • Enable Fast Combine (default is false)   When set to true, the mashup engine will ignore data source privacy levels when combining data.  
  • Enable Legacy Redirects (default is false)  When set to true, the mashup engine will follow HTTP redirects that are potentially insecure (for example, a redirect from an HTTPS to an HTTP URI).  
  • Return Error Values as Null (default is false)  When set to true, cell level errors will be returned as null. When false, an exception will be raised if a cell contains an error.  

DataAccessOptions 1024x725 New Get Data Capabilities in the GA Release of SSDT Tabular 17.0 (April 2017)

And especially with the Enable Fast Combine setting you can now begin to refer to multiple data sources in a single source query.

Yet another great feature that is now available to you in SSDT Tabular is the Add Column from Example capability introduced with the April 2017 Update of Power BI Desktop. For details, refer to the article “Add a column from an example in Power BI Desktop.” The steps are practically identical. Add Column from Example is a great illustration of how the close collaboration and teamwork between the AS engine, Mashup engine, Power BI Desktop, and SSDT Tabular teams is compounding the value delivered to our customers.

Looking ahead, apart from tying up loose ends, such as the Navigator dialog for file-based sources, there is still a sizeable list of data sources we are going to add in further SSDT releases. Named expressions discussed in this blog article a while ago also still need to find their way into SSDT Tabular, and there are other things such as support for the full set of impersonation options that Analysis Services provides for data sources that can use Windows authentication. Currently, only service account and explicit Windows credentials can be used. Forthcoming impersonation options include current user and unattended accounts.

In short, the work to enable the modern Get Data experience in SSDT Tabular is not yet finished. Even though SSDT Tabular 17.0 GA is fully supported in production environments, Tabular 1400 is still evolving. The database schema is considered complete with CTP 2.0, but minor changes might still be coming. So please be invited to deploy SSDT Tabular 17.0 GA, use it to work with your Tabular 1200 models and take Tabular 1400 for a thorough test drive. And as always, please send us your feedback and suggestions by using ProBIToolsFeedback or SSASPrev at Microsoft.com. Or use any other available communication channels such as UserVoice or MSDN forums. Influence the evolution of the Analysis Services connectivity stack to the benefit of all our customers!

Let’s block ads! (Why?)

Analysis Services Team Blog

Introducing a DAX Editor Tool Window for SSDT Tabular

The April 2017 release of SSDT Tabular for Visual Studio 2015 and 2017 comes with a DAX editor tool window that can be considered a complement to or replacement for the formula bar. You can find it on the View menu under Other Windows, and then select DAX Editor, as the following screenshot illustrates. You can dock this tool window anywhere in Visual Studio. If you select a measure in the Measure Grid, DAX Editor lets you edit the formula conveniently. You can also right-click on a measure in Tabular Model Explorer and select Edit Formula. Authoring new measures is as easy as typing a new formula in DAX Editor and clicking Apply. Of course, DAX Editor also lets you edit the expressions for calculated columns.

DAX Editor 1024x559 Introducing a DAX Editor Tool Window for SSDT Tabular

SSDT Tabular also displays the DAX Editor when defining Detail Rows expressions, which is an improvement over previous releases of SSDT Tabular that merely let you paste an expression into the corresponding textbox in the Properties windows, as the following screenshot illustrates. When working with measures, calculated columns, and the detail rows expression properties, note that there is only one DAX Editor tool window instance, so the DAX Editor switches to the expression you currently want to edit.

DetailRowsDaxEditor Big 1024x506 Introducing a DAX Editor Tool Window for SSDT Tabular

The DAX Editor tool window is a continuous improvement project. We have plans to include features such as code formatting and additional IntelliSense capabilities. Of course, we are also looking forward to hearing from you. So please send us your feedback and suggestions via ProBIToolsFeedback or SSASPrev at Microsoft.com, and report any issues you encounter. Or use any other available communication channels such as UserVoice or MSDN forums. You can influence the evolution of SSDT Tabular to the benefit of all our customers.

Let’s block ads! (Why?)

Analysis Services Team Blog

Improving the Measure Grid in SSDT Tabular

As the name implies, the measure grid is an SSDT Tabular feature to define and manage measures, as illustrated in the following screenshot. It is available for each table when you work in Data View in Tabular Model Designer. You can toggle it on and off by using the Show Measure Grid option on the Table menu.

oldmg Improving the Measure Grid in SSDT Tabular

The measure grid is not without shortcomings and receives a fair share of customer feedback. Among other things, drag-and-drop or copy-and-paste operations are currently not supported. It is also hard to locate the measure you want if your table has many measures because the grid does not sort the measures alphabetically and clips their names if the cell size is too small, which it usually is. You can increase the cell widths, but that also increases the widths of the table columns above, which is not great either. You can see the effect in the previous screenshot.

Tabular Model Explorer (TME), introduced with the August release of SSDT, helps to alleviate some of these shortcomings because TME displays all metadata objects in a sortable treeview, including measures and KPIs. We are also planning to add drag-and-drop as well as copy-and-paste operations in a future release. The measure grid, on the other hand, might not see the same improvements because we are considering to replace it in the midterm. In the meantime, however, we do want to address your valuable feedback. So, the October release of SSDT Tabular includes some very targeted improvements to deliver a more user-friendly measure grid experience. Check out the screenshot below. As you can see, the grid now adjusts the cell height and cell width automatically to avoid clipping the measure names, thus making it easier to navigate through the measures without affecting the widths of the table columns above too much.

newmg 1024x707 Improving the Measure Grid in SSDT Tabular

Of course, this is only a small improvement, but the big question is if you’d like us to continue improving the measure grid or if you’d rather want us to replace it with a completely different and hopefully better alternative? Please don’t hesitate to let us know. We want to guide our investments in SSDT Tabular based on what will help you be the most productive and help you deliver great solutions to your customers. While it will take some time to deliver on all of the feedback and feature requests, we will make updates each month and work against the backlog in the order of priority based on your input. Your feedback is essential for making SSDT Tabular better — be it for new features, existing features, or entirely missing capabilities. So send us your suggestions on UserVoice or MSDN forums and influence the evolution of SSDT Tabular to the benefit of all our customers. Thank you in advance for taking the time to provide input and stay tuned for the upcoming monthly releases of SSDT Tabular with even more exciting improvements!

Let’s block ads! (Why?)

Analysis Services and PowerPivot Team Blog

Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

When you work with the model designer in SSDT Tabular, you are working with a temporary Analysis Services database that SSDT Tabular automatically loads on a workspace server. During the initial project creation, you must point SSDT Tabular to the desired workspace server, which must be a tabular Analysis Services instance that you can control with full permissions of a SSAS Administrator. In a typical configuration, you would deploy a workspace server on the local computer running SSDT Tabular. Yet, having to run SQL Server Setup in order to deploy a full SSAS instance in Tabular mode just for development purposes is burdensome. Now there is a better way—Integrated workspace mode!
The following screenshot shows the Tabular Model Designer dialog box displayed when creating a new tabular project by using the SSDT September release. Note the Integrated Workspace option. If you select it, SSDT Tabular no longer requires an explicit workspace server. Instead, it relies on its own internal Analysis Services instance.
integratedworkspace Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

In integrated workspace mode, SSDT Tabular dynamically starts its internal SSAS instance in the background and loads the database so that you can add and view tables, columns, and data in the model designer, and if you add additional tables, columns, relationships, and so forth, you are automatically modifying the workspace database, as well. Integrated workspace mode does not change how SSDT Tabular works with a workspace server and  database. What changes is where SSDT Tabular hosts the workspace database.

For existing tabular projects that currently use an explicit workspace server, you can switch to integrated workspace mode by setting the Integrated Workspace Mode parameter to True in the Properties window, which is displayed when you select the Model.bim file in Solution Explorer, as highlighted in the following screenshot. Note that the Integrated Workspace Mode option does not let you configure any other workspace settings. SSDT Tabular uses default values for Workspace Database, Workspace Retention, and Workspace Server.

connecting to workspace 1024x644 Introducing Integrated Workspace Mode for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

The Workspace Database and Workspace Server settings let you discover the name of the temporary database and the TCP port of the internal SSAS instance where SSDT Tabular hosts this database. By using this information, you can connect to the workspace database with SSMS or any other tool—as long as SSDT Tabular has the database loaded. The Workspace Retention setting, on the other hand, specifies that SSDT Tabular keeps the workspace database on disk, but no longer in memory after a model project is closed. This ensures a good user experience while consuming less memory than if the model was kept in memory at all times. If you want to control these settings, set the Integrated Workspace Mode property to False and then provide an explicit workspace server. An explicit workspace server would also make sense if the data you are importing into a model exceeds the memory capacity of your SSDT workstation. You can continue to use your existing workspace server and this is still fully supported.
The integrated workspace server is basically equivalent to the Developer edition of SQL Server Analysis Services, so you can try out advanced features, such as DirectQuery mode, which are typically only available with the Enterprise edition. Also note that SSDT Tabular will ship the latest version of the Analysis Services engine with every monthly release, so you automatically get the latest updates and new capabilities. However, keep in mind that the ultimate deployment target in your production environment must support the capabilities you use in the model. For example, if your production server runs Standard edition, then you will not be able to deploy a model that uses Enterprise-only features. If you use integrated workspace mode, make sure you test the deployment on a reference server early on to ensure your model is compatible with your production servers.

Another aspect worth mentioning is that the integrated workspace server is a 64-bit Analysis Services instance, while SSDT Tabular runs in a 32-bit environment of Visual Studio. Hence, if you are connecting to special data sources, make sure you install both the 32-bit and 64-bit versions of the corresponding data providers on your workstation. The 64-bit provider is required for the 64-bit Analysis Services instance and the 32-bit version is required for the Table Import Wizard in SSDT Tabular.

And that’s it for a brief introduction of Integrated Workspace Mode. We hope you find this new capability useful. Give it a try, send us your feedback through Microsoft Connect, community forums, or as comments to this blog post, and let us know what other capabilities you would like us to add to SSDT Tabular. As always, stay tuned for even more improvements in upcoming monthly SSDT releases.

Let’s block ads! (Why?)

Analysis Services and PowerPivot Team Blog

Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

If you download and install the August 2016 release of SQL Server Data Tools (SSDT) for Visual Studio 2015, you can find a new feature in SSAS Tabular projects, called Tabular Model Explorer, which lets you conveniently navigate through the various metadata objects in a model, such as data sources, tables, measures, and relationships. It is implemented as a separate tools window that you can display by opening the View menu in Visual Studio, pointing to Other Windows, and then clicking Tabular Model Explorer. The Tabular Model Explorer appears by default in the Solution Explorer area on a separate tab, as illustrated in the following screenshot.

TME Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

As you no doubt will notice, Tabular Model Explorer organizes the metadata objects in a tree structure that closely resembles the schema of a tabular 1200 model. Data Sources, Perspectives, Relationships, Roles, Tables, and Translations correspond to top-level schema objects. But there are also exceptions, specifically for KPIs and Measures, which technically aren’t top-level objects yet child objects of the various tables in the model. However, having consolidated top-level containers for all KPIs and Measures makes it easier to work with these objects, especially if your model includes a very large number of tables. Of course, the measures are also listed under their corresponding parent tables, so that you have a clear view of the actual parent-child relationships. And if you select a measure in the top-level Measures container, the same measure is also selected in the child collection under its table—and vice versa. Boldface font calls out the selected object, as the following side-by-side screenshots illustrate for selecting a measure at the top level (left) versus the table level (right).

measuresselected Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

As you would expect, the various nodes in Tabular Model Explorer are linked to appropriate menu options that until now were hiding under the Model, Table, and Column menus in Visual Studio. It no doubt is easier to edit a data source by right-clicking on its object in Tabular Model Explorer and clicking Edit Data Source versus opening the Model menu, clicking on Existing Connections, and then selecting the desired connection in the Existing Connections dialog box, and then clicking Edit. This is great, even though not all treeview nodes have a context menu yet. Namely the top-level KPIs and Measures containers don’t yet have a menu while the Perspectives container does but its child objects do not. We will add further options in subsequent releases, including completely new commands that now make perfect sense in the context of an individual metadata object.

The same can be said for the Properties window. If you select a table, column, or measure in Tabular Model Explorer, SSDT populates the Properties window accordingly, but if you select a data source, relationship, or partition, SSDT does not and leaves the Properties window empty, as shown in the next screenshot comparison. This is simply because SSDT never had to populate the Properties window for the latter types of metadata objects before. Subsequent SSDT releases will provide more consistency and enable even more convenient editing scenarios through the Properties window. We just did not want to wait another one or two months with the initial Tabular Model Explorer release.

Properties window Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

The initial version already goes beyond what was previously possible in SSDT Tabular. For example, assume you have a very large number of measures in a model. Navigating through these measures in the Measure Grid can be tedious, yet Tabular Model Explorer offers a convenient search feature. Just type in a portion of the name in the Search box and Tabular Model Explorer narrows down the treeview to the matches. Then select the measure object and SSDT also selects the measure in the Measure Grid for editing. It’s a start to say good bye to Measure Grid frustration!

TME search Introducing Tabular Model Explorer for SQL Server Data Tools for Analysis Services Tabular Projects (SSDT Tabular)

But wait, there is more! The Tables node and the Columns and Measures nodes under each table support sorting. The default is Alpha Sort, which lists the object alphabetically for easy navigation, but if you’d rather list the objects based on their actual order in the data model, just right-click the parent node and select Model Sort. In most cases, Alpha Sort is going to be more useful, but if you need Model Sort on other parent nodes as well, such as Hierarchies and Partitions, let us know and we’ll add it in a subsequent release.

Note also that the Tabular Model Explorer is only available for the tabular 1200 compatibility level or later. Models at compact level 1100 or 1103 are not supported because Tabular Model Explorer is based on the new Tabular Object Model (TOM).

And that’s about it for a whirlwind introduction of Tabular Model Explorer in SSDT Tabular. We hope you find this new feature useful, especially if your models are complex and contain a very large number of tables, columns, partitions, measures, and other metadata objects. Give it a try, send us your feedback through Microsoft Connect, community forums, or as comments to this blog post, and let us know what other capabilities you would like us to add. Import/export of selected objects? Drag and drop support? And stay tuned for even more capabilities coming to an SSDT Tabular workstation near you in the next monthly releases!

Let’s block ads! (Why?)

Analysis Services and PowerPivot Team Blog

Why You Should Use a SSDT Database Project For Your Data Warehouse

In this post, I will try to convince you that using SQL Server Data Tools (SSDT) Database Projects is a really good idea. Recently during a project I’ve been advocating that it indeed is worth the effort. Since I’m a BI architect, I’m framing this conversation around a data warehouse, but it certainly applies to any type of database. 

What is a Database Project in SQL Server Data Tools (SSDT)?

A data warehouse contains numerous database objects such as tables, views, stored procedures, functions, and so forth. We are very accustomed to using SSDT BI projects (formerly BIDS) for SSIS (Integration Services), SSAS (Analysis Services), and SSRS (Reporting Services). However, it’s a less common is using SSDT to store the DDL (data definition language) for database objects.

 Why You Should Use a SSDT Database Project For Your Data Warehouse

Below is an example of how you could structure your database project (am only showing a few tables and views in the screen shots for brevity). You don’t have to structure it this way, but in this project it’s sorted first by schema, then by object type (table, view, etc), then by object (table name and its DDL, etc).

 Why You Should Use a SSDT Database Project For Your Data Warehouse

The contents of items in the SSDT DB project are the ‘Create Table’ statements, ‘Create View’ statements, ‘Create Schema’ statements, and so forth. This is based upon “declarative database development” which focuses on the final state desired for an object. For instance, here’s the start for a DimDate view:

 Why You Should Use a SSDT Database Project For Your Data Warehouse

Since the DimDate view resides in the DW schema, the database project would do me the favor of generating an error if the DW schema didn’t already exist, like follows:

 Why You Should Use a SSDT Database Project For Your Data Warehouse

Team Foundation Server does integrate well with database projects (i.e., for storing scripts for database objects such as tables, views, and functions), Integration Services, Analysis Services, and Reporting Services.

There’s an online mode as well as offline mode; personally I always use the project-oriented offline mode.

Now that we know about the structure of what’s in a project, let’s talk next about how we manage changes, such as an alter to add a new column.

Managing Changes To Database Objects

The next big thing to know is that there’s a mechanism for managing DDL changes, for instance a new column or a change to a data type. Instead of putting an ‘Alter Table’ statement into the database project, instead you edit that original ‘Create Table’ statement which focuses on the final state that includes the new column.

Now let’s say you are ready to promote that new column to Dev, QA, or Production. Here’s where it gets fun. In the database project you can do a ‘Schema Comparison’ operation which will compare the DB objects between the project and the database. It will detect the difference and script out the necessary ‘Alter Table’ script to use in your deployment to Production.

 Why You Should Use a SSDT Database Project For Your Data Warehouse

The output above tells us there’s a data type difference between the project and the database for an address column. This helps us reconcile the differences, then we can generate a script which would have an Alter Table statement for the address column (though in the above case, the address is varchar(150) in the database which probably means the ETL developer widened the column but forgot to circle back to the database project – so there’s still a lot of judgment when comparing the project to Dev).

Let’s take this one step further. When we’re ready to promote to a new environment, we can do a schema comparison between Dev and QA, or QA and Prod, and generate a script that’ll contain all of the Creates and Alters that we need to sync up the environments. If you are envisioning how handy this is for deployment purposes, then I’ve already accomplished my mission. (Keep reading anyway though!)

There’s a lot more to know about using schema compare, but let’s move next to the benefits of using an SSDT database project.

Benefits of Using a Database Project in SQL Server Data Tools (SSDT)

DB projects serve the following benefits:

  • Easy availability to DDL for all objects (tables, views, stored procedures, functions, etc) without having to script them out from the server and/or restore a backup. (See additional benefits in the next list if you also integrate with source control, which is highly recommended.)
  • Functionality to script out schema comparison differences for the purpose of deployment between servers. If you’ve ever migrated an SSIS package change and then it errored because you forgot to deploy the corresponding table change, then you’ll appreciate the schema comparison functionality (if you use it before all deployments that is).
  • Excellent place for documentation of a database which is easier to see than in extended properties. For example, recently I added a comment at the top of my table DDL that explains why there’s not a unique constraint in place for the table.
  • Provides a location for relevant DML (data manipulation language) statements as well, such as the unknown member rows for a dimension table. Note: DML statements do need to be excluded from the build though because the database project really only understands DDL.
  • Snapshot of DDL at a point in time. If you’d like, you can generate snapshot of the DDL as of a point in time, such as a major release. 

Additional benefits *if* you’re using a DB project also in conjunction with source control such as TFS:

  • Versioning of changes made over time, with the capability to quickly revert to a previous version if an error has occurred or to retrieve a deleted object. Useful comments should be mandatory for all developers who are checking in changes, which provides an excellent history of who, when, and why a change was made. Changes can also be optionally integrated into project management processes (ex: associating a work item from the project plan to the checked-in changeset).
  • Communicates to team (via check-outs) who is working on what actively which improves team effectiveness and potential impact on related database items.

Tips and Suggestions for Using a SSDT Database Project

Use Inline Syntax. To be really effective for table DDL, I think it really requires working -from- the DB project -to- the database which is a habit change if you’re used to working directly in SSMS (Management Studio). To be fair, I still work in SSMS all the time, but I have SSMS and SSDT both open at the same time and I don’t let SSDT get stale. The reason I think this is so important is related to inline syntax – if you end up wanting to generate DDL from SSMS in order to “catch up” your database project, it won’t always be as clean as you want. Take the following for instance:

 Why You Should Use a SSDT Database Project For Your Data Warehouse

In the above script I’ve got some default constraints (which are named because who wants the ugly DB-generated constraint names for our defaults and our foreign keys and such, right?!?). The constraints are all inline — nice and tidy to read. If you were to script out the table shown above from SSMS, it would generate Alter Table statements for each of the constraints. Except for very small tables, that becomes impossible to validate that the DDL is just how you want it to be. Therefore, I suggest using inline syntax so that your database project SQL statements are all clean and easy to read.

Store Relevant DML in the Project (Build = None).  If you have some DML (data manipulation language) statements that are manually maintained and need to get promoted to another environment, that makes them an excellent candidate for being stored in the DB project. Since the database project only understands DDL when it builds the project, the ‘Build’ property for each DML SQL script will need to be set to None in order to avoid errors. A few examples:

 Why You Should Use a SSDT Database Project For Your Data Warehouse

Build the Project Frequently. You’ll be unpopular with your coworkers if you check something in that doesn’t build. So you’ll want to develop the habit of doing a build frequently (around once a day if you’re actively changing DB objects), and always right after you check anything in. You can find the build options if you right-click the project in Solution Explorer. Sometimes you’ll want to choose Rebuild because then it’ll validate every object in the solution whether it changed or not (whereas the Build option only builds objects it detects changed, so although Rebuild takes longer it’s more thorough).

 Why You Should Use a SSDT Database Project For Your Data Warehouse

One more tip regarding the build – if a schema comparison operation thinks a table exists in the database but not in the project, check the build property. If it’s set to None for an actual DDL object, then it will accidentally be ignored in the schema comparison operation. Bottom line: set all DDL objects to build, and any relevant DML to not build.

Do a Schema Comparison Frequently. Regularly running a schema compare is a good habit to be in so that there isn’t a big cleanup effort right before it’s time to deploy to another environment. Let’s say I’m responsible for creating a new dimension. As soon as the SSIS package is done with the DDL for the table and views(s) as appropriate, I’ll do a schema compare to make sure I caught everything. If your team is a well-oiled machine, then if you do see something in the schema comparison between the project and the Dev DB, it should be something that you or a coworker is actively working on.

Save the Schema Comparison (SCMP) Settings in Your Project.  To make it quick and easy to use (and more likely your whole team will embrace using it), I like to save the schema comparison settings right in the project. You can have various SCMPs saved: Project to Dev DB, Dev DB to QA DB, QA DB to Prod DB, and so forth. It’s a big time-saver because you’ll want to tell the schema compare to ignore things like users, permissions, and roles because they almost always differ between environments. By saving the SCMP you can avoid the tedious un-checking of those items every single time you generate the comparison.

SSDT schemacompare2 Why You Should Use a SSDT Database Project For Your Data Warehouse

Do a ‘Generate Script’ for the Schema Comparison; Don’t Routinely Use the Update Button. Just to the right of the Update button (which I wish were less prominent) is the Generate Script button. This will create the relevant Create and Alter statements that it detects are necessary based on the differences found. Scripting it out allows you to validate the script before it’s executed, and to save the history of exactly what changes are being deployed when (assuming it’s being done manually & you’re not doing automated continuous deployments to Prod). I also prefer to generate the script over letting SSDT do a straight publish because items that are checked out are still part of a publish and we don’t usually want that (though it does depend on how you handle your source control branching). 

 Why You Should Use a SSDT Database Project For Your Data Warehouse

While we’re on the subject of the scripts generated by the DB project: a couple of things to know. First, you’ll need to run the script in SQLCMD mode (in SSMS, it’s found on the Query menu). Second, the scripts are not always perfect. For simple changes, they work really well, but sometimes things get complicated and you need to ‘manhandle’ them. For instance, there might be data in a table and the script has a check statement in the beginning that prevents any changes and might need to be removed or handled differently.

Separate Installation for SSDT vs SSDT-BI Prior to SQL Server 2016. If you go to create a new project in SSDT and you don’t see SQL Server Database Project as an option, that means you don’t have the right ‘flavor’ of SSDT installed yet. Thankfully the tools are being unified in SQL Server 2016, but prior to that you’ll need to do a separate installation. The SSDT installation files for Visual Studio 2013 can be found here: https://msdn.microsoft.com/en-us/library/mt204009.aspx.

There’s a lot more to know about DB projects in SSDT, but I’ll wrap up with this intro. There is a learning curve and some habit changes, but hopefully I’ve encouraged you to give database projects a try.

Finding More Information

Jamie Thomson’s blog – 10 Days of SSDT

Jamie Thomson’s blog – Considerations When Starting a New SSDT Project

MSDN – Project-Oriented Offline Database Development

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Blog – SQL Chick

SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI)

Yesterday we released SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI) for SQL Server 2014.  With this release you can use SSDT BI in Visual Studio 2013 and it includes the Visual Studio Project Templates for Analysis Services, Integration Services, and Reporting Services.  The project templates in this release generally work with down level versions of the server (with the exception of SSIS).  See the image below for details. 

2548.compat SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI)

We have also retired the CTP version that was based on VS 2012.

SSDT-BI for Visual Studio 2013 has been re-released in English. It can be downloaded from: http://www.microsoft.com/en-us/download/details.aspx?id=42313.

Any chance of turning any of those red dots green?

I have SQL Server Express 2014 x64 installed on my machine. When I want to install the Business Intelligence SSDT package it wants to install a new x86 instance of SQL Server Express. Setup will stop with an error if I tell it to add the Features to the existing x64 SQL instance. Any ideas?

Is this only available as a 32bit Version; I can’t add the Features to a 64bit instance (Enterprise Edition)

I’m with Paul, what are the chances of compatibility with SQL Server 2012 SP1 for SSIS please?

After reviewing it I have to apologize for my question icon smile SQL Server Data Tools – Business Intelligence for Visual Studio 2013 (SSDT BI)

SSDT is only available in x86, which is fine. As it only consists of Shared Features, you simply have to select “Create new instance …” in the Installation wizard. The Features of course can’t be added to a 64bit instance as there’s no need to…

@Hassi: This also applies to your question.

SSIS SQL 2012 support, PLEASE!

SSDT-BI is only available in 32 bit because Visual Studio is only available in 32 bit.  It can work with 64 bit processes (as external out of proc dlls) but is limited to 32 bit because of VS.  

@ Paul.  The red dots are representative of the way that SSIS has worked for years.  They typically only support the current server.  However, the SSIS team has put the mechanism in place for this release that will enable them to support down level versions as well.  For this release it means those red dots stay the same.  However, future releases will have more green dots.  

So  if I have av Sql Server 2012 SSIS server, I can deploy but NOT run a SSDT BI project made in Visual Studio 2013? If so when will this be fixed?

I think the confusing thing here is that Books Online says “SQL Server 2014 Integration Services is unchanged from the previous release.”…yet for some reason we can’t use SSDT BI 2014 to create solutions for the “previous release” (2012). Anyone care to explain what’s going on?

This behavior has been there for many releases for SSIS, SSIS only allows you to deploy to the same version as you have installed. The fix has been made that going forward this is not an issue any more. I have passed this feedback on to the SSIS folks (we are the SSAS team). A better way to give feedback is to file a suggestion / bug on connect. connect.microsoft.com/sqlserver


“For this release it means those red dots stay the same.  However, future releases will have more green dots.”

Does that mean there will be more green dots for future releases of SSDT-BI 2014 for VS 2013, or are you referring to future major updates of SQL Server (for example from 2012 to 2014)? In other words, is there a chance SSDT-BI 2014 for VS 2013 might be compatible with SQL Server 2012 SSIS in the future?

How come SQL Server Database Template is not included in this? How do i install it?

Wow – that was pretty quick! I won’t ask what branch it came from :). Congrats!

I’ve actually been holding back moving our data team off of VS 2008 hoping their SSIS would work for this.  Looks like we’re stuck on VS 2012 though unless there is a planned patch to include SSIS support for SQL 2012?  We just upgraded to SQL 2012 so SQL 2014 is probably a few years away if not skipped for the latest at that time.

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Analysis Services and PowerPivot Team Blog