1.ER Model
Entity relationship model describes business , Topic oriented abstraction from the perspective of enterprise , It is not an abstraction of entity object relationships for a business process
requirement :
* Comprehensive understanding of business data
* Long implementation cycle
* High requirement of modeling ability
ER The starting point of the model is to integrate enterprise data , Merge data from systems by topic , And the consistency is processed , Service for data analysis and decision making , But not directly for decision-making
Modeling steps :
* High level model Highly abstract model , Used to describe the main topics and the relationships between them , General summary of enterprise business
* Middle level model Refine data based on high level model
* Underlying model Physical table design based on Performance Storage , Table merging partition design
ER Model representation
FS-LDM
2. Dimension model
Dimension modeling triggers data warehouse model from analysis decision , Service for analysis , Complete data analysis quickly for users , Support complex query and corresponding performance , The representation is star model
Modeling steps :
* Select business process
A business process can be a single business event , Like trading , Refund, etc , It can also be the state of an event , Such as the balance of the current account ; It can also be a business process composed of a series of related business events , It depends on our analysis of certain events , Or the current state , Or the efficiency of event flow
* Select granularity
* Identification dimension table
* Identifying facts
3.Data Vault Model
For data integration , Establish auditable data layer , Emphasize the historical nature of data , Traceability , Atomicity
form :
* Hub Business entity
* Link Hub The relationship between
* satellite Hub Detailed description , One Hub There can be more than one Satellite
4.Anchor Model
Dimensional modeling
Fundamentals of dimensional design
Dimension design method
* Select a dimension or create a new dimension
* Determine the main dimension table
* Determine the relevant dimension table
* Determine dimension properties
1) It can generate rich dimension attributes as much as possible
2) Give as many meaningful field descriptions as possible
3) Distinguish between attributes and facts
4) Try to precipitate general dimension attributes
Dimension hierarchy
Normalization and denormalization
* Convenience of anti normalization , Ease of use , Good performance
Dimensional consistency
1) Shared dimension table
2) Consistency roll up , A dimension attribute of one dimension is a subset of the dimension attributes of another dimension
3) Cross attribute , Two dimensions have the same part of the dimension attributes
Dimension design advanced theme
Dimensional integration
Integrated embodiment
* Field name unification
* Unified field type
* Code and code value statistics
* Unified tables with the same business meaning ( High cohesion , Low coupling )
split horizon
principle
* Expansibility
* Ease of use
* performance
basis
* Business relevance
* Attribute differences
Split Vertically
* Master slave dimension
Historical archiving
Dimensional change
Slowly changing dimension
handle
* Override dimension values No history
* Insert new dimension row
* Add dimension column
Snapshot dimension table
Extreme storage
Special dimensions
Recursive hierarchy
* delayering
* Hierarchical bridging
Behavior dimension
Multi value dimension
Multivalued attribute
Miscellaneous dimensions
Technology
Daily Recommendation