Define OLAP table references and many-to-many cardinality

Select the analytical object

In the dataflow diagram, locate the intended object under Datamart layer and right-click it. This area contains the analytical objects in the Data Mart Layer (DM). Check the object's schema and name before opening its context menu.

Expand the References menu

Point to References in the object's context menu to expand the submenu.

Open the reference definition

Click Add reference. The reference definition opens.

Select both tables and their aliases

On the reference page, Table 1 and Table 2 identify the two sides of the relationship. Each row has a schema selection followed by a table selection. Select the schema and table for each side before choosing the direction of the cardinality and join.

If your validated relationship expression uses aliases, enter each alias in the Alias field beside the corresponding table. An alias is the identifier used to refer to that table in an expression; use the same identifier consistently. If the design does not use aliases, leave these fields blank.

Check: both schema and table names must identify the intended objects. The pictured schema STAR and table DIM_Categories are examples, not required selections.

Select the cardinality and join

In Cardinality, select the value that describes how many rows can match between the tables selected in Select both tables and their aliases. The direction is from Table 1 to Table 2. For the many-to-many task, select ManyToMany after confirming that neither side is unique under the relationship key.

1. OneToOne — Each side has at most one matching row under the intended relationship key.
2. OneToMany — One row on Table 1 can match multiple rows on Table 2.
3. ManyToOne — Multiple rows on Table 1 can match one row on Table 2.
4. ManyToMany — Multiple rows on both sides can match. Validate the intended model and aggregation behavior; selecting this label alone does not prove a correct many-to-many analytical design.
5. Unknown — The relationship cardinality has not been specified. Do not use it as evidence of key uniqueness.

In Join, select how the tables should be combined. Base this choice on whether the result needs only matching rows or also rows that have no match. A join does not establish the uniqueness described by Cardinality.

1. INNER JOIN — Retains matching combinations from both sides.
2. LEFT JOIN — Retains Table 1 rows, with matching Table 2 data where available.
3. RIGHT JOIN — Retains Table 2 rows, with matching Table 1 data where available.
4. FULL JOIN — Retains rows from both sides, including unmatched rows.
5. CROSS JOIN — Combines every row from one side with every row from the other; it is not a key-based match.

Example: when every row in Table 1 must remain even if Table 2 has no match, LEFT JOIN expresses that requirement. This is separate from whether each Table 1 row matches one or several Table 2 rows.

Enter the relationship expression when required

If your relationship design includes a complete expression, enter it in Reference Statement. This field holds the relationship expression. Check every table identifier, alias, and column against the objects selected in Select both tables and their aliases. For a design based only on column mappings, continue to Map the participating columns without entering an expression here.

Expression example: the pictured text compares Orders.CustomerID with Customers.CustomerID and also compares their CompanyID values. The AND requires both comparisons to hold. However, the capture selects DIM_Categories and DIM_Calendar, so the pictured expression is not a valid template for those selected tables. Use the validated expression for your actual relationship.

Map the participating columns

In the Columns grid, select the relationship column from Table 1 in Column1 and its corresponding column from Table 2 in Column2, on the same row. Repeat for each column pair required by the relationship design. Match columns by their business meaning and compatible values, not merely by their names or positions in the list.

If the validated design includes an expression for a particular side of a pair, enter the complete expression in Statement1 for Table 1 or Statement2 for Table 2. Leave a statement cell blank when no expression is required for that side. Keep any aliases consistent with Select both tables and their aliases.

Check: the pictured CategoryID/CurrentDate pair does not demonstrate a meaningful key relationship. Confirm that each selected pair relates the intended records and that all parts of a multi-column relationship are included.

Review and save the reference

Review the selected tables, aliases, cardinality, join, expressions, and column pairs. Check Description so that it identifies the intended relationship; if it needs clarification, enter a meaningful description.

Inheritance concerns how references are inherited by dependent objects. Its choices are Default, No inherit, and Force inherit. If the task includes an inheritance change, use the choice specified by the project's relationship design; otherwise leave the current selection unchanged. The pictured Default selection is an example, not a universal requirement.

If Used in transformations lists existing uses, review the displayed Schema and Transformation columns to identify the affected objects. A Transformation combines input objects and applies processing logic to produce a reusable result. This list provides usage information; it does not show whether a Transformation has executed.

Click Save at the bottom of the reference page. Verify the saved definition as described in Expected Results.

Assign the saved reference for OLAP use

Follow this step when the saved relationship must be used by an analytical table column. If the task is only to define the table reference, proceed to Expected Results.

4.9.1. In the navigation tree, expand Layers, Datamart layer, and the schema containing the intended analytical table. Right-click Tables and click List tables. Find the required table by its schema and name, then double-click its row.

4.9.2. On the table's Columns tab, locate the column participating in the saved reference. Scroll horizontally if necessary to reach OLAP Reference and select the intended existing column reference.

1. OLAP Reference — Selects an existing column reference for use in the analytical model. Confirm that it identifies the relationship just saved, rather than another reference involving the column.
2. 2-sided — For tabular OLAP models only, selecting this checkbox requests a two-sided reference. Select it when the model design calls for a two-sided relationship; clear it when that design calls for a one-sided relationship. This setting does not select ManyToMany cardinality. For other model types, leave it unchanged.

4.9.3. Click Save on the table page to save the OLAP assignment. Repeat for other participating columns only when the relationship design requires an assignment for them.