| Q: How can I see what internal commands PowerPivot executes in its engine? |
| Written by Vidas Matelis |
| Sunday, 09 May 2010 03:30 |
|
Q: How can I see what internal commands PowerPivot executes in its engine? A: To see what internal commands (XMLA, MDX, etc) PowerPivot executes behind the scene in it engine, you should enable PowerPivot trace and then review it. Start Excel and go to PowerPivot menu and then click on the "Settings" button. New window will pop up "PowerPivot Options and Diagnostics". At the bottom of this screen you will see a check-box "Enable PowerPivot Tracking for the current Excel session".
As soon as you click on that check-box, you will get a warning "After the PowerPivot window is opened, tracing will start":
After you will click OK, you will notice that "Trace File Location" property in the "PowerPivot Options & Diagnostics" window changed, but it is not specified yet:
Now close "PowerPivot Options & Diagnostics" window and open PowerPivot window. From this point anything you will do in PowerPivot will be recorded in the PowerPivot trace. To see trace file name go back to "PowerPivot Options & Diagnostics" screen. At this point "Trace File Location" property now should show where PowerPivot trace file is saved.
Remember this trace file location. By default PowerPivot traces are stored on users desktop. This trace file is intended for Microsoft technical support, but you can open it using Microsoft SQL Server 2008 R2 Profiler and review executed statements. Results look very similar to Microsoft SQL Server Analysis Services (SSAS) trace.
If you will load PowerPivot workbook after trace file is started, it will record XMLA commands to create PowerPivot (that is SSAS) cube and then all commands executed against that cube - for example statements to count records in tables, etc. PowerPivot tracing is enabled per sessions - that is if you will close Microsoft Excel workbook and re-open it again, PowerPivot tracing will not be running unless you enable it again. To stop PowerPivot trace you should close Excel workbook.
|
Author articles
- How to solve issue when PowerPivot for Excel crashes on all workbooks
- Using PowerPivot to analyze web access log in "Combined Log" format (CLF)
- Q: I have integer amount that represents duration in seconds. How can show this amount in format "h:mm:ss"?
- Interview with Marco Russo and Alberto Ferrari about their new book about PowerPivot
- DAX cheat sheet
Most Popular
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of suggested datasets to test PowerPivot
- List of PowerPivot DAX functions with description
- How to install PowerPivot for Excel and list of know issues
- Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
- List of PowerPivot DAX functions (short)
- Learning PowerPivot and DAX
- PowerPivot's impact on BI pros?
good point - PTPower looks good for showing MDX submitted by Pivot to engine. There is another similar free tool: "OLAP PivotTable Extensions" available on codeplex, but to be honest I did not try them on Office 2010.
Again, thanks for pointing this out - many readers need to see just MDX and using these free tools is much easier than enabling full PowerPivot trace.
Nice post. Sometimes I like to see the MDX generated in PowerPivot. Thus far, I've been using the PTPower Excel add-in. PTPower was designed for working with traditional SSAS cubes, but with PowerPivot PivotTables, it allows you to view the MDX code.