ssms的导入功能_SSMS 17中的新增功能； PowerShell和DAX

ssms的导入功能

介绍 (Introduction)

In earlier versions, SSMS was included in the SQL Server installer. Now it is a tool that is installed separately.

在早期版本中，SSMS包含在SQL Server安装程序中。 现在，它是一个单独安装的工具。

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.

在这篇新文章中，我们将重点介绍PowerShell和DAX。 PowerShell的处理方式与SQL Server 2016中的处理方式不同。我们将为您提供一些技巧来处理这些差异。 在本文的第二部分，我们将讨论DAX。 如果您没有DAX的经验，我们将向您介绍多维表格数据库，您将能够了解新功能并在新的SSMS 17中运行第一个DAX查询。

要求 (Requirements)

1. SSAS multidimensional database installed 已安装SSAS多维数据库
2. SQL Server 2016 Installed.
   SQL Server 2016已安装。
3. SSMS 17 installed. 已安装SSMS 17。

Let’s get started …

让我们开始吧 …

SQL Server中的PowerShell在哪里？ (Where is PowerShell in SQL Server?)

In older SQL Server versions, PowerShell was installed by default with SSMS. Now it is a separated feature.

在较早SQL Server版本中，默认情况下，PowerShell与SSMS一起安装。 现在，它是一个单独的功能。

When we try to install PowerShell, we noticed that it is not installed:

当我们尝试安装PowerShell时，我们注意到它尚未安装：

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?

在此计算机上找不到SQL Server cmdlet。
在powershellgallery.com上获取“ SqlServer”模块
在没有SQL Server cmdlet的情况下启动PowerShell？

For help, click here

如需帮助，请点击 此处

In the SSMS 17, you need to install the PowerShell module for SQL Server if you want to use it.

在SSMS 17中，如果要使用它，则需要为SQL Server安装PowerShell模块。

To install the PowerShell module for SQL Server you will need to run the following cmdlets in PowerShell:

要为SQL Server安装PowerShell模块，您将需要在PowerShell中运行以下cmdlet：

Install-Module -Name SqlServer

The command will install the SQL Server Module:

该命令将安装SQL Server模块：

You can optionally include the version to download:

您可以选择包括要下载的版本：

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.

最后一个软件包是从PowerShell库下载的，您可以从此处获得。

Once installed, you will be able to access to PowerShell from the SSMS:

安装后，您将能够从SSMS访问PowerShell：

Note that the new PowerShell use colors for the words:

请注意，新的PowerShell使用颜色来表示单词：

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:

为了测试PowerShell，我们将运行Invoke-sqlcmd以运行系统过程sp_who。 此过程将帮助您查找用户，进程和会话：

To get the version of the SQL Server module, you can run the following cmdlets:

若要获取SQL Server模块的版本，可以运行以下cmdlet：

(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.

“ SqlServer”是模块的名称，Get-Module用于获取有关模块的信息。

The Find-Module cmdlet will show you the last version of the module specified (in this example “sqlserver”) in the PowerShell Galley:

Find-Module cmdlet将向您显示PowerShell Galley中指定的模块的最新版本（在本示例中为“ sqlserver”）：

Find-Module -Name "sqlserver"

You can download the last version in a local path:

您可以在本地路径中下载最新版本：

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:

运行cmdlet之后，可以检查文件是否已下载到“ mysqlservermodule”文件夹中：

SSMS中的DAX 17 (DAX in SSMS 17)

In earlier versions, you only had icons to run SQL queries, MDX queries, DMX queries and XMLA queries:

在早期版本中，只有图标可以运行SQL查询，MDX查询，DMX查询和XMLA查询：

SSMS 17 now incorporates the DAX option (and all the icons are nicer and new!):

现在，SSMS 17包含DAX选项（所有图标更加新颖！）：

If you do not know what MDX, DMX, XMLA and DAX is, we will give you a brief explanation.

如果您不知道什么是MDX，DMX，XMLA和DAX ，我们将为您简要说明。

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).

SQL Server附带有Analysis Services，这是一个用于在生成更快的报表（旨在快速生成报表）的数据库（在线分析过程数据库）中生成报表的数据库。

The design, technology and aggregation was made to generate business information quickly and efficiently.

进行设计，技术和聚合可快速高效地生成业务信息。

MDX was created to query multidimensional databases.

创建MDX来查询多维数据库。

DMX is used to work and query Data Mining projects.

DMX用于处理和查询数据挖掘项目。

Data Mining is used to predict the future based on Multidimensional information or other sources like relational databases. It used DMX for queries.

数据挖掘用于基于多维信息或关系数据库等其他来源来预测未来。 它使用DMX进行查询。

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).

XMLA用于数据定义语言（DDL）动作，例如创建对象（多维数据集，维度），删除，修改或处理信息（加载数据或加载结构）。

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.

DAX是在表格模型中引入的。 许多用户抱怨多维数据库难以理解和创建。 这就是为什么出现表格模型的原因。 它们与Excel更兼容，并且使用DAX语言。 DAX是比MDX更简单的语言，它看起来像Excel函数。 现在，DAX可用于表格和多维模型，并且该语言正变得越来越流行。

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.

在早期版本中，您可以在MDX界面中运行DAX查询，但是它们看起来像隐藏的功能。 他们被记录在案，但MDX脚本是否支持DAX并不明显。 这是因为DAX比MDX更新。

We will do a small demo about DAX in a Multidimensional Database using the new DAX options.

我们将使用新的DAX选项在多维数据库中进行有关DAX的小型演示。

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.

对于此演示，您可以下载Adventure Works DW 2014 Full Database Backup.zip备份和Adventure Works多维模型SQL 2014 Full Database Backups.zip 。

You need to restore the Adventure Works DW in the Database Engine and restore the multidimensional model.

您需要在数据库引擎中还原Adventure Works DW并还原多维模型。

To do that, connect to your SSAS Multidimensional database:

为此，请连接到SSAS多维数据库：

Unzip the Adventure Works Multidimensional Model SQL 2014 Full Database Backups.zip file and use the backup to restore your multidimensional backup:

解压缩Adventure Works多维模型SQL 2014完整数据库Backups.zip文件，并使用备份还原多维备份：

When you right-click on your database, you now have the option to run a DAX new query:

右键单击数据库后，现在可以选择运行DAX新查询：

DAX (and the MDX) is now Intellisense, which helps a lot in the query development:

DAX（和MDX）现在是Intellisense，它对查询开发有很大帮助：

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:

如果我们按F5，我们将能够运行查询。 评估就像DAX中的选择一样。 在此示例中，我们将检查“日期”维度的信息。 就像一个名为Date的表中的select *：

You also have the option to Browse to run queries in the Adventure Works cube:

您还可以选择浏览以在Adventure Works多维数据集中运行查询：

By default, you can drag and drop columns (measures, dimensions) to generate queries visually using MDX:

默认情况下，您可以拖放列（度量，维）以使用MDX直观地生成查询：

If you select the DAX option you will receive a warning message saying that your current MDX content will be lost:

如果选择DAX选项，您将收到一条警告消息，提示您当前的MDX内容将丢失：

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:

您可以将维度拖放到DAX的查询窗格中，但是不能像在MDX中那样直观地运行查询。 将来可能会改变：

You can run a DAX query. In this example, we will run the Evaluate function.

您可以运行DAX查询。 在此示例中，我们将运行评估功能。

At the time of this writing, you cannot visually create DAX queries by Dragging and dropping measures and dimensions like you do in MDX.

在撰写本文时，您无法像在MDX中一样通过拖放度量和维度来直观地创建DAX查询。

结论 (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.

在本文中，我们展示了如何下载将PowerShell与SQL Server集成在一起SQL Server。 我们展示了一些有用的PowerShell cmdlet，以获取最新版本SQL Server PowerShell模块以及如何将模块下载到本地路径。

In the second part, we learned about the new DAX buttons and options. We ran some DAX queries in a multidimensional database.

在第二部分中，我们了解了新的DAX按钮和选项。 我们在多维数据库中运行了一些DAX查询。

翻译自: https://www.sqlshack.com/whats-new-ssms-17-powershell-dax/

ssms的导入功能