This documentation is archived and is not being maintained.

OLE DB for OLAP Properties Used by Excel 2007

Office 2007

Microsoft Office Excel 2007 uses an OLE DB for OLAP (OnLine Analytical Processing) provider to connect to OLAP cubes. When connecting to an OLAP cube, Excel 2007 reads and sets various OLE DB and OLE DB for OLAP properties. Excel considers Connection properties, Data Source Information Properties, Schema Rowset Queries, and Cell properties.

Rather than address all the existing properties, this topic focuses on the properties that have a unique relationship with Excel 2007. OLAP connections in Excel are used for PivotTables and OLAP Formulas. When you are testing an existing OLAP provider, it is recommended that you have Excel read a set of provider properties to determine whether an OLAP provider supports the features required for OLAP PivotTable design and functionality. If the provider does not support certain capabilities, the features that depend on these capabilities are either disabled or limited. Other properties are set in order to get desired behavior, and if these properties are not implemented for an OLAP provider, Excel might not work with it.


Connection Properties

Property SetPropertySet ifSet to
DBPROPSET_MSOLAPINITDBPROP_MSMD_SAFETY_OPTIONSSupportedOLAPUDFSecurity reg key or DBPROPVAL_MSMD_SAFETY_OPTIONS_ALLOW_SAFE
See also: Safety Options Property.
DBPROPSET_MSOLAPINITDBPROP_MSMD_MDXCOMPATIBILITYSupportedDBPROP_MSMD_MDXCOMPATIBILITY_70
See also: MDX Compatibility Property.
DBPROPSET_MSOLAPINITDBPROP_MSMD_SOURCE_DSN_SUFFIXDBPROP_MSMD_SOURCE_DSN in DBPROPSET_MSOLAPINIT is presentString "Prompt=CompleteRequired;Window Handle=0x<hwnd>"
See also: Source_DSN_Suffix Property.
DBPROPSET_MSOLAPINITDBPROP_MSMD_MDX_MISSING_MEMBER_MODESupportedIf property is supported, Excel sets it to the string “Error”. Ignored if not supported.
DBPROPSET_DBINITDBPROP_INIT_LCIDSupportedSet before making the connection. It is possible to specify any LCID to be used in the connection.
If translations are turned on for the connection, Excel sets this to the UI language (default).
If property is not supported, Excel has no problem other than losing the functionality of having translations based on UI language.
DBPROPSET_DBINITDBPROP_INIT_PROMPTSupportedNot OLAP specific.
Set before making the connection. If setting this property fails because a certain value is not supported, Excel ignores the failure.
DBPROPSET_DBINITDBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFOSupportedNot OLAP specific.
Set before making the connection. Excel appears to always set this to True.
DBPROPSET_DBINITDBPROP_INIT_HWNDSupportedNot OLAP specific.
Set before making the connection. Excel sets this to the main window of the application so the provider displays the alert using the correct parent window.
DBPROPSET_DBINITDBPROP_INIT_ASYNCHSupportedNot OLAP specific.
Set before making the connection. Excel sets this property to DBPROPVAL_ASYNCH_INITIALIZE based on a registry setting (you can also disable it by using a registry setting). If property is not supported, Excel ignores it and does not set it.
DBPROPSET_DBINITDBPROP_CMD_PROMPTSupportedNot OLAP specific.
Set before making the connection.
DBPROPSET_DBINITDBPROP_CMD_HWNDSupportedNot OLAP specific.
Set before making the connection.

Data Source Information

Property SetPropertyValueUse
DBPROPSET_MDX_EXTENSIONSDBPROP_MSMD_MDX_DDL_EXTENSIONSIf bit set for DBPROPVAL_MDX_DLL_CREATESESSIONCUBE.The grouping feature of OLAP PivotTables is enabled if CREATE SESSION CUBE is supported.
DBPROPSET_MDX_EXTENSIONSDBPROP_MSMD_MDX_DDL_EXTENSIONSIf bit set for DBPROPVAL_MDX_DDL_REFRESHCUBE.If REFRESH CUBE command is supported, Excel executes it when an OLAP PivotTable is refreshed.
DBPROPSET_MDX_EXTENSIONSDBPROP_MSMD_MDX_CALCMEMB_EXTENSIONSIf bit set for DBPROPVAL_MDX_CALCMEMB_ADD.The show calculated members feature in OLAP PivotTable is enabled if ADDCALCULATEDMEMBERS is supported in MDX (Multidimensional Expressions).
DBPROPSET_DATASOURCEINFOMDPROP_MDX_FORMULAS If both bits set MDPROPVAL_MF_SCOPE_SESSION, MDPROPVAL_MF_CREATE_CALCMEMBERS.If the provider supports creating session members (CREATE SESSION MEMBER), Excel enables this feature in OLAP PivotTables (only available in the object model in Excel 2007).
DBPROPSET_SESSIONDBPROP_VISUALMODEIf supported (and subselect not supported, see MDPROP_MDX_SUBQUERIES below). Enables control of Include hidden items in totals (toggle visual totals).
DBPROPSET_DATASOURCEINFOMDPROP_MDX_SUBQUERIESIf the two lowest bits are set (with this, Excel does not support non-visual totals, see DBPROP_VISUALMODE above).Enables Label, Date, and Value filtering in Excel 2007 PivotTables. Generally uses Excel 2007 MDX query construction.
Note that this property is introduced with SQL Server 2005 Service Pack 2. Value is always VARIANT_TRUE in msolap90.dll.
DBPROPSET_DATASOURCEINFOMDPROP_MDX_DRILL_FUNCTIONSIf the two lowest bits of this property are set, Excel interprets it as the server supporting tuple-based drilling with the DrillDownLevel and DrillDownMember functions. However, Excel only allows attribute drilling if the lowest two bits of MDPROP_MDX_SUBQUERIES are also set (subselects supported).
DBPROPSET_DATASOURCEINFOMDPROP_FLATTENING_SUPPORTCheck that it is set to MDPROPVAL_FS_FULL_SUPPORT.Read by Excel, and if it is not set to MDPROPVAL_FS_FULL_SUPPORT, an error occurs because Excel does not consider it an OLAP provider.
DBPROPSET_DATASOURCEINFOMDPROP_NAMED_LEVELSExcel checks that the lowest bit is set (MDPROPVAL_NL_NAMEDLEVELS).If the lowest bit of this property is not set, Excel fails.
DBPROPSET_DATASOURCEINFOMDPROP_MDX_SET_FUNCTIONS Excel queries for this property, but it has no feature-relevant effect.
DBPROPSET_DATASOURCEINFODBPROP_DBMSVERExcel checks whether this value is a string.Excel does not check the actual value of this property; it only verifies whether it is a string. If it is not a string, Excel fails to connect.
DBPROPSET_DATASOURCEINFODBPROP_DATASOURCE_TYPEExcel checks whether the second lowest bit is set (DBPROPVAL_DST_MDP).If the lowest bit is set, the provider is considered a multidimensional (OLAP) provider.
DBPROPSET_ROWSETDBPROP_ROWSET_ASYNCHIf supported.Excel tries to set this to DBPROPVAL_ASYNCH_INITIALIZE but if this fails, Excel falls back into synchronous mode. If supported, it enables Excel to support the user pressing the Esc key to stop query execution before it is finished.

Schema Rowset Queries

Schema RowsetColumnValueControls
MDSCHEMA_CUBESIS_DRILLTHROUGH_ENABLEDTRUEIf set to TRUE, the drill-through (Show Details) feature is enabled for cells in the OLAP PivotTable values area.
MDSCHEMA_HIERARCHIESSTRUCTUREMD_STRUCTURE_UNBALANCEDExcel has special handling of filtering for unbalanced hierarchies, so these are marked as such for control purposes.
MDSCHEMA_HIERARCHIESHIERARCHY_ORIGINMD_ORIGIN_ATTRIBUTE set and not MD_ORIGIN_USER_DEFINEDExcel has special handling of attribute hierarchies in OLAP PivotTables, so attribute hierarchies are marked as such.
MDSCHEMA_HIERARCHIESHIERARCHY_DISPLAY_FOLDERBased on this property, the PivotTable Field List displays hierarchies in folders under their dimensions.
MDSCHEMA_MEASUREGROUPSMEASUREGROUP_NAMEMeasures are listed in a folder representing their measure group in the PivotTable Field List.
MDSCHEMA_MEASUREGROUPSMEASUREGROUP_CAPTIONMeasures are listed in a folder representing their measure group with this caption in the PivotTable Field List.
MDSCHEMA_SETSSET_DISPLAY_FOLDERExcel reads the display folder property to enable it to place sets in display folders in the PivotTable Field List.
MDSCHEMA_SETSSET_CAPTIONExcel reads the set caption for displaying in the PivotTable report and in the PivotTable Field List.
MDSCHEMA_KPISKPI_DISPLAY_FOLDERKPIs (key performance indicators) defined on the server are listed in the PivotTable field list, and the components (value, goal, status, and trend) can be added to the values area. Excel reads this property to place the KPI in the correct display folder in the PivotTable Field List.
MDSCHEMA_KPISKPI_PARENT_KPI_NAMEExcel reads this property to place child KPIs in subfolders under their parent KPI in the PivotTable Field List (if display folders are defined, those are used instead).
MDSCHEMA_KPISKPI_TREND_GRAPHICExcel reads this property and, based on the value, maps it to the closest conditional formatting icon set in Excel when Trend is added to the PivotTable.
MDSCHEMA_KPISKPI_STATUS_GRAPHICExcel reads this property and, based on the value, maps it to the closest conditional formatting icon set in Excel when Status is added to the PivotTable.
MDSCHEMA_ACTIONSAdditional Actions feature. Excel exposes server-defined actions in the shortcut menu of an OLAP PivotTable report when actions exist on the server for the selected context.
MDSCHEMA_MEASURESMEASURE_DISPLAY_FOLDERRead by Excel so it can place measures in the correct display folder in the PivotTable Field List.
MDSCHEMA_MEASURESEXPRESSIONRead by Excel to determine whether a measure is calculated. If it is a string and not empty, Excel considers it a calculated measure.
MDSCHEMA_PROPERTIESPROPERTY_NAME“MEMBER_VALUE”

This schema also used for getting regular member properties. The "MEMBER_VALUE" value is a special case, but there are other usage.
Excel gets the member value property of the key attribute in a dimension by restricting to “MEMBER_VALUE” in the PROPERTY_NAME column.
If the data type (DATA_TYPE) of the MEMBER_VALUE property of the key attribute of a Time dimension is Date, the PivotTable exposes date filtering instead of label filtering. The actual date filtering is done based on the member value property of the key independent of which hierarchy of that dimension is filtered.
Bb407625.vs_note(en-us,office.12).gif  Note
Date filtering requires support for subselects (see MDPROP_MDX_SUBQUERIES above).
Bb407625.vs_note(en-us,office.12).gif  Note
Date filtering requires support for subselects (see MDPROP_MDX_SUBQUERIES above).
MDSCHEMA_DISCOVERRESTRICTIONSDepending on usage, Excel restricts on hierarchies, levels, or measures when reading the MDSCHEMA_DISCOVER rowset to get the RESTRICTIONS.
Excel reads schema row by row and finds list of restrictions for all other relevant schemas to obtain the index of the restrictions that affect Excel.
The RESTRICTIONS column has a chapter handle to another rowset from which Excel looks at the NAME column. In the NAME column, Excel expects to find the strings HIERARCHY_VISIBILITY, MEASURE_VISIBILITY, LEVEL_VISIBILITY (if the provider supports restriction on visibility).

If Excel cannot find <xxx>_VISIBILITY strings (or if MDSCHEMA_DISCOVER is not supported) it will assume that provider doesn't support returning hidden items, and it will not query for them.
MDSCHEMA_LEVELSLEVEL_ATTRIBUTE_HIERARCHY_NAMEUsed by Excel to hide special grouping levels with system-generated names. Note that this is not needed with Microsoft SQL Server 2005 Analysis Services Service Pack 2.
MDSCHEMA_LEVELSCUSTOM_ROLLUP_SETTINGS0If not 0, Excel assumes the level has custom rollup. Excel checks this for all levels of each hierarchy, and if custom rollup is present, some operations are disabled (such as grouping).

Cell Properties

Property NameUse
LanguageLCID for determining how to interpret FORMAT_STRING when it is CURRENCY.
Excel uses this property to determine which currency symbol to use when formatting values with FORMAT_STRING set to Currency.

Retrieving Cell Properties

Example of calculated measure definition specifying the LANGUAGE property for the client application to pick up:
CREATE MEMBER CURRENTCUBE.[Measures].[Internet Gross Profit]
 AS
	[Measures].[Internet Sales Amount] 
	- 
	[Measures].[Internet Total Product Cost],

	FORMAT_STRING = "Currency",
	BACK_COLOR = 12615680 /*R=0, G=128, B=192*/,
	FORE_COLOR = 65408 /*R=128, G=255, B=0*/,
	FONT_FLAGS = 3 /*Bold, Italic*/,
	NON_EMPTY_BEHAVIOR = { [Internet Sales Amount],[Internet Total Product Cost] },
	VISIBLE = 1,
	LANGUAGE = 1033 /*Telling client application to display US currency symbol*/;
CREATE MEMBER CURRENTCUBE.[Measures].[Internet Gross Profit]
 AS
	[Measures].[Internet Sales Amount] 
	- 
	[Measures].[Internet Total Product Cost],

	FORMAT_STRING = "Currency",
	BACK_COLOR = 12615680 /*R=0, G=128, B=192*/,
	FORE_COLOR = 65408 /*R=128, G=255, B=0*/,
	FONT_FLAGS = 3 /*Bold, Italic*/,
	NON_EMPTY_BEHAVIOR = { [Internet Sales Amount],[Internet Total Product Cost] },
	VISIBLE = 1,
	LANGUAGE = 1033 /*Telling client application to display US currency symbol*/;



Show: