OLE DB for OLAP Properties Used by Excel 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

Data Source Information

Schema Rowset Queries

Schema Rowset	Column	Value	Controls
MDSCHEMA_CUBES	IS_DRILLTHROUGH_ENABLED	TRUE	If set to TRUE, the drill-through (Show Details) feature is enabled for cells in the OLAP PivotTable values area.
MDSCHEMA_HIERARCHIES	STRUCTURE	MD_STRUCTURE_UNBALANCED	Excel has special handling of filtering for unbalanced hierarchies, so these are marked as such for control purposes.
MDSCHEMA_HIERARCHIES	HIERARCHY_ORIGIN	MD_ORIGIN_ATTRIBUTE set and not MD_ORIGIN_USER_DEFINED	Excel has special handling of attribute hierarchies in OLAP PivotTables, so attribute hierarchies are marked as such.
MDSCHEMA_HIERARCHIES	HIERARCHY_DISPLAY_FOLDER		Based on this property, the PivotTable Field List displays hierarchies in folders under their dimensions.
MDSCHEMA_MEASUREGROUPS	MEASUREGROUP_NAME		Measures are listed in a folder representing their measure group in the PivotTable Field List.
MDSCHEMA_MEASUREGROUPS	MEASUREGROUP_CAPTION		Measures are listed in a folder representing their measure group with this caption in the PivotTable Field List.
MDSCHEMA_SETS	SET_DISPLAY_FOLDER		Excel reads the display folder property to enable it to place sets in display folders in the PivotTable Field List.
MDSCHEMA_SETS	SET_CAPTION		Excel reads the set caption for displaying in the PivotTable report and in the PivotTable Field List.
MDSCHEMA_KPIS	KPI_DISPLAY_FOLDER		KPIs (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_KPIS	KPI_PARENT_KPI_NAME		Excel 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_KPIS	KPI_TREND_GRAPHIC		Excel 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_KPIS	KPI_STATUS_GRAPHIC		Excel 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_ACTIONS			Additional 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_MEASURES	MEASURE_DISPLAY_FOLDER		Read by Excel so it can place measures in the correct display folder in the PivotTable Field List.
MDSCHEMA_MEASURES	EXPRESSION		Read by Excel to determine whether a measure is calculated. If it is a string and not empty, Excel considers it a calculated measure.
MDSCHEMA_PROPERTIES	PROPERTY_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.   Note Date filtering requires support for subselects (see MDPROP_MDX_SUBQUERIES above).
Note
Date filtering requires support for subselects (see MDPROP_MDX_SUBQUERIES above).
MDSCHEMA_DISCOVER	RESTRICTIONS		Depending 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_LEVELS	LEVEL_ATTRIBUTE_HIERARCHY_NAME		Used 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_LEVELS	CUSTOM_ROLLUP_SETTINGS	0	If 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

  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,
<strong>LANGUAGE = 1033 /*Telling client application to display US currency symbol*/;</strong></code>

  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,
<strong>LANGUAGE = 1033 /*Telling client application to display US currency symbol*/;</strong></code>