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 3

By William Pearson

We will create an MDX query that helps us to "qualify" a second query as containing elements that cause it to fall within the two conditions we have exposed above that force a query to process on the server.

6.  Type the following query into the query pane of the Sample Application:

-- MXAS11-1:  Qualification through Count
   MEMBER[Measures].[Count] AS 
   'COUNT({ [Product].[Product Name].Members})'
{[Measures].[Count]}ON COLUMNs
FROM Warehouse

Again, our intent here is to ascertain that an upcoming example expression ([Product].[Product Name].Members) will "qualify" as meeting conditions that would force it to process on the server. Therefore, in our first query, we are simply obtaining a count of the members of the given level.

7.  Execute the query using the Run Query button

The results dataset appears as shown in Illustration 3.

Illustration 3: Results Datset, Count Query

8.  Save the query in a convenient location as MXAS11-1.

We see that [Product].[Product Name].Members refers to a genuine Large Level, because the number of members in the Product Name level (1,560) of the Product dimension exceeds the Large-Level Threshold we set in our last lesson (750). For that matter, it exceeds even the default threshold that existed before our modifications (1000). (The number of members is also verifiable at the RDBMS level in the FoodMart2000.mdb sample that is installed with MSSQL Server 2000 Analysis Services).

Let's use the level whose population we have just quantified in the COUNT query above to illustrate. The inclusion of a filter within our query will also be a driver for server-based processing, as we shall see.

9.  Create the following new query:

-- MXAS11-2:  Qualification through Count & Filter
{[Measures].[Units Shipped]} ON COLUMNS,
TopCount ([Product].[Product Name].Members, 
7, [Measures].[Units Shipped]) ON ROWS
FROM Warehouse

The use of TopCount() above provides an instance where server-based execution is likely to be appropriate, for the reasons we have already exposed. Most of the large level will be pruned away by the filter action before returning the result. As we noted earlier, existence of a filter operation within the query is another driver for server processing.

10.  Execute the query using the Run Query button.

The results dataset appears as shown in Illustration 4.

Illustration 4: Results Set, TopCount() Query

11.  Save the query as MXAS11-2.

While level-size and filter requirements are good criteria to use in most cases for determining the likelihood of forced server-based processing, there are scenarios where even meeting or exceeding the parameters of these two criteria will not force a query to execute on the server. Examples of these situations include the presence of a function or functions within the query (say a user-defined function that is registered solely on the client) that cannot execute on the server. Also, as is somewhat obvious, a query that is executed against a local cube will not process on a remote server.

We have seen that options exist in the form of the Execution Location and Large Level Threshold properties to influence the physical location in which a query is processed. These two options within the query processing location intervention type could allow us to take advantage of performance gains based upon our being able to assign resources appropriately. While the Analysis Server provides for tuning in its provision for the establishment of a Large Level Threshold, as we saw in our previous session together, optimal syntax arrangement in our queries is perhaps a more important consideration. Before we attempt to optimize performance in this way, we need to gain a solid understanding of how the PivotTable Service works.

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