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

Procedure

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:

We are initially greeted by the Connect dialog, shown in Illustration 14.

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

The MDX Sample Application window appears.

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).

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:

SELECT      
    {[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
FROM  
   [MDX30 Optimize CrossJoin]
WHERE
    ([Time].[1998])

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.

--MDX30-01:  Original Query (Suboptimal)
SELECT      
    {[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
FROM  
   [MDX30 Optimize CrossJoin]
WHERE
    ([Time].[1998])

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.