Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

OLAP Server Formatting results in DISCOVER command against all cubes

View products that this article applies to.


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.

↑ Back to the top

More information

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

↑ Back to the top

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.

↑ Back to the top

Keywords: KB2535767

↑ Back to the top

Article Info
Article ID : 2535767
Revision : 3
Created on : 11/1/2012
Published on : 11/1/2012
Exists online : False
Views : 340