Attribute relationships

After you have created attributes for your project, you can define attribute relationships to define how the engine generates SQL, how tables and columns are joined and used, and which tables are related to other tables.

You link directly related attributes to each other by defining parent-child relationships, as explained in Attribute relationships. Attribute elements, or the actual data values for an attribute, dictate the relationships that you define between attributes.

The parent-child relationships you create determine the system hierarchy within the project.

The implications of whether attributes are related become clear when you begin building reports. You can run a report with two attributes that are related without any problems. For example, it is easy to include Country and City, on a report together. A report with two unrelated attributes, however, must include a metric based on a fact that is on or below the level of the two attributes, or else a Cartesian join occurs, which is generally undesirable. A Cartesian join, or cross join, is very database intensive as every row in one table is joined to every row in the other table.

In MicroStrategy Developer, you can define relationships for the attributes in your project. This step is covered in Simultaneously creating multiple attributes, as part of the initial project design effort and in Viewing and editing the parents and children of attributes, after a project has already been created.

Attributes can be either related or unrelated to one another:

•

Related: A parent-child relationship is defined between two or more related attributes. The relationship is defined through the attribute’s lookup table or a relationship table.

Four types of direct relationships can exist between related attributes, and these relationships are defined by the attribute elements that exist in the related attributes. Each type is described below:

▫

One-to-one: Each element in the parent attribute corresponds to one and only one element in the child attribute, and each child attribute corresponds to one and only one element in the parent attribute. A common example of a one-to-one relationship is citizen and taxpayer ID. A citizen can have only one taxpayer ID and a taxpayer ID can be assigned to only one citizen.

▫

One-to-many: Each element in the parent attribute corresponds to one or more elements in the child attribute, and each child attribute corresponds to one and only one element in the parent attribute. These are the most common types of attribute relationships. Year has a one-to-many relationship to quarter. One year has many quarters, but a specific quarter can be in one year only. This assumes that quarters are defined with an accompanying year such as Q4 2006, Q1 2007, and so on.

▫

Many-to-one: Each element in the parent attribute corresponds to one and only one element in the child attribute, and each child attribute corresponds to one or more elements in the parent attribute. Many-to-one relationships are the same type of relationship as a one-to-many, but it is defined from a different perspective. For example, year is described above as having a one-to-many relationship to quarter. This means that quarter has a many-to-one relationship to year.

▫

Many-to-many: Each element in the parent attribute can have multiple children and each child element in the child attribute can have multiple parents. In banking, customers and accounts are an example of a many-to-many relationship. One customer may have many accounts, and each account may be associated with many customers, such as in the case of a joint checking account.

Attributes can also be related to other attributes through a chain of attribute relationships. Attributes of this type are often in the same hierarchy. For example, a Geography hierarchy contains the attributes Customer Region, Customer State, and Customer City:

In this scenario, Customer Region and Customer State are directly related to each other and Customer State and Customer City also have a direct relationship. While Customer City is not directly related to Customer Region, these two attributes are related through Customer State. This allows you to include Customer Region and Customer City on a report and view the different customer cities for each customer region.

•

Unrelated: No parent-child relationship has been defined and the attributes are not related through a chain of attribute relationships. No relationship is present in the lookup tables or relationship tables for these attributes. Unrelated attributes can exist together in fact tables, giving context to the fact. For example, the Customer and Day attributes have no relationship to one another. A particular customer and a particular day only make sense together if a fact is associated with that combination. For example, a certain customer, Don Addison, spent $2,500 on January 5, 2003 on behalf of the health care company in which he works. In this case, care must be taken when using unrelated attributes on a single report. In general, however, these attributes are relatively straightforward to deal with from a project design perspective.