Configure Relationships using Data Rules

Author: Ian Britz

Introduction

In this article, let's walk through the steps to configure a relationship using Data Rules. For more information on relationships and Linked Models, visit the following articles:

• Use Linked Models
• Use Relationships
• Configure Relationships using Input Forms
• Configure Many-To-Many Relationships

 

 

Example

In this example, we have an application with two Dimensions: Department and Employee. Each employee works for a single department, designated by the Department Code attribute in the Employee Dimension.

 

 

The problem is that in our Payroll Planning Form, regardless of which department is selected, we can enter payroll information for any employee-- even if they don't belong to that department. To fix this, let's define a relationship between Department and Employee, so that we can only access employees within the selected department.

 

 

Step 1. First, we need to create a Validation Model to define which combinations of Dimension Members are valid. Let's call it Valid Dep-Emp, and include just the Department and Employee Dimensions. Then deploy the application.

 

 

Step 2. Next, we're going to use this Model as a matrix to define the relationships. Each record in the Model will represent a valid combination. For example, to define all the employees that belong to the Sales & Marketing Department, we would insert the following records:

 

 

Rather than managing these records manually, let's create a simple Data Rule that generates the valid combinations automatically. This way we don't need to worry as new employees and departments are added or removed. We'll create it as a VIEW Rule called Department-Employee Mapping.

 

 

Step 3. Let's bring in both the Department Dimension and the Employee Dimension as our sources. Make sure to include the Department Code attribute for each Dimension. This will give us a common value to map the two together.

 

 

Step 4. Now we want to take these two separate tables and merge them into a single table, structured like the example in Step 2, with the columns DepartmentID, EmployeeID, and Value. To do this, we can select a Join component, and join the tables where the Department Code attribute from an employee matches the Department Code attribute from a department.

 

 

Then in the COLUMN tab we can setup the table structure we need. Use the green plus icon  to add a new calculated column, and rename it Value.

 

 

Select the gear icon  to configure the Value column. Change the Type to Custom Expression and set the value to 1. Ensure that the Aggregation is set to none.

 

 

In the PREVIEW tab we can confirm that the table structure is now what we need.

 

 

Step 5. Select the Publish to Model option and publish this mapping table to the Valid Dep-Emp Model we created.

 

 

Step 6. We want this relationship to affect our Payroll Planning Form, so let's start by selecting the Model which contains that Form. Under the LINKED MODEL tab click Add and select our Valid Dep-Emp Model. Then deploy the application.

 

 

Step 7. In the Payroll Planning Form, let's navigate to the ADVANCED section and select the RELATIONSHIP tab. Click Add to build a new relationship.

 

 

The Valid Dep-Emp measure is now a selectable option here because we added it as a Linked Model. Select the measure then click OK.

 

 

Step 8. Lastly, select where the relationship should be applied in the Form. In this case, we want to limit which Employees are visible on the Rows, so select the Rows checkbox.

 

 

Now in the Payroll Planning Form, only Employees from the selected Department will be shown on the Rows.