Using Outer References in a Query

OLE DB Programmer's Reference

In a multicube database, available data is segmented into various subcubes. For example, the data for actual sales and the data for budgeted sales may be in two subcubes. In such situations, it is useful to have the ability to reference a member (or in general, a tuple) from a cube that is not part of the FROM clause of an MDX statement.

In this example using Actual Sales and Budgeted Sales cubes, suppose the goal is to obtain the actual sales of all products that exceeded their budgeted sales. In this query, data is being retrieved from the Actual Sales cube but there is a filter condition on a tuple from the Budgeted Sales cube. The MDX statement for this query looks as follows:

SELECT FILTER(Products.MEMBERS, [Measures].[Sales] >    [Budgeted Sales].[Measures].[Sales]) ON COLUMNS,    Geography.MEMBERS ON ROWS FROM [Actual Sales] WHERE ([1997])

An outer reference in the FILTER function is made to the Sales member of the Measures dimension of the Budgeted Sales cube. The outer reference just refers to one coordinate of the Budgeted Sales cube. Like the other coordinates, it is derived from the MDX statement for the dimensions that are common between Actual Sales and Budgeted Sales cubes. The coordinate is the default member of that dimension for dimensions in the Budgeted Sales cube that are not in the Actual Sales cube.

1998-2001 Microsoft Corporation. All rights reserved.