Part II: Creating Multidimensional Models

In This Part

Chapter 4. Conceptual Data Model

The conceptual multidimensional data model is the foundation for multidimensional databases. All the components and architecture of a multidimensional database create, control, and provide access to the data in the model. Because it is simple and flexible, not to mention effective, the multidimensional model has led to widespread adoption of Analysis Services in a short period of time.

Many people look at the multidimensional data model as simply metadata—data that describes the data stored in the relational database. We’re going to look at this from a different angle. We see the conceptual model as an independent specification of the data in the multidimensional system. A relational database might be the source of the data or the place where the data is stored. But, the multidimensional database is a completely independent system that can be both the source and the storage place of the data. If the source of the data is external to the multidimensional database, it is defined by the Data Source property. Any dependency between multidimensional data and relational data is defined by the Data Binding property.

Data Definition Language

We use Data Definition Language (DDL) to define and alter the data model. Extensible Markup Language (XML), which has grown popular among software developers in recent years, turns up in many of the components of our system, including DDL. As the foundation for DDL, XML is easy to use, convenient, and efficient. Throughout this book, we use DDL a lot to describe the data model, so you’ll want to be familiar with it. However, we focus our discussion here on the semantic properties of the language. You can look for details of the syntax of DDL in Books Online.

DDL is object oriented. It enables you to define a set of objects that are part of the multidimensional model and to define all the properties necessary to those objects.

You can use Microsoft Business Intelligence Development Studio and SQL Server Management Studio to work with the conceptual model of Analysis Services. Under the hood, those applications generate DDL commands and send them to Analysis Services. However, although you can avoid delving into the details of DDL, we highly recommend you to spend time trying to understand it so that you can take full advantage of the capability to talk directly to the Analysis server in its own language. In this chapter, we introduce you to the object model that defines the conceptual model and discuss the common characteristics of all the objects. In the following chapters, we’ll delve deeper into the most important objects of the conceptual model, such as Dimension, Cube, Measure Group, and so on.

Objects in DDL

All the objects in DDL are either major objects or minor objects. Major objects are objects that the user can manipulate—independently of their parent objects—to create and change the model. Minor objects are children of major objects.

The root object (which is a major one) of the model is Database (sometimes called Catalog), which allows you to navigate to all the objects of the model.

Major objects must have two unique identifiers: the ID and Name properties. Minor objects sometimes don’t need these properties, because they are always part of a major object. In addition, each object (major or minor) can have a Description property that contains text that describes the purpose of the object (useful for the developer who created the object and the user of the application that uses the object). Objects can also have the Annotation property, or lists of Annotations, that external applications use to display or manipulate their data.

Listing 4.1. The DDL Definition of the FoodMart Database

<Databasexmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <ID>FoodMart 2008</ID>
     <Name>FoodMart 2008</Name>
     <CreatedTimestamp>0001-01-01T08:00:00Z</CreatedTimestamp>
     <LastSchemaUpdate>0001-01-01T08:00:00Z</LastSchemaUpdate>
     <LastProcessed>0001-01-01T08:00:00Z</LastProcessed>
     <State>Unprocessed</State>
     <LastUpdate>0001-01-01T08:00:00Z</LastUpdate>
     <DataSourceImpersonationInfo>
          <ImpersonationMode>Default</ImpersonationMode>
          <ImpersonationInfoSecurity>Unchanged</ImpersonationInfoSecurity>
     </DataSourceImpersonationInfo>
     <Dimensions />
     <Cubes />
     <DataSources />
     <DataSourceViews />
     <Translations />
</Database>

You can see in the example presented in Listing 4.1 that the database contains collections of the objects, dimensions, cubes, and so forth. (The ending s on Dimensions, Cubes, and so on denotes a collection.) The Dimension and Cube objects are major objects and can be changed independently of the Database object definition. You can find detailed information about dimensions in Chapter 5, “Dimensions in the Conceptual Model,” and about cubes in Chapter 6, “Cubes and Multidimensional Analysis.”

Figure 4.1 contains the most important objects of our multidimensional model, with major objects in dark gray. Objects that represent the physical model and objects that represent database security aren’t included in the figure. These objects are discussed in later chapters.

In the following sections, we give you an idea of some of the properties that are commonly used in our conceptual model:

• Multilanguage support

• Ways of ordering your data

• Ways to specify default properties

Multilanguage Support

The Analysis Services multidimensional model features multilanguage support, which comes in handy considering the trend toward internationalization and globalization of today’s enterprises. That support means that the data warehouse can contain data in multiple languages, which, of course, affects data storage requirements. The object’s Language (sometimes known as Locale) property is the identifier for a specific language; it is used for both the metadata and in the data itself. In the metadata, it defines the language of the object’s Name property.

The Translation property of an object enables you to define translation of the object’s Name into different languages. The Language property of the Translation defines to which language Name is translated.

The ID property of the object, once it’s specified, can’t be changed. The name of the object and the translation of that name can be easily changed, and because of that can’t be used for cross-references.

Listing 4.2. Translating a Database Object Name to Russian

<Database xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
     <ID>FoodMart 2008</ID>
     <Name>FoodMart 2008</Name>
     <Translations>
          <Translation>
               <Language>1049</Language>
               <Caption>

</Caption>
          </Translation>
     </Translations>
</Database>

If you specify this translation in DDL definition, the user application will have access to the caption that is specified in the translation, and can use it in place of the name wherever necessary.

Rules of Ordering

The order of object elements is not all that important. When possible, however, Analysis Services preserves the order that you assigned for the elements. But for some objects, Analysis Services assigns a new order, usually based on the alphabet.

If order is based on the alphabet, the order can differ from one language to another. Not only does alphabetic order change from one language to another, but there are rules of ordering, defined by the Collation property, that add different bases for ordering. Collation and its properties, such as Ignore Case or the ignoring of special characters, define different ways of ordering for different languages.

Specifying Default Properties

The DDL language has rules for specifying default properties. These rules specify the values that properties take if they’re not explicitly specified in the object definition. Usually if there isn’t a specific default value, the server assigns a value. Therefore, it’s not always possible to predict what value will be assigned. It also might turn out that in the next version of Analysis Services, the default values will be interpreted differently. It’s a good idea to avoid situations where the server would define values for you. However, if you’re not interested in the value, you can just go with whatever value the server assigns.

Another rule holds that you don’t need to specify a collection of objects if the collection is empty. The server doesn’t have default values for empty collections; it assumes that an empty collection doesn’t have any objects, and therefore no values. (We would assume that, too.) However, there are some cases in which the server would copy a collection from another object. (For more information about exceptions to these rules, see Chapter 6, “Cubes and Multidimensional Analysis,” and Chapter 7, “Measures and Multidimensional Analysis.”)

Rules of Inheritance

Analysis Services has two distinct ways of inheriting values from the parent at the time of object’s creation:

• Static inheritance

• Dynamic inheritance

Static inheritance means that at the time the object is created any of the values not specified in the object’s definition will be explicitly assigned values taken from the parent. For example, if you create a Dimension object and omit the Language property, one will be created in the new dimension and an assigned value will be copied from the Database object. That also means changing parent object properties leads to the new child objects inheriting new values and old objects keeping the old values. Therefore, you might end up in a situation where some of the objects in the collection differ without you taking explicit action.

Dynamic inheritance means the property of the object is not explicitly defined for the object at the time of the creation (if not specified by you). When you are trying to obtain object metadata from the server, the dynamically inherited property will not appear in the object definition. Its value is calculated by the server at the time it requires. For example, if the ErrorConfiguration object is not explicitly specified for the Partition object at the time of creation, you will discover the ErrorConfiguration object missing from the partition definition when you try to query partition metadata. During the partition processing, the server will query parent objects of the partition to obtain values needed to handle errors.