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":
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...
In SSMS, the connection string properties for Analysis Services can be set via Options -> Additional Connection Parameters
when connecting to the SSAS.
In the profiler, we can see this: