SSMS Improvements for Analysis Services in the April 2017 Release

The April 2017 Release of SSMS for Analysis Services is the first release with support for the modern Get Data experience. This release also features additional capabilities for the DAX parser, which come in handy when authoring or fine-tuning queries in the DAX Query Window. Azure Analysis Services now also supports Multi-Factor Authentication (MFA) based on Active Directory Universal Authentication. So, don’t delay and download SQL Server Management Studio 17.0 today.

JSON-based data source definition

As far as the modern Get Data experience is concerned, you can now view, edit, and script out structured data sources, as the following screenshot illustrates. This comes in handy when you want to update data source information or credentials in a deployed Tabular 1400 model on-prem or in the cloud. Azure Analysis Services will release support for 1400 models very soon. You can develop with the Integrated workspace until this is available.

Note that you can edit the connection details, credentials, options, as well as the context expression for a data source in SSMS. The values for these parameters correspond to strings in JSON format, and they depend on the data source type. For example, a SQL Server data source requires different connection details than an OData feed. The documentation covering all these options is not yet available, but you can glean the settings from a Tabular 1400 model in SSDT. Just create a data source of the desired type, set the connection details and credentials as desired, and then analyze the JSON-based data source definition in the resulting Model.bim file.

 DAX Query Window

The DAX parser improvements, on the other hand, show up in the DAX Query Window. Among other things, the DAX Query Window now supports parentheses matching in IntelliSense, which facilitates examining how parentheses match up with each other in an expression. For example, if you select a closing parenthesis, the DAX Query Window will automatically highlight the corresponding opening parenthesis. Moreover, you can now enjoy support for DEFINE MEASURE and DEFINE VAR to define measures and named variable without distracting read squiggles. The following screenshot shows parentheses matching based on a simple DAX query with a named variable.

Active Directory Universal Authentication Finally, when connecting to Azure Analysis Services, you can choose Active Directory Universal Authentication as the authentication method, enter your user name, and then click Connect. SSMS then displays a tenant-specific sign-in page to provide the remaining credential information, such as a password, and complete any other MFA steps (see the following screenshot). If another user is already logged in previously, the sign-in dialog will give you a warning, asking you to log off as the previous user before signing in. After a successful login, SSMS caches the sign-in token in memory for future reconnects. Note that the in-memory token is not shared across processes. So a new or second instance of SSMS will not have the cached credentials. 

And last but not least, SQL Server Management Studio 17.0 is now released for general availability (GA), meaning it is fully supported in production environments. This GA release is also closely aligned with SQL Server vNext CTP 2.0. This is important to note because in CTP 2.0, Analysis Services is introducing some changes that break backwards compatibility for Tabular 1400 models. You will get errors if you connect with SSMS 17.0 GA to an Analysis Services servers running an earlier CTP. For example, you cannot access the database properties dialog for a Tabular 1400 model. To avoid such issues, make sure you update all your preview server deployments to SQL Server vNext CTP 2.0.