Key data concept – data relationships
Let's quickly review what we learned so far:
- We covered how data tables are structured
- We discussed how data tables differ from spreadsheet workbooks
- We know how to load a datafile into Spotfire
Now, we're going to explore one of the most powerful aspects of data tables: linking or relating the information in two or more different tables. Building structural links between data tables is at the heart of the relational database model that underpins well-known databases, such as Oracle and Microsoft SQL Server. Spotfire is not a relational database as such, but it uses relational principles and provides some table relationship functionality.
If you think back to our discussion of rows and columns, you should be able to conclude that the relationships between tables are defined at the column level. In other words, if we have a table with a Department column, we might want to relate it to another table that also has a Department column. In plain English, we're going to say something like "relate these two tables based on the row values found under their respective Department columns; if you select Marketing records from one table, the relationship will be associated with any Marketing records in the other table."
An instructive corollary of this relationship—and this is a very important concept for you to grasp—is that the row values must match perfectly but the column names can be completely different. You can define a relationship between a "Department" column in one table and a "Dept" column in another. There is a limit, however, to how much you can tell the relationship engine how to interpret the values. For example, will Marketing, marketing, and MARKETING match? You have to explicitly define such match rules.
