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 Apr 4, 2005

MDX Essentials: Enhancing CROSSJOIN() with Calculated Members - Page 7

By William Pearson


Having created a clone cube, complete with the calculated member that appeared in the now-lost production cube, we can pursue our objective 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, shown in Illustration 14.

Click for larger image

Illustration 14: 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 MDX30 OPTIMIZE CROSSJOIN 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 depicted in Illustration 15, complete with the information from the MDX30 OPTIMIZE CROSSJOIN cube displaying in the Metadata tree (left section of the Metadata pane).

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

We will begin creating our query with a focus on returning results efficiently. As we mentioned earlier, we are able to obtain the original query from the development server abandoned by the developer upon his lay off. We have requested the specific requirements for the query from the information consumers, simply to confirm that the query is conceptually sound (it makes little sense to attempt to enhance a query that is not designed to return the correct data in the first place, no matter how efficient its performance).

The consumers explain that they have requested to see a simple summary of 1998 Warehouse Sales, by Product Name, and by Store Country, for a specific Warehouse, Bellmont Distributing in Vancouver, Canada. The query not only meets an immediate need, but will act prospectively as a template for identical queries that will be directed against other Warehouse locations (both singly and in groups).

NOTE: Parameterization will be managed within MSSQL Server Reporting Services, but that is beyond the scope of this article. For a discussion of how this might be handled in general, see my Database Journal article Mastering OLAP Reporting: Cascading Prompts. For an approach whereby the picklists supporting parameterization might be cube-based, see MDX in Analysis Services: Create a Cube-Based Hierarchical Picklist.

The query appears as follows:

    {[Measures].[Warehouse Sales]} ON COLUMNS,
    {CROSSJOIN({[Warehouse].[All Warehouses].[Canada].[BC].[Vancouver].
        [Bellmont Distributing]},
CROSSJOIN([Store].[Store Country].AllMembers, 
       [Product].[Product Name].Members))} ON ROWS
   [MDX30 Optimize CrossJoin]

The consumers with whom we are interacting tell us that the query does, indeed, give them the results they want, in the appropriate general layout (although they would prefer that it did not display line items for Products with no activity: the high volume of "blanks" makes the data output far too lengthy). We determine that we will create an identical query in the MDX 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):

--MDX30-01:  Original Query (Suboptimal)
    {[Measures].[Warehouse Sales]} ON COLUMNS,
    {CROSSJOIN({[Warehouse].[All Warehouses].[Canada].[BC].[Vancouver].
        [Bellmont Distributing]},
CROSSJOIN([Store].[Store Country].AllMembers, 
       [Product].[Product Name].Members))} ON ROWS
   [MDX30 Optimize CrossJoin]

2.  Execute the query using the Run Query button.

After running for up to a minute, perhaps longer on resource-challenged machines, (the query is processing intensive), the topmost section of the results dataset appears as shown in Illustration 16.

Illustration 16: The Results Dataset (Top Section Only) - Original Approach

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