Analyzing MDX query evaluation

Are there any tools or tricks for looking at how an MDX query is evaluated in SSAS 2005? Something that indicates how aggregations are being used, for example - sort of like a SHOWPLAN for MDX.
As you might know, AS 2005 MDX query execution events are now captured in SQL Server Profiler. What's not as clear from the docs (that I've come across, anyway) is the aggregation usage info available via SQL Profiler. This was well explained at the recent SQL PASS Conference, in Session 108: "Understanding Analysis Services 2005 Aggregations from Every Angle", by Dave Wickert and Len Wyatt:

>>
SQL Profiler

Will tell you when:
an aggregation is used for a query
the partition data is used for a query

It won’t tell you why it decides to use or not use a particular aggregation

§>>

|||Deepak,
Thanks, that sounds very promising. Do you know where I can find more information about using Profiler for this purpose? That SQL PASS session doesn't appear to be downloadable (that I can find, at least), and the documentation isn't much help here (as you point out).
Thanks,
Kevin
|||Hi Kevin,

If you're a PASS member, then the presentation slides should be downloadable, or you can order the physical media with MP3 sound. Now maybe Dave and Len are making their presentation (or equivalent info) available elsewhere - don't know!

http://www.softconference.com/250927
>>

CO1: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for attendees of the 2005 PASS Summit) CDROM (Attendees only) $ 195.00 CO2: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for members of PASS who did not attend the 2005 Summit) CDRom (Members only) $ 395.00 CO3: Compilation CD-ROM set with full desktop capture - MP3 audio including live demos and PowerPoints (for non-attendees, non-members of PASS) CDRom $ 595.00

>>

Analysis services query timeout from SSIS

Hi,

I've just developed a simple integration services package which picks up a list of MDX queries from a table then runs them against our Cube using a .NET OleDb connection and saves the results to a csv file.

Unfortunately the queries frequently timeout, despite the fact that the Timeout property on the Connection Manager is set to 100000 and the Connect Timeout property is set to 600.

I have some logging on the packages and this revels that the queries will often timeout after less than 60 seconds with the error below:
<<Query>> failed with the following error: "XML for Analysis parser: The XML for Analysis request timed out before it was completed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Looking at the properties of the connection manager, I can see that the ConnectionString which has been generated is:
Data Source=Server;Initial Catalog=CubeName;Provider=MSOLAP.3;Integrated Security=SSPI;Connect Timeout=600;Auto Synch Period=1000;Timeout=100000;

Subsequent re-running of the package will often succeed, I'm guessing this is because the Cube has cached the query result and is able to return it fast enough.

Is there an additional timeout property that I'm missing somewhere?

Your help is much appreciated!

-Stuart

Perhaps try ExternalCommandTimeout? (http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx)|||Chris Webb built a "cache warming" prototype using SSIS and I think he found a way around this issue, you can find the post on his blog about this at http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!1062.entry|||

Hi,

Sorry for the delay in replying and thanks for your suggestions. It seems that Chris Webb's solution was to ignore the timeout, which was fine given that he was building a cache warmer and didn't actually need to get the results. Given that I did need the results I decided to try and build some retry logic into my SSIS package.

I had a very clear and simple idea in my mind of how this would work. I would simply add an additional flow connector to the Execute SQL task with a Failure & Expression constraint where @.RetryCount < @.MaxRetries, then I would flow into a script task to increment @.RetryCount then back into the same Execute SQL task. Unfortunately though SSIS will not allow this as this would result in looping dependency.

The somewhat contrived solution I have now come up with is to extract my MDX Query list into a .NET Dataset pointed to by an SSIS Object variable, onto which I add an additional column to keep track of the number of retires. I then loop over the dataset and in the event of a query failing, I copy the row in the dataset and add it to the bottom, incrementing RetryCount.

It seems like a crazy solution to the problem of a query timing out. Is anyone at Microsoft aware that the Execute SQL task will timeout after 30 seconds when using an ADO .NET OleDb Connection with the Analysis Services provider, regardless of what you set the timeouts to be?

-Stuart

Analysis Services Help

I'm looking for some modest help in developing some MDX queries in Analysis Services against a datacube I've built.
Would anyone be willing to answer some specific e-mail on this? I am willing to provide a small honorarium for some timely help here.Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?|||Originally posted by DBA
Unless there are disclosure issues you may have the best help from posting your questions here (Most likely, if you contact those who are most helpful, many will not refuse whatever you might wish to donate via paypal, etc.)?

I'm not too concerned about disclosure issues. I was mostly concerned about the detail involved. Providing enough context for someone to help me is going to be somewhat involved, and I'm not sure exactly what questions I need to ask. I was hoping for some assistance that might be a bit more iterative than is really practical (and perhaps appropriate) with a public web-based forum.

Given the suggestion, I'll try and work up a summary and sub-scenario and see if anyone is interested in helping (and perhaps helping more).

Thanks!

Analysis Services and Reporting Services

Hello !
I am currently evaluating ability of Reporting Services (RS) to work
with output of MDX queries. RS looks very appealing when working with
relational output. But working with multidimensional output is somewhat
cumbersome. I haven't found any examples so far running against Analysis
Services.
Any thoughts\sources of information on this subject would be greatly
appreciated. We have to decide whether go with RS as UI to display output
from the cube .
Thank you in advance,
Igor.There's no real integration for AS currently in RS - just the OLAP provider
for OLE DB. This means you need to re-format the data you retrieve from cube
s and there's no query builder type implementation. What you could do is wri
te a reporting tool in .NET
to create the rdl (examples in the RS books online) and supply the MDX via a
client tool - such as a web page or desktop app. That way you can handle th
e formatting etc in the reporting tool, rather than doing every time in the
Report Designer.
HTH
Phil.|||Thanks a lot,
Igor.
"Phil Austin" <anonymous@.discussions.microsoft.com> wrote in message
news:E99A5AFD-CB71-4878-BD98-4380C528CBFC@.microsoft.com...
> There's no real integration for AS currently in RS - just the OLAP
provider for OLE DB. This means you need to re-format the data you retrieve
from cubes and there's no query builder type implementation. What you could
do is write a reporting tool in .NET to create the rdl (examples in the RS
books online) and supply the MDX via a client tool - such as a web page or
desktop app. That way you can handle the formatting etc in the reporting
tool, rather than doing every time in the Report Designer.
> HTH
> Phil.|||There are some examples in the samples folder of Reportin service to
fetch from OLAP. Try it
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Hi,
I would like to suggest u to use the MS Reporting Services addin of
Panorama NovaView to have MS Analysis Services views in MS Reporting
Services with a simple wizard. On our website www.gmsbv.nl you will
find some screenshots.
Marco
"imarchenko" <imarchenko@.hotmail.com> wrote in message news:<#$1yPagBEHA.1600@.tk2msftngp13.
phx.gbl>...
> Hello !
> I am currently evaluating ability of Reporting Services (RS) to work
> with output of MDX queries. RS looks very appealing when working with
> relational output. But working with multidimensional output is somewhat
> cumbersome. I haven't found any examples so far running against Analysis
> Services.
> Any thoughts\sources of information on this subject would be greatly
> appreciated. We have to decide whether go with RS as UI to display output
> from the cube .
>
> Thank you in advance,
>
> Igor.