FIX: Introduction of the Cache Ratio2 Parameter (821180)

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

   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