The dimensional model has been widely accepted as a means of modeling certain types of query and reporting requirements, especially in a data mart environment. A dimensional model is a data model in which data is preclassified as a fact or dimension. Its most notable structure is the star schema.
The dimensional model distinguishes facts from dimensions. A fact is a measure of the business, such as sales volume or sales revenue. A dimension is a reference or master data table that describes, identifies or interrelates facts. The typical relationship between dimension levels or between facts and dimensions is many-to-one (M:1). However, some requirements can not be reduced to a M:1, because they inherently involve a many-to-many (M:M) structure. Dimensional modeling has defined two overall patterns for structuring informational data, the star and the snowflake schema.
Purpose
This article will take a closer look at the M:M in a dimensional model. The relationship can be across a multileveled dimension or between a dimension and the fact it describes. There are essentially four ways to support such a M:M relationship. Each of the solutions is workable but each has its own set of pros and cons. Each involves its own unique trade-offs. Trade-off is the name of the game at this stage, because no matter what solution you choose, you will be compromising one feature in favor of another. One feature will become an advantage, the other a disadvantage. The four solutions are: arrays, groups, bridges and fixed structures. Before delving further into them a discussion of the basic dimensional model structures is required.
Star Schema
The basis for the dimensional model is the phenomenon that many queries can be subdivided into fact and dimension. For example, provide the breakdown of revenue and volume by customer, product and week. A host of other queries do not benefit from this distinction, however. For example, what two products are sold together, most often and when? What are the most important characteristics of customers that are about to lapse? What top 10 customers should I call this week?
A star schema is a structure with a fact table surrounded by a single perimeter of dimensions, wherein any dimension that is multileveled is flattened into a single dimension. There are cases where the star schema is just not appropriate, however, because it is not suitable for the business requirements or for query performance.
Dimensions in a Star
The typical relationship between dimensions and facts in a dimensional model is one-to-many (1:M) - a dimension that applies to many facts. A product can appear in multiple purchase orders, or a customer can place multiple orders. Dimensions often consist of multiple summary levels. When a multileveled dimension is flattened, there is an implied internal structure to the dimension. A common relationship is for the dimension grain to be at the lowest level with the higher levels in order above it. The relationship between lower and higher levels is M:1. Figure 2 illustrates an example. A product group consists of multiple product sub-groups and a sub-group consists of multiple products.
Complex Dimensions
