Sending XMLA DISCOVER_XML_METADATA to SSAS from an SQL query

Question

I am trying to issue an XMLA statement for "discover" to an Analysis Services Instance. Sending the XMLA from an XMLA window in Management Studio works fine and returns the desired result, so I am confident I got the basic syntax right.

Wraping it in an SQL Query (see below) it returns: "OLE DB provider "MSOLAP" for linked server "ANALYSIS" returned message "Das Discover-Element in Zeile 2, Spalte 58 (urn:schemas-microsoft-com:xml-analysis-Namespace) ist unter Envelope/Body/Execute/Command/Batch nicht zulässig."

Own Translation: "The Discover-Element in Row 2, Column 58 ... is under Envelope/Body/Execute/Command/Batch ... not allowed.".

I followed SSAS XMLA DISCOVER: how to get list of roles in SSAS database which has a working example and a screen shot included. That was 2010... so the version used there was most likely SQL 2008 or SQL 2008 R2. My code should be identical, yet it does not work (anymore?) on SQL 2012.

I even think this is correct, quoting from https://docs.microsoft.com/de-de/analysis-services/xmla/xml-elements-commands/batch-element-xmla?view=sql-analysis-services-2016: "Child elements (of Batch): Bindings, DataSource, DataSourceView, ErrorConfiguration, Parallel

One or more of the following XMLA commands: Alter, Backup, BeginTransaction, ClearCache, CommitTransaction, Create, Delete, DesignAggregations, Drop, Insert, Lock, MergePartitions, NotifyTableChange, Process, Restore, RollbackTransaction, SetPasswordEncryptionKey, Statement, Subscribe, Synchronize, Unlock, Update, UpdateCells"

"Discover" is not mentioned as a valid command there. It does not work without the Batch, too. I tested that of course. The error given above just changes to "is not allowed under Envelope/Body/Execute/Command"

So, any idea how this can be done?

DECLARE @xmla AS NVARCHAR(MAX) = '<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
    <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
        <RequestType>DISCOVER_XML_METADATA</RequestType>
        <Restrictions>
            <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis">
                <DatabaseID>DBName</DatabaseID>
                <CubeID>CubeName</CubeID>
                <MeasureGroupID>MGName</MeasureGroupID>
            </RestrictionList>
        </Restrictions>
        <Properties/>
    </Discover>
</Batch>';

EXEC (@xmla) AT [ANALYSIS_LINKED_SERVER];

If you browse the link to the Microsoft Page, you find that "Discover" is not a command, but a method. "Execute" is also a method. If you do "EXEC" from SQL, you are already down the "Execute" method path, and cannot "go back" to using the "Discover" method instead - that makes sense. But how does one issue another method to SSAS then? I tried OpenQuery() but that also returns the error "under command not allowed." — Ralf

Ralf Ralf · Accepted Answer · 2020-11-25T12:15:27

Answering my own question is surely not great style, but as it happens, I seem to have found an acceptable way to query the data. I will not accept my answer, I hope someone finds a better way than mine here.

The idea is, if "SQL EXEC" puts you too far down in the "Envelope-Body-Execute" method, why not do SOAP from the root yourself and choose the "Discover" method when appropriate instead?

I took the setup of the SOAP Envelope mainly from here: Calling a SOAP webservice from TSQL stored procedure and tweaked it as needed.

I did not manage to call this on the SSAS native protocol port (2383 is default, I think) but since we run the msmdpump.dll that is not a big issue for me. If someone knows how to post SOAP to the native port, I'd be happy to hear.

DECLARE @url AS VARCHAR(8000) = 'http://webserver/olap/msmdpump.dll';
DECLARE @requestBody AS VARCHAR(8000) = '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Body>
        <Discover xmlns="urn:schemas-microsoft-com:xml-analysis">
            <RequestType>DISCOVER_XML_METADATA</RequestType>
            <Restrictions>
                <RestrictionList>
                    <DatabaseID>DBID</DatabaseID>
                    <CubeID>CubeID</CubeID>
                    <MeasureGroupID>MGID</MeasureGroupID>
                </RestrictionList>
            </Restrictions>
            <Properties/>
        </Discover>
    </soap:Body>
</soap:Envelope>';

DECLARE @obj AS INT;
EXEC sys.sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT;

EXEC sys.sp_OAMethod @obj, 'Open', NULL, 'POST', @url, false

EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'Content-Type', 'text/xml'
EXEC sys.sp_OAMethod @obj, 'setRequestHeader', NULL, 'SOAPAction', '"urn:schemas-microsoft-com:xml-analysis:Discover"'
EXEC sys.sp_OAMethod @obj, 'send', NULL, @requestBody

CREATE TABLE #t (x XML);
INSERT INTO #t
EXEC sys.sp_OAGetProperty @obj, 'responseText';

EXEC sys.sp_OADestroy @obj;

That's it - works for me. If there is any comment or suggestion, please let me know! Thanks!

Sending XMLA DISCOVER_XML_METADATA to SSAS from an SQL query

1 Answers