Export (0) Print
Expand All

OLE DB for OLAP Properties Used by Excel

Microsoft Excel uses an OLE DB for OLAP (OnLine Analytical Processing) provider to connect to OLAP cubes. When connecting to an OLAP cube, Excel 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. 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.

Property Set

Property

Set if

Set to

DBPROPSET_MSOLAPINIT

DBPROP_MSMD_SAFETY_OPTIONS

Supported

OLAPUDFSecurity reg key or DBPROPVAL_MSMD_SAFETY_OPTIONS_ALLOW_SAFE

See also: Safety Options Property.

DBPROPSET_MSOLAPINIT

DBPROP_MSMD_MDXCOMPATIBILITY

Supported

DBPROP_MSMD_MDXCOMPATIBILITY_70

See also: MDX Compatibility Property.

DBPROPSET_MSOLAPINIT

DBPROP_MSMD_SOURCE_DSN_SUFFIX

DBPROP_MSMD_SOURCE_DSN in DBPROPSET_MSOLAPINIT is present

String "Prompt=CompleteRequired;Window Handle=0x<hwnd>"

See also: Source_DSN_Suffix Property.

DBPROPSET_MSOLAPINIT

DBPROP_MSMD_MDX_MISSING_MEMBER_MODE

Supported

If property is supported, Excel sets it to the string “Error”. Ignored if not supported.

DBPROPSET_DBINIT

DBPROP_INIT_LCID

Supported

Set 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_DBINIT

DBPROP_INIT_PROMPT

Supported

Not OLAP specific.

Set before making the connection. If setting this property fails because a certain value is not supported, Excel ignores the failure.

DBPROPSET_DBINIT

DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO

Supported

Not OLAP specific.

Set before making the connection. Excel appears to always set this to True.

DBPROPSET_DBINIT

DBPROP_INIT_HWND

Supported

Not 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_DBINIT

DBPROP_INIT_ASYNCH

Supported

Not 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_DBINIT

DBPROP_CMD_PROMPT

Supported

Not OLAP specific.

Set before making the connection.

DBPROPSET_DBINIT

DBPROP_CMD_HWND

Supported

Not OLAP specific.

Set before making the connection.

Property Set

Property

Value

Use

DBPROPSET_MDX_EXTENSIONS

DBPROP_MSMD_MDX_DDL_EXTENSIONS

If bit set for DBPROPVAL_MDX_DLL_CREATESESSIONCUBE.

The grouping feature of OLAP PivotTables is enabled if CREATE SESSION CUBE is supported.

DBPROPSET_MDX_EXTENSIONS

DBPROP_MSMD_MDX_DDL_EXTENSIONS

If bit set for DBPROPVAL_MDX_DDL_REFRESHCUBE.

If REFRESH CUBE command is supported, Excel executes it when an OLAP PivotTable is refreshed.

DBPROPSET_MDX_EXTENSIONS

DBPROP_MSMD_MDX_CALCMEMB_EXTENSIONS

If 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_DATASOURCEINFO

MDPROP_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).

DBPROPSET_SESSION

DBPROP_VISUALMODE

If supported (and subselect not supported, see MDPROP_MDX_SUBQUERIES below).

Enables control of Include hidden items in totals (toggle visual totals).

DBPROPSET_DATASOURCEINFO

MDPROP_MDX_SUBQUERIES

If 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 PivotTables. Generally uses Excel 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_DATASOURCEINFO

MDPROP_MDX_DRILL_FUNCTIONS

If 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_DATASOURCEINFO

MDPROP_FLATTENING_SUPPORT

Check 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_DATASOURCEINFO

MDPROP_NAMED_LEVELS

Excel checks that the lowest bit is set (MDPROPVAL_NL_NAMEDLEVELS).

If the lowest bit of this property is not set, Excel fails.

DBPROPSET_DATASOURCEINFO

MDPROP_MDX_SET_FUNCTIONS

Excel queries for this property, but it has no feature-relevant effect.

DBPROPSET_DATASOURCEINFO

DBPROP_DBMSVER

Excel 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_DATASOURCEINFO

DBPROP_DATASOURCE_TYPE

Excel 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_ROWSET

DBPROP_ROWSET_ASYNCH

If 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

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 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 row set 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).

Property Name

Use

Language

LCID 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*/;

Show:
© 2014 Microsoft