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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 7, 2005

MDX Essentials: The CROSSJOIN() Function: Breaking Bottlenecks - Page 5

By William Pearson

Procedure

We now have a clone cube with which we can pursue our examination of CROSSJOIN() optimization. Let's initialize the MDX Sample Application, as a platform from which to perform our practice exercises, taking the following steps:

1.  Start the MDX Sample Application.

We are initially greeted by the Connect dialog, depicted in Illustration 7.

Click for larger image

Illustration 7: The Connect Dialog for the MDX Sample Application

The illustration above depicts the name of my server, MOTHER1, and properly indicates that we will be connecting via the MSOLAP provider (the default).

2.  Click OK.

The MDX Sample Application window appears.

3.  Ensure that FoodMart 2000 is selected as the database name in the DB box of the toolbar.

4.  Select the new MDX27_CROSSJOIN_TESTING cube in the Cube drop-down list box.

5.  Click File --> New to open a blank Query pane.

The MDX Sample Application window should resemble that shown in Illustration 8, complete with the information from the MDX27_CROSSJOIN_TESTING cube displaying in the Metadata tree (left section of the Metadata pane).


Illustration 8: The MDX Sample Application Window (Compressed View)

We will begin creating our query with a focus on returning results efficiently. We request the specific requirements for the query from the information consumers. They explain that they have requested to see Warehouse Profit, based upon individual Store (denoted in the cube as Store Name), Product (denoted as Product Name), and Store Type ("Store Type") combinations, for Supermarket Store Types specifically, for operating year 1997. They want to see the top fifteen combinations only, in descending order, as the final presentation.

We obtain the original query, to which we have referred above, from the MSAS instance on the computer abandoned by the developer upon his lay off. The query appears as follows:


SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {CROSSJOIN([Warehouse].[Warehouse Name].Members,
CROSSJOIN([Store].[Store Name].Members, 
       [Product].[Product Name].Members))}, 15, ([Store Type].
           [All Store Type].[Supermarket], [Measures].[Warehouse Profit]))
               ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
    ([Time].[1997]) 

The consumers with whom we are interacting tell us that the query does, indeed, give them the results they want, in the appropriate layout. We determine that we will create an identical query in the Sample Application, upon which we will apply enhancements to tune its performance. We will save each step as a separate query to allow us to "fall back," if necessary, to a previous step, as we incrementally modify the query.

1.  Create the following new query (identical, except for comment line, to the original):


--MDX28-1:  Original Query (Suboptimal)
SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {CROSSJOIN([Warehouse].[Warehouse Name].Members,
CROSSJOIN([Store].[Store Name].Members, 
       [Product].[Product Name].Members))}, 15, ([Store Type].
           [All Store Type].[Supermarket], [Measures].[Warehouse Profit]))
               ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
    ([Time].[1997]) 

2.  Execute the query using the Run Query button.

After running for a few seconds, the results dataset appears as depicted in Illustration 9.


Illustration 9: The Results Dataset - Original Approach

3.  Save the query as MDX28-1.

Based upon what we know about the CROSSJOIN() function, we can readily see that the query above can be optimized. First, we note that the query as originally written creates every Warehouse Name (24), Store Name (24), and Product Name (1,560) combination. This means, once again, that the query generates 898,560 combinations, many of which (as we mentioned above) are empty, a frequent occurrence in CROSSJOIN() results. Furthermore, in addition to taking the time and resources to generate all combinations, we are next performing our TOPCOUNT() function on all combinations, most of which, again, are empty in the first place.

Let's take the first step to make the query more efficient. We will substitute the NONEMPTYCROSSJOIN() function for the CROSSJOIN(). Our objective will be to filter out the empties. This alone will give us a significant boost in processing speed.

4.  Create the following new query:


--MDX28-2:  Introduce NONEMPTYCROSSJOIN() as initial improvement
SELECT 
    {[Measures].[Warehouse Profit]} ON COLUMNS,
   TOPCOUNT( {NONEMPTYCROSSJOIN([Warehouse].[Warehouse Name].Members,
        [Store].[Store Name].Members, 
                [Product].[Product Name].Members)} , 15, 
                   ( [Store Type].[All Store Type].[Supermarket], 
                       [Measures].[Warehouse Profit])) ON ROWS
FROM  
   [MDX27_CROSSJOIN_TESTING]
WHERE
   ([Time].[1997])


MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date