Joint child relationships in MSTR

Joint child relationships

Some attributes exist at the intersection of other indirectly related attributes. Such attributes are called joint children.

Joint child relationships connect special attributes that are sometimes called cross-dimensional attributes, text facts, or qualities. They do not fit neatly into the modeling schemes you have learned about thus far. These relationships can be modeled and conceptualized like traditional attributes but, like facts, they exist at the intersection of multiple attribute levels.

Many source systems refer to these special attributes as flags. Therefore, if flags are referenced in your source system documentation, these are likely candidates for joint child relationships.

Joint child relationships are really another type of many-to-many relationship where one attribute has a many-to-many relationship to two otherwise unrelated attributes. For example, consider the relationship between three attributes: Promotion, Item, and Quarter. In this case, Promotion has a many-to-many relationship to both Item and Quarter, as shown in the following diagram.

An example of a promotion might be a “Red Sale” where all red items are on sale. A business might run this promotion around Valentine's Day and again at Christmas time.

Supporting joint child relationships

One way to resolve a many-to-many relationship is to have a relationship table for the attributes involved in the many-to-many relationships. In this case, you might create two relationship tables, one to relate Promotion and Item. The second relates Promotion and Quarter as shown in the following diagram.

These two tables are sufficient to answer questions such as:

•

What items have been in what promotions?

•

What quarters have had what promotions?

However, these tables are not sufficient to answer the following more detailed and insightful questions:

•

What items were in what promotions in a given quarter?

•

In what quarters was a certain item involved in a certain type of promotion?

To answer these questions, you must combine the two relationship tables, creating one table to relate all three attributes.

The relationship in the distinct relationship table must exist for a joint child relationship to be properly defined. However, it does not necessarily have to be in its own, distinct relationship table. Defining the relationship directly in the lookup table for the parent of the joint child—in this case, Promotion—would be fine. Alternatively, you can build the relationship directly into the fact table.

In these examples, it is important to notice the relationship between the three attributes. The Promotion attribute is related to a particular Item-Quarter pair, as opposed to it being related to Item and Quarter separately. This is the essence of a joint child relationship and is shown in the following diagram.

Notice that a joint child relationship can be one-to-many or many-to-many. The issues with many-to-many relationships, including loss of analytical capability and multiple counting, also apply to many-to-many joint child relationships.

If you have a joint child relationship in your data, it is important for you to define it in MicroStrategy so that you get the correct data for reports that use the parent attribute in a joint child attribute. This ensures that when you need to join the fact table to the parent attribute of a joint child relationship (for example, to see sales by promotion) the join will always use both joint children rather than just one or the other.