This estimation of the number of members in a large level can cause implementation difficulties. Client applications also have thresholds for what's considered a large number of members, and these thresholds are frequently larger than Analysis Services' threshold. Because Analysis Services estimates the number of members, the application can't proactively decide whether to fetch all of a dimension member's children or all of a level's members. Thus, you can't estimate how long a query will take or how much memory it will use. This information is important because you might need to warn the user or avoid the operation. Such a situation might occur when a client application needs to fill a drop-down list with all the members from a dimension level. Or a user might expand a node in a dimension's tree representation that causes the retrieval of more than 1000 members. Or a dimension level could contain more than 1 million members.
One way to circumvent such a problem is to use MDX queries instead of schema rowset calls to fetch dimension members and to surround the MDX query with a subset function to limit the number of members the query returns, as the following example shows:
SELECT subset( Customers.Name.Members, 0, 5000 ) ON columns
FROM Sales
You might also use a schema rowset function to retrieve the members. But if your application might have to deal with many members, using an MDX query with a subset function is safer.
Default Isolation Mode. You use this property in write-back cubes, but using this property has performance implications in nonwrite-back implementations. When the Default Isolation Mode is set to true, PivotTable Service isolates the cache contents in a single query. PivotTable Service flushes the cache before a query starts, then fills the cache with the results before retrieving them so that every read of a cell returns the same value. You want to make sure that subsequent reads of the same query results don't change because of other users' writes, even if the underlying cube changes because other users write new values. However, you do want to receive other users' updates, so PivotTable Service flushes the cache between queries.
If Default Isolation Mode is set to false, PivotTable Service preserves the cache between queries. But when the cache exceeds its available memory, PivotTable Service flushes a portion of the cache and might not preserve the read integrity of the current query.
A side effect of this property is that it can improve performance when a query executes remotely on the server. PivotTable Service retrieves and locks in memory the cell values in the query result, which reduces the number of round-trips to the server.
Client Cache Size. This property controls how much client memory is available as a PivotTable Service cache. You can set the value as a percentage of available memory or as a fixed number of kilobytes. For details about the setting, see SQL Server Books Online (BOL).
You should know a couple of things about the PivotTable Service cache. First, the cache is database-specific. As long as you keep the OLE DB property for the current database constant, the cache can grow. However, if you change the current database, PivotTable Service will flush the cache. Also, when PivotTable Service runs out of cache memory, it will try to flush from the cache all the information associated with cubes other than the one you're accessing. If the cube your query is accessing is a virtual cube, then PivotTable Service might flush the cache memory of one of the underlying base cubes before another base cube, thus disrupting the data.
Second, if you have the Client Cache Size property set even a little smaller than the total amount of memory required for the current query, you can destroy the performance of the cache. In such a situation, if the Default Isolation Mode is set to false, then PivotTable Service reads in the cell values during the query execution and the cell retrieval steps, which causes PivotTable Service to constantly flush the cache and make extra round-trips to the server.
Finally, you need to understand how PivotTable Service makes requests to the server and stores them in the cache. PivotTable Service has a query-optimization component that predicts the cell values that a query needs to retrieve from the server. This query-optimization component creates cell value requests to the server that contain two pieces of information: One is a tuple (a particular cell in the cube), and the other is the distance (in dimension levels) to retrieve descendants from that tuple for each dimension in the cube. The server returns the non-NULL cell values in that slice of the cube.
PivotTable Service caches both the server request and the result. Later, when PivotTable Service checks a new cell reference against the cache, it first determines whether the new cell reference is within the bounds of any of the cached server requests. If the reference is within these bounds, PivotTable Service checks the cell value cache. If no cell value is in the cache, then PivotTable Service knows the value is NULL because it stores only non-NULL values.
You have an advantage when you understand the inner workings of each software component you use. Knowing how to set the execution location, large-level threshold, isolation mode, and cache size properties is useful when you're trying to optimize the performance of queries running against cubes larger than 500MB or when you're trying to minimize query time in a cube to improve usability or scalability. I hope some of the details about PivotTable Service that I provide can help you in your next Analysis Services implementation. Thanks to Cristian Petculescu from Microsoft's Analysis Services development team for providing much of the information in this column.
End of Article
Prev. page
1
[2]
next page -->
