SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Q: When opening Excel 2007 file with Pivot table connected to SSAS 2005 I am getting message:
"Security Warning Data connections have been disabled" and there is an "Options..." button
that lets me choose "Enable this content". How to disable this warning and enable connection to SSAS database by default?

A:You have to add location where you excel file resides into trusted location list. You can do this by following these steps:

• Start Excel 2007, click on the "Office" button
• Click on the "Excel Options" button.
• Select "Trust Center" tab and click on the "Trust Center Settings..." button.
• Select "Trusted Locations" tab
• Make sure that checkox "Disable all Trusted Locations. Only files signed by Trusted Publishers will be trusted." is not checked (do this just if that complies with your company security policies).
• Click on "Add new location..." button.
• In the Path enter location that will be trusted. Example: "C:\Users\UserID.Domain\Documents". If required, select checkbox "Subfolders of this location are also trusted". Optionally enter description for this location
• Click "OK" button
• Click "OK" to close "Trust Center" dialog
• Click "OK" to close "Excel Options" dialog.

Now when you open Excel files with Pivot Table connection to SSAS 2005 server by default will be enabled.

If you are using External connections (for example Sharepoint), then instead you should enable all data connections for external content in the Excel Trust Center. Steps for that are:

• Start Excel 2007, click on the "Office" button
• Click on the "Excel Options" button
• Select "Trust Center" tab and click on the "Trust Center Settings..." button
• Select "External Content" tab
• Select "Enable all Data Connections". Other options are "Prompt user about Data Connections" and "Disable all Data Connections".
• Save changes.