Figure 4 shows a typical .oqy file. In this case, the Connection= keyword signals the beginning of the connection string. The format of the connection-string properties is the same as the .odc variety, so you add the Log File property the same way you did for the .odc file.
After logging begins, PivotTable Services logs MDX statements in the specified file after each PivotTable manipulation. Each entry's format is the same: The application name in upper case (in this case, EXCEL) is first, followed by the process ID (PID) of the Excel instance that created the query, the query date and time, the query type (MDX), and the query text. Tabs separate these items.
Different sessions of Excel activity that use the same connection string overwrite the log file, effectively clearing it out and starting over. So when you collect some good MDX examples, always copy the log file contents to another file to avoid losing the logged information. Also, only one connection at a time can own the log file. If you open another Excel instance or worksheet that uses the same log file, the log file will fail to open. Consequently, you can't start another PivotTable report on another Excel worksheet, for example. If the log file seems to sit dormant when it should be receiving updates, check to make sure that the same data source isn't already in use in another Excel worksheet.
Examining the Entries
Let's look at some typical Log File entries that I collected when I dragged some dimensions onto the PivotTable in Excel. For a data source, I used the Sales cube from the FoodMart 2000 sample database that ships with Analysis Services. I opened the Sales cube and performed several PivotTable actions. I dragged the Gender dimension to the Row Fields area of the Excel PivotTable, then dragged Marital Status to the Column Fields area, and finally dragged Sales Average to the Data Items area. Figure 5 shows the results of the actions. Figure 6 shows the resulting log entry, which describes what happened behind the scenes.
Ignoring for the moment all the extra details (e.g., time, date) that Excel tacked on to the MDX statements, notice that the statements are records of what happened during each action and that each statement can stand on its own. You need to examine only the last generated statement—it contains everything in the previous statements and more. Additionally, the last generated statement would be the same regardless of the order in which I performed the drag operations. If I had dragged the Sales Average first, then Marital Status, then Gender, the last MDX statement that Excel generated would have been the same.
Looking at this last generated statement (which I highlighted in Figure 6), you can see that Excel added lots of extra information to the MDX statement. Let's take this statement apart from the inside out. First, the DrillDownLevel() function automatically takes the set specified as a parameter and drills down to reveal that level's members. For example, in the string DrillDownLevel({[Gender].[All Gender]}), DrillDown-Level() drills down from the All Gender level to the next level in the hierarchy of the Gender set and provides a set of members from that level—M and F. When you drag a dimension onto the Excel PivotTable, you don't see what happens to make those members appear. The MDX in this scenario is the same MDX that Excel uses to drill down when you double-click a dimension member in the PivotTable. But in that case, the DrillDownLevel() string that Excel generates includes an additional level parameter to show that DrillDownLevel() should return members of the specified level instead of just the next level, which is the default. You can see more DrillDownLevel() examples by experimenting with Sales cube dimensions—such as the Store dimension—that have additional levels.
While you're examining the generated MDX, copy it to the clipboard, then paste it into the MDX Sample Application that ships with Analysis Services. The MDX Sample Application is a great place to experiment with and see the results of various MDX statements. Figure 7 shows the result of my example statement. I removed most of the trimmings except for the DrillDownLevel() functions.
The next function in the generated MDX, AddCalculatedMembers(), includes in the set any calculated members of the dimension. The use of this function is interesting because a metadata-based function, such as DrillDownLevel() in this case, doesn't typically include calculated members. Perhaps the Excel designers included the call to AddCalculatedMembers() as a safety measure so that these members would never be excluded from the PivotTable output and you can have confidence that you're seeing complete results in the output.
The next layer in the generated MDX is the call to HIERARCHIZE(), an ordering function that, in this case, ensures that the members appear in the correct order, according to their hierarchy (e.g., children follow their parents in the hierarchy). This hierarchy is easy to see in the PivotTable but can be difficult to see in the MDX Sample Application.
Another interesting detail is the liberal use of PARENT_UNIQUE_NAME, which is an intrinsic member property available for a drilled-down dimension and which contains the name of the current level's parent. Excel PivotTable uses this property to correctly label parent levels in the PivotTable—for example, Store Country is a parent level for Store State. If the property is present in the query result, Excel will use it for this purpose.
Better Skills Produce Better BI
That's all there is to getting Excel to reveal the MDX queries it's using during PivotTable operations. Experimenting with the logged results of different Excel PivotTable operations can be an inexpensive and instructive way to build your MDX knowledge. Combining a good grasp of your business data with the expertise to manipulate cube data using MDX in ways that go beyond what the tools offer can give you a leg up when it comes to producing better BI information from the data you already have.