When you have large subjects that include data accumulated in the platform over a period of time, it is common to have certain values that need to be updated across the whole dataset. For instance, if a customer changes their name, you want the name to change across the entire sales subject rather than only for new rows added after their name changed.
To allow for this scenario without requiring a complete reload of the historic data, inmydata allows you to implement lookup tables. In our example, our sales subject would simply contain the customer id. We would load an additional subject called customers, that contained the customer id and the customer name (and any other details for the customer). We then create a linked column in the customer subject, joining the customer name from the customer subject. Now if the customer name changes, we simply update the customer subject and the customer name is changed across the entire sales subject.
To add a linked column, follow these steps;
- Select Settings from the user menu
- Select Subjects in the left tab, then select the subject you want to add a linked column to and press the edit button.
- If you want to add a linked dimension, click on the add button under the list of dimensions, or if you want to add a new linked metric, click on the add button under the list of metrics.
- Select the option to add a new linked column.
- Select the table that contains the column that you wish to link to this subject
- Select the column that you wish to link to this subject
- Enter the name you wish the new linked column to have in your subject
- Click the add button to add a new join condition to define the join to the subject that contains the column you wish to link to
- Select the logical operator, parent column, operator and child column to define the join condition that joins the parent subject to the child subject, and press OK
- Repeat steps 8 and 9 to add any further conditions. If you need to put brackets around a group of conditions, select the conditions in the conditions list and select Group
- Once you have defined the join, press OK to close the new column editor
- Press OK to close and save the edit to your subject
- Press Save to close the Administrator Settings
