Generating Outer Joins of Cluster Synonym Contexts

Reporting against multiple root segments and a shared dimension generates multiple contexts in a cluster synonym. For example, in the following image Sales and Products form one context, while Shipments and Products form a second context.

When a request contains fields from both contexts, by default, an inner join is passed to the SQL engine. This retrieves only matching values of the shared dimension fields from both contexts.

You can use the BLEND-MODE parameter to generate a full outer join instead of an inner join and retrieve all values from both contexts.

Control Join Processing of Cluster Synonym Contexts

You can set the blend mode parameter from the server Web Console and store the setting in a profile or procedure. On the Adapters page, click Change Common Adapter Settings on the ribbon, and select Select all values from the BLEND-MODE drop-down list in the Request Transformation Settings section, as shown in the following image.

You can also use the following syntax to set the blend mode parameter.

ENGINE INT SET BLEND-MODE {COMMON-VALUES|ALL-VALUES}

where:

COMMON-VALUES

Generates an inner join of cluster synonym contexts and returns only matching values of the shared dimension fields. This is the default value.

ALL-VALUES

Generates a full outer join of cluster synonym contexts and returns all values of the shared dimension fields. Missing values are returned for fields from contexts that do not have a matching value of the shared dimension fields.

Controlling Join Processing of Cluster Synonym Contexts

The following Excel file (excelroot.xlsx) will be uploaded to the server using the Adapter for Excel and joined as a root to the WF_RETAIL Master File, creating two contexts. A report request will then be issued against the two roots and the shared dimension.

Note that this file has no data for product categories Camcorder, Stereo Systems, and Video Production. It has a product category named Displays that does not exist in WF_RETAIL.

The following is the Master File generated for this Excel file.

FILENAME=EXCELROOT, SUFFIX=DIREXCEL,
 DATASET=ibisamp/excelroot.xlsx, $
  SEGMENT=EXCELROOT, SEGTYPE=S0, $
    FIELDNAME=PRODUCT_CATEGORY, ALIAS='Product Category', USAGE=A15V, ACTUAL=A15V,
      MISSING=ON,
      TITLE='Product Category', $
    FIELDNAME=PRODUCT_SUBCATEGORY, ALIAS='Product Subcategory', USAGE=A31V, ACTUAL=A31V,
      MISSING=ON,
      TITLE='Product Subcategory', $
    FIELDNAME=PROJECTED_COG, ALIAS='Projected COG', USAGE=D15.2:C, ACTUAL=A64V,
      MISSING=ON,
      TITLE='Projected COG',
      CURRENCY_DISPLAY=LEFT_FLOAT,  CURRENCY_ISO_CODE=USD,  $
    FIELDNAME=PROJECTED_SALE_UNITS, ALIAS='Projected Sale Units', USAGE=I9, ACTUAL=A11V,
      MISSING=ON,
      TITLE='Projected Sale Units', $

The following request joins the Excel file as a root and generates a report that contains fields from both roots and the shared dimension. Using the default value for BLEND-MODE produces an inner join that returns only common values of PRODUCT_CATEGORY.

JOIN AS_ROOT PRODUCT_CATEGORY AND PRODUCT_SUBCATEG IN ibisamp/WF_RETAIL
  TO PRODUCT_CATEGORY AND PRODUCT_SUBCATEGORY IN ibisamp/EXCELROOT
  AS J1
  END
TABLE FILE ibisamp/WF_RETAIL
SUM COGS_US PROJECTED_SALE_UNITS
BY PRODUCT_CATEGORY
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image.

The following version of the request issues the ENGINE INT SET BLEND-MODE ALL-VALUES command to produce a full outer join that returns all values of PRODUCT_CATEGORY.

ENGINE INT SET BLEND-MODE ALL-VALUES
JOIN AS_ROOT PRODUCT_CATEGORY AND PRODUCT_SUBCATEG IN ibisamp/WF_RETAIL
  TO PRODUCT_CATEGORY AND PRODUCT_SUBCATEGORY IN ibisamp/EXCELROOT
  AS J1
  END
TABLE FILE ibisamp/WF_RETAIL
SUM COGS_US PROJECTED_SALE_UNITS
BY PRODUCT_CATEGORY
ON TABLE SET PAGE NOPAGE
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image. Note the missing value indicators:

  • For the Projected Sale Units field in the rows that correspond to product categories Camcorder, Stereo Systems, and Video Production, which are not represented in the Excel file.
  • For the Cost of Goods field in the row that corresponds to product category Displays, which is not represented in the WF_RETAIL data source.