Data Warehouse

1. Data Warehouse Predefined reports Interactive data analysis Operations data Daily data transfer OLTP Database 3NF tables Data warehouse Star configuration Flat files

2. Existing databases optimized for Online Transaction Processing (OLTP) Online Analytical Processing (OLAP) requires fast retrievals, and only bulk writes. Different goals require different storage, so build separate dta warehouse to use for queries. Extraction, Transformation, Transportation (ETT) Data analysis Ad hoc queries Statistical analysis Data mining (specialized automated tools) Data Warehouse Goals

3. Extraction, Transformation, and Transportation (ETT) Customers Convert Client to Customer Apply standard product numbers Convert currencies Fix region codes Data warehouse: All data must be consistent. Transaction data from diverse systems.

4. OLTP v. OLAP

5. Multidimensional Cube Pet Store Item Sales Amount = Quantity*Sale Price Category Customer Location Time Sale Date

6. Sales Date: Time Hierarchy Year Roll-up To get higher-level totals Levels Quarter Month Drill-down To get lower-level details Week Day

7. Star Design Dimension Tables Products Sales Date Fact Table Sales Quantity Amount=SalePrice*Quantity Customer Location

8. Snowflake Design City CityID ZipCode City State Merchandise Sale ItemID Description QuantityOnHand ListPrice Category SaleID SaleDate EmployeeID CustomerID SalesTax Customer CustomerID Phone FirstName LastName Address ZipCode CityID OLAPItems SaleID ItemID Quantity SalePrice Amount Dimension tables can join to other dimension tables.

9. OLAP Computation Issues Compute Quantity*Price in base query, then add to get $23.00 If you use Calculated Measure in the Cube, it will add first and multiply second to get $45.00, which is wrong.

10. OLAP Data Browsing

11. Microsoft Excel Pivot Table

12. Excel Pivot Table Reports Can place data in rows or columns. By grouping months, can instantly get quarterly or monthly totals.

13. OLAP in SQL 99 GROUP BY two columns Gives you totals for each month within each category. You do not get super-aggregate totals for the category, or the month, or the overall total. SELECT Category, Month(SaleDate) AS Month, Sum(Quantity*SalePrice) AS Amount FROM Sale INNER JOIN (Merchandise INNER JOIN SaleItem ON Merchandise.ItemID = SaleItem.ItemID) ON Sale.SaleID = SaleItem.SaleID GROUP BY Category, Month(SaleDate);

14. SQL ROLLUP SELECT Category, Month…, Sum … FROM … GROUP BY ROLLUP (Category, Month...) Category Month Amount Bird 1 135.00 Bird 2 45.00 … Bird (null) 607.50 Cat 1 396.00 Cat 2 113.85 … Cat (null) 1293.30 … (null) (null) 8451.79

15. Missing Values Cause Problems If there are missing values in the groups, it can be difficult to identify the super-aggregate rows. Category Month Amount Bird 1 135.00 Bird 2 45.00 … Bird (null) 32.00 Bird (null) 607.50 Cat 1 396.00 Cat 2 113.85 … Cat (null) 1293.30 … (null) (null) 8451.79 Missing date Super-aggregate

16. GROUPING Function SELECT Category, Month…, Sum …, GROUPING (Category) AS Gc, GROUPING (Month) AS Gm FROM … GROUP BY ROLLUP (Category, Month...) Category Month Amount Gc Gm Bird 1 135.00 0 0 Bird 2 45.00 0 0 … Bird (null) 32.00 0 0 Bird (null) 607.50 1 0 Cat 1 396.00 0 0 Cat 2 113.85 0 0 … Cat (null) 1293.30 1 0 … (null) (null) 8451.79 1 1

17. CUBE Option SELECT Category, Month, Sum, GROUPING (Category) AS Gc, GROUPING (Month) AS Gm FROM … GROUP BY CUBE (Category, Month...) Category Month Amount Gc Gm Bird 1 135.00 0 0 Bird 2 45.00 0 0 … Bird (null) 32.00 0 0 Bird (null) 607.50 1 0 Cat 1 396.00 0 0 Cat 2 113.85 0 0 … Cat (null) 1293.30 1 0 (null) 1 1358.8 0 1 (null) 2 1508.94 0 1 (null) 3 2362.68 0 1 … (null) (null) 8451.79 1 1

18. GROUPING SETS: Hiding Details SELECT Category, Month, Sum FROM … GROUP BY GROUPING SETS ( ROLLUP (Category), ROLLUP (Month), ( ) ) Category Month Amount Bird (null) 607.50 Cat (null) 1293.30 … (null) 1 1358.8 (null) 2 1508.94 (null) 3 2362.68 … (null) (null) 8451.79

19. SQL OLAP Analytical Functions VAR_POP variance VAR_SAMP STDDEV_POP standard deviation STDEV_SAMP COVAR_POP covariance COVAR_SAMP CORR correlation REGR_R2 regression r-square REGR_SLOPE regression data (many) REGR_INTERCEPT

20. SQL RANK Functions SELECT Employee, SalesValue RANK() OVER (ORDER BY SalesValue DESC) AS rank DENSE_RANK() OVER (ORDER BY SalesValue DESC) AS dense FROM Sales ORDER BY SalesValue DESC, Employee; Employee SalesValue rank dense Jones 18,000 1 1 Smith 16,000 2 2 Black 16,000 2 2 White 14,000 4 3 DENSE_RANK does not skip numbers

21. SQL OLAP Windows SELECT Category, SaleMonth, MonthAmount, AVG(MonthAmount) OVER (PARTITION BY Category ORDER BY SaleMonth ASC ROWS 2 PRECEDING) AS MA FROM qryOLAPSQL99 ORDER BY SaleMonth ASC; Category SaleMonth MonthAmount MA Bird 200101 1500.00 Bird 200102 1700.00 Bird 200103 2000.00 1600.00 Bird 200104 2500.00 1850.00 … Cat 200101 4000.00 Cat 200102 5000.00 Cat 200103 6000.00 4500.00 Cat 200104 7000.00 5500.00 …

22. Ranges: OVER SELECT SaleDate, Value SUM(Value) OVER (ORDER BY SaleDate) AS running_sum, SUM(Value) OVER (ORDER BY SaleDate RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_sum2, SUM (Value) OVER (ORDER BY SaleDate RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_sum; FROM … Sum1 computes total from beginning through current row. Sum2 does the same thing, but more explicitly lists the rows. Sum3 computes total from current row through end of query.

23. LAG and LEAD Functions LAG or LEAD: (Column, # rows, default) SELECT SaleDate, Value, LAG (Value 1,0) OVER (ORDER BY SaleDate) AS prior_day LEAD (Value 1, 0) OVER (ORDER BY SaleDate) AS next_day FROM … ORDER BY SaleDate Prior is 0 from default value SaleDate Value prior_day next_day 1/1/2003 1000 0 1500 1/2/2003 1500 10002000 1/3/2003 2000 1500 2300 … 1/31/2003 3500 3200 0 Not part of standard yet? But are in SQL Server and Oracle.

24. Data Mining • Goal: To discover unknown relationships in the data that can be used to make better decisions. Transactions and operations Reports Queries Specific ad hoc questions Aggregate, compare, drill down OLAP Databases Unknown relationships Data Mining

25. Exploratory Analysis • Data Mining usually works autonomously. • Supervised/directed • Unsupervised • Often called a bottom-up approach that scans the data to find relationships • Some statistical routines, but they are not sufficient • Statistics relies on averages • Sometimes the important data lies in more detailed pairs

26. Common Techniques • Classification/Prediction/Regression • Association Rules/Market Basket Analysis • Clustering • Data points • Hierarchies • Neural Networks • Deviation Detection • Sequential Analysis • Time series events • Websites • Textual Analysis • Spatial/Geographic Analysis

27. Classification Examples • Examples • Which borrowers/loans are most likely to be successful? • Which customers are most likely to want a new item? • Which companies are likely to file bankruptcy? • Which workers are likely to quit in the next six months? • Which startup companies are likely to succeed? • Which tax returns are fraudulent?

28. Classification Process • Clearly identify the outcome/dependent variable. • Identify potential variables that might affect the outcome. • Supervised (modeler chooses) • Unsupervised (system scans all/most) • Use sample data to test and validate the model. • System creates weights that link independent variables to outcome.

29. Classification Techniques • Regression • Bayesian Networks • Decision Trees (hierarchical) • Neural Networks • Genetic Algorithms • Complications • Some methods require categorical data • Data size is still a problem

30. Association/Market Basket • Examples • What items are customers likely to buy together? • What Web pages are closely related? • Others? • Classic (early) example: • Analysis of convenience store data showed customers often buy diapers and beer together. • Importance: Consider putting the two together to increase cross-selling.

31. Association Details (two items) • Rule evaluation (A implies B) • Support for the rule is measured by the percentage of all transactions containing both items: P(A ∩ B) • Confidence of the rule is measured by the transactions with A that also contain B: P(B | A) • Lift is the potential gain attributed to the rule—the effect compared to other baskets without the effect. If it is greater than 1, the effect is positive: • P(A ∩ B) / ( P(A) P(B) ) • P(B|A)/P(B) • Example: Diapers implies Beer • Support: P(D ∩ B) = .6 P(D) = .7 P(B) = .5 • Confidence: P(B|D) = .857 = P(D ∩ B)/P(D) = .6/.7 • Lift: P(B|D) / P(B) = 1.714 = .857 / .5

32. Association Challenges • If an item is rarely purchased, any other item bought with it seems important. So combine items into categories. • Some relationships are obvious. • Burger and fries. • Some relationships are meaningless. • Hardware store found that toilet rings sell well only when a new store first opens. But what does it mean?

33. Cluster Analysis • Examples • Are there groups of customers? (If so, we can cross-sell.) • Do the locations for our stores have elements in common? (So we can search for similar clusters for new locations.) • Do our employees (by department?) have common characteristics? (So we can hire similar, or dissimilar, people.) • Problem: Many dimensions and large datasets Large intercluster distance Small intracluster distance

34. Geographic/Location • Examples • Customer location and sales comparisons • Factory sites and cost • Environmental effects • Challenge: Map data, multiple overlays