Difference between revisions of "OLAP"
Jump to navigation
Jump to search
| Line 28: | Line 28: | ||
==== Example ==== | ==== Example ==== | ||
* Fact table | * Fact table | ||
| − | Sales(storeID, itemID, custID, qty, price) <br /> | + | : Sales(storeID, itemID, custID, qty, price) <br /> |
| − | + | ''dimension'' attributes: storeID, itemID, custID (=> foreign keys) | |
| − | + | ''dependent attributes'': qty, price | |
* Dimenstion tables | * Dimenstion tables | ||
| − | Store(storeID, city, state)<br /> | + | : Store(storeID, city, state)<br /> |
| − | Item(itemID, category, brand, color, size)<br /> | + | : Item(itemID, category, brand, color, size)<br /> |
| − | Customer(custID, name, address) | + | : Customer(custID, name, address) |
Revision as of 04:46, 13 November 2013
Two Types of Database Activity
- OLTP (Online Transaction Processing)
- short transactions
- simple queries
- touch small portions of data
- frequent update
- OLAP (Online Analytical Processing)
- long transactions
- complex queries
- touch large portions of the data
- infrequent updates
Terminologies
- Data Warehousing
Bring data from operational(OLTP) sources into a single "warehouse" for (OLAP) analysis
- Decision Support System (DSS)
Infrastructure for data analysis, e.g., data warehouse tuned for OLAP
Star Schema
- Fact table
Updated frequently, often append-only, very large
e.g. sales transactions, course enrollments, page view
- Dimension tables
Updated infrequently, not as large
e.g. stores, items, customers, students, courses, webpages, users, advertisers
- Fact table references dimension tables. Hence, the star shape.
Example
- Fact table
- Sales(storeID, itemID, custID, qty, price)
dimension attributes: storeID, itemID, custID (=> foreign keys) dependent attributes: qty, price
- Dimenstion tables
- Store(storeID, city, state)
- Item(itemID, category, brand, color, size)
- Customer(custID, name, address)