Before you get to start knowing about the schema objects ( Attributes ) creation – you need to know about the relationships that exist in the database & then how to map them while you create attributes etc..
One – One Relationship
For attributes that have a one-to-one relationship, you do not need a separate relationship table. Typically, you do not even have a separate lookup table for the parent attribute. Instead, you place the parent directly in the lookup table of the child attribute to map the relationship between the two attributes.
The following illustration shows an example of using a single lookup and relationship table for two attributes:
E-mail and Employee have a one-to-one relationship. Therefore, the LU_EMPLOYEE table can serve as both the lookup and relationship table for both attributes.
Note: In MicroStrategy, you often treat the parent attribute in a one to one relationship as a Attribute form of the attribute it describes. If you want to treat the parent attribute as a separate attribute, you may want a separate lookup table for the parent.
One – Many Relationship
For attributes that have a one-to-many relationship, you also do not need a separate relationship table. Instead, you can define the parent-child relationship by including the ID column for the parent attribute in the lookup table of the child attribute. Placing the parent ID in the child table creates a foreign key on the parent ID that enables you to map the relationship between the two attributes using their respective lookup tables.
The following illustration shows an example of a lookup table that also functions as a relationship table:
Category and Subcategory have a one-to-many relationship. Therefore, the LU_SUBCATEGORY table can serve as both the lookup table for the Subcategory attribute and the relationship table for the Category and Subcategory attributes. The Category_ID column is a foreign key that maps back to the LU_CATEGORY table. ( regarding lookup table & relationship table selection – it comes in the attribute creation editor – which i’ll explain you in detail later )
Many to Many Relationship
For attributes that have many to many relationship, you must create a separate relationship table to map the parent-child relationship.
The following illustration shows an example of a distinct relationship table:
Supplier and Item have a many-to-many relationship. Therefore, you cannot place the ID column for the Supplier attribute in the LU_ITEM table and still retain only the Item_ID column as the primary key. The REL_SUPPLIER_ITEM table includes the ID columns for both the Supplier and Item attributes to map the relationship between them. These two ID columns form a compound primary key for the relationship table.
Generally when there is a many to many relationships, we go for a separate relationship table in data warehouse ( as how it is present in above scenario )
Hope it helps for the day to know about how to work with relationships!!
Have a Good Day!!!