Post by account_disabled on Feb 22, 2024 7:26:36 GMT
In the previous article in this series, we looked at Kimball's first two mistakes, as well as brief definitions of the basic terms that form the basis of this series. In this part, we'll look at 4 more mistakes related to data warehouse design, and we'll continue our overview of the core of a BI system. "Mistake 10: Dividing hierarchies and levels of hierarchies into multiple dimensions." If additional dimensions are created within hierarchy levels, the statements that must be executed when consuming data from the data warehouse become complex. Furthermore, since it is an unnecessary dimension, the space occupied increases significantly (as the dimension field generates millions of records.
Given the following hierarchy for the Point of Sale dimension: Country > Region > City > Store in Normalized modeling, we would get: Tabla Obviously, this structure Switzerland Mobile Number List is more intuitive, but when querying the sales fact table by country, we should generate a query that joins all these tables, and we might try to put the location Move to the Country fact table. As shown below: Tabla This will generate a lot of unnecessary data in the fact table. Therefore, it is recommended to add the primary key of each hierarchy of the point of sale dimension to the store table, leaving : Tabla This would keep the star design, with the dimensions defined in a single table.
Mistake 9: Not handling slowly changing dimensions." As I have already mentioned, not considering this possibility will mean that the data in the data warehouse The utilization of will suffer huge losses. Given the following dimension table: Tabla assumes that over time, the company changes its business model to a more specialized model and changes the product master. If this is not considered during the design This fact, then when this data changes, the entire history will lose its value because the products recorded in the sales fact table will not correspond to the current products.
Mistake 8: Creating "Smart Keys" to Concatenate Dimension Tables with Facts tables are related." In an operational sense, using user-defined keys may be more useful for business processes (mnemonic rules). But its use in a data warehouse is incorrect because when we assume it is unique Inconsistencies can occur in the data when that key changes or is repeated. Additionally, numeric values take up less disk space than strings. For example, suppose the category primary key is the first 3 letters of the category description, and the music category is added .The music category already exists and its key Mus, so we are not allowed to dump the data otherwise we will get incorrect results. "Error 7: Adding dimensions to the fact table before defining the granularity.
Given the following hierarchy for the Point of Sale dimension: Country > Region > City > Store in Normalized modeling, we would get: Tabla Obviously, this structure Switzerland Mobile Number List is more intuitive, but when querying the sales fact table by country, we should generate a query that joins all these tables, and we might try to put the location Move to the Country fact table. As shown below: Tabla This will generate a lot of unnecessary data in the fact table. Therefore, it is recommended to add the primary key of each hierarchy of the point of sale dimension to the store table, leaving : Tabla This would keep the star design, with the dimensions defined in a single table.
Mistake 9: Not handling slowly changing dimensions." As I have already mentioned, not considering this possibility will mean that the data in the data warehouse The utilization of will suffer huge losses. Given the following dimension table: Tabla assumes that over time, the company changes its business model to a more specialized model and changes the product master. If this is not considered during the design This fact, then when this data changes, the entire history will lose its value because the products recorded in the sales fact table will not correspond to the current products.
Mistake 8: Creating "Smart Keys" to Concatenate Dimension Tables with Facts tables are related." In an operational sense, using user-defined keys may be more useful for business processes (mnemonic rules). But its use in a data warehouse is incorrect because when we assume it is unique Inconsistencies can occur in the data when that key changes or is repeated. Additionally, numeric values take up less disk space than strings. For example, suppose the category primary key is the first 3 letters of the category description, and the music category is added .The music category already exists and its key Mus, so we are not allowed to dump the data otherwise we will get incorrect results. "Error 7: Adding dimensions to the fact table before defining the granularity.