In this section: |
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 manual.
The following list shows the rules for creating a report request against a multi-fact cluster Master File.
The following request against the WFLITE 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 WFLITE 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.
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.
JOIN AS_ROOT sfld1 [AND sfld2 ...] IN [app1/]sfile TO UNIQUE tfld1 [AND tfld2 ...] IN [app2/]tfile AS jname END
where:
Are fields in the child (dimension) segment of the source file that match values of fields in the target file.
Is the source file.
Are fields in the target file that match values of fields in the child segment of the source file. The join must be unique.
Is the target file.
Is the join name.
Is required to end the JOIN command.
The following request joins the product category and product subcategory fields in the WebFOCUS Retail data source to a Db2 table named PROJECTD.
The Master File for the Db2 table is:
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 Db2 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
How to: |
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.
You can use the following syntax to set the blend mode parameter.
ENGINE INT SET BLEND-MODE {COMMON-VALUES|ALL-VALUES}
where:
Generates an inner join of cluster synonym contexts and returns only matching values of the shared dimension fields. This is the default value.
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.
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:
The following Master File describes a multi-parent structure. The two fact tables sales and wf_retail_shipments are parents of the dimension table product.
FILENAME=WFMULTI, $ SEGMENT=WF_RETAIL_SHIPMENTS, CRFILE=SHIPMENT, CRINCLUDE=ALL, DESCRIPTION='Shipments Fact', $ SEGMENT=WF_RETAIL_SALES, PARENT=., CRFILE=SALES, CRINCLUDE=ALL, DESCRIPTION='Sales Fact', $ SEGMENT=WF_RETAIL_PRODUCT, CRFILE=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 request joins the product segment to the dimension table vendor based on the vendor ID and issues a request against the joined structure:
JOIN ID_VENDOR IN WFMULTI TO ID_VENDOR IN VENDOR AS J1 TABLE FILE WFMULTI SUM COGS_US DAYSDELAYED BY PRODUCT_CATEGORY BY VENDOR_NAME WHERE PRODUCT_CATEGORY LT 'S' ON TABLE SET PAGE NOPAGE END
The output is:
Product Days Category Vendor Name Cost of Goods Delayed -------- ----------- ------------- ------- Accessories Audio Tech $38,000.00 28 Denon $25,970.00 17 Grado $21,930.00 15 Logitech $61,432.00 114 Niles Audi $73,547.00 150 Pioneer $16,720.00 71 Samsung $5,405.00 83 Sennheiser $78,113.00 128 Sony $21,760.00 157 Camcorder Canon $110,219.00 97 JVC $72,292.00 75 Panasonic $22,356.00 91 Sanyo $31,590.00 179 Sony $216,748.00 333 Computers Samsung $33,129.00 156 Sony $76,152.00 186 Media Player JVC $87,057.00 267 LG $3,830.00 13 Panasonic $143,600.00 171 Pioneer $169,810.00 206 Roku $10,248.00 85 Samsung $151,620.00 191 Sharp $66,024.00 157 Sony $142,190.00 121 Toshiba $5,214.00 7
|
Information Builders |