| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide > Data Modeling >
Understanding the Physical Database Model
The Siebel Analytics Server provides an interface to map the business model to the underlying physical databases. Sometimes you can control the physical design of the underlying databases, and it might be modeled like the business model. But sometimes the database already exists and you have to work with what is there. Regardless of whether you have input into the physical database design, you need to understand both its structure and its content.
Types of Physical Models
There are two basic types of physical models—entity-relationship (E-R) models and dimensional models. E-R models are designed to minimize data storage redundancy and optimize data updates. Dimensional models are designed to enhance understandability and to optimize query performance.
Entity-Relationship (E-R) Schemas
The entity-relationship (E-R) model is the classic, fully normalized relational schema used in many online transaction processing (OLTP) systems. The relationships between tables signify relationships between data, not necessarily business relationships.
Typically, E-R schemas have many tables, sometimes hundreds or even thousands. There are many tables because the data has been carefully taken apart—normalized, in database terminology—with the primary goal of reducing data redundancy and bringing about fast update performance. E-R models are very efficient for OLTP databases. When E-R databases are queried, joins are usually predetermined and can be optimized. E-R databases are usually queried by applications that know exactly where to go and what to ask. These applications typically query small units of information at a time, such as a customer record, an order, or a shipment record.
E-R schemas generally do not work well for queries that perform historical analysis due to two major problems—poor performance and difficulty in posing the question in SQL:
- Performance problems persist with historical E-R queries because the queries require the database to put the data back together again; this is a slow, complex process. Furthermore, because the cost-based optimizers in database management systems are not designed for the level of complexity in such a query, they can generate query plans that result in poor performance.
- A Database Analyst (DBA) who is very familiar with the data might be able to write a SQL query against an E-R schema that can theoretically answer a business question, but such detailed knowledge of the data is generally beyond the scope of the end user business analyst. Even when the SQL is crafted properly, there is often an unacceptably high response time in returning results to the user.
Dimensional Schemas
A dimensional schema is a denormalized schema that follows the business model. Dimensional schemas contain dimension tables, which contain attributes of the business, and fact tables, which contain individual records with a few facts and foreign keys to each of the dimension tables. Dimensional schemas are very good for business analysis and have two major advantages over E-R schemas for decision support:
- Better query performance
- Easier to understand
Dimensional schemas are not nearly as efficient as E-R schemas for updating discrete records, but they are excellent for queries that analyze the business across multiple dimensions.
Star Schema
A star schema is a dimensional schema with a single fact table that has foreign key relationships with several dimension tables. Figure 3 shows a sample star schema.
The dimension tables model the business and contain columns with descriptive attributes, such as Product, Size, and Color in the sample Products dimension. Dimension tables also have a key column (or columns) that uniquely identifies each row in the table.
The fact table has a multipart primary key, often made up of the foreign key references to the dimension tables. The fact table also contains all the measures, or facts, used to measure business performance. The lowest level of detail stored is the granularity of the fact table. Information at higher levels of aggregation is either calculated from the detail level records or precomputed and stored in separate aggregate fact tables, resulting in a multiple-star schema. For a discussion of aggregate tables, read Knowing the Aggregate Table Definitions.
Snowflake Schema
A snowflake schema is a dimensional schema where one or more of the dimensions are normalized to some extent. The difference between the type of normalization in a snowflake schema and in an E-R schema is that the snowflake normalization is based on business hierarchy attributes. The tables snowflaked off the dimensions have parent-child relationships with each other that mimic the dimensional hierarchies. Figure 4 shows a typical snowflake schema where the time dimension has been normalized into a snowflake.
| Bookshelf Home | Contents | Index | Search | PDF | |
Siebel Analytics Server Administration Guide Published: 23 June 2003 |
