可以远程处理SSAS多维数据集遍历脚本吗？ [英] Is it possibile to remotely process an SSAS cube throgh script?

问题描述

我有一个 SQL Server Analysis Service （ SSAS ）多维数据集（由 BIDS 2012 开发），我想借此机会用户（通过 PowerPivot 使用多维数据集）来处理其本地计算机中的多维数据集。

I have an SQL Server Analysis Service (SSAS) cube (developed with BIDS 2012) and I would like to give the opportunity to the users (that use cube through PowerPivot) to process the cube in their local machines.

我发现了一些有关如何进行计划的材料通过Powershell或SQL Agent或SSIS在服务器上完成作业，但没有远程处理多维数据集的资料。有什么建议吗？

I found some material on how to make a scheduled job on the server through Powershell or SQL Agent or SSIS but no material on remotely process the cube. Any advice?

推荐答案

触发多维数据集处理有多种可能性。低级方法是向包含多维数据集的数据库发出XMLA语句。若要查看外观，请打开SQL Server Management Studio，连接到AS实例，右键单击AS数据库，然后选择进程。配置处理设置，但不要单击确定，而是从顶部工具栏中选择脚本以为您生成XMLA处理命令。使对话框保留取消。
处理多维数据集的所有方法都以某种方式结束或

There are several possibilities to trigger a cube processing. The low level method is issuing an XMLA statement to the database containing the cube. To see how this looks like, open SQL Server Management Studio, connect to the AS instance, right-click on an AS database, and select "Process". Configure the processing settings, but instead of hitting OK, select "Script from the top toolbar to have the XMLA process command be generated for you. Leave the dialog with Cancel. All methods that process a cube end in some way or the other in sending a command like this to the AS database.

有多种选项可触发多维数据集处理：

There are several options to trigger a cube processing:

• 在Management Studio中，通过在上述对话框中单击确定。


• 在PowerShell中（请参阅 http://technet.microsoft.com/en-us/library/hh510171.aspx ）。


• 在Integration Services中，有一个Analysis Services处理任务（ http： //msdn.microsoft.com/zh-cn/library/ms141779.aspx ）。


• 您可以设置SQL Server代理作业，作业步骤可以是直接执行XMLA步骤或Integration Services

• In Management Studio, by clicking OK in the above mentioned dialog.
• In PowerShell (see http://technet.microsoft.com/en-us/library/hh510171.aspx).
• In Integration Services, there is an Analysis Services processing task (http://msdn.microsoft.com/en-us/library/ms141779.aspx).
• You can set up a SQL Server Agent job, job steps could either be a direct XMLA step, or an Integration Services step containing the process task (among possibly other tasks).

但是，问题是如何通过以下方式访问上述设置：终端用户。当然，这里的一个重要问题是执行处理任务的用户需要具有处理多维数据集的权限。由于您可能不想直接授予此权限，因此在调用它的方式上使用一些模拟可能很有意义。对于Management Studio（据我所知，对于PowerShell），这不容易实现。

The question, however, is how the setups described above can be accessed by end users. An important issue here is of course that the user executing the process task needs to have the permission to process the cube. As you might not want to give this permission directly, it might make sense to use some impersonation on the way of calling it. With Management Studio - and as far as I am aware with PowerShell - this cannot easily be achieved.

集成服务和Agent作业提供了模拟的可能性。集成服务包由dtexec命令行工具（SQL Server客户端工具的一部分）执行，还有一个名为dtexecui的工具（在标准SQL Server客户端工具安装中可作为执行包实用程序使用），它使您可以使用一个对话框来配置所有设置，然后执行一个程序包，但是它还可以根据您的设置显示dtexec的命令行。

Integration services and Agent jobs offer the possibility of impersonations. Integration services packages are executed by the dtexec command line tool (part of the SQL Server client tools), there is also a tool called dtexecui (available as "Execute Package Utility" in a standard SQL Server client tool installation), which lets you use a dialog to configure all settings, and then execute a package, but it also can display the command line for dtexec, according to your settings.

并调用SQL Server代理程序作业，一个简单的界面就是存储过程（ http://msdn.microsoft。 com / en-us / library / ms187763.aspx ），尤其是 sp_start_job （请注意，这是异步的，您可以调用它，它开始工作并返回。）不等待作业完成再返回。）和 sp_help_jobactivity 询问工作状态，以及 sp_help_jobhistory 询问工作状态。

And to call a SQL Server Agent job, an easy interface are the stored procedures (http://msdn.microsoft.com/en-us/library/ms187763.aspx), especially sp_start_job (Note this is asynchronous, you call it, it starts the job and returns. It does not wait for the job to complete before returning.) and sp_help_jobactivity to ask for job status as well as sp_help_jobhistory for details of jobs that were running.

总的来说，我认为没有最终的解决方案，但是我提到了一些构建块，您可以根据自己的环境偏好来编写自己的解决方案。

All in all I think there is no final solution available, but I mentioned some building blocks that you could use to code your own solution, depending on the preferences in your environment.

这篇关于可以远程处理SSAS多维数据集遍历脚本吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！