Relationships often exist between Dimension Members in different Dimensions. For example, employees may belong to a particular department; some products are only sold in certain countries; or a set of accounts are only used within a particular entity. To prevent end users from accidentally accessing a combination of Dimension Members that shouldn't be valid, we can define relationships using Linked Models.
We first need a place to store the information that defines the relationship. The standard approach is to create a Validation Model. A Validation Model is a regular Model that is used to map the relationships between Dimensions Members.
1. Go to the All Models node and select Add.
2. Name the Model Validation.
3. Select Add to include relevant Dimensions.
Below is an example of a Validation Model which is used to map Brands to Entities:
Tip: While you could create a separate Validation Model for every relationship, it's considered a best practice to use a single Model with Multiple Measure Groups, instead.
A relationship between Dimension Members is determined by whether or not a record exists in the Model with the relevant Members. In the example below, we can see that the brands Adair, Aviva, and Harper are valid Brands within the United States, while Zofia isn't.
Note: The values used in these Models are not important. By convention the number 1.00 is used, but what's important is that a record exists, not the value itself.
Adding Relationships to Forms
Once you have a Validation Model configured, you can link it to another Model using the Linked Models feature.
1. Go to Advanced > Relationship to can create a new relationship.
2. Click Add.
3. Select relevant Measures from Linked Models and click Add.
The format for the naming here is ModelName_MeasureName. By selecting the previously configured Validation_Value measure, we can apply the relationship defined in the Validation Model.
4. Select to choose where to apply the relationship.
If we want to limit which brands can be selected based on the entity, we would apply this relationship to the Brand Filter. If we instead applied the relationship to the Entity Filter, we would see the inverse—i.e., the selectable entities would be limited by the chosen brand.
The key to defining valid relationships is building and maintaining the records in the Validation Model. You can do this by:
- Uploading data to the Model from a .csv file
- Generate data via a Data Rule or SQL query
- Define data manually via an input Form.
Below are a few examples of common configurations: