Article ID: 821180
Article Last Modified on 2/22/2007
APPLIES TO
- Microsoft SQL Server 2000 Analysis Services
- Microsoft SQL Server 2000 Analysis Services
BUG #: 14040 (PLATO7x)
SUMMARY
The MSOLAP provider contains a data retrieval module that applies an adaptive algorithm that dynamically determines the granularity of data to retrieve from the server. The algorithm is used as part of the query optimization process, and it runs in the client process as part of the OLE-DB provider.
There are two different data retrieval optimizers that SQL Server can use, depending on the query. The first data retrieval optimizer is SONAR. SQL Server uses SONAR for the majority of queries.
The second data retrieval optimizer is SONAR2. If a query includes a NonEmptyCrossJoin function, or a NON EMPTY clause, SQL Server may use SONAR2. The optimizer that SQL Server uses for a query is chosen automatically, based on the operations used in the query.
The MSOLAP provider requests data from the server by requesting the descendants of a particular dimension member at a given level. Generally speaking, the more cells that are retrieved, the better the chances that future queries will be satisfied through the client's cache. Retrieving more cells, however, uses more memory and introduces more server and network latency. The determination of what data granularity to request is done by looking at the ratio of useful members versus total members at a particular dimension level.
The data retrieval optimizer will try to determine the most efficient way to pull back the data from the server while maximizing the chance that future queries can be resolved from cache on the client. This is based on a ratio of useful members to total members for a particular sub-hierarchy of a dimension. Useful members are dimension members that are required as part of the results of the current query. Total members include all the members that would be pulled back at that level of the sub-hierarchy. The total members may include dimension members that would be pulled back as part of the data retrieval because it is anticipated that they may be useful in the future, but are not required for the current query.
For regular SONAR, the threshold used for this ratio can be controlled by the connection string parameter Cache Ratio. For example, the following connection string will cause a query where the ratio of useful members to total members at a particular level is greater than 0.35 to request all members at that dimension level. If the ratio of members required to total members is less than 0.35, each member that is required will be requested individually:
Data Source=localhost;Provider=MSOLAP;Initial Catalog=Foodmart;Cache Ratio=0.35
Because the current Cache Ratio parameter only works for SONAR, before this hotfix there was no way to adjust the ratio used by SONAR2 to affect this particular query. The ratio threshold used for SONAR2 was fixed at 0.5. This hotfix implements a Design Change Request (DCR) to provide a parameter named Cache Ratio2 for SONAR2 that is similar to the Cache Ratio parameter for SONAR.
The new parameter is independent of Cache Ratio, but they can both be specified, if you want, to control the cache ratio for both SONAR and SONAR2. The following is an example of a connection string that would set both ratios to 0.35:
Data Source=localhost;Provider=MSOLAP;Initial Catalog=Foodmart;Cache Ratio=0.35;Cache Ratio2=0.35
RESOLUTION
Service pack information
To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:
290211 How to obtain the latest SQL Server 2000 service pack
Hotfix information
SQL Server 2000 Analysis Services
The English version of this fix has the file attributes (or later) that are listed in the following table. The dates and times for these files are listed in coordinated universal time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Date Time Version Size File name
-------------------------------------------------------------
13-May-2003 06:14 8.0.806.0 217,668 Msmdcb80.dll
23-Jul-2003 04:23 8.0.0.843 4,055,616 Msmddo80.dll
13-May-2003 06:14 8.0.767.0 1,024,576 Msmdgd80.dll
16-May-2003 08:26 8.0.823.0 172,608 Msmdpump.dll
13-May-2003 06:14 8.0.0.761 9,638,464 Msmdsgn80.dll
22-Jul-2003 23:03 8.0.843.0 1,831,492 Msmdsrv.exe
27-Jun-2003 19:06 8.0.838.0 2,077,264 Msolap80.dll
10-May-2003 08:05 8.0.820.0 213,069 Msolap80.rll
Note Because of file dependencies, the most recent hotfix or feature that contains the files may also contain additional files.
SQL Server 2000 Analysis Services 64-bit
For additional information about a hotfix build designed to be applied on a server that is running SQL Server 2000 Analysis Services 64-bit, or to find out if a fix is scheduled to be included in SQL Server 2000 Analysis Services 64-bit Service Pack 4, click the following article number to view the article in the Microsoft Knowledge Base:
822672 FIX: Hotfix Information for Build 8.0.843.0 of SQL Server 2000 Analysis Services 64-bit
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 4.
REFERENCES
Keywords: kbbug kbfix kbqfe kbsqlserv2000presp4fix kbhotfixserver KB821180
