Relationship tables (or hierarchy tables)
A relationship table stores parent-child relations of a dimension. This can also be done in a member table.
The table can have any name. The name is stored in _Dimensions.Param2 or _Hierarchies.Param2.
This table describes the relationship table:
| Column | Type | Description |
|---|---|---|
| MemberName | String(71) | Name of child element. Must be present in member table. |
| ParentMemberName | String(71) | Name of parent element. Must be present in member table. |
| Weight | Real | The weighting given to data on this element within this parent-child relationship. If NULL, take the value to be 1. |
| MemberParentOrder | Integer | The numeric position of the child element among the parent's children. If NULL, take the value to be 0. |
General information
This table is an alternative to using its corresponding member table to store relationships. But if this is used, it is more flexible. A child can have any number of parents. A parent can have any number of children in any case; this also holds if the member table is used.
The name of this table must be in _Dimensions.Param2 in the row for the dimension whose name is found in DimensionName.
Optionally, columns can be used to describe the dimension's element hierarchy. If used, they must be identical to the columns of the relations table and would be used as an alternative to using a separate relations table. The advantage of placing the relations here (and avoiding a separate table) is system simplicity. The disadvantage is that this does not allow multiple parents of an element. The name of this table must be in _Dimensions.Param2 in the row for the dimension whose name is found in DimensionName or in _Hierarchies.Param2 in the row for the hierarchy whose name is found in HierarchyName.
A dimension is loaded with its relationships using JobType 1 in _Jobs with the dimension name in _Jobs.JobObject. The relationships are loaded automatically with the elements where these relationships exist and if the name of this table is in _Dimensions.Param2 or in _Hierarchies.Param2.
Relationship table creation with SQL Server
CREATE TABLE [dbo].[my_relationship_table /* Generic name */](
[MemberName] [nvarchar](71) NOT NULL,
[ParentMemberName] [nvarchar](71) NULL,
[Weight] [real] NULL,
[MemberParentOrder] [int] NULL
)