Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Jan 26, 2004

MDX in Analysis Services: Optimizing MDX: More on Location, and the Importance of Arrangement - Page 4

By William Pearson

The Importance of Optimal Arrangement

To paraphrase some of what we have learned so far in the Optimizing MDX mini-series, queries that contain levels whose populations of members exceed the Large Level parameter will be processed by the server; queries with levels whose member populations do not exceed the Large Level Threshold in number are within the processing capabilities of the client-based PivotTable Service operation.

The manner in which the PivotTable Service processes queries can become a consideration within the determination of the location of processing. The service acts in such a way that each set (and every combination existing therein) defined in the query is fully incarnated in memory before proceeding with operations. Unsurprisingly, the demands on resources can be crippling for even the most robust machines.

Let's consider an example:

12.  Create the following new query:

-- MXAS11-3:  Pre-optimized Set Operation
{[Measures].[Units Shipped]} ON COLUMNS,
TopCount (
      [Product].[Product Name].Members
[Measures].[Units Shipped]
FROM Warehouse

We have expanded our existing query well beyond its last incarnation, adding another dimension and a CrossJoin() function, as if to enhance it to meet (as an illustration) a specific need of an information consumer. We know that the query will be processed on the client if the population of the [Product].[Product Name] level is less than the Large Level Threshold (1,000 default / 750 the setting from our last lesson).

An important fact to remember is that the threshold refers to the level's members; it does not refer to the tuples that exist within a given set within a query. Say our Large Level Threshold has been set at 2200. We already know that we have 1560 named products (from the count we did above); we also have 23 warehouse cities. While the "memberships" of both levels fall well below the threshold of 2200, and thus qualify for client processing, and while our results dataset is the top seven city-product tuples from the perspective of units shipped, we have far more combinations, in the way of resource requirements, with which to contend from the scope of our query.

Our query will assemble over 35,000 combinations (1560 x 23) - all to arrive at the small results dataset that we obtain in the next step.

13.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 5.

Illustration 5: Results Dataset, CrossJoin() added to Query

Our query executes in a matter of moments, even though it is completing myriad more steps than we may have considered. However, things might have turned out quite differently, within the scope of the realities of similar - but much larger - scenarios that we might have encountered in the real world.

14.  Save the query as MXAS11-3.

Not taking the real cost of the actual combinations, and relying upon the large level and other level-member-based safeguards to protect us, may actually place a load upon the client that would challenge even a robust server. At the heart of this resource intensive situation lies our old friend the CrossJoin() function, although there are many other similar potential participants in inefficient query construction and operation. We will examine ways to manage these scenarios in the next section.

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM