OLAP Server Formatting results in DISCOVER command against all cubes

Excel 2013Excel 2010


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.

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
End Sub