Analysis Services data cache sharing of an OLAP cube may not work correctly in Excel Calculation Services

Consider the following scenario. You configure Microsoft Office SharePoint Server 2007 to use Single Sign-On and Windows authentication. In Excel Calculation Services, you define a trusted file location that allows for external data with embedded credentials. In this scenario, Analysis Services data cache sharing of an OLAP cube may not always work correctly in Excel Calculation Services.

To share the data cache, an OLAP cube must be configured so that members of the same security role all see the same data. A Multidimensional Expression (MDX) expression may contain items that cause the data results to depend on the user who is currently requesting the data. When this scenario occurs, Analysis Services data cache sharing with Excel Calculation Services does not work correctly.

The following list contains some of the items that can cause data results from an MDX expression to depend on the user who is currently requesting the data:

• The UserName function
• The LookupCube function
• Stored procedures
• User-defined functions (UDFs) that do not call Visual Basic for Applications (VBA) code

This behavior is by design.

For more information about MDX functions, visit the following Microsoft Developer Network (MSDN) Web site: