itm618 week 2
What are the 3 types of data's within an evolutionary path?
- Operational Databases
- Data Warehouses
- Preprocessed data
Explain the purpose of operational databases.
They run fundamental business tasks
What is the operation of the operational database? Provide an example.
The operation is On-Line Transaction Processing (OLTP). For example, banking or sales transactions, course registration, etc.
What is the driver between operational databases and data warehouses?
Driven by the need for decision support
Explain the purpose of data warehouses.
They help with planning, problem solving, and decision-making
What is the operation of the data warehouses? Provide an example.
On-Line Analytical Processing (OLAP). For example, data summarization, data aggressions, etc
Who are the users of data warehouses?
Decision makers (managers, analysts, executives, etc.)
What is the driver between data warehouses and preprocessed data?
Driven by the need for complex decision support functions
What is the purpose of preprocessed data?
To help with more complicated decision making and prediction
What is the operation of the preprocessed data? Provide an example.
Data mining. For example, assocaition, classification, clustering, etc.
Who are the users of preprocessed data?
Decision makers
What are the 2 purposes of using a data warehouse?
1) Save time building reports
2) Slice and dice in ways you could not do before
What is a data warehouse? (provide 2 definitons)
- It is a data store (e.g a database of some sort) that integrates data from different sources throughout an organization for decision-support purposes
- A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management's decision-making process
What are the characteristics of a data Warehouse?
- Subject-oriented
- Integrated
- Time-variant
- Non - volatile
Explain what the subject-oriented characteristic refers to.
- Organized around major subjects, such as customer, product, sales.
- Excluding data that are not useful in the decision support process.
Explain what the integrated characteristic refers to.
Constructed by integrating multiple, heterogeneous data sources
Explain what the time-variant characteristic refers to.
- Stores historical data (such as the past 5 or 10 years),
- The "time" dimension is important
- Does not store the current data as in operational databases
Explain what the non-volatile characteristic refers to.
- A physically separate store of data transformed from the operational environment
- Operational update of data does not occur in the data warehouse environment
- Does not require transaction processing, recovery, and concurrency control mechanisms
- May require periodical update of data
- Much less frequent than operational update of data
What is the ETL process?
- It is the process of combining data from multiple sources into a large, central repository called a data warehouse
- Data warehouse systems use back-end tools and utilities to populate and refresh their data
What are the 4 steps of ETL?
- Data extraction
- Data Cleaning
- Data Transformation
- Load
What is a data warehouse based on?
It is based on a multidimensional data model
What are the 2 aspects of a data cube? Provide examples for each
- Dimensions. Examples include item, time, and location
- Fact (about a subject). Examples include numerical measures like sales in dollars
What are the 2 possible sets of data cubes?
- Different dimensions
- Single Subject or multiple subjects
True or false. A data warehouse cannot contain data cubes of different subjects.
False. A data warehouse may contain data cubes of different subjects
What is a common OLAP operation?
To aggregate a measure over one or more dimensions
What is OLAP?
- It stands for On-Line Analytical Processing
- It allows for fast analysis of multi-dimensional data
- It views the multi-dimensional data from a different perspective
What are the basic OLAP operations?
Roll-up
- Drill-down
- Slice
- Dice
- Pivot
Explain what the roll-up operation does. Provide an example
- It summarize the data by climbing up a hierarchy of dimensions or by dimension reduction
- For example, given sales by city, quarter and product, we can roll-up to get sales by province, quarter and product
Explain what the drill-down operation does. Provide an example
- It is the inverse of a roll-up
- From a higher-level summary to a lower-level summary or detailed data, or introducing new dimensions
- For example, Given sales by province, quarter and product, get sales by city, quarter and product. Another example is given total sales per quarter, get total sales per quarter for each product.
Explain what the slice operation does.
- Equality selections on one or more dimensions
- Selection on one dimension.
Explain what the dice operation does.
- Range selections on all dimensions.
- Selection on two or more dimensions
Explain what the pivot operation does. Provide an example.
- It re-orient the multi-dimensional view/the cube so that you can view the data from a different angle for visualization purposes
- For example, a financial analyst might want to view or "pivot" data in various ways, such as displaying all the cities down the page and all the products across a page.
Explain the different between OLTP and OLAP.
- OLTP is the major task of traditional relational DBMS. The day-to-day operations include purchasing, inventory, banking, manufacturing, payroll, registration, accounting, etc.
- OLAP is the major task of data warehouse system. It is used for data analysis and aggregation for decision making