Data Warehousing, OLAP and data mining: what and why (now)?
Relation to OLTP
A case study
A producer wants to know…. Which are our lowest/highest margin customers ? Who are my customers and what products are they buying? Which customers are most likely to go to the competition ? What impact will new products/services have on revenue and margins? What product prom- -otions have the biggest impact on revenue? What is the most effective distribution channel?
collection of data that is used primarily in organizational decision making.
-- Bill Inmon, Building the Data Warehouse 1996
Explorers, Farmers and Tourists Explorers: Seek out the unknown and previously unsuspected rewards hiding in the detailed data Farmers: Harvest information from known access paths Tourists: Browse information harvested by farmers
Data Warehouse Architecture Data Warehouse Engine Optimized Loader Extraction Cleansing Analyze Query Metadata Repository Relational Databases Legacy Data Purchased Data ERP Systems
Given a database of 100,000 names, which persons are the least likely to default on their credit cards?
Which types of transactions are likely to be fraudulent given the demographics and transactional history of a particular customer?
If I raise the price of my product by Rs. 2, what is the effect on my ROI?
If I offer only 2,500 airline miles as an incentive to purchase rather than 5,000, how many lost responses will result?
If I emphasize ease-of-use of the product as opposed to its technical capabilities, what will be the net effect on my revenues?
Which of my customers are likely to be the most loyal?
Data Mining helps extract such information
Application Areas Industry Application Finance Credit Card Analysis Insurance Claims, Fraud Analysis Telecommunication Call record analysis Transport Logistics management Consumer goods promotion analysis Data Service providers Value added data Utilities Power usage analysis
Optimized to handle large numbers of simple read/write transactions
Optimized for fast response to predefined transactions
Used by people who deal with customers, products -- clerks, salespeople etc.
They are increasingly used by customers
Examples of Operational Data Data Industry Usage Technology Volumes Customer File All Track Customer Details Legacy application, flat files, main frames Small-medium Account Balance Finance Control account activities Legacy applications, hierarchical databases, mainframe Large Point-of- Sale data Retail Generate bills, manage stock ERP, Client/Server, relational databases Very Large Call Record Telecomm- unications Billing Legacy application, hierarchical database, mainframe Very Large Production Record Manufact- uring Control Production ERP, relational databases, AS/400 Medium
Outside sources with questionable quality procedures
Production systems with no built in integrity checks and no integration
Operational systems are usually designed to solve a specific business problem and are rarely developed to a a corporate plan
“ And get it done quickly, we do not have time to worry about corporate standards...”
Data Integration Across Sources Trust Credit card Savings Loans Same data different name Different data Same name Data found here nowhere else Different keys same data
Data Transformation Example encoding unit field appl A - balance appl B - bal appl C - currbal appl D - balcurr appl A - pipeline - cm appl B - pipeline - in appl C - pipeline - feet appl D - pipeline - yds appl A - m,f appl B - 1,0 appl C - x,y appl D - male, female Data Warehouse
Vertical Partitioning Frequently accessed Rarely accessed Smaller table and so less I/O Acct. No Name Balance Date Opened Interest Rate Address Acct. No Balance Acct. No Name Date Opened Interest Rate Address
Select month, dollars, cume(dollars) as run_dollars, weight, cume(weight) as run_weights from sales, market, product, period t where year = 1993 and product like ‘Columbian%’ and city like ‘San Fr%’ order by t.perkey
Using SubQueries in Calculations select product, dollars as jun97_sales, (select sum(s1.dollars) from market mi, product pi, period, ti, sales si where pi.product = product.product and ti.year = period.year and mi.city = market.city) as total97_sales, 100 * dollars/ (select sum(s1.dollars) from market mi, product pi, period, ti, sales si where pi.product = product.product and ti.year = period.year and mi.city = market.city) as percent_of_yr from market, product, period, sales where year = 1997 and month = ‘June’ and city like ‘Ahmed%’ order by product;
Dimensions: Product, Region, Time Hierarchical summarization paths Product Region Time Industry Country Year Category Region Quarter Product City Month Week Office Day Month 1 2 3 4 7 6 5 Product Toothpaste Juice Cola Milk Cream Soap Region W S N
Relational OLAP: 3 Tier DSS Store atomic data in industry standard RDBMS. Generate SQL execution plans in the ROLAP engine to obtain OLAP functionality. Obtain multi-dimensional reports from the DSS Client. Data Warehouse ROLAP Engine Decision Support Client Database Layer Application Logic Layer Presentation Layer
MD-OLAP: 2 Tier DSS MDDB Engine MDDB Engine Decision Support Client Database Layer Application Logic Layer Presentation Layer Store atomic data in a proprietary data structure (MDDB), pre-calculate as many outcomes as possible, obtain OLAP functionality via proprietary algorithms running against this data. Obtain multi-dimensional reports from the DSS Client.
Typical OLAP Problems Data Explosion Data Explosion Syndrome Number of Dimensions Number of Aggregations (4 levels in each dimension) Microsoft TechEd’98