Chapter 5. Dimensions in the Conceptual Model

Attribute Member Keys

Each member of an attribute is identified by its key; each member key is unique within the attribute. The keys of all the members of an attribute have the same type. As a member is loaded into the collection that makes up the attribute, it receives a unique number, called Data ID (mostly used internally).

Simple and Composite Keys

In contrast to the “domain” of the relational model, where the scalar is always a simple value, the members of an attribute in the multidimensional model can have either simple keys or composite keys:

• A simple key is defined by a single value of any data type allowed in the multidimensional database.

• A composite key is defined by a combination of values of various data types. A composite key, like any key, must be unique for each attribute member.

You need a composite key when you can’t rely on the uniqueness of the key to identify a specific member. For example, if you have an attribute (collection) of world cities, to uniquely identify any one city you need to include in the key the country and perhaps even province or state. (There could be a number of cities with the same name located in one country—the United States is notorious for that.) Thus, the composite key.

Now, if you also had an attribute of countries, the value of a country would be present in both attributes (cities and countries). Composite keys, then, lead to duplication of data and consume more resources.

The Unknown Member

In a relational database, you have a value with its own set of rules—NULL—that takes the place of an unspecified value. In our implementation of the multidimensional database, the concept of the Unknown Member serves a similar purpose: to define a dimension member with an unspecified key. Every attribute has an unknown member, even though it might be inaccessible. Even an empty attribute has one member, the unknown member.

An unknown member has no specified key. It’s possible that some other member of the attribute would have a key with the value NULL. Such an attribute would have both an unknown member and a member with a NULL key value. We talk more about unknown members in Chapter 7, “Measures and Multidimensional Analysis.”

Data Specification

To define keys and other data in the multidimensional model, you can use the DDL object DataItem. DataItem defines the primary properties of multidimensional data such as key, name, and so on. Table 5.2 describes the properties of DataItem.

Listing 5.4 shows the DDL definition of a composite key, which uses DataItems.

Listing 5.4. DDL Definition of a Composite Key

<KeyColumns>
     <KeyColumn>
          <DataType>WChar</DataType>
          <DataSize>50</DataSize>
          <Source>
               <TableID>dbo_customer</TableID>
               <ColumnID>city</ColumnID>
          </Source>
     </KeyColumn>
     <KeyColumn>
          <DataType>WChar</DataType>
          <DataSize>50</DataSize>
          <Source>
               <TableID>dbo_customer</TableID>
               <ColumnID>state_province</ColumnID>
          </Source>
     </KeyColumn>
</KeyColumns>

Attribute Member Names

An attribute member’s identifier is its name. The attribute member name is used pretty much where you want to reference a specific attribute member. The name of the attribute member can be either unique inside of the attribute or not. However, if the name is unique within an attribute, Analysis Services can support it more efficiently. You can use a property such as the MemberNameUnique. Otherwise, the property will come from the key. The MemberNameUnique property is uniqueness of the attribute members’ names, Analysis Services uses the names of members to generate the member unique name; otherwise, it uses member keys.

We use the DataItem object to specify the attribute member name. Unlike the data type for the key, the data type for the member name can only be text or a format that can be converted to text.

When you define a member name, avoid using spaces and special characters; they complicate the use of member names. It’s a good idea to avoid long names, too, because they take more resources to store and retrieve and can therefore decrease system performance. Storing the attribute member names can require gigabytes of disk space. Loading the names into memory can be the main cause of a slowdown.

Collation is important part of the member name specification. The collation of a name can change whether the name is unique or not. For example, suppose you have some name that uses capital letters whereas another, similar name uses lowercase letters (for instance, DeForge and Deforge). Depending on the Collation property value, the system might treat these similar names as either different or the same, resulting in confusion and unpredictable results for the user.

In addition, Collation affects the order the members will be sorted in. Sorting is an important aspect of how attribute members are used in the system. Defining the right ordering scheme is not always a simple task. For example, months in the year are usually not sorted by name; it’s typical to sort months by the numbers that indicate their place in the year.

In a typical sort order, you can use the key to define the order of the attribute members. If the key is defined by the text names of the months, the typical sort order would begin with August, which wouldn’t make sense in most cases. To solve this problem, you create another attribute, related to the original one that contains the numbers that indicate the order the months appear in the year. Then, you order by the key value of that related attribute.

The locale for the attribute member name defines the language of the member. When a certain locale is specified, the attribute member name itself is in the language of that locale. To specify that the name of the attribute member should be translated when it appears in other locales, use the Translations property.

Everything we’ve said about the member attribute name is also true for the Translations property, with one exception. Uniqueness of a translation is not defined in the model because the translation is not used to identify the member, but only for displaying the name of the member. When a user works on a client computer with a specific language, the Language property enables the server to use the right translation to create a Member Caption (used in the application model) in the language of the user.

Relationships Between Attributes

The Relationship between attributes in a dimension defines the possible associations that one attribute can have with another. Relationship affects all the functions of Analysis Services. It defines the properties of association that the current attribute has with other attributes, including whether an attribute can be accessed through the current attribute.

If one attribute can be accessed through another, that other attribute is treated as a member property of the current attribute. For example, Age and Marital Status are additional properties for our Customer attribute. Table 5.3 describes the properties of Relationship that can be used in the conceptual model.

In Figure 5.3 you can see a diagram of the Customer dimension that shows the relationships of attributes and the properties of those relationships:

• In terms of Type, you can see a Rigid relationship, indicated by a solid line, between Customer ID and Gender.

• A Flexible type is indicated by a dotted line. You can see a Flexible relationship between Customer ID and Marital Status.

• In terms of Cardinality, One-to-Many is indicated by a triangle in the middle of the relationship line. You have a one-to-many relationship for most attribute relationships.

• A One-to-One cardinality is indicated by a diamond in the middle of the line. There is only one example of a one-to-one relationship, that between the Customer ID and Address attributes. (That’s true in our FoodMart 2008 database, although in reality you could have two customers who live at the same address.)

• A Mandatory relationship is indicated by a V sign and the end of a line. You can see a mandatory relationship between the City and State Province attributes.

• An Optional relationship (most of the relationships in Figure 5.3) is indicated by an absence of symbols.

  Figure 5.3. The Customer dimension has different attributes with different types of relationships.