back

Learn how to define relationships between tables.




A relationship works by matching data in key columns, usually columns with the same name in both tables.

In most cases, the relationship matches the primary key from one table, which provides a unique identifier for each row, with an entry in the foreign key in the other table.

For example, products can be associated with the specific category of products by creating a relationship between the CategoryID column in the Categories table (the primary key) and the CategoryID column in the Products table (the foreign key).

To create the relationship, you can drag and drop the CategoryID column in the Products table to the CategoryID column in the Categories table

Or you can select "New Relationship..." from the Query menu.

And select the columns you want.

Join Types

There are three join types, as follows:

Option 1 defines an inner join. An inner join is a join where records from two tables are combined in a query's results only if values in the joined fields are equal. It is the most common join operation.

Option 2 defines a left outer join. The result of this join contains all records of the Products table, even if the join-condition does not find any matching record in the Categories table.

Option 3 defines a right outer join. The result of this join contains all records of the Categories table, even if the join-condition does not find any matching record in the Products table.

How to modify the type of join
To modify the join type, right-click the "join" and select Properties

How to delete a relationship
To delete a relationship, click the "join" to select it, and hit the DEL key


Restriction for text files
If your data is stored in text files, it is not possible to define relationships between tables. You can use multiple text files to create a single cube, but all files must have the same structure.

If you need to create relationship between text files, there is a workaround, but it requires Microsoft Access.
You'll have to create linked tables that point to your text files in a new Access database. Then, you will be able to use this Access database in Olapcube.

back