| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide > Data Modeling >
Understanding Dimensional Models
To analyze business data, the data needs to be mapped logically to a business model. The Siebel Analytics Server can use dimensional models for this purpose. This section discusses some of the components and variants of representative dimensional models.
Businesses are analyzed by relevant dimensional criteria, and the business model is developed from these relevant dimensions. These dimensional models form the basis of the valid business models to use with the Siebel Analytics Server. All dimensional models build on a star schema. That is, they model some measurable facts that are viewed in terms of various dimensional attributes.
Dimensional Hierarchies
Dimensions are categories of attributes by which the business is defined. Common dimensions are time periods, products, markets, customers, suppliers, promotion conditions, raw materials, manufacturing plants, transportation methods, media types, and time of day. Within a given dimension, there may be many attributes. For example, the time period dimension can contain the attributes day, week, month, quarter, and year. Exactly what attributes a dimension contains depends on the way the business is analyzed.
A dimensional hierarchy expresses the one-to-many relationships between attributes. Given a sample time dimension, consider the hierarchies it implies, as shown in Figure 6.
With this sample time dimension, days may aggregate, or roll up, into weeks. Months may roll up into quarters, and quarters into years. When one attribute rolls up to another, it implies a one-to-many relationship. The total of all the hierarchy definitions in this sample time dimension make up this time dimension.
These hierarchy definitions have to be specific to the business model—one model may be set up where weeks roll up into a year, and another where they do not. For example, in a model where weeks roll up into a year, it is implied that each week has exactly one year associated with it; this may not hold true for calendar weeks, where the same week could span two years.
Some hierarchies might require multiple elements to roll up, as when the combination of month and year roll up into exactly one quarter. The Siebel Analytics Server allows you to define the hierarchy definitions for your particular business, however complex, assuring that analyses will conform to your business definitions.
Factual Measures
Factual measures, or facts, are typically additive data such as dollar value or quantity sold, and they can be specified in terms of the dimensions. For example, you might ask for the sum of dollars for a given product in a given market over a given time period. Measures can also be aggregated by applying other aggregation rules, such as averaging instead of summing. Furthermore, the aggregation rules can be specific to particular dimensions. The Siebel Analytics Server allows you to define these complex, dimension-specific aggregation rules.
Star and Snowflake Models
Star and snowflake models follow the dimensional rules of one-to-many relationships. Star schemas have one-to-many relationships between the logical dimension tables and the logical fact table. Snowflake schemas have those same types of relationships, but also include one-to-many relationships between elements in the dimensions.
Bridge Tables to Model Many-to-Many Relationships
Star schemas and snowflake schemas work well for modeling a particular part of a business where there are one-to-many relationships between the dimension tables and the fact tables. However, sometimes it is necessary to model many-to-many relationships between dimension tables and fact tables.
When you need to model many-to-many relationships between dimension tables and fact tables, you can create a bridge table that resides between the fact table and the dimension table. A bridge table stores multiple records corresponding to that dimension.
To understand how a bridge table works, consider the following portion of a sample health care schema, as shown in Figure 7.
The many-to-many relationship is that for each patient admission, there can be multiple diagnoses. For example, a patient can be diagnosed with the flu and with a broken wrist. The bridge table then needs to have a weight factor column in it so that all of the diagnoses for a single admission add up to a value of 1. The weight factor has to be calculated as part of the process of building the data. For the case of the patient diagnosed with the flu and a broken wrist, there would be one record in the Admission Records table, two records in the Diagnosis Record table, and two records in the Diagnosis table, as shown in Figure 8.
NOTE: This type of design can create more records in the Diagnosis Records table than in the Admission Records table. You can limit the number of records in the Diagnosis Records table by predefining groups of diagnosis and forcing each admission record to fit in one of these predefined groups.
In the Administration Tool, the Logical Table dialog box has an option you can select to specify that a table is a bridge table.
Single Table Models
For the greatest simplicity for end users, you can construct a subject area that consists of a single table. To create a single table model, you first create a logical dimensional model, and then present it as a single table schema in the Administration Tool's Presentation layer. The logical dimensional model is required to set up the necessary metadata for the Siebel Analytics Server to navigate to the proper physical tables. For information about the Presentation layer, see Creating and Maintaining the Presentation Layer in a Repository.
| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide Published: 11 March 2004 |
