Lecture
In the early 1970s, the Anglo-American scientist E. Codd developed the relational model of stored data organization, which marked the beginning of a new stage in the evolution of DBMSs. Thanks to its simplicity and flexibility, the relational model became dominant, and relational DBMSs became the de facto industry standard.
A relational database — a set of relations containing all the information that needs to be stored in the database. Physically, this means that information is stored in the form of two-dimensional tables linked by key fields.
Applying the relational model when building a data warehouse (DW) can, in a number of cases, provide advantages over multidimensional technology, especially in terms of efficiency when working with large data volumes and computer memory usage. ROLAP systems are built on the basis of relational data warehouses (RDWs), and this idea also belongs to Codd.
The RDW technology is based on the principle whereby dimensions are stored in flat tables just as in ordinary relational DBMSs, while facts (aggregated data) are stored in separate special tables within the same database. In this case, the fact table serves as the basis for the dimension tables linked to it. It contains the quantitative characteristics of objects and events, the aggregate of which is intended for subsequent analysis.
At the logical level, two schemas for building an RDW are distinguished — the «star» schema and the «snowflake» schema.
When using the «star» schema, the fact table is central, with all dimension tables linked to it. Thus, the information for each dimension is located in a separate table, which simplifies browsing them and makes the schema itself logically transparent and easy for the user to understand (fig. 8).
Fig. 8. RDW «star» schema
However, placing all information about a dimension in a single table is not always justified. For example, if the products being sold are organized into groups (i.e., there is a hierarchy), then it becomes necessary, in one way or another, to show which group each product belongs to, which leads to repeated duplication of group names. This not only increases redundancy but also raises the likelihood of inconsistencies arising (for instance, if the same product is mistakenly assigned to different groups).
To work more efficiently with hierarchical dimensions, a modification of the «star» schema was developed, called the «snowflake» schema. The main feature of the «snowflake» schema is that information about a single dimension can be stored across several linked tables. That is, if at least one of the dimension tables has one or more other dimension tables linked to it, then the «snowflake» schema is being applied (fig. 9).
Fig. 9. RDW «snowflake» schema
The main functional difference between the «snowflake» schema and the «star» schema is the ability to work with hierarchical levels that determine the degree of data detail. In the example given, the «snowflake» schema makes it possible to work with data at the maximum level of detail — for example, with each product individually — or to use a generalized representation by product groups with corresponding fact aggregation.
The choice of schema for building an RDW depends on the data collection and processing mechanisms used. Each schema has its own advantages and disadvantages, which, however, may manifest to a greater or lesser degree depending on the specifics of how the DW as a whole operates. The advantages of the «star» schema include:
The disadvantages of the «star» schema are:
The advantages of the «snowflake» schema are as follows:
In addition, there are a number of technical features that may determine RDW developers' preferences when choosing which schema to use.
The main advantages of RDWs are as follows:
The main drawback of relational data warehouses is that when a high level of data generalization and hierarchical dimensions are used, aggregate tables begin to «multiply» in such warehouses. As a result, the query execution speed of the relational warehouse slows down.
At the same time, in multidimensional warehouses, where data is stored in the form of multidimensional cubes, this problem practically does not arise, and in most cases a higher query execution speed can be achieved.
Thus, choosing the relational model when building a DW is advisable in the following cases.
- The volume of stored data is significant (multidimensional DWs become inefficient).
- The dimension hierarchy is simple (in other words, there is little aggregated data).
- Frequent changes to data dimensionality are required. With the relational model, one can simply add new tables, whereas with the multidimensional model a complex restructuring of the warehouse's physical structure would be required.
Comments