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

How to:

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.

Top of page
x
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:

WebFOCUS