Actions in an SQL Server 2000 Analysis Services cube may return incorrect results or the actions are not accessible when you try to access the actions by using a client application (872977)


The information in this article applies to:

  • Microsoft SQL Server 2000 Analysis Services

SYMPTOMS

After you create actions in a Microsoft SQL Server 2000 Analysis Services cube, when you try to access the actions or use the actions by using a client application like Cube Browser in Analysis Manager, you may notice the following behavior:
  • Actions in the cube return results that are not correct.
  • Even if actions are defined on an object, the actions are not accessible for that object. For example, even if an action of the Data set type is defined on an object, the Data set action is not accessible for that object in Cube Browser.

CAUSE

This problem may occur because the following behavior may occur in a client application that is used to access and to use the actions in an Analysis Services cube:
  • Some actions return results that are not correct because all members or coordinates of the slicer dimensions that are used in the multidimensional expressions (MDX) query that is associated with the actions are treated as if the members are at the All level.
  • Some actions are not accessible because the client application may not be able to retrieve the actions.

WORKAROUND

To work around this problem, create or use a client application that can access and can use actions in SQL Server 2000 Analysis Services. To retrieve the information that is related to actions that are available in a cube, the client application can use the OpenSchema method.

For additional information about the OpenSchema method, visit the following Microsoft Developers Network (MSDN) Web site:

http://msdn.microsoft.com/library/en-us/ado270/htm/mdmthopenschema.asp

For example, the following code samples can be used to retrieve actions that are available in a cube.

Note These code samples follow the Microsoft Visual Basic language syntax. Additionally, adoCon is an object of the ADODB.Connection class.
  • The following code sample retrieves the Cube level actions on the Sales Cube:
    Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific , _
Array(Empty, Empty, "Sales", _
Empty, _
Empty, "Sales", _
ActionCoordinateTypeCube), _
SCHEMA_ROWSET_ACTIONS)
  • The following code sample retrieves the actions on the Store dimension of the Sales cube:
    Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific, _
Array(Empty, Empty, "Sales", _
Empty, _
Empty, "[Store]", _
ActionCoordinateTypeDimension), _
SCHEMA_ROWSET_ACTIONS)
  • The following code sample retrieves the actions on a member of the Store dimension of the Sales cube:
    Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific, _
Array(Empty, Empty, "Sales", _
Empty, _
Empty, "[Store].[All Stores].[USA].[CA]", _
ActionCoordinateTypeMember), _
SCHEMA_ROWSET_ACTIONS)
  • The following code sample retrieves the actions on a member of the [Store State] level of the Store dimension of the Sales cube:
    Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific, _
Array(Empty, Empty, "Sales", _
Empty, _
Empty, "[Store].[Store State].[CA]", _
ActionCoordinateTypeMember), _
SCHEMA_ROWSET_ACTIONS)
  • The following code sample retrieves the actions on the [Store State] level of the Store dimension of the Sales cube:
    Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific, _
Array(Empty, Empty, "Sales", _
Empty, _
Empty, "[Store].[Store State]", _
ActionCoordinateTypeLevel), _
SCHEMA_ROWSET_ACTIONS)
To effectively use the actions in a cube, the client application must retrieve the coordinate level information (or a full set of cell coordinates) from the MDX query that is submitted to the Analysis Server. To do this, the client application must traverse through the MDX query, including each axis and any slicer in the MDX query. After the MDX query is traversed, the client application can use the OpenSchema method of the ADODB.Connection class to retrieve the information that is related to the actions in the cube that is queried.

The following code sample retrieves the actions on the coordinates that are defined in a variable that is named sSelectedUniqueName. For example, sSelectedUniqueName can contain the coordinates of the MDX query that is submitted to the Analysis Server:
Set rsActions = adoCon.OpenSchema(adSchemaProviderSpecific, _
Array("FoodMart 2000", Empty, "Sales", _
Empty, _
Empty, sSelectedUniqueName, _
ActionCoordinateTypeCell), _
SCHEMA_ROWSET_ACTIONS)

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

271773 SAMPLE: How to retrieve actions, functions and sets schemas from the Microsoft OLAP Provider

186246 How to use the ADO OpenSchema method in Visual Basic

For additional information, see the "Implementing Actions" section and the "Actions for Developers" section in the following MSDN Web site:

http://msdn.microsoft.com/library/en-us/dnsqlmag02/html/olapfordevelopers.asp

Modification Type:MajorLast Reviewed:8/4/2004
Keywords:kbADO kbProgramming kbfunctions kbAppDev kbCodeSnippet kbtshoot kbprb KB872977 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.