Differences
This shows you the differences between two versions of the page.
| Next revision | Previous revision | ||
| arch:data_modeling [2021/10/17 11:39] – Vadim Zhdanov - load essay: Data modeling languages for data warehouses and data lakes vbzhdanov | arch:data_modeling [2026/08/29 07:59] (current) – external edit 127.0.0.1 | ||
|---|---|---|---|
| Line 1: | Line 1: | ||
| ====== Data modeling languages for data warehouses and data lakes ====== | ====== Data modeling languages for data warehouses and data lakes ====== | ||
| - | By --- //[[vbzhdanov@edu.hse.ru|Vadim Zhdanov]]// | + | |
| + | By — //Vadim Zhdanov// | ||
| ===== Introduction ===== | ===== Introduction ===== | ||
| + | |||
| All data modeling methods can be divided into 2 groups: structural and process. The relational paradigm, relevant for typical QCD, focuses on defining entities and relationships between them, in order to then represent these concepts in the form of related tables using ER-diagrams (Entity-Relationship). Data Flow Diagrams (DFDs) are used to describe data flows in business analysis. DFD diagrams clearly show the exchange of information between data warehouses (not in the sense of QCD, but rather, source databases, consumers and other storage locations), internal processes of the system and entities external to it. This dynamic method can be used in the conceptual design of Big Data solutions, when the main sources and consumers of data, as well as their processing processes, in particular, ETL, are determined. Such visualization will be useful when designing modern QCD integrated with data lakes and cloud services of advanced Big Data analytics. | All data modeling methods can be divided into 2 groups: structural and process. The relational paradigm, relevant for typical QCD, focuses on defining entities and relationships between them, in order to then represent these concepts in the form of related tables using ER-diagrams (Entity-Relationship). Data Flow Diagrams (DFDs) are used to describe data flows in business analysis. DFD diagrams clearly show the exchange of information between data warehouses (not in the sense of QCD, but rather, source databases, consumers and other storage locations), internal processes of the system and entities external to it. This dynamic method can be used in the conceptual design of Big Data solutions, when the main sources and consumers of data, as well as their processing processes, in particular, ETL, are determined. Such visualization will be useful when designing modern QCD integrated with data lakes and cloud services of advanced Big Data analytics. | ||
| Returning to the structural design of DWH, here are the 3 most common modeling techniques: | Returning to the structural design of DWH, here are the 3 most common modeling techniques: | ||
| + | |||
| * multidimensional for data marts and the core level in the LSA structure, when the analytics needs of business users are known and correctly defined. In this case, the data mart is considered not a physical layer, but a set of representations of the model core. | * multidimensional for data marts and the core level in the LSA structure, when the analytics needs of business users are known and correctly defined. In this case, the data mart is considered not a physical layer, but a set of representations of the model core. | ||
| * relational (IDEF1X) for defining relationships between QCD tables, BI dashboards (data marts) and other target systems that are relatively stable and should be integrated with the QCD core model. | * relational (IDEF1X) for defining relationships between QCD tables, BI dashboards (data marts) and other target systems that are relatively stable and should be integrated with the QCD core model. | ||
| * Data Vault or Data Vault is a hybrid approach that combines the merits of the third normal form (3NF) and the star schema used in denormalized QCD. | * Data Vault or Data Vault is a hybrid approach that combines the merits of the third normal form (3NF) and the star schema used in denormalized QCD. | ||
| + | |||
| Инструментально все эти методы реализованы в виде различных CASE-средств, | Инструментально все эти методы реализованы в виде различных CASE-средств, | ||
| + | |||
| ===== Multi-dimensional modeling method ===== | ===== Multi-dimensional modeling method ===== | ||
| + | |||
| A multidimensional model is visually represented using a cube (or, in the case of more than three dimensions, a hypercube). | A multidimensional model is visually represented using a cube (or, in the case of more than three dimensions, a hypercube). | ||
| - | {{ : | + | [[arch: |
| ==== Basic concepts of the method ==== | ==== Basic concepts of the method ==== | ||
| + | |||
| Multivariate modeling is a method of modeling and visualizing data as a set of numerical or linguistic metrics or parameters that describe general aspects of an organization' | Multivariate modeling is a method of modeling and visualizing data as a set of numerical or linguistic metrics or parameters that describe general aspects of an organization' | ||
| The multivariate modeling method is based on the following basic concepts: facts, attributes, dimensions, parameters (metrics), hierarchy, granularity. | The multivariate modeling method is based on the following basic concepts: facts, attributes, dimensions, parameters (metrics), hierarchy, granularity. | ||
| Line 21: | Line 29: | ||
| Often, the members of a dimension are in a part-whole or parent-child relationship, | Often, the members of a dimension are in a part-whole or parent-child relationship, | ||
| A parameter, metric or indicator is a numerical characteristic of a fact that determines the effectiveness of an organization' | A parameter, metric or indicator is a numerical characteristic of a fact that determines the effectiveness of an organization' | ||
| + | |||
| ==== Facts ==== | ==== Facts ==== | ||
| + | |||
| From the point of view of the relationship between dimensions and facts, the latter can be divided into the following classes: | From the point of view of the relationship between dimensions and facts, the latter can be divided into the following classes: | ||
| + | |||
| * additive facts. A fact is called additive if it makes sense to use it with any dimensions to perform summation operations in order to obtain some meaningful result. | * additive facts. A fact is called additive if it makes sense to use it with any dimensions to perform summation operations in order to obtain some meaningful result. | ||
| * semi-additive facts. A fact is called semi-additive if it makes sense to use it in conjunction with some measurements to perform summation operations in order to obtain some meaningful result. | * semi-additive facts. A fact is called semi-additive if it makes sense to use it in conjunction with some measurements to perform summation operations in order to obtain some meaningful result. | ||
| * non-additive facts. A fact is called non-additive if it does not make sense to use it in conjunction with any dimension to perform summation operations in order to obtain any meaningful result. | * non-additive facts. A fact is called non-additive if it does not make sense to use it in conjunction with any dimension to perform summation operations in order to obtain any meaningful result. | ||
| * numerical measures of intensity. A fact is called a numerical measure of intensity if it, being non-additive in time, allows aggregation and summation over a certain number of time periods. | * numerical measures of intensity. A fact is called a numerical measure of intensity if it, being non-additive in time, allows aggregation and summation over a certain number of time periods. | ||
| + | |||
| ==== Table of facts ==== | ==== Table of facts ==== | ||
| + | |||
| Facts in a multidimensional model are usually presented in the form of a fact table. In the logical entity-relationship model, a fact table is represented by an entity whose attributes are facts (metrics or descriptions) and a composite key that links the fact table to the dimension tables with a one-to-many relationship. | Facts in a multidimensional model are usually presented in the form of a fact table. In the logical entity-relationship model, a fact table is represented by an entity whose attributes are facts (metrics or descriptions) and a composite key that links the fact table to the dimension tables with a one-to-many relationship. | ||
| Fact tables are divided into three main categories based on the level of detail of the facts. | Fact tables are divided into three main categories based on the level of detail of the facts. | ||
| + | |||
| * Transactional fact table. In such a fact table, facts are stored that record certain events (transactions). | * Transactional fact table. In such a fact table, facts are stored that record certain events (transactions). | ||
| * Periodic snapshot fact table This table collects facts that capture the current state of a particular line of business. | * Periodic snapshot fact table This table collects facts that capture the current state of a particular line of business. | ||
| * Cumulative snapshot fact table This table collects facts that capture some of the final state of a particular line of business at the current point in time. | * Cumulative snapshot fact table This table collects facts that capture some of the final state of a particular line of business at the current point in time. | ||
| + | |||
| The main characteristics of the fact table are as follows. | The main characteristics of the fact table are as follows. | ||
| + | |||
| - The fact table contains numerical parameters (metrics). | - The fact table contains numerical parameters (metrics). | ||
| - Each fact table has a composite key made up of the primary keys of the dimension tables. | - Each fact table has a composite key made up of the primary keys of the dimension tables. | ||
| - The fact table has, as a rule, a small number of fields, no more than 20. | - The fact table has, as a rule, a small number of fields, no more than 20. | ||
| - The data in the fact table has the following properties: | - The data in the fact table has the following properties: | ||
| + | |||
| - numerical parameters are used for aggregation and summation; | - numerical parameters are used for aggregation and summation; | ||
| - data values must have the properties of additivity or semi-additivity with respect to dimensions in order to be summarized; | - data values must have the properties of additivity or semi-additivity with respect to dimensions in order to be summarized; | ||
| - All data in the fact table must be uniquely identified through the keys of the dimension tables. | - All data in the fact table must be uniquely identified through the keys of the dimension tables. | ||
| + | |||
| ==== Measurement ==== | ==== Measurement ==== | ||
| + | |||
| The main characteristics of the measurement table are as follows. | The main characteristics of the measurement table are as follows. | ||
| + | |||
| * Dimension tables contain data on the detail of facts. | * Dimension tables contain data on the detail of facts. | ||
| * Dimension tables contain descriptive information about the numerical values in the fact table. | * Dimension tables contain descriptive information about the numerical values in the fact table. | ||
| Line 48: | Line 68: | ||
| * Dimension tables usually contain significantly fewer rows than fact tables. | * Dimension tables usually contain significantly fewer rows than fact tables. | ||
| * Attributes of dimension tables are commonly used when visualizing data in reports and queries. | * Attributes of dimension tables are commonly used when visualizing data in reports and queries. | ||
| + | |||
| ==== Basic schemas ==== | ==== Basic schemas ==== | ||
| - | There are several schemas for multidimensional data modeling. Two of these are considered basic: the star schema and the snowflake schema. In more complex cases, so-called | + | |
| + | There are several schemas for multidimensional data modeling. Two of these are considered basic: the star schema and the snowflake schema. In more complex cases, so-called | ||
| A star schema has one fact table and multiple dimension tables. Dimension tables are denormalized. | A star schema has one fact table and multiple dimension tables. Dimension tables are denormalized. | ||
| - | {{ : | + | [[arch: |
| A snowflake schema has one fact table and multiple normalized dimension tables. | A snowflake schema has one fact table and multiple normalized dimension tables. | ||
| - | {{ : | + | [[arch: |
| ===== IDEF1X ===== | ===== IDEF1X ===== | ||
| + | |||
| IDEF1X is a data modeling language for developing semantics of data models. IDEF1X is used to form graphical representations of information models that reflect the structure and semantics of information within an environment or system. | IDEF1X is a data modeling language for developing semantics of data models. IDEF1X is used to form graphical representations of information models that reflect the structure and semantics of information within an environment or system. | ||
| This modeling technique is used to model data in a standard, consistent, and predictable manner in order to manage it as a resource. The main goals of the IDEF1X standard are to provide: | This modeling technique is used to model data in a standard, consistent, and predictable manner in order to manage it as a resource. The main goals of the IDEF1X standard are to provide: | ||
| + | |||
| * A tool for a complete understanding and analysis of information resources of organizations | * A tool for a complete understanding and analysis of information resources of organizations | ||
| * A general means of representing and conveying the complexity of data | * A general means of representing and conveying the complexity of data | ||
| Line 62: | Line 87: | ||
| * Means for defining an application-independent representation of the data that can be verified by users and translated into a physical database design | * Means for defining an application-independent representation of the data that can be verified by users and translated into a physical database design | ||
| * Techniques for deriving an integrated data definition from existing data resources. | * Techniques for deriving an integrated data definition from existing data resources. | ||
| - | The main purpose of IDEF1X is to support integration. The integration approach focuses on capturing, managing and using a single semantic definition of a data resource called a "conceptual schema". A "conceptual schema" | + | |
| + | The main purpose of IDEF1X is to support integration. The integration approach focuses on capturing, managing and using a single semantic definition of a data resource called a “conceptual schema”. A “conceptual schema” provides a single, integrated definition of data within an enterprise that is not biased towards any single use of the data and is independent of how the data is physically stored or accessed. The main purpose of a conceptual schema is to provide a consistent definition of values and relationships between data that can be used to integrate, share, and manage data integrity. A conceptual diagram should have three important characteristics: | ||
| * Be consistent with the business infrastructure and be correct for all areas of application | * Be consistent with the business infrastructure and be correct for all areas of application | ||
| * Expand so that new data can be defined without changing previously defined data. | * Expand so that new data can be defined without changing previously defined data. | ||
| * Transformability both in the required user views and in various data storage and access structures. | * Transformability both in the required user views and in various data storage and access structures. | ||
| - | {{ : | + | |
| + | [[arch: | ||
| ===== DATA VAULT ===== | ===== DATA VAULT ===== | ||
| + | |||
| Data Vault is one of the data modeling methods used in QCD design. | Data Vault is one of the data modeling methods used in QCD design. | ||
| To maintain simplicity of design and ensure maximum flexibility of QCD, a minimum of basic concepts are used: | To maintain simplicity of design and ensure maximum flexibility of QCD, a minimum of basic concepts are used: | ||
| + | |||
| * Hub - a table that stores the main view of a business entity from the functional position of the subject area, for example. The hub contains a unique and unchanging business key - one or more fields that identify an entity in terms of business. In addition to the business key, the hub contains meta-fields: | * Hub - a table that stores the main view of a business entity from the functional position of the subject area, for example. The hub contains a unique and unchanging business key - one or more fields that identify an entity in terms of business. In addition to the business key, the hub contains meta-fields: | ||
| * A link that represents a relationship or transaction between two or more business components, linking them through their respective business keys. It contains the same metadata as the Hub: download timestamp and data source. The keys of the Hubs to be linked are also migrated to the Link entity, forming a composite key. | * A link that represents a relationship or transaction between two or more business components, linking them through their respective business keys. It contains the same metadata as the Hub: download timestamp and data source. The keys of the Hubs to be linked are also migrated to the Link entity, forming a composite key. | ||
| * Satellite - a table with contextual information of the Hub key. To be flexible, the structure of the Satellites should be capable of storing new or changed detail data. In addition to the single key of the parent hub and its context data, the Satellite table also contains a typical set of metadata. Thus, in Satellites, you can store the history of context changes by adding a new record when updating in the source system. It is customary to store the context from different source systems in separate satellite tables. | * Satellite - a table with contextual information of the Hub key. To be flexible, the structure of the Satellites should be capable of storing new or changed detail data. In addition to the single key of the parent hub and its context data, the Satellite table also contains a typical set of metadata. Thus, in Satellites, you can store the history of context changes by adding a new record when updating in the source system. It is customary to store the context from different source systems in separate satellite tables. | ||
| + | |||
| ==== Model building rules ==== | ==== Model building rules ==== | ||
| + | |||
| The Data Vault model is created according to the following algorithm: | The Data Vault model is created according to the following algorithm: | ||
| + | |||
| - defining Hubs based on key business entities and their use in the subject area; | - defining Hubs based on key business entities and their use in the subject area; | ||
| - identifying Links by identifying possible relationships between business keys - and understanding the context of its work; | - identifying Links by identifying possible relationships between business keys - and understanding the context of its work; | ||
| - Definition of Satellites - modeling the context of each business entity and transaction (Links) connecting the Hubs; | - Definition of Satellites - modeling the context of each business entity and transaction (Links) connecting the Hubs; | ||
| - Simulation of point-in-time tables derived from Satellites. | - Simulation of point-in-time tables derived from Satellites. | ||
| + | |||
| In this case, you must adhere to the following rules: | In this case, you must adhere to the following rules: | ||
| + | |||
| - Hub keys cannot be migrated to other Hubs, so as not to violate the flexibility and extensibility of the Data Vault modeling technique; | - Hub keys cannot be migrated to other Hubs, so as not to violate the flexibility and extensibility of the Data Vault modeling technique; | ||
| - Business keys and primary keys of the Hub never change; | - Business keys and primary keys of the Hub never change; | ||
| Line 91: | Line 127: | ||
| - Satellites record only changes without duplicating lines; | - Satellites record only changes without duplicating lines; | ||
| - Data is distributed according to the structure of the Satellites based on the type of information and the rate of its change. | - Data is distributed according to the structure of the Satellites based on the type of information and the rate of its change. | ||
| + | |||
| ===== Conclusion ===== | ===== Conclusion ===== | ||
| + | |||
| Since historically DWHs evolved from relational DBMSs, they also inherited the design approaches adopted in this area. In particular, the use of 3NF - the third normal form, when each non-key attribute of the table should provide information about only the full key and nothing more. However, this application of this approach showed its following disadvantages: | Since historically DWHs evolved from relational DBMSs, they also inherited the design approaches adopted in this area. In particular, the use of 3NF - the third normal form, when each non-key attribute of the table should provide information about only the full key and nothing more. However, this application of this approach showed its following disadvantages: | ||
| + | |||
| * scalability and flexibility issues due to tightly coupled tables where adding an additional parent table will cause cascading changes to all underlying subordinate tables. And when inserting a new row with an existing parent key, all child rows must be reassigned to the new parent key. | * scalability and flexibility issues due to tightly coupled tables where adding an additional parent table will cause cascading changes to all underlying subordinate tables. And when inserting a new row with an existing parent key, all child rows must be reassigned to the new parent key. | ||
| * dependence of the primary key on time and on the aforementioned table connectivity; | * dependence of the primary key on time and on the aforementioned table connectivity; | ||
| Line 98: | Line 137: | ||
| * time consuming queries; | * time consuming queries; | ||
| * top-down design and corresponding top-down implementation. | * top-down design and corresponding top-down implementation. | ||
| - | To get around these shortcomings, | + | |
| + | To get around these shortcomings, | ||
| * isolation of subject-oriented information; | * isolation of subject-oriented information; | ||
| * possible redundancy of data; | * possible redundancy of data; | ||
| Line 108: | Line 149: | ||
| * inconvenient environment for data mining; | * inconvenient environment for data mining; | ||
| * the need for top-down design with bottom-up implementation. | * the need for top-down design with bottom-up implementation. | ||
| + | |||
| For this, the Data Vault method is now used as the most promising. | For this, the Data Vault method is now used as the most promising. | ||
| + | |||
| ===== Sources ===== | ===== Sources ===== | ||
| - | - http:// | ||
| - | - https:// | ||
| - | - https:// | ||
| - | - https:// | ||
| - | - https:// | ||
| - | - https:// | ||
| - | - http:// | ||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| - | |||
| + | - [[http:// | ||
| + | - [[wp> | ||
| + | - [[wp> | ||
| + | - [[wp> | ||
| + | - [[https:// | ||
| + | - [[https:// | ||
| + | - [[http:// | ||