Quiz 4 Flashcards
business applications
- Function orientation
- Process-centric
- Goal: Efficient execution of business operations while maintaining data integrity
business applications examples
- Order processing
- Payroll processing
- Maintaining inventory
- Accounts receivable
operational databases
- support day-to-day business activities
- optimized for transaction processing
transaction
- well-defined process that occurs in exactly the same way and is routine (i.e. repeats)
- produce one set of data (i.e. record(s))
- characterize one single operation – i.e. is indivisible (Atomic)
transaction processing
- chronological processing of transactions
- aims to data changes (Insert, Update, Delete) immediately upon completion of transaction
- aims to maintain data integrity
business decision making:
What kinds of decisions are made?
- How to boost sales levels
- Revenues
- Sales Price
- Number of Customers
- How to reduce costs
- Effect of marketing campaigns on product sales
- etc.
business decision making:
What level are the decisions being made?
- Different individual products
- (ipod, ipad, ipad-2, samsung-tv1, samsung tv-2)
- Different groups of products
- (tablets, apple products, samsung products, televisions)
- Different levels of groupings
business decision making:
What type of information is needed for such decisions?
- Individual sale transaction is insufficient
business decision making:
Decision making in support of Business Objectives
Example business objectives:
- Over next year, increase customer based by 20%
- Improve customer retention by 25%
- Increase sales by 15%
- Increase profit by 20%
business decision making:
Information Requirements
- More than business function needs Enterprise-wide Integrated View
- Timely information delivery
- Data consistency
- Historical Information
business decision making:
What would be the characteristics of the queries?
- Large number of records
- Historical
- Information across business functions and domains
- (Sales, Marketing, Financials, etc.)
information hierarchy
[insert graphic]
From Silo to Enterprise Wide View graphic
Data Warehouse Architecture graphic
How is Data Warehouse different from Operational Database?
ODB vs. DW:
Data Content
ODB:
- current value
DW:
- archived
- historic
- summarized
ODB vs. DW:
Data Structure
ODB:
- optimized for transaction processing
DW:
- optimized for complex querying
ODB vs. DW:
Access Frequency
ODB:
- high
DW:
- low
ODB vs. DW:
Access Type
ODB:
- read
- update
- delete
DW:
- read only
ODB vs. DW:
Usage
ODB:
- predictable
- routine
DW:
- ad-hoc
- random
- context dependent on decision-making
ODB vs. DW:
Response Time
ODB:
- sub-seconds
DW:
- several seconds to minutes
ODB vs. DW:
Users
ODB:
- many
DW:
- few
What is ETL?
- Extraction
- Transformation
- Loading
ETL sources of data
- Production Data:
- data from operational systems
- Internal Data:
- data gathered and utilized typical systems
- example: spreadsheets, departmental databases, etc.
- External Data:
- data purchased from outside sources
- Archived Data:
- old data from within the company
- (typically one-time addition)
data staging
- Characterized by ETL
- Extraction:
- Data from diverse systems and data models
- Transformation:
- Cleaning
- Standardization:
- Data type
- Measurement
- Interpretation (Synonym vs. Homonym)
- Summarization
- Loading
Defining features of data warehouse
- Subject Orientation
- (vs. Functional Orientation of Operational DB)
- Enterprise-wide Integration
- Time-Variant Data / Historical
- Non-volatile / read-only
- Granularity
Defining Features of DW:
Subject-Orientation
Defining Features of DW:
Time-Variant
- Operational database store current values.
- Examples:
- What is the balance owed by customer?
- What is the list price of the car?
- Examples:
- Data warehouse helps analyze changes in outcomes because of changes in dimensions.
- Example:
- to help answer questions like what was the cause of drop in sales.
- Example:
Defining Features of DW:
Time-Variant allows…
- Historical analysis
- Analysis of patterns for predictive use
- Impact of decisions in different operational areas
- e.g., marketing on sales, customer retention, profits.
- Measure outcome influence
- e.g., impact of decision to drop prices on revenues, number of customers, …
Defining Features of DW:
Non-Volatile Data
- Data is moved from operational into data warehouse at intervals of time
- Captured snapshot does not change
- For example:
- balance owed by customer at the time is captured
- any change in balance is loaded next extraction.
- (time variant and nonvolatile)
- For example:
Defining Features of DW:
Data Granularity
- defines level of detail
- multiple levels of detail are usually present
- example:
- grocery store may store data in warehouse on:
- hourly intervals
- daily intervals
- weekly …
- customer behavior may be tracked on:
- Individual customer
- Zip code
- Customer type, etc.
- grocery store may store data in warehouse on:
metadata
- metadata is basically Data Dictionary
- often defined as “data about data”
types of metadata
- Operational
- ETL
- End-User
operational metadata
- information about source systems
- where the data is coming from
- details of fields used, etc.
ETL metadata
- extraction methods
- business rules for transformation
- when was data last loaded
- percentage errors, …
end-user metadata
- navigational map for end-users
- what information is located where
- what does it mean
- what is the measurement unit, etc.
Operational DB vs. Data Warehouse
Defining Business Requirements:
Identifying Focus of Decision Support
- Overall Goal:
- What information is needed for decision making?
- Identifying the Subject:
- What is the outcome being focused on?
- Example: Sales, Inventory
- Analyzing decision making:
- What about the outcome is being analyzed? (measures)
- Measures of success/failure of strategy (decision)
examples of measures
Decisions involving sales may focus on:
- market share
- number of unique customers
- number of customer visits
- average sales to each customer
- total sales
- profits
- profit margins, …
Defining Business Requirements:
Identifying Business Dimensions of Decisions
- What are the components of the decision?
- Example:
- What can the decision maker do to accomplish the outcomes identified?
- Example:
- Identify various types decisions that are made to influence the outcome
- Identify the Business Dimensions of these decisions
example business dimensions
To increase total sales (subject), decision may be taken to launch a promotion campaign offering discounts on…
- PRODUCTS,
- at different STORES,
- on certain DATES.
Note: besides the decision, the outcome measures are also analyzed on the dimensions. Monthly Sales for Product at different stores
Defining Business Requirements:
Hierarchies in Business Dimensions
Decisions are defined by:
- BUSINESS DIMENSIONS
- LEVEL
- CATEGORY
level
- level corresponds to different levels of hierarchy within a dimension
- example: Day—>Month—>Quarter—>Year
- the finest level of hierarchy identifies the GRANULARITY of the data
- both decisions taken and outcomes measured can be analyzed up to this level of detail
category
- categories are dimensions characterized by groupings.
- for example: ethnicity, college education, etc.
multi-dimensional data
