1/90
Looks like no tags are added yet.
Operational information
Collected and used for day-to-day operations (e.g., ATM withdrawal, booking a flight); lives in an operational database
Analytical information
Used for analysis like trends, sales, product statistics, and growth projections; based on operational data and lives in a data warehouse
Why keep a data warehouse separate? (3 reasons)
1) Time horizon: operational DBs keep 60-90 days, warehouses keep years. 2) Performance: analytical queries slow operations. 3) Structure: one database cannot be efficient for both uses
Source systems
Operational databases that feed the warehouse; each has its original operational purpose plus feeding the warehouse. Can include external sources like census, market research, weather
ETL
Extract - Transform - Load: extract useful data from sources, transform it to fit the warehouse model and ensure quality, load cleaned data into the warehouse
Data warehouse (target system)
The destination / target system that periodically pulls selected, analytically useful data from source systems
Front-end (BI) applications
Applications that give most users indirect access to the warehouse; S-E-W-F = Sources, ETL, Warehouse, Front-end
OLTP
Online Transaction Processing: inserting, modifying, deleting, and retrieving data for operational purposes
OLAP
Online Analytical Processing: retrieving data from analytical databases / warehouses for analytical purposes
Slice and dice (OLAP)
Adds, replaces, or eliminates specified attributes or values in the displayed result (e.g., limit result to only Stores 1 and 2)
Pivot / Rotate (OLAP)
Moves a dimension's values from one axis to another; reorganizes layout, same data, same detail level
Drill down (OLAP)
Makes granularity finer / more detail (e.g., product category -> individual products)
Drill up / Roll up (OLAP)
Makes granularity coarser / less detail (e.g., individual products -> product category)
Data visualization
Graphical display (scatterplots, area charts, heat maps) using points, lines, color, shading, symbols to help comparison, pattern recognition, and change detection
Direct access to warehouse
Technical users write SQL directly or use OLAP/BI tools that generate the SQL for them
Indirect access to warehouse
Most users use front-end / BI applications (pick parameters like date range and stores and get results) because they cannot write SQL
Executive dashboard
Organized, easy-to-read display of a few critically important performance queries for higher-level decision makers; needs little or no training
Big Data
Massive volumes of diverse, rapidly growing data that is NOT formally modeled for efficient retrieval; heterogeneous, semi-structured and unstructured, can be 80-90%+ of stored data
Big Data vs warehouse relationship
Big Data methods do NOT replace databases and warehouses; they complement them
Data warehouse vs data lake: process
Warehouse: ETL (transform BEFORE loading). Data lake: E-L (load first, transform later when needed)
Data warehouse vs data lake: schema
Warehouse: schema defined up front, predefined structure, heavy/costly prep, predictable yield. Lake: raw data in native format, schema not defined until queried, simpler/cheaper prep, less predictable yield
Data lake analogy
Warehouse = agriculture (clear, plow, tend, reliable crop). Data lake = hunting and gathering in a fenced wild area (cheap prep, unpredictable yield)
Insurance example - Big Data
Telemetry data from tracking devices in insured cars; used to price policies and segment by distance driven, speed, hard acceleration/deceleration
Data mining
Discovering novel and interesting patterns in large amounts of data; good patterns are accurate, meaningful, understandable, actionable
Predictive analytics
Using past data to predict future events
Market basket / Association rule mining
Finds groups of items that tend to appear together in transactions; rule form X -> Y means transactions containing X are likely to also contain Y
Support (formula)
Support = (# transactions containing X AND Y) / (# of ALL transactions). Measures SIGNIFICANCE of the rule
Confidence (formula)
Confidence = (# transactions containing X AND Y) / (# transactions containing X). Measures STRENGTH of the correlation
Support vs Confidence trap
Support denominator = ALL transactions. Confidence denominator = transactions containing X only. A rule is kept only if it meets BOTH thresholds
Artificial Intelligence (AI) - IBM definition
Technology that lets computers and devices learn, read, write, create, and analyze
Pattern recognition
Machines identifying patterns in data and using them to decide or predict; a vital component of AI
Descriptive AI
Discriminative AI that describes patterns in data. Example: CLUSTERING for customer segmentation
Predictive AI
Discriminative AI that uses data and patterns to forecast outcomes. Example: CLASSIFICATION for credit card applications
Generative AI
Uses data, patterns, and content to generate NEW content (e.g., ChatGPT, Copilot, Gemini; multimodal: Sora, Udio)
Large Language Model (LLM)
Neural network with billions of parameters, fed enough examples to recognize patterns and synthesize content from patterns + user prompt. Foundation came from Google in 2017
How an LLM works
Predicts the next word probabilistically, chains predictions to build phrases/sentences; trained on petabytes of text for months on high-end hardware
Hallucination
AI produces a confident but totally wrong / made-up answer. Happens because LLM output is probabilistic, not deterministic - so verify
Prompt engineering
The content and context of your question / prompt determine the answer you get
AI supply chain (in order)
Lithography makers (ASML) -> Chip manufacturers (TSMC) -> Chip developers (NVIDIA) -> Cloud/data centers (Microsoft Azure) -> Generative AI companies (OpenAI) -> Copilot/ChatGPT
AI model vs AI chatbot
Model = underlying software / LLM, BACK END (GPT, Claude, Gemini models). Chatbot = user-facing app/interface that uses a model, FRONT END (ChatGPT, Claude, Gemini app, Copilot)
Model weights
Numerical floating-point parameters inside a model that store what it learned
Closed model
Inner workings, architecture, training data, and weights kept internal; you rent access via chatbot or API (GPT, Claude, Gemini)
Open-weight model
Weights are shared so you can download and run locally, but training data and code are often hidden (Qwen, DeepSeek, Meta open models)
True open-source model
Weights, source code, AND training data released for full reproducibility. Example: OLMo
Token
A small chunk of text, about 3/4 of an English word; about 1,000 tokens per 750 words. Pricing can be by tokens processed
AI chatbot/assistant vs AI agent
Chatbot: prompt-driven, collaborative, human reviews/decides, moderate autonomy. Agent: goal-driven, multi-step, perceives-reasons-acts-learns, uses tools, high autonomy, human sets goals and monitors exceptions
Supply chain
A network between a company, its suppliers, and its customers to produce and distribute products/services; includes all steps to get a product/service to the customer
Upstream (supply chain)
Supplier side: raw material suppliers -> Tier suppliers -> company. Here company is the CUSTOMER, wants its customer power high
Downstream (supply chain)
Customer side: company -> direct customers -> end customers. Here company is the SUPPLIER, wants its supplier power high
SCOR model - 6 processes
Plan, Source, Make, Deliver, Return, Enable (P-S-M-D-R-E)
SCOR: Enable
Jointly manage all SCM processes and elements (rules, contracts, regulations, risks, HR) and measure/monitor performance using data and information systems
Logistics
Part of supply chain that plans, implements, and controls efficient forward AND reverse flow and storage of goods, services, and related information from origin to consumption - more than just transportation/shipping
Back order rate (formula)
Back order rate = # back orders / # of all orders. Goal: DECREASE it (higher rate = more cancellations, costs, lost goodwill)
Inventory turnover rate (formula)
Inventory turnover = Cost of Goods Sold (COGS) / value of average (or end-of-period) inventory. Tells how many times a year company sells its entire inventory. Goal: generally INCREASE it
Days inventory (formula)
Days inventory = 365 / inventory turnover rate. Example: turnover 9.16 = about 40 days
Rate of return (formula)
Rate of return = # returned items / # of all shipped (bought) items. Goal: DECREASE it
SCM decision levels
Operational = day-to-day (how much inventory to order today). Tactical = medium-term (annual supplier contract terms). Strategic = longer-term (use overseas suppliers in next 3-5 years). Based on TIME FRAME, not job title
Bullwhip effect
Demand information gets distorted (delayed) as it passes through the supply chain, causing excessive or insufficient inventory
RFID
Radio Frequency Identification microchip; costs more than a barcode but tracks any item automatically at each stage, holds more information, accessed more quickly
CRM
Customer Relationship Management: use existing customer data to drive loyalty and engagement and maximize profitability from lapsed, existing, and potential customers; integrates sales, marketing, and customer service
RFM analysis
Recency (when was most recent purchase?), Frequency (how often do they buy?), Monetary value (how much did they spend?). Each scored 1-5 in quintiles, 5 = BEST, forming a 3-digit cell like 5-5-4
Starbucks loyalty - data collected
Behavioral (time of day, weekday/weekend, frequency, location, length of visit via wifi) and transactional (product type, payment method)
Business process
A set of activities that, once completed, accomplish a company's task or goal (e.g., making a sale, credit check, resolving fraud)
BPR
Business Process Reengineering: analysis and redesign of workflows within and between enterprises to optimize processes. Hammer 1990: Don't Automate, Obliterate
BPR steps (cycle)
1) Identify process to improve, 2) Diagram/review/analyze the AS-IS process (how it works now), 3) Design the TO-BE process (how it could be better), 4) Test and implement the To-Be
Automating (change type)
Use technology to perform the process (e.g., computerized phone menu instead of human rep)
Streamlining (change type)
Remove unnecessary, outmoded, or inefficient steps (e.g., same phone menu with fewer levels and shorter wording)
BPR / Reengineering (change type)
Completely redesign the process instead of doing the same thing faster/cheaper (e.g., company detects fraud first with data mining, calls customer, cancels card immediately)
Paradigm shift (change type)
One company's BPR redefines the whole industry (e.g., other card companies adopt proactive fraud detection too)
MS Access - Primary key
One or more fields that uniquely identify each record; set via Table Tools Design tab, Tools group, Primary Key button
MS Access - Foreign key
A field that matches the primary key of another table; this is how tables link. Should have same data type and size as the PK it points to
Composite primary key
A key made of two or more fields together. WORKS_ON key = PID + Essn, so same employee-project pair cannot repeat
WORKS_ON table
Bridge table for the many-to-many Employee-Project (WorksOn) relationship; fields PID + Essn, composite PK, FKs to PROJECT and EMPLOYEE
ExampleCompany relationships
Department 1-to-many Employee (WorksFor), Department 1-to-many Project (Controls), Employee many-to-many Project (WorksOn, stored as WORKS_ON table)
PROJECT foreign key in Access
Called ControllingDeptNo in the Access table (ER diagram calls it Dnumber); FK to DEPARTMENT.Dnumber
Referential integrity
Every non-null foreign key value must match an existing primary key value; you cannot add a record to the FK table unless a matching record exists in the linked table
Creating relationships in Access
Database Tools tab -> Relationships -> Show Table (add all 4 tables) -> drag PK from one side to FK on many side -> check Enforce Referential Integrity -> Create. Shown as 1 to infinity
MS Access - Default data type
Default data type for a new field = SHORT TEXT
Short Text Field Size
Maximum characters 1 to 255, default 255. Field Size is only available for Short Text and Number fields
Datasheet view vs Design view
Datasheet = rows and columns of data. Design = defining structure: add/remove/rearrange fields, set names, data types, properties, primary key
Navigation Pane
Central place to work with database objects (tables, queries, forms, reports); open/close with Shutter Bar button
Access saving
Access AUTO-SAVES data when added/changed/deleted, so a new database is saved to a file before you start. You still save design changes when closing
Data entry order in Access
Parent before child, due to referential integrity: 1) DEPARTMENT first, 2) EMPLOYEE and PROJECT, 3) WORKS_ON last
Query (Access object)
Extracts, combines, and displays data from one or more tables based on criteria; can sort and summarize. Create tab -> Query Design, run with ! Run icon
Form (Access object)
Makes inserting, modifying, and deleting records easier and more visually appealing; data from tables or a query. Create tab -> Form Wizard, Columnar layout in tutorial
Report (Access object)
Used primarily for PRINTING; labels, groups, sorts, and summarizes data. Create tab -> Report Wizard, in tutorial: by DEPARTMENT, Stepped layout, Portrait
Access objects summary
Table = stores data, Query = extracts/combines/summarizes data, Form = easy data entry/editing, Report = formatted output mainly for printing
Practice: Milk -> Cereal
200 transactions, 40 contain milk, 10 contain both. Support = 10/200 = 5%, Confidence = 10/40 = 25%
Practice: Inventory turnover
COGS $600M, average inventory $75M. Turnover = 600/75 = 8 turns, Days inventory = 365/8 = about 45.6 days
Practice: Tokens in 1,500 words
About 2,000 tokens (1,000 tokens per 750 words)
Practice: RFM code 5-1-2
Very recent purchase (5), low frequency (1), low-to-mid monetary value (2)