Introduction
In earlier versions, SSMS was included in the SQL Server installer. Now it is a tool that is installed separately.
In this new article, we will emphasize PowerShell and DAX. PowerShell is handled in a different way than it was in SQL Server 2016. We will give you some tips to handle these differences. In the second part of the article, we will talk about DAX. If you do not have experience with DAX, we will give you an introduction to Multidimensional, Tabular databases and you will be able to understand the new features and run your first DAX query in the new SSMS 17.
Requirements
- SSAS multidimensional database installed
- SQL Server 2016 Installed.
- SSMS 17 installed.
Let’s get started …
Where is PowerShell in SQL Server?
In older SQL Server versions, PowerShell was installed by default with SSMS. Now it is a separated feature.
When we try to install PowerShell, we noticed that it is not installed:
The message received is the following:
No SQL Server cmdlets found on this computer.
Get the ‘SqlServer’ module at powershellgallery.com
Start PowerShell without SQL Server cmdlets?
For help, click here
In the SSMS 17, you need to install the PowerShell module for SQL Server if you want to use it.
To install the PowerShell module for SQL Server you will need to run the following cmdlets in PowerShell:
|
1 |
Install-Module -Name SqlServer |
The command will install the SQL Server Module:
You can optionally include the version to download:
|
1 |
Install-Module -Name SqlServer -RequiredVersion 21.0.17152 |
It is a module installed separately because It is easier to update and maintain separated modules than a huge package.
The last package is downloaded from the PowerShell Gallery, that you can get from here.
Once installed, you will be able to access to PowerShell from the SSMS:
Note that the new PowerShell use colors for the words:
In order to test PowerShell, we will run the Invoke-sqlcmd to run the system procedure sp_who. This procedure will help you to find users, processes, and sessions:
To get the version of the SQL Server module, you can run the following cmdlets:
|
1 |
(Get-Module SqlServer).Version |
The result will be like the following:
“SqlServer” is the name of the module and Get-Module is used to get information about the module.
The Find-Module cmdlet will show you the last version of the module specified (in this example “sqlserver”) in the PowerShell Galley:
|
1 |
Find-Module -Name "sqlserver" |
You can download the last version in a local path:
|
1 |
Find-Module -Name "sqlserver" -Repository "PSGallery" | Save-Module -Path "C:\mysqlservermodule\" |
After running the cmdlets, you can check that the files were downloaded in the “mysqlservermodule” folder:
DAX in SSMS 17
In earlier versions, you only had icons to run SQL queries, MDX queries, DMX queries and XMLA queries:
SSMS 17 now incorporates the DAX option (and all the icons are nicer and new!):
If you do not know what MDX, DMX, XMLA and DAX is, we will give you a brief explanation.
SQL Server comes with Analysis Services, which is a database created to generate reports in a faster database designed to generate reports quickly (an Online Analytical Process Database).
The design, technology and aggregation was made to generate business information quickly and efficiently.
MDX was created to query multidimensional databases.
DMX is used to work and query Data Mining projects.
Data Mining is used to predict the future based on Multidimensional information or other sources like relational databases. It used DMX for queries.
XMLA is used for Data Definition Language (DDL) actions like creating an object (cube, dimensions), deleting, modifying or processing information (load data or load the structure).
DAX was introduced in Tabular Models. Many users complained that Multidimensional Databases were harder to understand and create. That is why Tabular Models appeared. They are more compatible with Excel and they use the DAX language. DAX is a simpler language easier than MDX and it looks like Excel functions. Now DAX can be used in Tabular and Multidimensional models and the language is becoming more popular.
In earlier versions, you could run DAX queries in MDX interfaces, but they looked like hidden features. They were documented, but it was not obvious that the MDX script supported DAX. This was because DAX is newer than MDX.
We will do a small demo about DAX in a Multidimensional Database using the new DAX options.
For this demo, you can download the Adventure Works DW 2014 Full Database Backup.zip backup and the Adventure Works Multidimensional Model SQL 2014 Full Database Backups.zip.
You need to restore the Adventure Works DW in the Database Engine and restore the multidimensional model.
To do that, connect to your SSAS Multidimensional database:
Unzip the Adventure Works Multidimensional Model SQL 2014 Full Database Backups.zip file and use the backup to restore your multidimensional backup:
When you right-click on your database, you now have the option to run a DAX new query:
DAX (and the MDX) is now Intellisense, which helps a lot in the query development:
If we press F5, we will be able to run the query. Evaluate is like a select in DAX. In this example, we will check the information of the Date dimension. It is like a select * from a table named Date:
You also have the option to Browse to run queries in the Adventure Works cube:
By default, you can drag and drop columns (measures, dimensions) to generate queries visually using MDX:
If you select the DAX option you will receive a warning message saying that your current MDX content will be lost:
You can drag and drop dimension to the query pane in DAX, but you cannot run queries visually like you can do in MDX. This may change in the future:
You can run a DAX query. In this example, we will run the Evaluate function.
At the time of this writing, you cannot visually create DAX queries by Dragging and dropping measures and dimensions like you do in MDX.
Conclusion
In this article, we showed how to download PowerShell for SQL Server to be incorporated with SSMS. We show some useful PowerShell cmdlets to get the last version of the SQL Server PowerShell module and how to download the module to a local path.
In the second part, we learned about the new DAX buttons and options. We ran some DAX queries in a multidimensional database.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023