There are occasions when you may need to specify more than one field to create unique values in the cubes. Some examples of this are:
- 2 or more companies share a numbering series for a master record (customers, vendors, etc.) but each value is unique to that company
- A value is only unique when 2 or more fields match, such as customer number and ship-to code
This KB article will walk you through the process of creating a composite primary key in a dimension and defining the relationships within the cubes.
To begin, select the dimension that needs to have the the composite primary key set up on it. This is done on the Cubes tab of the Jet Data Manager under the "Database" level dimensions. Find the dimension and expand the node to expose the dimension levels. Locate the level that has an icon that includes a key and open the edit dialog of the level by right-clicking and selecting "Edit Dimension Level" from the context menu.
This will open the properties dialog for the dimension level. Once the box is open as shown below, click on the ellipse (...) button next to the "Key Column" field.
This will open a new dialog box that shows the current keys for that dimension level.
In this box, select the additional fields that will form the composite primary key.
Once you have all the fields selected (likely only 2), click the OK button and OK on the Dimension Level dialog to return to the Jet Data Manager Cubes tab. Changing the primary key on the dimension level will break relations with all cubes and those relations must be reset to use the new key.
Navigate to the cubes that use the dimension and open the Dimensions node for each. You will see the dimension that was modified now showing a yellow warning symbol.
This will open a new dialog in which you will see the levels that exist within the dimensions. You will see a red warning icon in one of the KEY levels.
Select the appropriate field from the drop-down list to create the relations between the dimension and the Fact tables that make up the Cube.
Once this process is completed for all instances of the dimension in the cube, click OK to close the window. You will now see that the dimension that was repaired shows a standard dimension icon.
Repeat this process as necessary for any remaining instances of the dimension in the Cubes. Once you are done, ensure that you do a full Deploy and Execute of the Cubes.