Defining relationships between attributes

Attribute relationships are associations between attributes that specify how attributes are connected. Attribute relationships define how tables and columns are joined and used, and which tables are related to other tables. Without relationships, there is no interaction between data, and therefore no logical structure. The relationships give meaning to the data by providing logical associations of attributes based on business rules.

Every attribute relationship has two parts—a parent and a child. A child must always have a parent and a parent can have multiple children. The parent attribute is at a higher logical level than the child is. For example, in a relationship between Year and Quarter, Year is the parent attribute and Quarter is the child.

Relationship types 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 can have many accounts, and each account can be associated with many customers, such as in the case of a joint checking account.

With each attribute, the relationship type can be set to auto-managed or user-managed. When a new dataset is imported, MicroStrategy detects the existing relationships between attributes and marks these relationships as auto-managed. Any new data a user adds after the initial data import is marked as user-managed. At any point in time, the type of relationship management can be switched from auto-managed to user-managed, and vice versa.

Prerequisite

The following steps assume that you have already begun importing data. The Preview page is displayed after you select a data source to import from, select the data to import, and click Prepare Data. For a list of the data sources that you can import from, and links to steps to import data, see About importing data into MicroStrategy Web.

For steps, see below:

To define relationships between attributes

To set relationships as user-manged or auto-managed

To define relationships between attributes

1 On the Preview page, hover the cursor over the name of a table, click the Menu icon, and select Define Relationships. The Define Relationships dialog box opens.
2 To add a new relationship, complete the following steps:
a Click Add a New Relation. A blank relationship is displayed.
b From the drop-down list in the Parent Attribute column, select the parent attribute. The parent attribute is at a higher logical level than the child is.
c From the drop-down list in the Child Attribute column, select the child attribute.
d By default, the relationship is defined as one to many. You can select a different relationship type from the drop-down list in the Relationship column:
1:1: 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.
1: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.
Many:1: 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:Many: Each element in the parent attribute can have multiple children and each child element in the child attribute can have multiple parents.
3 You can edit existing relationships, by changing the Parent Attribute, Child Attribute, and Relationship options.
4 To delete all the relationships for the table, click Delete All.
5 When you have defined all the relationships for the table, click Save to return to the Preview page

To set relationships as user-manged or auto-managed

1 On the Preview page, hover the cursor over the name of a table, click the Menu icon, and select Define Relationships. The Define Relationships dialog box opens.
2 There are two ways to manage attribute relationships:
Auto-managed: This is the default relationship management type when importing new data. MicroStrategy manages the relationships according to how elements and attributes relationship types are set. If an attribute does not match the relationship criteria that is set, MicroStrategy automatically deletes the attribute. This eliminates duplicate attributes in your dataset.
User-managed: This relationship management type allows a user to oversee and manage the relationships between attributes and elements. This is recommended for advanced analysts who are comfortable with Project attributes, as MicroStrategy does not automatically managed the imported datasets.
3 To select a management type for each attribute, click the icon to the left of the Parent Attribute.
4 To select a management type for all attributes, click Convert All and select an option from the drop-down list.

If user-managed is selected and the attribute relationships are invalid, an error message is displayed and you won't be able to save the relationships between the highlighted attributes and elements.

5 Click Save to make your changes.

In the future, you can edit the management types for attributes relationships by click Edit Dataset from the drop-down menu under the name of the dashboard dataset.

Related topics

Previewing your data and specifying data import options
About importing data into MicroStrategy Web