Directory
Objective
Types of dimension tables
Usage Scenarios for dimension tables
Keys and properties for dimension tables
Summary
Preface
From the macroscopic point of view, we tell you 7 questions. So from the microscopic point of view, concrete changes to how to build a consistent dimension table? This article summarizes the types and usage scenarios of the tables, as well as the settings and properties of the keys in the construction process.
Types of dimension tables
Generally speaking, there are two kinds of typei (invariant) and TYPEII table (change).
- Dimension attribute values persist, only additions and deletions.
- Properties can not change within a certain period, such as one day.
- Slowly changing dimensions. Some dimension attributes can vary, but the frequency of change is low.
- Fast change dimension. Some dimension attributes can vary, but the frequency of change is high.
- A large number of different scattered dimensions are integrated together
Usage Scenarios
- The Typei dimension table is suitable for most of these dimensional attributes, such as most properties of a date dimension, the address and name of a store, and so on. Generally speaking, business dimension information is the same day, and DW is used to do statistical analysis of data according to a certain period, most of the time is the cycle of the day. So from the point of view of data analysis, although all dimension attributes cannot be completely unchanged, the data needs of the business can be met by taking a snapshot of the dimension information at the end of the day. The full scale can be built for the life cycle without any change, and the rest can be built into the snapshot table. The snapshot table is preferred in case of ambiguity. Snapshots do not occupy too much space.
- Typeii
- Slowly changing dimensions. In some cases, the statistical cycle of the business is relatively short, it is not possible to build the table according to the Typei situation. At this point, if the frequency of changes in the dimension is not so fast, you can build a slowly changing dimension table. There are several construction methods for the slow-change dimension table, the more common one is the combination of EFFECT_FROM_DT (record effective start time), effect_to_date (record effective end time), Current_flag (is currently active) and other fields. For example, in the marketing process, a store in a few hours to adopt a different marketing means, in order to obtain the cost of all transactions, if there is no ready-made marketing data, can be in accordance with the transaction time to match the marketing activities, so as to obtain marketing costs.
- Fast change dimension. The situation is similar to the slow change dimension, the only problem is that the dimension attribute changes extremely frequently, even the second level minute change frequency, this time if still adopt the slow change dimension's construction way, the dimension table's data volume increases violently. And most of the dimension attributes do not change, only the individual attributes of the strong changes, resulting in a lot of space storage is unchanging information. The outcome of this scenario is that the vast majority of invariant or low-frequency attribute sets are built into Typei or slowly changing dimension tables, and the properties of super-fast changes are created as micro-dimension tables separately. The two tables have no dependencies, and they are all attached to the fact table.
primary keys and properties
There are two choices for the primary key of a dimension table: Natural key, surrogate key. A natural key is an ID with business meaning, such as a social security number, a date, and so on, and the surrogate key is the only key that is automatically generated. How do you make a choice between these two types of changes? If the business ID compares to the uniqueness of the business (no need to integrate with other business), or has a share (such as a company-level, store ID, etc.), consider using a natural key, especially a date dimension table that uses the date directly to master the key. If you need different business dimension information for integrated integration, this situation is more appropriate for generating surrogate keys to master keys.
Summary
The construction of the dimension table seems relatively simple, in most cases the business library will be directly, but in addition to the different levels of the dimension of Redundancy (Star model), but also need to grasp the details of the following dimensions of the construction of attention, after all, the dimension of errors will cause all the data errors.
PS recently has been working overtime Ah, nearly 2 weeks did not summarize, the dimension of the construction of a new head, and strive to take some time to learn and summarize, only a summary can improve AH.
Building the Data Warehouse No. 08: Logical Modeling –5– Dimension modeling Core Conformance Dimension 2
