Creating a Multiple-Root Cluster Master File

How to:

Define a Multi-Fact Cluster

A cluster Master File is a Master File 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.

Each fact table that is a root of the cluster must have a PARENT=. attribute in the Master File to identify it as a root segment.

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). Each shared dimension has multiple PARENT attributes in the Master File.

The following image shows a simple multi-fact structure.

For information about reporting against a multi-fact Master File, see the Creating Reports manual.

Syntax: How to Define a Multi-Fact Cluster

Each root segment description must have a PARENT=. attribute in the Master File. The following syntax describes the attributes necessary to define a root segment in a multi-fact cluster. All other segment attributes are also supported.

SEGMENT=rsegname, PARENT=., CRFILE=[rapp/]rfilename,
         CRINCLUDE=ALL,$

where:

SEGMENT=rsegname: Is the name of the root segment.
PARENT=.: Defines this segment as a root segment in a multi-fact cluster.
CRFILE=[rapp/]rfilename: Is the optional application path and the name of the Master File where the root fact table is described.
CRINCLUDE=ALL: Makes all fields from the fact table accessible using this cluster Master File. If you omit this attribute, you must list the fields from the fact table that you want to be accessible using this Master File.

The following is an example of a root segment description from the WFLITE Master File that is in the wfretail application.

SEGMENT=WF_RETAIL_SALES, PARENT=., CRFILE=wfretail/facts/wf_retail_sales,
      CRINCLUDE=ALL, DESCRIPTION='Sales Fact', $

Each shared dimension must have multiple PARENT attributes in the Master File and a JOIN WHERE attribute for each parent. The following syntax describes the attributes necessary to define a shared dimension in a multi-fact cluster Master File.

 SEGMENT=dsegname, CRFILE=[dapp/]dfilename,
          [CRSEGMENT=crsegname,] CRINCLUDE=ALL,$
     PARENT=parent1, SEGTYPE=KU, CRJOINTYPE=jointype1,
       JOIN_WHERE=expression1;, $
    PARENT=parent2, SEGTYPE=KU, JOIN_TYPE=jointype2,
       JOIN_WHERE=expression2;,
    . . . $

where:

SEGMENT=dsegname

Is the name of the shared dimension segment.

CRFILE=[dapp/]dfilename

Is the optional application path and the name of the Master File where the dimension table is described.

CRSEGMENT=crsegname

Is the name of the segment to which to join in the dimension Master File. This is optional if the dimension Master File has a single segment.

CRINCLUDE=ALL

Makes all fields from the dimension table accessible using this cluster Master File. If you omit this attribute, you must list the fields from the fact table that you want to be accessible using this Master File.

PARENT=parent1 PARENT=parent2 ...

Are the names of the parent segments of the shared dimension.

CRJOINTYPE=jointype1

Is a supported join type for the join between the shared dimension and the first parent segment. Valid values are INNER, LEFT-OUTER, RIGHT-OUTER, FULL-OUTER. The type of join specified must be supported by the relational engine in which the tables are defined.

JOIN_TYPE=jointype2

Is a supported join type a join between the shared dimension and a subsequent parent segment. Valid values are INNER, LEFT-OUTER, RIGHT-OUTER, FULL-OUTER. The type of join specified must be supported by the relational engine in which the tables are defined.

JOIN_WHERE=expression1; JOIN_WHERE=expression2;

Are the join expressions for the joins between each parent segment and the shared dimension.

For a synonym that describes a star schema, each expression usually describes an equality condition (using the EQ operator) and a 1-to-many relationship.

The following is an example of a shared dimension segment definition from the WFLITE Master File, where the synonym is defined in the wfretail application.

SEGMENT=WF_RETAIL_CUSTOMER, CRFILE=wfretail/dimensions/wf_retail_customer,
       CRINCLUDE=ALL, DESCRIPTION='Customer Dimension', $
   PARENT=WF_RETAIL_SALES, SEGTYPE=KU, CRJOINTYPE=LEFT_OUTER,
    JOIN_WHERE=WF_RETAIL_SALES.ID_CUSTOMER EQ
      WF_RETAIL_CUSTOMER.ID_CUSTOMER;, $
   PARENT=WF_RETAIL_SHIPMENTS, SEGTYPE=KU, JOIN_TYPE=LEFT_OUTER,
    JOIN_WHERE=WF_RETAIL_SHIPMENTS.ID_CUSTOMER EQ 
       WF_RETAIL_CUSTOMER.ID_CUSTOMER;, $

The following is an example of a non-shared dimension segment definition from the WFLITE Master File, where the synonym is defined in in the wfretail application.

SEGMENT=WF_RETAIL_TIME_DELIVERED, SEGTYPE=KU, PARENT=WF_RETAIL_SHIPMENTS, 
      CRFILE=wfretail/dimensions/wf_retail_time_lite, 
      CRSEGMENT=WF_RETAIL_TIME_LITE, 
      CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
      JOIN_WHERE=ID_TIME_DELIVERED EQ WF_RETAIL_TIME_DELIVERED.ID_TIME;,
      DESCRIPTION='Shipping Time Delivered Dimension',
      SEG_TITLE_PREFIX='Delivery,', $