在Integration Services中使用XMLA查询的结果

I have an XMLA query which returns the State and Last Processed date of an Analysis Services cube as XML, like so:

我有一个XMLA查询,它将Analysis Services多维数据集的State和Last Processed日期作为XML返回,如下所示:

Query:

<Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
  <RequestType>DISCOVER_XML_METADATA</RequestType>
  <Restrictions >
    <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
      <DatabaseID>SSAS - Premium and Claims V2</DatabaseID>
      <CubeID>PDW04 1</CubeID>
      <ObjectExpansion>ReferenceOnly</ObjectExpansion>
    </RestrictionList>
  </Restrictions>
  <Properties />
</Discover>

Result:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:schema targetNamespace="urn:schemas-microsoft-com:xml-analysis:rowset" xmlns:sql="urn:schemas-microsoft-com:xml-sql" elementFormDefault="qualified">
      <xsd:element name="root">
        <xsd:complexType>
          <xsd:sequence minOccurs="0" maxOccurs="unbounded">
            <xsd:element name="row" type="row" />
          </xsd:sequence>
        </xsd:complexType>
      </xsd:element>
      <xsd:simpleType name="uuid">
        <xsd:restriction base="xsd:string">
          <xsd:pattern value="[0-9a-zA-Z]{8}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{4}-[0-9a-zA-Z]{12}" />
        </xsd:restriction>
      </xsd:simpleType>
      <xsd:complexType name="xmlDocument">
        <xsd:sequence>
          <xsd:any />
        </xsd:sequence>
      </xsd:complexType>
      <xsd:complexType name="row">
        <xsd:sequence>
          <xsd:element sql:field="METADATA" name="METADATA" type="xmlDocument" />
        </xsd:sequence>
      </xsd:complexType>
    </xsd:schema>
    <row>
      <xars:METADATA xmlns="http://schemas.microsoft.com/analysisservices/2003/engine" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:xars="urn:schemas-microsoft-com:xml-analysis:rowset">
        <Cube>
          <Name>Premium</Name>
          <ID>PDW04 1</ID>
          <CreatedTimestamp>2008-11-23T22:31:06</CreatedTimestamp>
          <LastSchemaUpdate>2009-01-22T00:50:13</LastSchemaUpdate>
          <LastProcessed>2009-01-07T22:28:34</LastProcessed>
          <State>Processed</State>
        </Cube>
      </xars:METADATA>
    </row>
  </root>
</return>

I would like to be able to use this XMLA query in an Integration Services package, parsing and storing the result in a SQL Server database table. However the only tasks that seems to execute an XMLA query is the "Analysis Services Execute DDL" task, which does not have a method of returning results of the query as far as I can tell.

我希望能够在Integration Services包中使用此XMLA查询,将结果解析并存储在SQL Server数据库表中。但是,似乎执行XMLA查询的唯一任务是“Analysis Services Execute DDL”任务,该任务没有一种方法可以返回查询结果。

Does anyone have any suggestions on how else to achieve this?

有没有人对如何实现这一目标有任何建议?

3 个解决方案

#1

While you can run "regular" MDX/XMLA through various mechanisms in SSIS, including via the Execute SQL task, it will always wrap it inside . is a top-level method just like and cannot be wrapped inside , hence the problem.

虽然您可以通过SSIS中的各种机制运行“常规”MDX / XMLA,包括通过执行SQL任务,但它始终将其包装在内部。是一个*的方法,就像不能包裹在里面,因此问题。

You've got two possible solutions:

你有两个可能的解决方案:

Wrap your XMLA inside an Execute SQL task using an OPENQUERY call inside your relational database. You'd do something like SELECT * from OPENQUERY() and you can actually do a good job of parsing the XML resultset inside SQL Server.

使用关系数据库中的OPENQUERY调用将XMLA包装在执行SQL任务中。您可以从OPENQUERY()执行类似SELECT *的操作,并且您实际上可以很好地解析SQL Server中的XML结果集。

Open up you SSAS servers by configuring HTTP access (http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx) so you can send XMLA as a web service call. There is a web service task in SSIS that you can use to execute your and consume the results.

通过配置HTTP访问(http://www.microsoft.com/technet/prodtechnol/sql/2005/httpasws.mspx)打开SSAS服务器,以便将XMLA作为Web服务调用发送。 SSIS中有一个Web服务任务,您可以使用它来执行和使用结果。

Both of these options obviously have some downside.

这两种选择显然都有一些缺点。

#2

For Discover commands:

对于Discover命令:

This works very well for me:

这对我很有用:

Create a script task using XMLA names space issue the discover query. Once you receive the response parse the xmla and add to VB.NET Datatable and insert the parsed data into sql table.

使用XMLA名称空间创建脚本任务发出发现查询。收到响应后,解析xmla并添加到VB.NET Datatable并将解析后的数据插入到sql表中。

#3

You can also use the new DMV syntax in SSAS 2008 or the DMV function from ASSP to in SSAS 2005 to return this data in a tabular format.

您还可以使用SSAS 2008中的新DMV语法或ASSP中的DMV函数以及SSAS 2005中的表格格式返回此数据。

#1