Search code examples
xmlssmsssasmdxsql-server-profiler

SSMS - How to Provide/Edit/Send In "RequestParameters" With a MDX Query

I am working on a solution to this ticket, as part of that I'm analyzing various types of MDX queries from various sources. I've noticed that we you intercept a MDX call from a cube in excel connected to a tabular cube in SSAS in SQL Profiler one of the columns provided is "RequestParamaters":

enter image description here

These look something like this:

<PropertyList
    xmlns="urn:schemas-microsoft-com:xml-analysis"
    xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <Catalog>db</Catalog>
    <Timeout>0</Timeout>
    <Format>Native</Format>
    <DbpropMsmdFlattened2>false</DbpropMsmdFlattened2>
    <SafetyOptions>2</SafetyOptions>
    <Dialect>MDX</Dialect>
    <MdxMissingMemberMode>Error</MdxMissingMemberMode>
    <DbpropMsmdOptimizeResponse>3</DbpropMsmdOptimizeResponse>
    <DbpropMsmdActivityID>5235D7A5-1F14-4329-B43F-B982DEFFFE87</DbpropMsmdActivityID>
    <DbpropMsmdRequestID>9B566A32-00D7-4DF8-AB69-D5E2263C7642</DbpropMsmdRequestID>
    <DbpropMsmdCurrentActivityID>5235D7A5-1F14-4329-B43F-B982DEFFFE87</DbpropMsmdCurrentActivityID>
    <LocaleIdentifier>1033</LocaleIdentifier>
    <DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility>
    <DbpropMsmdSubqueries>2</DbpropMsmdSubqueries>
    <DbpropMsmdCellErrorMode>TextValue</DbpropMsmdCellErrorMode>
</PropertyList>

I assume these properties can affect the results that are returned, for example changing "MdxMissingMemberMode" clearly changes the result set.

My question is how do you provide these properties/change these properties when you're running this query in SSMS? I've googled around a bit and there doesn't seem to be any discussion on it, is this part of connection string properties? If so how do I edit those for a connection to SSAS, the "Connection String" button in the property window is greyed out...

Solution

  • In SSMS, the connection string properties for Analysis Services can be set via Options -> Additional Connection Parameters when connecting to the SSAS.

    enter image description here

    enter image description here

    In the profiler, we can see this:

    enter image description here