database modeling: OLAP OLTP
2019-08-27 本文已影响0人
鲸鱼酱375
1.what is OLTP
- Online Transaction Processing
- OLTP = Data Base
- Smaller system with focus on organization of data for efficiency working in real time
- System in place that allows for transactions to happen
2.what is OLAP
- Online Analytical Processing
- OLAP = Data Warehouse
- Larger systems that can contain 50+ years of data
- Main focus is storing large amounts of data for use in analysis and computation to find trend or measurable for upper management
2.1 Features in OLAP
-
Dimensions
Tables in a data warehouse used to store descriptive data -
Facts
Tables in a data warehouse used to stored mathematical or analytical data for measurements -
Surrogate Keys
Replacement for primary keys
Auto incremental keys for only use in the data warehouse for finding data（当business key repeating) -
Business Key
Key used to connect or reference a key in an OLTP
3. OLAP VS OLTP
| OLTP | OLAP |
|---|---|
| Data Base | Data Warehouse |
| relation tables | dimensional and fact tables |
| schema | different schema(star/snowflake) |
| Less than 1 year of data typical | More than 50 years of data typical |
| Optimized for searching data in real time and normalization | Optimized for storing and analyzing data when needed and is de-normalized |
| Less likely to have a large number of indexes or partitions | More likely to have larger numbers of indexes and partition for the data |
4.Why is an Archive Data Warehouse?
- An offline portion of the data warehouse
- You can store much older data that is no longer being referenced
- Helps to optimize search through data by shrinking the amount of data available for search
5. 笔记
why denormalized?
less join,less ram
dml in processing from oltp to olap:
- delete clustered index,then loading data,then recreate the clustered index in olap
- partition: (?)
