Data Warehouse and Business Intelligence Dr. Minder Chen Professor of MIS

1. Data Warehouse and Business Intelligence Dr. Minder Chen Professor of MIS Martin V. Smith School of Business and Economics CSU Channel Islands Minder.Chen@CSUCI.EDU

2. BI “The key in business is to know something that nobody else knows.” -- Aristotle Onassis PHOTO: HULTON-DEUTSCH COLL Business Intelligence (BI) is the process of gathering meaningful information to answer questions and identify significant trends or patterns, giving key stakeholders the ability to make better business decisions. “To understand is to perceive patterns.” — Sir Isaiah Berlin "The manager asks how and when, the leader asks what and why." — “On Becoming a Leader” by Warren Bennis

3. BI Questions • What happened? • What were our total sales this month? • What’s happening? • Are our sales going up or down, trend analysis • Why? • Why have sales gone down? • What will happen? • Forecasting & “What If” Analysis • What do I want to happen? • Planning & Targets Source: Bill Baker, Microsoft

4. Business Valuation Models for BI

5. Performance Dashboards for Information Delivery

6. Scorecards for Information Delivery Balanced Scorecard

8. Inmon's Definition of Data Warehouse – Data View • A warehouse is a • subject-oriented, • integrated, • time-variant and • non-volatile collection of data in support of management's decision making process. • Bill Inmon in 1990 Source: http://www.intranetjournal.com/features/datawarehousing.html

9. Inmon's Definition Explain • Subject-oriented: They are organized around major subjects such as customer, supplier, product, and sales. Data warehouses focus on modeling and analysis to support planning and management decisions vs. operations and transaction processing. • Integrated: Data warehouses involve an integration of sources such as relational databases, flat files, and on-line transaction records. Processes such as data cleansing and data scrubbing achieve data consistency in naming conventions, encoding structures, and attribute measures. • Time-variant: Data contained in the warehouse provide information from an historical perspective. • Nonvolatile: Data contained in the warehouse are physically separate from data present in the operational environment.

10. Business Intelligence Increasing potential to support business decisions (MIS) End User Making Decisions Business Analyst Data Presentation Visualization Techniques Data Analyst Data Mining Information Discovery Data Exploration OLAP, MDA, Statistical Analysis, Querying and Reporting Data Warehouses / Data Marts DBA Data Sources (Paper, Files, Information Providers, Database Systems, OLTP)

11. Where is Business Intelligence applied? ERP Reporting KPI Tracking Product Profitability Risk Management Balanced Scorecard Activity Based Costing Global Sourcing Logistics Sales Analysis Sales Forecasting Segmentation Cross-selling CRM Analytics Campaign Planning Customer Profitability Operational Efficiency Customer Interaction

12. OLTP Versus Business Intelligence: Who asks what? OLTP Questions When did that order ship? How many units are in inventory? Does this customer haveunpaid bills? Are any of customer X’s line items on backorder? Analysis Questions What factors affect order processing time? How did each product line (or product) contribute to profit last quarter? Which products have the lowest Gross Margin? What is the value of items on backorder, and is it trending up or downover time?

13. The Data Warehouse/BI Architecture & Process E T L ETL: Extract, Transform, and Load OLAP Cubes Data Marts Source Systems E T L E T L Clients Data Warehouse Query Tools Reporting Analysis Data Mining 1 2 3 4 Design the Populate Create Query Data Warehouse Data Warehouse OLAP Cubes Data

14. Normalized Database for OLTP

15. OLTP vs. OLAP Source: http://datawarehouse4u.info/OLTP-vs-OLAP.html

16. Measuring Performance • Real estate consumer services and analysis firm Trulia reports that Oct. 2013 saw only an 0.6% rise in home asking prices comparing to Sept. 2013. • However, the average home asking price rose by 11.7% from Oct. 2012 to Oct. 2013. • The year-over-year figure is the largest jump since the housing bubble popped back in 2007-08. Source: http://www.thestreet.com/story/12100873/1/home-sellers-price-hikes-coming-unsustainably-fast.html

17. compare with last period vs. year-on-year comparison A time series is a sequence of data points, measured typically at successive points in time spaced at uniform time intervals. Examples of time series are the daily closing value of the Dow Jones Industrial Average  - Wikipedia “Time series” The year-over-year data compares a time period (e.g., a month or a quarter) against the same time period last year. You can compare a performance indicator with one from last period (quarter, month, week, day) One of the advantages of year-over-year comparisons is that it automatically negates the effect of seasonality (e.g., seasonal effect). It is a more effective way of looking at performance.

18. Identifying Measures and Dimensions Information for Decision Making Performance Measures for KPI Performance Drivers Measures Dimensions Attribute Type? Why? What? • The attribute (column) varies • continuously: • Unit Sold • Cost • Sales • Balance • The attribute is perceived as • a constant or discrete value: • Name/Description • Location • Color • Size

19. Star Schema Products Dimension Table Multi-dimensional Data model Channels Dates Sales Dimension Table Dimension Table Fact Table with measures Dimension Table Promotions Customers Dimension Table

20. Snowflake Schema Brands Normalized Star Schema Products Dimension Table Channels Dates Sales Dimension Table Dimension Table Fact Table Normalized Customers Dimension Table Promotions Customer type Dimension Table Source: http://www.diffen.com/difference/Snowflake_Schema_vs_Star_Schema

21. Designing Data Warehouse: Dimensional Design Process • Select the business process to model • Declare the grain of the business process/data in the fact table (The grain represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". ) • Identify the numeric facts/meaures that will populate each fact table row • Choose the dimensions that apply to each fact table row Business Requirements Data Realities Ref: http://en.wikipedia.org/wiki/Fact_table

22. Select a business process to model • Not business departments or business functions • Cross-functional business processes • Business events • Examples: • Raw materials purchasing • Order fulfillment process • Shipments • Invoicing • Inventory • General ledger • Insurance claims • Class enrollment • Airline ticket sales

23. Facts Table Measurements of business events. Dimensions Measures The Fact Table contains keys and units of measure

24. Fact Tables Fact tables have the following characteristics: • It contains numeric measures (metric) of the business. • It may contain summarized (aggregated) data. • It almost always contains date-stamped data. • Measures are typically additive. • Have key value that is typically a concatenated key composed of the primary keys of the dimensions. • Joined to dimension tables through foreign keys that reference primary keys in the dimension tables. • Fact tables are narrow (few attributes) but many records.

25. A Dimensional Model for a Grocery Store’s Sales

26. Creating Dimensional Model • Identify fact tables • Translate business measures into fact tables • Analyze information from source systems for additional measures • Identify base and derived measures • Document additivity of measures (e.g., non-additive[price], semi-additive [quantity-on-hand is not additive over time], or additive [quantity]) • Identify dimension tables • Link fact tables to the dimension tables • Create views for users

27. Transaction Level Order Item Fact Table

28. Inside a Dimension Table • Dimension table key: Uniquely identify each row. Use surrogate key (integer). • Table is wide: A table may have many attributes (columns). • Textual attributes. Descriptive attributes in string format. No numerical values for calculation. • Attributes not directly related: E.g., product color and product package size. No transitive dependency. • Not normalized (star schema). • Drilling down and rolling up along a dimension. • One or more hierarchy within a dimension. • Fewer number of records.

29. OLAP Solutions • Data Warehouse • Data Mart • Cubes • Dimensions • Measures • Cells OLAP Server (e.g., Oracle ESSBase & SQL Server’s Analysis Services) Europe Asia US Gadgets Gizmos Thingies Widgets 130 135 140 142 205 390 350 475 A cube is a collection of data that’s been aggregated to allow queries to return data quickly. 175 230 190 250 310 340 410 450 Q1 Q2 Q3 Q4

30. Hierarchy

31. A Hierarchy in the Product Dimension • SKU: Stock Keeping Unit • Hierarchy: • Department  Category  Subcategory  Brand  Product

32. Multidimensional Query Techniques Performance Drivers Product Time Why? Slicing Performance Measures Geography What? Why? Dicing Aggregated data Hierarchy Why? Drilling down Roll up Drill down Detail data

33. Roll-Up and Drill-Down Source: http://www.tutorialspoint.com/dwh/dwh_olap.htm

34. Slice and Dice Source: http://www.tutorialspoint.com/dwh/dwh_olap.htm

35. A Visual Operation: Pivot (Rotate) NY LA SF 10 Juice Cola Milk Cream 47 30 12 Date 3/1 3/2 3/3 3/4 Month Region Product

36. Operations in Multidimensional Data Model • Aggregation (roll-up) • dimension reduction: e.g., total sales by city • summarization over aggregate hierarchy: e.g., total sales by city and year  total sales by region and by year • Navigation to detailed data (drill-down) • e.g., (sales - expense) by city, top 3% of cities by average income • Selection (slice or dice) defines a subcube • e.g., sales where city = Palo Alto and date = 1/15/96 • Visualization operations (e.g., Pivot)

37. Pivot Table in Excel

38. Date Dimension of the Retail Sales Model

39. Store Dimension • It is not uncommon to represent multiple hierarchies in a dimension table. Ideally, the attribute names and values should be unique across the multiple hierarchies.

40. ETL ETL = Extract, Transform, Load. ETL cycle includes • Build reference data (e.g., currency codes) • Extract (from sources) • Validate • Transform (clean, apply business rules, check for data integrity, create aggregates) • Stage (load into staging tables, if used) • Audit reports on compliance with business rules. • Publish/load (to target tables in the data warehouse) • Clean up

41. Data Quality Issues • No common time basis • Different calculation algorithms • Different levels of extraction • Different levels of granularity • Different data field names • Different data field meanings • Missing information • No data correction rules • No drill-down capability

42. Building The Warehouse Transforming Data

43. The Anomalies Nightmare NAME ADDRESS TYPE CUST # 90328575 187 N. PARK St. Salem NH 01458 OEM Digital Equipment 187 N. Pk. St. Salem NH 01458 OEM 90328575 DEC 187 N. Park St Salem NH 01458 $#% 90238475 Digital Digital Corp 187 N. Park Ave. Salem NH 01458 Comp 90233479 Digital Consulting 15 Main Street Andover MA 02341 Consult 90233489 Digital Info Service PO Box 9 Boston MA 02210 Mail List 90234889 Park Blvd. Boston MA 04106 SYS INT 90345672 Digital Integration Noise in Blank Fields Spelling Anomalies No Standardization No Unique Key How does one correctly identify and consolidate anomalies from millions of records?

44. Data Mining & Knowledge Discovery in Database (KDD) Process Data Mining is the practice of searching through large amounts of computerized data to find useful patterns or trends Data mining is the analysis step of the "Knowledge Discovery in Databases" process (KDD) involving methods such as artificial intelligence, machine learning, statistics, and database systems. Source: http://www2.cs.uregina.ca/~dbd/cs831/notes/kdd/1_kdd.html

45. Knowledge Discovery Knowledge discovery in databases is the non-trivial process of identifying valid, novel, potentially useful, and ultimately understandable patterns in data. http://www2.cs.uregina.ca/~dbd/cs831/notes/kdd/1_kdd.html

46. Cross Industry Standard Process for Data Mining Action Decision Source: http://en.wikipedia.org/wiki/Cross_Industry_Standard_Process_for_Data_Mining

47. Data Mining Tasks and Examples • Classification - Customer profiling into predefined categories via supervised learning using Decision Tree or Neural Network • Clustering -  grouping a set of objects in such a way that objects in the same group (cluster) are more similar to each other than to those in other groups (clusters) Market segmentation , e.g., • Summarization - Credit scoring and risk analysis using Bayesian inference. It is considered a Structured prediction technique. • Association - What is the likelihood that a customer will buy a product next month, if he buys a related item today? (sequence association) http://www2.cs.uregina.ca/~dbd/cs831/notes/kdd/2_tasks.html

48. OLAP and Data Mining Address Different Types of Questions While reporting and OLAP are informative about past facts, only data mining can help you predict the future of your business. Source: http://www.dmreview.com/editorial/dmreview/print_action.cfm?articleId=2367

49. Shopping Basket Analysis • Which items are purchased in a retail store at the same time? • Amazon use collaborative filtering that use shopping basket (sales) data to make recommendations when you select an item. Ref: http://en.wikipedia.org/wiki/Collaborative_filtering

50. Issues on Interpreting Modeling Results • Housing price: Use factors, such as location, number of bedrooms, and square footage, to determine the market value of a property. • Beer and Diaper Source: http://dssresources.com/newsletters/66.php