Tracing MDX generated by Analysis Services in Excel

Is there a way to trace the actual MDX generated by querying a Cube in Excel, and get the actual MDX used ?

I've tried using SQL Profiler, and while that does return the MDX, it doesn't take into account the role that the connection is using (it's passed in a separate PropertySet).

I'm just wanting to get the MDX that the query is using, as it doesn't appear to be filtering correctly using different roles (but the same base MDX query).

Update: Ok, maybe I need to be a bit clearer. I'm trying to get the resulting query that is firing at the Cube, after the Role has been applied.

SQL Profiler gives me the MDX, and the name of the role in a PropertySet, so is before the Role is applied.

Update 2: In case it helps, this is an MDX query that I've been using to test the functionality.

select {[Measures].[Company Value]} on columns,
{[Dim Mode开发者_运维知识库l].[Title Name].Members} on rows
from [DWHName]

---

You might want to try the following add-in from codeplex. http://olappivottableextend.codeplex.com/ It just gives you what was sent to the OLAP server. BEWARE though, it adds a bunch of extra mdx that does UI display.

---

I'm pretty sure you can't actually get what you're after. The security role is a property of the connection or of the user running the query and the various restrictions put on the resultset are applied in the query engine. So you never get to see what restrictions are applied (which, I suppose, could be a security issue in itself)

---

I'm not sure whether I've properly understood your question, but it is possible to get the MDX generated by Excel using a macro to query the PivotTable.MDX property.

See here for an example implementation.

---

Not sure I understand either. Are you saying that you want to see the results that role will see? (The MDX in the trace is exactly what was submitted, no?)

Have you tried executing the captured MDX through MDX Studio (where you can set the Role for your connection)?