Reporting Against a Multi-Fact Cluster 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 ibi™ 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.

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