OLAP Server Formatting results in DISCOVER command against all cubes

Excel 2013 and Excel 2010 include a feature to retrieve formatting from the server. This feature is enabled by default on new OLAP connections.

Excel is sending an MDSCHEMA_PROPERTIES DISCOVER command with a request for <PROPERTY_TYPE>2</PROPERTY_TYPE> at the level of the database. Since the request is being made at the database level rather than the cube level, it causes all cubes contained in the database to be loaded and the calculation script for each executed. That also results in any associated security expressions being evaluated for each cube contained in the database.

To turn off the feature when creating the PivotTable click Properties in the 'Import Data' dialog to access the Connection Properties dialog. Note: You must create the PivotTable from the Data tab, through�Get External Data | From Other Sources | from Analysis Services to access the the Import Data dialog and the Properties button.
For existing PivotTables select the PivotTable. Select Connection Properties from the PivotTable Tools | Options�| Data | Change Data Source ribbon.

In the Connection Properties�dialog unselect Number Format, Fill Color, Font Style & Text Color from the OLAP Server Formatting options. Click OK to apply the settings.

To automate turning off the formatting for all connections in a workbook�you can use a VBA macro similar to the following:

Sub TurnOffOLAPServerFormattingOptions()
��� For Each cn In ActiveWorkbook.Connections
������� With cn.OLEDBConnection
����������� .ServerFillColor = False
����������� .ServerFontStyle = False
����������� .ServerNumberFormat = False
����������� .ServerTextColor = False
������� End With
��� Next
End Sub

�

Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.