Once you have registered files and optionally created datasets, you can create relationships among the different files and datasets. This process is very similar to creating table relationships. You can add registered files or datasets, and you can even add the same registered files or datasets multiple times. For example, you may have a table of employee data and want to create a relationship between employees and their managers. However, a manager is also an employee so you need to add the employee data once to use it for managers and add it again to use it for employees.
Relationships are shown by a line drawn between fields in different files or datasets. The line specifies the relationship. Drag a field to another field to connect the fields and create a relationship. For example, you may want to create a relation between a field named Country in one dataset with a field named Nation from another. The two fields need to be the same type. You can't create a relationship between a numeric field of Country Code and a text field of Country Name, for example.
You can also drag the files or datasets around in the window, and you can drag the property panes and other onscreen elements around to organize them in a way that works for you.
Users with data designer role and access to underlying data tables can create data relationships.
You can expand the folders in these panels or use Search to quickly find the file you are seeking.
To add a file
- Drag a file onto the workspace.
- Drag a field to map it to a field in a different node to create a relationship.
- Click the line to view the relationship details.
Adding datasets works the same way.
Select a node then view its properties in the Properties panel. You can specify that node as a fact table by selecting the Is Fact Table checkbox.
You can also define the relationship style as a line or a curve.
View the source information for the selected node in the Source pane. You can change the node source by clicking the link next to a dataset or view the node details in a different window by clicking Open Source. You can also change the node source by clicking the ... menu at the top of the page and choosing Change Node Source.
Relationships pane in the Properties panel
In the Relationships pane, you can view or change the relation type. For example, you can change from one-to-many to many-to-many by clicking the blue icon next to the Actions menu ( ⋮ ), then clicking Yes to confirm the change.
To add a relation
- Click Add relation and specify a source and target for an outgoing relationship.
- To change the incoming or outgoing relationship to one-to-many, click the blue icon next to the Actions menu ( ⋮ ) and click Change.
- To change the operator between a source and target, click the operator (the blue equal sign in the screenshot shown here) and choose
between. If you choose
betweenyou must select a target.
- To delete a relationship, click the Minus button and click Delete to confirm the deletion.
You cannot rename the datasets or fields used in the Relationships Designer.
Collapse or Expand
Click Collapse Nodes or Expand Nodes at the top of the workspace to expand or collapse all nodes.
For complex relationships, you can also view and edit the nodes in a table format. The table format is best used when you have a larger number of nodes, for example, 25. It helps you avoid additional scrolling and moving relationship lines. You can see all of the information more easily in table format. The table view allows you to more easily search, sort, and filter relationships.
Once you have made changes using the tabular view, you can't switch back to the graphical view.
To switch to the tabular view, click the ... menu and and choose Switch to Tabular View.
You can create relationships using data that hasn't been built into a cube. Select the file you want to use and create relationships using it. See Working with non-materialized or raw data cubes.
After you have made changes, click the ... menu and choose Validate. See Validating an object.
You can view a data profile result to see information that will help you optimize the relationships that you recreate. To learn more about data profiles, see, Creating a data profile.