Article ID: 945685
Article Last Modified on 12/20/2007
APPLIES TO
- Microsoft SQL Server 2005 Analysis Services
SUMMARY
This article describes a behavior in Microsoft SQL Server 2005 Analysis Services that occurs when you directly request a large MDSCHEMA_MEMBERS schema rowset. This article also provides alternative methods to request an MDSCHEMA_MEMBERS schema rowset.
Typically, a MEMBERS schema rowset is large because it contains many rows. Information in this article also applies to any MEMBERS schema rowset generally.
SYMPTOMS
Consider the following scenario. You create a client application that uses the SQL Server 2005 Analysis Services 9.0 OLE DB provider. In the application, you use the IDBSchemaRowset::GetRowset method to request a large MDSCHEMA_MEMBERS schema rowset. In this scenario, the request may take a long time to respond. Additionally, the client application uses lots of virtual memory. In some cases, the client application exhausts the available virtual memory. In this case, you obtain an E_FAIL result, and you receive the following error message:
CAUSE
This behavior occurs because the SQL Server 2005 Analysis Services 9.0 OLE DB provider caches the whole MDSCHEMA_MEMBERS schema rowset in the available virtual memory for the client application.
Generally, the MDSCHEMA_MEMBERS schema rowset is very large because it contains a row for each member of a dimension attribute. Also, dimension attributes typically contain a significant number of members. For example, the unrestricted MDSCHEMA_MEMBERS schema rowset for the AdventureWorks DW sample database contains about 1.2 million rows and will use about 1 gigabyte (GB) virtual memory.
For more information about the MEMBERS rowset, visit the following Microsoft Developer Network (MSDN) Web site:
WORKAROUND
To work around this behavior, request a subset of the MDSCHEMA_MEMBERS schema rowset based on the needs of the users. For example, if a user only needs some members of the MDSCHEMA_MEMBERS schema rowset, you can request only those members that meet the requirements of the user. To request a subset of the MDSCHEMA_MEMBERS schema rowset, use one of the following methods.
Use restrictions to request a subset of the MDSCHEMA_MEMBERS schema rowset
You can use restrictions to request a subset of the MDSCHEMA_MEMBERS schema rowset. In most cases, restrictions can meet your needs.
For more information about the available restrictions for the MEMBERS schema rowset, visit the following MSDN Web site:
You can use restrictions to request a specific member. Or, you can use restrictions to request members that share a relationship with a specific member. For example, you can request the descendants of a specific member. Or, you can request the siblings of a specific member. Therefore, the number of members that you request is smaller. This causes the SQL Server 2005 Analysis Services 9.0 OLE DB provider to use less virtual memory to cache these members.
Use MDX queries to request a subset of members
In some cases, restrictions cannot provide the specific subset of members that you want. In these cases, you can create Multidimensional Expressions (MDX) queries to request the subset that you want. Typically, the members that an MDX query returns on an axis rowset contain most of the properties that you want.
If you want to obtain additional properties, you can use the DIMENSION PROPERTIES clause to access these additional properties. However, be aware that you cannot use the DIMENSION PROPERTIES clause to access the following properties:
- SCHEMA_NAME
- MEMBER_ORDINAL
- MEMBER_GUID
- EXPRESSION
Additionally, if you access the CHILDREN_CARDINALITY property, you may obtain an incorrect result for this property. To resolve this problem, obtain the cumulative update package (build 3175) for SQL Server 2005 Service Pack 2 (SP2). For more information, click the following article number to view the article in the Microsoft Knowledge Base:
936305 Cumulative update package 2 for SQL Server 2005 Service Pack 2 is available
For example, you may have a drop-down list that displays the members of the MDSCHEMA_MEMBERS schema rowset. Usually, a drop-down list will not display too many members. In these scenarios, you can use MDX functions to obtain a subset of members. For example, you can use the HEAD function, the TAIL function, or the SUBSET function.
An example of how to use the SUBSET function
SELECT SUBSET([Customer].[Customer].[Customer].Members, 0, 1000) ON Columns FROM [Adventure Works]
You can use the FILTER function to request a subset of members that meet some specific criteria. In this example, you request all members whose names begins with the letter "F."
An example of how to use the FILTER function
SELECT FILTER([Customer].[Customer].[Customer].Members,
[Customer].[Customer].CurrentMember.MEMBER_NAME >= 'F' AND
[Customer].[Customer].CurrentMember.MEMBER_NAME < 'G') ON Columns
FROM [Adventure Works]
You can also combine functions in an MDX query to specify more complex criteria.
An example of how to use a combination of the SUBSET function and the FILTER function
SELECT SUBSET(FILTER([Customer].[Customer].[Customer].Members,
[Customer].[Customer].CurrentMember.MEMBER_NAME >= 'F' AND
[Customer].[Customer].CurrentMember.MEMBER_NAME < 'G'), 0, 10) ON Columns
FROM [Adventure Works]
Note In addition to these MDX functions, you can use the COM functions and the .NET-based functions in SQL Server 2005 Analysis Services to create more complex filters.
Note The previous methods request only the members that are relevant to the users. These methods limit the members that the SQL Server 2005 Analysis Services 9.0 OLE DB provider caches for the client application.
Use a forward-only rowset
You can also use a forward-only rowset to obtain members from a MDSCHEMA_MEMBERS schema rowset. To do this, you can set the following properties to the VARIANT_FALSE value on your request for a MDSCHEMA_MEMBERS schema rowset:
- DBPROP_CANFETCHBACKWARDS
- DBPROP_CANSCROLLBACKWARDS
- DBPROP_CANHOLDROWS
A forward-only rowset limits the members that are cached at the client application by caching members to a buffer. The buffer can contain at most 1,001 members.
Advantage of a forward-only rowset
You can obtain an almost immediate response to the initial request for a MDSCHEMA_MEMBERS schema rowset. Additionally, only a small buffer of members is stored at the client application. Therefore, a forward-only rowset reduces memory overhead at the client application.
Disadvantages of a forward-only rowset
The following list includes the disadvantages of a forward-only rowset:
- The connection through which the application request for a MDSCHEMA_MEMBERS schema rowset is locked to other operations until the application reads the whole schema rowset.
- In the SQL Server 2005 Analysis Services 9.0 OLE DB provider, a thread that populates the buffer may time out if the client application does not read the members from the buffer in 10 minutes.
After the thread finishes populating the buffer that contains 1,001 rows, the thread will stop reading members from the server until the client application reads all the members from the buffer. As soon as the client application reads all the members from the buffer, the time-out value is reset. Then, the thread starts to read additional members from the server to populate the buffer from the MDSCHEMA_MEMBERS schema rowset. If the client application does not read the members from the buffer within 10 minutes, the thread times out. After the thread times out and the client application reads all the members from the buffer and tries to read additional members from the buffer, the DB_S_ENDOFROWSET condition is returned.
- A forward-only rowset takes some time to read a member from the MDSCHEMA_MEMBERS schema rowset and to populate the buffer. If a member has many member properties, a forward-only rowset takes more time. The cumulative duration to read all members may take a while. Therefore, in some scenarios, you should not use a forward-only rowset.
STATUS
This behavior is by design.
MORE INFORMATION
When the number of members in a dimension attribute increases, the size of the MDSCHEMA_MEMBERS schema rowset also increases. Eventually, the size of the MDSCHEMA_MEMBERS schema rowset may reach a threshold where the behavior that is mentioned in the "Symptoms" section occurs easily. However, we cannot estimate the threshold for a specific client application. This is because the threshold is highly dependent on many factors. These factors can include the following:
- The hardware of the server computer and of the client computer
- Whether the version of the operating system is 32-bit or 64-bit
- The data types of the dimension attributes
- The names of the objects in the Analysis Services database
Note The objects can be cubes, dimensions, and dimension attributes.
- The number of member properties in the dimension attributes
For example, you may request a large MDSCHEMA_MEMBERS schema rowset in a 32-bit environment. In a 32-bit environment, the available virtual memory for an application is limited to 2 GB. When you request a large MDSCHEMA_MEMBERS schema rowset, the available virtual memory for the client application can be exhausted. In a 64-bit environment, the available virtual memory for an application does not have this limitation. Therefore, the MDSCHEMA_MEMBERS schema rowset may be successfully cached.
If you decide to cache members of the MDSCHEMA_MEMBERS schema rowset in your own data structures, these data structures should also contain small and manageable chunks of members. These data structures should not contain all members of the dimension attributes. Otherwise, these data structures and the client application may use lots of virtual memory.
Generally, the more data that you request, the more time and memory are required to obtain and store the data. We highly recommend that you do not request a whole MEMBERS schema rowset. You can use one of the previous methods to request a subset of the members that are relevant to the needs of the users. You should select a method to reduce memory usage on the client application depending on your production environment.
REFERENCES
For more information about the MEMBERS rowset, visit the following MSDN Web site:
For more information about the available restrictions for the MEMBERS schema rowset, visit the following MSDN Web site:
For more information about the incorrect value that you may obtain from the CHILDREN_CARDINALITY property, click the following article number to view the article in the Microsoft Knowledge Base:
936595 FIX: The estimate of the number of children that a dimension member has may be inaccurate when you request the CHILDREN_CARDINALITY property for a dimension member in SQL Server 2005 Analysis Services
Keywords: kbsql2005bi kbsql2005as kbexpertiseadvanced kbtshoot kbprb KB945685
