Chapter 5. Dimensions in the Conceptual Model

Chapter 5. Dimensions in the Conceptual Model

You define multidimensional space by specifying a set of dimensions. Therefore, the dimension is the central object of the multidimensional model. Many other objects are based on the dimension. In different objects of the model, dimensions are represented in different forms. The basic form of dimension is the Database dimension, from which all other dimension forms in the model derive. The Database dimension is a major object, and therefore has Name and ID properties. Other dimensions reference the Database dimension and then add properties that distinguish them. For example, a cube uses Cube dimension, which references a Database dimension.

Listing 5.1 is the Data Definition Language (DDL) that describes the most generic definition of the Database dimension. The Name property in our sample dimension is Customer; the ID property is also Customer. These two are the only properties defined in our dimension (the Customer dimension). In the next sections of this chapter, we slowly introduce other properties of a dimension object.

Listing 5.1. Use DDL to Define a Basic Database Dimension

<Dimension>
     <ID>Customer</ID>
     <Name>Customer</Name>
     <Annotations />
     <Attributes />
     <Hierarchies />
</Dimension>

This definition contains neither a definition of multidimensional space nor a dimension hierarchy. If we want to define a multidimensional space, we must use a collection of attributes to define the coordinates of that space. In the next section, we look at the attribute collection for our sample Customer dimension.

Dimension Attributes

The multidimensional model contains a collection of attributes that defines a set of “domains” of a dimension’s data; one domain is a dimension attribute. We use the term domain as it defined in the relational database theory: a limited list of values (scalars). However, implementations of relational databases essentially ignore this definition. In practice, implementations of the relational data model manipulate columns that will accept any value (of the appropriate type).

In the multidimensional model, attribute defines domain—a list of the same type values, which we call key values. All key values of attribute have the same data type. Key is a unique identifier of a dimension member. (For the definition of a dimension member, see Chapter 2, “Multidimensional Space.”) For each unique key value, there is a unique dimension member. The difference between the implementation of a domain in the relational and multidimensional data models is shown in Figure 5.1.

As shown in a Figure 5.1, an attribute is an analog of a column in the relational implementation of the domain. However, in addition to the scalar values, the multidimensional model allows the association of an additional set of properties with every dimension member (for example, Name, Caption, Value, and so on).

Listing 5.2 shows the DDL definition of the key attribute of the Customer dimension in the FoodMart 2008 database.

Listing 5.2. DDL Definition for the Key Attribute of the Customer Dimension

<Attribute>
     <ID>Customer</ID>
     <Name>Customer</Name>
     <Usage>Key</Usage>
     <KeyColumns>
          <KeyColumn>
               <DataType>Integer</DataType>
               <Source>
                    <TableID>dbo_customer</TableID>
                    <ColumnID>customer_id</ColumnID>
               </Source>
          </KeyColumn>
      </KeyColumns>
      <NameColumn>
          <DataType>WChar</DataType>
          <DataSize>151</DataSize>
          <Source>
               <TableID>dbo_customer</TableID>
               <ColumnID>name</ColumnID>
          </Source>
     </NameColumn>
     <AttributeRelationships />
</Attribute>

Attribute Properties and Values

A dimension attribute contains a limited list of key values. Each unique key value is associated with a unique member of the dimension. The key is the unique identifier of that dimension member. Dimension members included in an attribute are called attribute members.

In the multidimensional model (as opposed to the relational model), the attribute also contains properties that define different characteristics of the attribute members. All attribute members have the same properties.

Table 5.1 lists the main properties of an attribute member.

Once you’ve specified these properties for an attribute, you’re ready to populate it. Sometimes, populating an attribute with members is a pretty simple task. For example, the Gender attribute generally requires only two values: male and female. On the other hand, populating an attribute such as Customer can turn out to be a more complex task, because the number of members in the attribute can be very large: tens and even hundreds of millions. See Chapter 21, “Dimension and Partition Processing,” for detailed information about loading attribute data.

Relationships Between Attributes

Even though the number of attributes in a dimension usually doesn’t vary as widely as the number of members of an attribute, it can reach to the tens and even hundreds. Nonetheless, only one of those attributes can be the key attribute. Additional (related) attributes define different aspects and properties of the key attribute. For example, we can add Gender and Marital Status attributes to the Customer attribute.

Related Attributes

Every attribute has a key, but sometimes the attribute itself is the key of the dimension. Additional attributes in a dimension can be related not only to the key attribute, but to each other. For example, in the Customer dimension, suppose we introduce the country and city where the customer lives. The country is a property of the city, so you get two new attributes related to each other. We call these related attributes.

Even though dimension attributes can be related to each other or not related, all attributes are related to the key attribute. The relationship to the key can be either direct or indirect, through other attributes.

You have a direct relationship between two attributes if there is no intervening attribute between them. An indirect relationship is a relationship in which there are one or more attributes between the two. The relationship chain can be longer than three attributes, but there has to be an attribute at the end that is directly related to the key.

The collection of attributes creates a single semantic space that defines one member of the key attribute. You can envision this semantic space as a tree of dimension attributes, such as that illustrated in Figure 5.2.

The key attribute is Customer ID, located in the root of the tree of attributes. The attributes Marital Status, Gender, and City directly relate to the key attribute. The Marital Status and Gender attributes are not related to each other. The connection between them is through Customer ID. Therefore, to traverse the tree from attribute Marital Status to attribute Gender, the direction of traversal will change from “toward” the key attribute to direction “from” the key attribute.

Attribute City is directly related to Customer ID. Attribute Country is directly related to attribute State Province, but indirectly related to Customer ID. Your eye can follow the line from Country to Customer ID without changing direction.

Listing 5.3 shows a simple DDL definition of the diagrammed collection of attributes.

Listing 5.3. DDL Definition of the Attribute Tree

<Attributes>
     <Attribute>
          <ID>Customer</ID>
          <Name>Customer</Name>
          <Usage>Key</Usage>
          <KeyColumns/>
          <AttributeRelationships>
               <AttributeRelationship>
                    <AttributeID>City</AttributeID>
                    <Name>City</Name>
               </AttributeRelationship>
               <AttributeRelationship>
                    <AttributeID>Marital Status</AttributeID>
                    <Name>Marital Status</Name>
               </AttributeRelationship>
               <AttributeRelationship>
                    <AttributeID>Gender</AttributeID>
                    <Name>Gender</Name>
               </AttributeRelationship>
          </AttributeRelationships>
     </Attribute>
     <Attribute>
          <ID>City</ID>
          <Name>City</Name>
          <KeyColumns/>
          <AttributeRelationships>
               <AttributeRelationship>
                    <AttributeID>State Province</AttributeID>
                    <Name>State Province</Name>
               </AttributeRelationship>
          </AttributeRelationships>
     </Attribute>
     <Attribute>
          <ID>State Province</ID>
          <Name>State Province</Name>
          <KeyColumns/>
          <AttributeRelationships>
               <AttributeRelationship>
                    <AttributeID>Country</AttributeID>
                    <Name>Country</Name>
               </AttributeRelationship>
          </AttributeRelationships>
     </Attribute>
     <Attribute>
          <ID>Country</ID>
          <Name>Country</Name>
          <KeyColumns />
     </Attribute>
    < Attribute>
          <ID>Marital Status</ID>
          <Name>Marital Status</Name>
          <KeyColumns />
     </Attribute>
     <Attribute>
          <ID>Gender</ID>
          <Name>Gender</Name>
          <KeyColumns/>
     </Attribute>
</Attributes>

To define a dimension, it is not enough to specify a relationship between attributes. Every relationship has multiple properties that specify, for example, the type of the relationship as flexible or rigid, optionally of the relationship as mandatory or optional, cardinality as one to one or one to many, and so on.