data warehouse Flashcards
What are the three levels of decision making in a company and how do they impact the underlying data infrastructure?
Operational Level
Tactical Level
Strategic Level
Operational Level:
Primary Focus: Handle simple data operations, such as INSERT, UPDATE, DELETE, and SELECT statements.
Key Performance Indicators (KPIs): Transaction throughput and response time.
Role in Decision Making:
Data Processing: Perform real-time processing and support day-to-day business operations.
Automation: Facilitate the automation of routine tasks and ensure that transactions are processed efficiently.
Examples: Point of Sale (POS) systems, Online Transaction Processing (OLTP) systems
Tactical Level:
Primary Focus: Provide information to support mid-term decision-making.
Key Features:
Complex Queries: Support complex ad-hoc SELECT statements for in-depth analysis.
Data Summarization: Offer various levels of data aggregation to present summarized information.
Multidimensional Data Analysis: Allow users to explore data from different perspectives (e.g., OLAP systems).
Role in Decision Making:
Trend Analysis: Detect and analyze trends and patterns within the data.
Scenario Analysis: Evaluate different scenarios and their potential outcomes to make informed decisions.
Examples: Management Information Systems (MIS), Business Intelligence (BI) tools.
Strategic Level:
Primary Focus: Aid in long-term decision-making and strategic planning.
Key Features:
Data Integration: Integrate data from various sources, providing a comprehensive view of the business.
Data Warehousing: Utilize data warehouses to store and manage large volumes of historical data.
Advanced Data Analysis: Support complex analyses, such as predictive modeling, forecasting, and time-series analysis.
Role in Decision Making:
Strategic Planning: Assist in formulating and evaluating strategic initiatives.
Long-term Trends: Analyze long-term trends and patterns to guide future business strategies.
Examples: Executive Information Systems (EIS), Enterprise Resource Planning (ERP) systems, Data Warehouses.
characteristics of
Operational and strategic levels
Operational and strategic levels have different decision-making horizons and require distinct data infrastructure support.
What is a data warehouse and how does it differ from traditional databases?
A data warehouse is a centralized repository of integrated data from multiple sources, designed to support business analysis and decision-making.
Key differences from traditional databases:
Purpose: While traditional databases are optimized for transactional processing, data warehouses are focused on analytical reporting and decision support.
Schema: Data warehouses typically have a star or snowflake schema, optimized for querying large datasets, whereas traditional databases often have a normalized schema for efficient updates.
Data: Data warehouses often contain historical data, while traditional databases focus on current data.
Access: Data warehouses are typically accessed by analysts and decision-makers, while traditional databases are used by operational systems
characteristics of data warehouse
Subject-Oriented:
Integrated:
non volatile
time variant
subject oriented data warehouse
Subject-Oriented:
Focuses on a specific business subject (e.g., customers, products, sales).
Data is organized around these subjects, making it easier to analyze and understand.
integerated in data warehouse
Integrated:
Combines data from various sources (e.g., different departments, systems).
Ensures a consistent view of the data across the organization.
non valatie in data warehouse
Data is primarily read-only.
This means that once data is loaded into the warehouse, it’s typically not updated in place.
data loading and data retrieval
time variant in Datawarehouse
Captures data over time as a series of periodic snapshots.
time series of periodic snapshots
what is the primary role of decision support systems
Decision Support Systems (DSS) since their primary aim is to provide information to support decisions either in the mid or long term. Those DSSs require other types of data manipulation than traditional operational systems.
To successfully merge and consolidate all this data, the data warehouse needs to ensure that all data is named, transformed and represented in a similar way.
data integration
time variant in operational data
Operational data is always up-to-date and represents the most recent state of the data elements, whereas a data warehouse is not necessarily up-to-date but represents the state at some specific moments in time.
Day-to-Day Operations: Designed to handle day-to-day business activities and operations.
transactional system
Real-Time Processing: Works with real-time data to support immediate business needs.
transactional system
Application-Oriented: The design is oriented around specific applications or business processes.
transactional system
Decision Support: Focuses on supporting tactical and strategic decision-making processes.
Datawarehouse
Historical Data Analysis: Utilizes periodic snapshots, including historical data, to provide a broader context for analysis.
Datawarehouse
Subject-Oriented: The design is centered around key subjects or areas of interest (e.g., sales, finance, customer data).
data warehouse
Data Latency in transactional systems and Datawarehouse
Transactional System:
Real-Time Data: Operates with real-time data, ensuring that the most current information is available for immediate operations.
Data Warehouse:
Periodic Snapshots: Works with data that is updated periodically, reflecting the state of the business at specific intervals over time.
Historical Data: Includes historical snapshots to support trend analysis and long-term planning.
What are the primary methods for representing conceptual data models for transactional databases?
ER (Entity-Relationship) and UML (Unified Modeling Language) are the two most common methods.
