One of the annoyances that I have come across in my short time working with Excel Services is the cycle of; change workbook -> publish to share point -> make sure everything works -> repeat.  Today I happened to find a compatibility checker that works extremely well, especially considering that it is in (probably permanent) beta.

I don't think that I can do a better job explaining how to use it than the author, so just go check out the site!  If you just want to download the installation file, you can get it here.

Issues that the compatiblity checker knows how to find/fix

The following is a list of the issues that can be found:

AutoFix available (all of these will be removed from the workbook when auto-fixed):

• Comments
• DialogSheets
• Display Formulas
• Macro Sheet
• OleObjects
• Query Tables
• Shapes
• Validation
• XML Maps

AutoFix sometimes available (will be removed if possible):

• IRM - AutoFix will work if there's no password.
• Protection - AutoFix will work if there's no password.
• VBA - AutoFix will work if the trust-cente enables Automation to interact with the VBA project.

AutoFix will not work for these:

• Unsupported formulas such as External Workbooks and RTD()
• FileFormatResult

excerpt from http://blogs.msdn.com/cumgranosalis

Data Connections in Excel 2007

Every workbook that imports data from an external location uses a data connection to retrieve data from the data source.  There are two types of connections in Excel 2007 – Embedded, and Linked connections.

Embedded connection
Embedded connections exist within the file definition of the containing Excel document – they are bundled together.  This is useful in scenarios where there is no central data connection library available, or for when you want to deploy a completely standalone solution.

Linked connection
Linked connections are connections that are stored in a separate file with a .odc (Office Data Connection) extension.   Using ODC files is good for scenarios where you have many different workbooks that utilize identical data connections.  You can deploy the connection file to a central location, where multiple workbooks can access it.  If the connection details ever need to be changed, only a single file needs to be updated, rather than updating the connection within every single workbook.

Within Excel both types of connections are utilized in the exact same way, there is no functional difference.

A single connection within an Excel workbook can be both embedded and linked at the same time.  Whenever you use the data connection wizard to setup a new connection file, the file is referenced by that connection by default.  However if you go into the connection settings within Excel and change any part of the connection, you will get a warning that the connection in the workbook will no longer match the linked connection, and the link to the external connection will be severed.

Optionally, the workbook can be forced to use the linked connection exclusively by checking the “always use connection file” option within the connection properties menu  (see screenshot to the right).  By doing this, Excel will always reference the external connection- even when it differs from the connection within the file.

The benefit of leaving “always use connection file” unchecked is that if one connection fails for whatever reason, the workbook will try the other one before giving up completely.  This gives you two shots at making the connection!

After running these scripts you can easily view the underlying MDX query for any pivot table that uses a SSAS cube for its datasource:
 

Works great!

The code comes from http://sqljunkies.com/WebLog/sqlbi/archive/2007/01/18/26875.aspx