Relationships define how separate table elements are logically connected to each other. Relationships in an ElastiCube can be used to determine a calculation path between any table element to any other. A relationship is defined between multiple fields in multiple separate tables. It indicates that two fields, even though they are placed in separate tables, are in fact the same field containing the same types of values.
- To create a relationship between to field, click on the first field and move the mouse over to the second field while still holding the left mouse button. Both fields must be of the same type.
Understanding Relationships
In the example below, the schema represents an ElastiCube made up of three tables: ProductSubcategory, Product and SalesOrderDetail. The first two tables have a relationship on the ProductSubcategoryID field and the last two tables have a relationship on the ProductID field. In effect that means that the SubcategoryID field is a shared field for the ProductSubcategory and Product tables and that the ProductID field is a shared field for the Product and SalesOrderDetails table.
These relationships make up a path through which navigation is possible between each of the individual table to each of the others. This is called a
fully-connected ElastiCube and in such cases every field in the ElastiCube can be calculated in the scope of any other, regardless of what physical table it belongs to.
For example, to calculate the total unit price (from SalesOrderDetail) for each product sub-category (from ProductSubcategory) the ElastiCube will collect all subcategory IDs existing in the ProductSubcategory table and match each of them to a product ID in the Product table with the same subcategory ID. Next, each product ID is matched to a unit price in the SalesOrderDetail table over the ProductID field. The result of this process is the total unit price for each product subcategory.
Semi-Connected ElastiCubes
ElastiCubes where some tables or disconnected from the rest are also valid and these types are called semi-connected ElastiCubes. However, trying to performing queries involving fields that are not connected to each other by some path will yield and error.
See Also