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


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


* Expansibility
* Ease of use
* performance

* Business relevance
* Attribute differences
Split Vertically

* Master slave dimension
Historical archiving

Dimensional change

Slowly changing dimension


* 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


©2019-2020 Toolsou All rights reserved,
Final review of database : Summary of comprehensive application questions Laplance operator ( Second derivative ) Simple learning of computer composition principle pyqt Button call python program _PyQt: Link button to function in program How much can you go up once you change jobs ? Today, I saw the ceiling of job hopping python in str Function usage _python in str Usage Summary of built-in functions MySQL trigger web The server nginx---linux Installation and deployment C++ Chapter V polymorphism exercises :( It's coming to an end )python Check built-in functions , How to check python Built in function