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 Another Table

The following request joins the product category and product subcategory fields in the WebFOCUS Retail data source to a table named PROJECTD.

The Master File for the PROJECTD table is:

FILENAME=PROJDB2 , SUFFIX=DB2     , $                                     
  SEGMENT=SEG01, SEGTYPE=S0, $                                            
    FIELDNAME=PRODUCT_CATEGORY, ALIAS='Product Category', USAGE=A16, 
      ACTUAL=A16, MISSING=ON,  TITLE='Product Category',
      WITHIN='*PRODUCT',$
    FIELDNAME=PRODUCT_SUBCATEGORY, ALIAS='Product Subcategory',USAGE=A25,
      ACTUAL=A25,  MISSING=ON,      TITLE='Product Subcategory',        
      WITHIN=PRODUCT_CATEGORY, $                                          
    FIELDNAME=PROJECTED_COG, ALIAS=' Projected COG', USAGE=D12, 
       ACTUAL=D8,   MISSING=ON,     
      TITLE=' Projected COG', MEASURE_GROUP=PROJECTED, 
      PROPERTY=MEASURE,  $  
    FIELDNAME=PROJECTED_SALE_UNITS, ALIAS=' Projected Sale.Units', 
       USAGE=I9, ACTUAL=I4, MISSING=ON,         
      TITLE=' Projected Sale Units', MEASURE_GROUP=PROJECTED,    
      PROPERTY=MEASURE,  $                                                
 DIMENSION=PRODUCT, CAPTION='Product', $                                  
  HIERARCHY=PRODUCT, CAPTION='Product', HRY_DIMENSION=PRODUCT, $ 
 MEASUREGROUP=PROJECTED, $

The following image shows the data in the PROJECTD table.

Product Category  Product Subcategory          Projected COG   Projected
                                                               Sale_Units
----------------  -------------------         --------------  -----------
Accessories       Charger                          2,068,508        75279        
Accessories       Headphones                      52,061,301       163152 
Accessories       Universal Remote Controls       36,297,267       127286
Camcorder         Handheld                        20,733,053       178704
Camcorder         Professional                    35,440,708         9095
Camcorder         Standard                        49,442,067       137489
Computers         Smartphone                      44,420,201       146858
Computers         Tablet                          26,047,885       105053
Media Player      Blu Ray                        182,459,862       485131
Media Player      DVD Players                      3,756,254        13346
Media Player      DVD Players - Portable             306,576         3981
Media Player      Streaming                        5,108,342        48630
Stereo Systems    iPod Docking Station            26,310,783       221723
Stereo Systems    Boom Box                           840,373         6687
Stereo Systems    Home Theater Systems            56,829,817       285041
Stereo Systems    Receivers                       40,620,030       107537
Stereo Systems    Speaker Kits                    81,962,756       174036
Televisions       CRT TV                           1,928,416         3268
Televisions       Flat Panel TV                   59,540,624        66119
Televisions       Portable TV                        545,348         5696
Video Production  Video Editing                   40,380,803       142594

The following request joins from the WF_RETAIL_PRODUCT segment of the WFLITE data source to the PROJECTD table as a new root and reports from both parent segments:

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

The output is:

 Product                                                                
 Category           Projected Sale Units                     Revenue    
 --------          ---------------------                     -------    
 Accessories                      365717                 $499,551.40    
 Camcorder                        325288                 $667,254.48    
 Computers                        251911                 $179,761.46    
 Media Player                     551088               $1,009,790.56    
 Stereo Systems                   795024               $1,216,393.89    
 Televisions                       75083                 $286,160.68    
 Video Production                 142594                 $259,179.84
Information Builders