Reporting Against a Multi-Fact Cluster Synonym

In this section:

Adding a New Fact To Multi-Fact Synonyms: JOIN AS_ROOT
Generating Outer Joins of Cluster Synonym Contexts
Joining From a Multi-Fact Synonym

A cluster synonym is a synonym in which each segment is added to the cluster by reference using a CRFILE attribute that points to the base synonym. Child segments are joined to their parents using a JOIN WHERE attribute. A cluster Master File can have multiple root segments. In this case, the root segments are usually fact tables and the child segments are usually dimension tables, as found in a star schema. This type of structure is called a multi-fact cluster.

A dimension table can be a child of multiple fact tables (called a shared dimension) or be a child of a single fact table (called a non-shared dimension). In most cases, the fact tables are used for aggregation and the dimension tables are used for sorting.

The following image shows a simple multi-fact structure.

For information about creating a multi-fact cluster Master File, see the Describing Data With TIBCO WebFOCUS® Language manual.

The following list shows the rules for creating a report request against a multi-fact cluster Master File.

You can report against only the fact tables, as long as you aggregate (SUM) at least one fact from each fact table and have at most one sort phrase.
The first sort field in the request must be from a shared dimension.
Any number of shared dimensions can be referenced in the request.
Multiple non-shared dimensions can be included in the request, as long as they have the same parent. More than one non-shared dimension from different parents cannot be referenced in a request.
The MATCH FILE command is not supported for reporting against a multi-fact synonym.

Example: Reporting Against a Multi-Fact Cluster Synonym

The following request against the WF_RETAIL_LITE multi-fact cluster synonym sums the COGS_US measure from the WF_RETAIL_SALES segment and the DAYSDELAYED measure from the WF_RETAIL_SHIPMENTS segment. The first BY field, BRAND, is in the shared dimension WF_RETAIL_PRODUCT. The second BY field, TIME_QTR, is from the non-shared dimension WF_RETAIL_TIME_DELIVERED.

TABLE FILE WF_RETAIL_LITE
SUM COGS_US DAYSDELAYED 
BY BRAND
BY WF_RETAIL_TIME_DELIVERED.TIME_QTR
WHERE BRAND EQ 'Denon' OR 'Grado'
WHERE DAYSDELAYED GT 1
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
END

The output is shown in the following image. The sum of DAYSDELAYED is totaled for each value of the shared dimension and, within each value of the shared dimension, for each value of the non-shared dimension.

Adding a New Fact To Multi-Fact Synonyms: JOIN AS_ROOT

How to:

Add an Additional Parent Segment

The JOIN AS_ROOT command adds a new fact table as an additional root to an existing fact-based cluster (star schema). The source Master File has a parent fact segment and at least one child dimension segment. The JOIN AS_ROOT command supports a unique join from a child dimension segment (at any level) to an additional fact parent.

Syntax: How to Add an Additional Parent Segment

JOIN AS_ROOT sfld1 [AND sfld2 ...] IN [app1/]sfile TO UNIQUE tfld1 [AND tfld2 ...] IN [app2/]tfile AS jname    
END

where:

sfld1 [AND sfld2 ...]: Are fields in the child (dimension) segment of the source file that match values of fields in the target file.
[app1/]sfile: Is the source file.
TO UNIQUE tfld1 [AND tfld2 ...]: Are fields in the target file that match values of fields in the child segment of the source file. The join must be unique.
[app2/]tfile: Is the target file.
jname: Is the join name.
END: Is required to end the JOIN command.

Example: Joining AS_ROOT From the WebFOCUS Retail Data Source to an Excel File

The following request joins the product category and product subcategory fields in the WebFOCUS Retail data source to an Excel file named PROJECTED.

To generate the WebFOCUS Retail data source in the Web Console, click Tutorials from the Applications page.

Select WebFOCUS - Retail Demo. Select your configured relational adapter (or select the flat file option if you do not have a relational adapter configured), check Limit Tutorial Data, and then click Create.

The Master File for the Excel File is:

FILENAME=PROJECTED, SUFFIX=DIREXCEL,
 DATASET=app2/projected.xlsx, $
  SEGMENT=PROJECTED, SEGTYPE=S0, $
    FIELDNAME=PRODUCT_CATEGORY, ALIAS='Product  Category', USAGE=A16V, ACTUAL=A16V,
      MISSING=ON,
      TITLE='Product  Category',
      WITHIN='*PRODUCT', $
    FIELDNAME=PRODUCT_SUBCATEGORY, ALIAS='Product     Subcategory', USAGE=A25V, ACTUAL=A25V,
      MISSING=ON,
      TITLE='Product     Subcategory',
      WITHIN=PRODUCT_CATEGORY, $
    FIELDNAME=PROJECTED_COG, ALIAS='              Projected COG', USAGE=P15.2C, ACTUAL=A15,
      MISSING=ON,
      TITLE='              Projected COG', MEASURE_GROUP=PROJECTED,
      PROPERTY=MEASURE,  $
    FIELDNAME=PROJECTED_SALE_UNITS, ALIAS='             Projected Sale Units', USAGE=I9, ACTUAL=A11,
      MISSING=ON,
      TITLE='             Projected Sale Units', MEASURE_GROUP=PROJECTED,
      PROPERTY=MEASURE,  $
 MEASUREGROUP=PROJECTED, CAPTION='PROJECTED', $
 DIMENSION=PRODUCT, CAPTION='Product', $
  HIERARCHY=PRODUCT, CAPTION='Product', HRY_DIMENSION=PRODUCT, HRY_STRUCTURE=STANDARD, $

The following image shows the data in the Excel file.

The following request joins from the wf_retail_product segment of the wf_retail data source to the excel file as a new root and reports from both parent segments:

JOIN AS_ROOT PRODUCT_CATEGORY AND PRODUCT_SUBCATEG IN WF_RETAIL
  TO UNIQUE PRODUCT_CATEGORY AND PRODUCT_SUBCATEGORY IN PROJECTED 
  AS J1
END
TABLE FILE WF_RETAIL
SUM PROJECTED_SALE_UNITS REVENUE_US 
BY PRODUCT_CATEGORY
ON TABLE SET PAGE NOPAGE
END

The output is:

Generating Outer Joins of Cluster Synonym Contexts

How to:

Control Join Processing 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.

Syntax: How to 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.

Example: 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.

Joining From a Multi-Fact Synonym

Multi-parent synonyms are now supported as the source for a join to a single segment in a target synonym.

A join from a multi-parent synonym is subject to the following conditions:

Conditional joins are not supported (JOIN WHERE).
The join must be unique. That is, the TO ALL or TO MULTIPLE phrase is not supported.
The target of the join cannot be a multi-parent synonym.
The target of the JOIN must be a single segment, either in a single segment synonym or one segment in a single parent, multi-segment synonym.
All fields in the JOIN must be FROM/TO a single segment. Any single segment in the source synonym can be used in the join.

Example: Joining From a Multi-Fact Synonym

The following Master File describes a multi-parent structure based on the WebFOCUS Retail tutorial. The two fact tables wf_retail_sales and wf_retail_shipments are parents of the dimension table wf_retail_product.

FILENAME=WF_RETAIL_MULTI_PARENT, $
  SEGMENT=WF_RETAIL_SHIPMENTS, CRFILE=WFRETAIL/FACTS/WF_RETAIL_SHIPMENTS, CRINCLUDE=ALL,
    DESCRIPTION='Shipments Fact', $
  SEGMENT=WF_RETAIL_SALES, PARENT=., CRFILE=WFRETAIL/FACTS/WF_RETAIL_SALES, CRINCLUDE=ALL,
    DESCRIPTION='Sales Fact', $
  SEGMENT=WF_RETAIL_PRODUCT, CRFILE=WFRETAIL/DIMENSIONS/WF_RETAIL_PRODUCT, CRINCLUDE=ALL,
    DESCRIPTION='Product Dimension', $
   PARENT=WF_RETAIL_SHIPMENTS, SEGTYPE=KU,
    JOIN_WHERE=WF_RETAIL_SHIPMENTS.ID_PRODUCT EQ WF_RETAIL_PRODUCT.ID_PRODUCT;, $
   PARENT=WF_RETAIL_SALES, SEGTYPE=KU,
    JOIN_WHERE=WF_RETAIL_SALES.ID_PRODUCT EQ WF_RETAIL_PRODUCT.ID_PRODUCT;, $

The following image shows the joins between these tables in the Synonym Editor of the Data Management Console (DMC).

The following request joins the product segment to the dimension table wf_retail_vendor based on the vendor ID and issues a request against the joined structure:

JOIN ID_VENDOR IN WF_RETAIL_MULTI_PARENT TO ID_VENDOR IN WF_RETAIL_VENDOR AS J1
TABLE FILE WF_RETAIL_MULTI_PARENT
SUM COGS_US DAYSDELAYED
BY PRODUCT_CATEGORY
BY VENDOR_NAME
WHERE PRODUCT_CATEGORY LT 'S'
ON TABLE SET PAGE NOPAGE
END

The output is: