Data Warehousing Flashcards
(101 cards)
1
Q
Data warehouse
A
Decision support database that is maintained separately from the organization’s operational database. It supports information processing by providing a solid platform of consolidated, historical data for analysis.
2
Q
Subject-Oriented Data Warehouses
A
Organized around major subjects like customers, products and sales. It focuses on modelling and analysis of data for decision makers which is not on daily operations or transaction processing. Provides a simple and concise view around particular subject issues by excluding data that isn’t useful in the decision support process.
3
Q
Integrated Data Warehouses
A
constructed by integrating multiple heterogeneous data sources such as relational DBs, flat files, Excel file and transaction records. Data cleaning and data integration techniques are applied to ensure consistency in naming conventions, encoding structures, attribute measures etc. among different data sources. When data is moved to the warehouse it is converted.
4
Q
Time Variant in Data Warehouses
A
the time horizon for data warehouses is much longer than of operational systems. As they provide historical data from the past 5-10 years. Every structure in the data warehouse contains an element of time, explicitly or implicitly, but the key of operational data may or may not contain a time element.
5
Q
Non-Volatile Data Warehouse
A
Physically separate store of data transformed from the operational environment. Operational update of data doesn’t occur in the data warehouse environment. It doesn’t require transaction processing, recovery and concurrency control mechanisms. It requires only two operations in accessing data: initial loading of data and access of data.
6
Q
heterogeneous DBMS
A
integrates disparate databases to provide a unified query interface for users. This system relies on a query-driven approach
7
Q
A data warehouse is an _____ driven system. What does it focus on
A
update driven system that integrates and stores information from heterogeneous sources in advance. It focuses on high performance for querying and analysis
8
Q
Operational DBMS
A
focuses on Online Transaction Processing (OLTP) while data warehouse focuses on Online Analytical Processing (OLAP)
9
Q
Fact Table
A
Contains measurable data or metrics about business processes ex sales
10
Q
Dimension Table
A
Contains descriptive attributes that label and filter data ex. Product Names
11
Q
Why do we need a separate warehouse?
A
- Performance: OLTP systems are tuned for transaction processing whilst OLAP is optimized for complex analytical queries, multidimensional views and data aggregation
- Historical Data: Operation databases typically lack historical data, which is critical for decision making
- Data Consolidation: Decision-making often requires aggregated and summarized data from multiple heterogeneous sources.
- Data Quality: Ensures consistency in data representation, codes and formats from diverse sources
12
Q
Data Warehouse Design Overview
Key Design Perspectives
A
1 Top-Down View: Identifies relevant information needed for the data warehouse.
2 Data Source View: Exposes data being captured and managed by operational systems
3 Data Warehouse View: Defines fact tables and dimension tables for analytical use.
4 Business Query View: Represents how end users will query and interact with the data
13
Q
Design Process
A
Top Down: Focuses on overall planning and design
Bottom Up: Starts with prototypes and experiments
Waterfall: Structured, step by step process
Spiral: Iterative development with quick functional prototypes.
14
Q
Design Steps
A
1 Choose a business process: Identify processes to model. Ex orders.
2 Define the grain: decide the atomic level of data granularity (ex. Individual orders or daily summaries)
3 Select Dimensions: Identify descriptive attributes (ex. Time or location)
4 Choose Measures: Define numerical metrics (ex. Sales amount) for the fact table.
15
Q
Enterprise Warehouse
A
Collects all subject info spanning the organization. It features a unified approach for organizing and representing data, the ability to classify data according to subject and give access to those divisions (sales etc.). Normalized design.
16
Q
DataMart
A
A DataMart is the access layer of the data warehouse environment. Used to get data out to the users. The data mart is a subset of the data warehouse that is usually oriented to a specific business line or team. Data marts represent small slices of the data warehouse.
17
Q
Virtual Warehouse
A
A set of SQL views over operational databases. Only some of the possible summary views may be persisted.
18
Q
Data Warehouse Tools and Utilities
A
- Data Extraction: Get data from multiple heterogeneous and external sources
- Data Cleaning: Detect errors in the data and rectify where possible
- Data Transformation: Convert data from legacy or host format to warehouse format.
- Load: Sort, summarize, consolidate, compute views, check integrity, and build indices and partitions.
- Refresh: Propagate the updates from the data source to the warehouse.
19
Q
Metadata is the data defining warehouse objects. It stores:
A
- Description of the structure of the data warehouse (schemas, views…)
- Operational Metadata (Data lineage, currency of data…)
- Technical Metadata (DB system names, tables etc…)
- Algorithms used for summarization
- The mapping from operational environment to the data warehouse
- Data related to system performance (Data Warehouse Schema, View…)
- Business Data (Business terms and definitions, ownership of data…)
20
Q
OLAP Server Architecture
A
- ROLAP
- MOLAP
- HOLAP
- Specialized SQL Servers
21
Q
Relational OLAP (ROLAP)
A
Use relational or extended relational DBMS to store and manage warehouse data and OLAP middleware. Include optimization of DBMS backend, implementation of aggregation based navigation logic and more tools. Scalable.
22
Q
Multidimensional OLAP (MOLAP)
A
Sparse array-based multidimensional storage engine. Fast indexing to pre-computed summarized data.
23
Q
Hybrid OLAP (HOLAP)
A
Flexibility ex. Low level: relational, high level: array
24
Q
Specialized SQL servers
A
ex Redbricks, specialized support for SQL queries over star/snowflake schemas.
25
Data Warehouse Usage
- Information Processing: Supports querying, basic statistical analysis, and reporting
- Analytical Processing: Multidimensional analysis of data warehouse data. Supports basic OLAP operations.
26
Data Mining:
Knowledge discovery from hidden patterns
Supports association, constructing analytical models, performing classification and prediction and presenting the mining results using visualization tools.
27
Table Descriptive Metadata
Physical Name
Logical Name
Type: Fact, Dimension, Bridge
Role: Legacy, OLTP, Stage
DBMS: DB2, Informix, MSSQL Server, Oracle
Location
Definition
28
Column Descriptive Metadata
Physical name
Logical name
Order in table
Datatype
Length
Decimal positions
Nullable/Required
Default Value
Edit Rules
Definition
29
Extract-Transform-Loads (ETL)
Extract - The process by which data is extracted from the data source
Transform - The transformation of the source data into a format relevant to the solution
Load - The loading of data into the warehouse
Develop the process backwards, start from business rules and code the ETL routines accordingly.
30
ETL Strengths
- Development Time: Since we are designing output backwards, only relevant data is extracted and processed.
- Targeted Data: Since we know the outcome of the load process, only presentation-relevant data is present in the warehouse. Also simplifies security regime and administration overhead.
- Tools Availability: A prolific number of tools are available that provide ETL functionality.
31
ETL Weaknesses
- Short-sighted requirements analysis: Targeting only relevant data means that future requirements will require changes to current ETL routines, maybe even re-designs.
- Hardware: Use of ETL processes will require additional hardware to accommodate the requirements of the ETL engine.
- Learning curve: 3rd party tools imply learning new scripting languages and applications. Lack of experience may lead to choosing difficult approaches.
32
Extract Load Transform (ELT)
Extract the data into a Staging Database
Apply data integrity and business rules check in the Staging Database
Load clean data into the warehouse
Transformations are done within the data warehouse
33
ELT Benefits
- Removes dependency between the load and transformation process.
- Extract and load process can include elements that may be needed in the future
- Break the project into smaller chunks (more predictable and manageable)
- Data integrity checks are performed earlier, so only cleaned and checked data is in the warehouse
34
ELT Strengths
- Project Management: Encourages specific and isolated tasks, therefore the project is manageable. Minimize risks by removing interdependencies between stages, and improving the change and maintenance phases due to process isolation.
- Flexible and Future Proof: All data from data sources can be loaded in the warehouse. Since transformation is a separate process, it is possible to accommodate future requirements.
- Utilizing existing hardware: Ability to use the tools provided by ODBMS. Alternatively, ELT can run on the same hardware as the data warehouse.
- Utilize existing skill set: ELT utilizes the existing tools.
35
ELT Weaknesses
Against the norm: ELT is an emerging technology, requires a paradigm shift.
Tools available: Not as many ELT tools as ETL
36
Data Staging
The construction site of the warehouse. Required by most scenarios, connected to a wide variety of sources. Used to clean, aggregate, compute and validate data
37
2 Models of remote staging
Staging area within the data warehouse environment and staging area outside the data warehouse.
Onsite staging model: Data staging area within the operational environment (possibly affecting the operational system).
38
Why do we need data marts?
Provide users with flexible access to frequently accessed data
Tailor data to match the specific needs of targeted user groups.
Improve end user response time for queries
Clearly define the scope of potential users, enabling targeted support.
Deliver data in structured forms suitable for end user tools and analytics.
Simplify and accelerate implementation compared to a corporate data warehouse
Lower implementation costs relative to a full-scale data warehouse.
39
Standalone Data Mart
Focuses exclusively on a single subject area or department, independent of a broader data warehouse. Collects data from multiple transactional systems for a specific business need. May use dimensional or entity-relationship models. Used by being queried by analytical or business intelligence tools.
40
Standalone Data Mart Adv.
Quick to build and deliver results
Lower costs compared to corporate data warehouses
Visible results in a short time frame
41
Standalone Data Mart Disadv.
Requires multiple ETL processes for each data mart.
Risk of data duplication across marts.
Not future-proof. May not scale if departmental needs grow.
Limited to single department analysis, lacking cross-departmental insight.
42
Dependent Data Mart
Derives its data from existing data warehouses, where data is pre aggregated, restructured and summarized before being moved to the data mart. Addresses performance and scalability issues by offloading some processing from the data warehouse.
43
Dependent Data Mart Benefits
Resolves performance bottlenecks by offloading data processing to the data mart.
Enhances security through departmental ownership of data marts.
Suitable for tracking KPIs over extended periods of time.
44
Exploiting Data Warehouse Data
A data warehouse enables the creation of a wide variety of reports by solving data integration and feed challenges. To transform warehouse data into actionable insight we have to:
Organize data into meaningful models for representation
Develop user specific interfaces to meet diverse business needs
45
Modeling the Data Warehouse
1 Define the business model: understand requirements to determine scope and structure.
2 Creating the dimensional model: design star or snowflake schemas for organizing data into facts and dimensions.
3 Modelling summaries: Identify common aggregations to enhance performance.
4 Creating the physical model: Define the physical implementation, including indexing, partitions, and storage optimization.
46
Aggregates
Operations performed on a dataset to return a single value. They are crucial for summarizing large datasets in data warehouses and are commonly used in OLAP operations. Examples:
Sum: Total of all values
Count: Number of records
Average: Mean value
Min/max: Minimum or maximum value
47
Relational OLAP: Dimensional Modelling using an RDBMS
Special schema design: Star, Snowflake
Special Indexes: Bitmap, Multi-Table join
Proven Technologies (RDBMS) tend to outperform specialized MDDB especially on large data sets.
Products such as IBM DB2, Oracle, Redbrick etc.
48
Star Schema
A single fact table with detail and summary data. Fact table primary key has only one key per column dimension. Each key is generated. Each dimension is a single table, highly denormalized.
49
Star Schema Benefits
Easy to understand, easy to define hierarchies, reduces number of physical joins, low maintenance, very simple metadata.
50
Snowflake Schema
A normalized star schema.
Components: A Fact table with metric attributes and the foreign keys necessary to join the dimension tables, and dimension tables with reference attributes and metric attributes (normalized).
51
Snowflake Schema Adv
May improve performance as smaller tables are joined. Easier to maintain and greater flexibility.
52
Snowflake Schema Disadv
Increases the number of tables to work with. Increases complexity of queries.
53
Star vs Snowflake Schema
Snowflake is easier to maintain because it has no redundancy.
Snowflake is less easy to understand as queries are more complex.
Snowflake has more foreign keys, hence longer query execution time
Snowflake has a higher number of joins.
Star only has a single dimension table for each dimension.
Star should be used when dimension table contains less number of rows. Snowflake better for larger dimension tables.
Dimension tables are normalized in snowflake but denormalized in star. Fact tables always denormalized.
Snowflake is bottom up, star is top down.
54
MDDB
A special-purpose data model. Facts stored in multi-dimensional arrays. Dimensions used to index array, sometimes on top of relational DB.
55
MOLAP: Dimensional Modelling using the multi-dimensional model
The application layer of MOLAP stores data in a multidimensional structure
The presentation layer provides the multidimensional view
Efficient storage and processing
Complexity hidden from user but not developer.
Analysis using pre-aggregated summaries and precalculated measures.
56
Important Points about MOLAP
Precalculated or pre-consolidated transactional data improves speed
However, MDDNs require an enormous amount of overhead in processing time and storage.
Application design is the definition of dimensions and calculation rules.
57
Hybrid OLAP (HOLAP) Advantages
Best of both worlds
Storing detailed data in RDBMS
Storing aggregate data in MDBMS
User access via MOLAP tools
58
Technology Choice
Performance: How fast will the system appear to the end user? Key point.
Data volume and scalability: While MDD servers can handle up to 50GB of storage, RDBMS servers can handle hundreds of GBs and TBs.
59
Use RDBMS/ROLAP solution for data mart if:
Data is over 100GB
Read only
Historical data at lowest level of granularity
Detailed access, long-running queries
Data assigned to lowest level elements
60
Use MDD/MOLAP solution for data mart if:
You require write access
Your data is under 50GB
Time to implement is 60-90 days
Lowest level already aggregated
Data access on aggregated level
Developing a general purpose application for inventory movement or asset management
61
Use HOLAP for data mart if:
OLAP on aggregated and detailed data
Different user groups
Ease of use and detailed data
62
Implementing the Data Warehouse
Four main processes involved in implementing a data warehouse:
Monitor: send data from sources
Integrate: Loading, Cleansing
Process: Query processing, indexing
Manage: metadata, design
63
Monitoring
Source types such as relational, flat file etc. Monitoring techniques used such as:
Periodic Snapshots
Database Triggers
Log shipping
Data shipping
Transaction shipping
Polling
Application level monitoring
64
Issues with monitoring
Frequency: Periodic or triggered on event
Data transformation: Convert data to uniform format, remove & add fields
Standards ex. ODBC.
65
Integration
Data cleaning, data loading and derived data.
66
Reasons for dirty data:
Dummy values
Absence of data
Multipurpose fields
Cryptic data
Contradicting data
Inappropriate use of address lines
Violation of business rules
Reused primary keys
Non-unique identifiers
Data Integration problems
67
Data Cleaning
Migration ex, yen to dollars
Scrubbing: using domain specific knowledge
Fusion ex. Mail list, customer merging
Auditing: discover rules and relationships (like data mining)
68
Parsing
Parsing locates and identifies individual data elements in the source files and then isolates these data elements in the target files. Ex. Parsing City and State.
69
Correcting
Correction parsed individual data components using sophisticated data algorithms and secondary data sources. Ex. Adding a zip code.
70
Standardizing
Applies conversion routines to transform data into its preferred format using both standard and custom business rules, ex. Adding a pre name.
71
Matching
Searching and matching records within and across the parsed, corrected and standardized data based on predefined business rules to eliminate duplications. Ex. identifying similar names and addresses.
72
Consolidating
Analyzing and identifying relationships between matched records and consolidating/merging them into one representation.
73
Householding
Identifying all members of a household. Ensures that you are able to target one household (ex in marketing). Can lead to substantial savings.
74
Enrichment
Bring data from external sources to augment/enrich operational data.
75
Scoring
Computation of a probability of an event ex. Chance a customer will defect to a different brand.
76
Loading Data
Physically move to the data warehouse
Incremental vs refresh
Offline vs Online
Frequency of loading
77
Refresh Techniques
Full extract from base tables. Read entire source table is too expensive, but may be the only choice for legacy systems.
78
Detecting changes
Create a snapshot log table to record ids of updated rows of source data and timestamps. Detect changes by defining after row triggers to update snapshot log when source table changes and by using regular transaction log to detect changes to source data.
79
Index Structures
Tradition Access Methods: B-Trees, Hash tables, R-Trees
Popular in Warehouses: Inverted index, Bitmap Indexes, Join indexes
80
Inverted Indexes
Each index term is associated with an inverted list, contains lists of documents and lists of word occurrences in documents aside other info. Each entry is called a posting. The part of the posting that refers to a specific document or location is called a pointer. Each document in the collection is given a unique number. Lists are usually document-oriented (sorted by document number).
81
Bitmap Indexes
A collection of bitmaps, one for each distinct value of the column. Each bitmap has N bits where N is the number of rows in the table. A bit corresponding to a value v for a row r is set iff r has the value for the indexed attribute. Good if domain cardinality is small. Bit vectors can be compressed (30:1).
82
Join Indexes
Pre-computed joins. A join index between a fact table and a dimension table correlates a dimension tuple with the fact tuples that have the same value on the common dimensional attribute. Ex. A join index on city dimension of calls fact table. Correlates for each city the calls from that city. Join indexes can also span multiple dimension tables ex. A join index on city and time dimension of fact table.
83
Volume Analysis
Estimates of average and max values for object sizes. Number of instances per entity. Realistic storage requirements (day one and growth over time). Headings: Row max, Row Avg, Record size, table max, table avg.
84
Usage Analysis (Internal Constraints)
Identify major transactions to hit each table and their frequency.
Insertions/updates/ retrievals/deletes or a mixture of all 4.
Compute volatile (transactions) vs non-volatile tables (reference tables) (Turnover/population ex. Over a year).
85
Transactional Analysis (Other Constraints)
Documenting critical transactions to hit DB.
Impact on systems resources.
Objects accessed and type of access.
Any constraints placed on processing (ex. time)
86
Access Requirements
type of access + type of usage = headache. Ex. Indexes both increase and decrease performance. Time sharing or avoidance (forbidden queries).
87
Integrity Analysis
Traditional DB Territory:
Entity Constraints (Primary key - no duplicated)
Referential Integrity (Foreign key ref is not null and valid)
Domain Constraints (Credit limit numeric + accepted value [1000,2000,5000]).
Enforcing these is a big hit on performance which cascades upwards.
88
Security Analysis
Outside frame of DB is physical access to building, logon procedures etc. It is difficult to contemplate DB security on its own ex. Operating systems level. Large scale DBMSs provide specific utilities (Preventing unauthorized access to data & preventing usage of DBMS functions). Use usage analysis to derive user profiles with associated access rights.
89
Data Warehouse and Security
A data warehouse by nature is an open, accessible system
The aim of a data warehouse is to make large amounts of data easily accessible to users, this enables them to extract information about the business as a whole
Any security restrictions can be seen as obstacles to that goal
They can become constraints on the design of the warehouse
90
Security Requirements
Describe all security conditions that have to be considered in a data warehouse environment. The first step for this is to classify the security objects and security subjects of the environment. Security objects can be classified by sensitivity (public, confidential) or job function (accounting, personnel).
91
Legal Requirements
Which arrangements have to be made to be allowed to hold legally sensitive data? Which data is subject to legal restrictions? Which separate handling does this data require concerning storage, access and maintenance? Which analysis may be performed on this data? If data is held online for trend analysis in summarized form, do legal restrictions apply?
92
Audit Requirements
Security audit information is the basis for further reviews and examinations, this is done to test the adequacy of system controls and to recommend any changes in the security policy.
The following activities are interesting or security audits; Connections, Disconnections, Access to data, Change of data, Deletion of data.
93
Network Requirements
Important part of security requirements. The transfer of data from the source system (usually an OS) into a data warehouse. Mostly transmitted over a network. Precautions must be taken in order to maintain the confidentiality and integrity of the data.
94
Security Model Requirements
Data Filtration and encryption prior to data warehouse.
Classical Security Model
Hybrid
View Based
95
Classic Security Model
Covers all stages, from requirement analysis to the physical schema. During this process flow, security measures are applied so that the proper and secure data warehouse schema can be developed. This may be fruitful if implemented in transactional databases, but as far as data warehouses are concerned it may be unsuitable.
96
Password Management
Accounts get locked after failed attempts
Password history so passwords cannot be reused
Characteristics of user’s accounts are set in profile (Password lifetime, number of failed attempts, frequency of changes, minimum length).
97
Standard Roles
Before the users are active, we need to assign roles.
CONNECT: access to DB and any other granted rights
RESOURCE: creation of objects
DBA: unlimited space quotas and grant privileges to other users.
98
Grant Role
CONNECT and RESOURCE don’t automatically give access to other people’s resources.
Need to grant access.
Object privilege = Select, Insert, Update, Delete
Can be granted on certain columns and not on others
99
Security Layers
There is an old debate regarding the best level at which to implement security. Databases contain data of increasing value. They are increasingly open to the outside.
Possible layers of security: By user, By table (using policies), At row level
100
Three Aspects of Security
Confidentiality: Information is not disclosed to unauthorized users
Integrity: Modifications of data must be legitimate
Availability: Authorized users should not be refused access (may happen by mistake)
101
Costs of Security
Significant performance impact
Complexity due to multiple levels
Possible errors from conflicting policies
Combination of measure may also have unpredictable effects on user’s queries
May conflict with constraints set across tables (such as referential integrity)
