Author: Ian Britz
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.
To begin, 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. 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 is not.
Note: The values used in these Models is not important. By convention the number 1 is used, but what's important is that a record exists, not what the value is.
Adding Relationships to Forms
Once you have a Validation Model configured, you can link it to another Model using the Linked Models feature. Then under the RELATIONSHIP tab, in the ADVANCED section a Form, you can create a new relationship.
From here, you can select any measures from the Models that have been linked through the LINKED MODELS tab. 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.
Then you can choose where to apply this 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 which Brand was chosen.
The key to defining valid relationships is building and maintaining the records in the Validation Model. There are a number of different approaches to doing so. The data can be uploaded into the Model from a .csv file; it can be generated via a Data Rule or SQL query; or it can be defined manually via an input Form. Below are a few examples of common configurations: