在Excel中使用VBA，如何获取服务器上所有OLAP多维数据集的名称? [英] Using VBA in Excel, how do I get the names of all OLAP Cubes on a server?

问题描述

该公司为我们的每个客户创建一个OLAP多维数据集数据库.我使用多维数据集中的值来处理许多事情.每当设置新客户的多维数据集时，我都会向使用的工作簿中添加工作表，并使用该多维数据集创建数据透视表.

The company creates an OLAP Cube Database for each of our clients. I keep up with many things using values in the cubes. Whenever a new client's cube is set up, I add a sheet to the workbook I use, and create a pivot table using that cube.

我想检查服务器上是否有可能添加的任何多维数据集.

I want to checks the server for any cubes that may have been added.

我认为这样是最好的方法.

I figured something like this would be the best way.

For Each Cube in Server.Cubes
    MsgBox Cube.Name
Next Cube

我找不到任何东西.我搜索了几天的答案.有什么方法可以通过服务器查看可用的多维数据集来进行解析?

I cannot find anything of the sort. I searched for an answer for a couple of days now. Any ways to parse through the server looking at the available cubes?

推荐答案

SSAS服务器具有这些说明进行操作建立连接，或参见下文.

The SSAS Server has DMVs that you can query to determine the number of cubes on a server. Then you can use VBA to compare that to the number of rows in the table before. Follow these instructions to make the connection, or see below.

1. 在Excel中创建新连接:在获取外部数据"部分中，选择从其他来源-> SQL Server(NOT Analysis Services)".
2. 输入您可以连接到的任何SQL Server的连接信息(我们将在以后的步骤中更改此信息).
3. 选择您有权访问的任何数据库和表.在向导中移动，最后选择仅保存连接".
4. 单击连接".找到您的连接，然后单击属性"按钮.

5. 在定义"选项卡上，将连接字符串更新为类似的样子

1. Create a new connection in Excel: In the Get External Data section choose From Other Sources -> SQL Server (NOT Analysis Services).
2. Enter connection information for any SQL Server to which you can connect (we'll change this info in a later step).
3. Pick any database and table to which you have access. Move through the wizard and choose Only save connection at the end.
4. Click Connections. Find your connection and click the Properties button.

5. On the Definition tab, update the connnection string to look like

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=SSASDB;Data Source=MyServer\MyInstance;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

将命令类型更改为默认

Change the Command Type to Default

将命令文本更改为以下内容:

Change the command text to the following:

SELECT 
[CATALOG_NAME] AS SSAS_Database_Name,
[CUBE_NAME] AS Cube_or_Perspective_Name,
[CUBE_CAPTION] AS Cube_or_Perspective_Caption,
[CUBE_TYPE] AS Cube_Type,
[BASE_CUBE_NAME] AS Base_Cube
FROM 
$SYSTEM.MDSCHEMA_CUBES
WHERE
CUBE_SOURCE=1
AND
[BASE_CUBE_NAME] < ''

单击确定"，然后单击关闭".

Click OK and then click Close.

单击现有连接".选择您的连接.

Click Existing Connections. Choose your connection.

在导入数据"窗口上选择表.选择将表放在新的工作表上.

Choose Table on the Import Data Window. Choose to Put your table on a new worksheet.

您的表应该在A到E列中.在单元格G2中，放入Prior Row Count:

Your table should be in columns A through E. In cell G2 put Prior Row Count:

在G3单元格中放入Current Row Count:

在H2单元格中输入0.

In cell H2 put 0.

在单元格H3中，输入以下公式:

In cell H3, enter the following formula:

=COUNTA(Table_ExternalData_1[SSAS_Database_Name])

编写一个宏，该宏将值从单元格H3复制到H2，然后刷新表的数据连接.我的看起来像这样:

Write a macro that copies the value from cell H3 to H2 and then refreshes the data connection for the table. Mine looks like this:

Sub UpdateCubeCount()
    Range("H3").Select
    Selection.Copy
    Range("H2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False
End Sub

作为奖励，将条件格式添加到单元格H3中.设置单元格值> $ H $ 2的格式使其具有粉红色背景和红色文本.

As a bonus, add conditional formatting to cell H3. Format it to have pink background and red text for the rule Cell Value > $H$2.

您最终得到的是这样的东西:

You end up with something that looks like this:

更新:如果要查找SSAS数据库而不是多维数据集本身，则可以使用此查询代替步骤7中的查询:

Update: If you are looking for the SSAS databases rather than the cubes themselves, you can use this query instead of the one in step 7:

SELECT [catalog_name] AS SSAS_Database_Name, [date_modified] 
FROM $system.DBSCHEMA_CATALOGS

如果您可以假设每个数据库只有一个多维数据集，这将非常有用.

This would be useful if you can assume you only have one cube per database, which is fairly common.

这篇关于在Excel中使用VBA，如何获取服务器上所有OLAP多维数据集的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！