Chapter 9 Business Intelligence Systems

1. Chapter 9Business Intelligence Systems Jason C. H. Chen, Ph.D. Professor of MIS School of Business Administration Gonzaga University Spokane, WA 99258 chen@gonzaga.edu

2. “Data Analysis, Where You Don’t Know the Second Question to Ask Until You See the Answer to the First One.” • Tracking race competitors from each of event, and having unbelievable success selling products to them. • Want to match competitors to personal trainers in same locale. • Earn referral fee. • How to track them? Mailing address? IP address? • Got data and Excel to start. • Serious data mining needs a data mart.

3. Event Driven Alert - A Scenario • Three transactions were posted in a credit account while the account holder was traveling in summer 2010: • Ranch 99 San Jose, California $102.33 Aug. 1, 2010 • Exxon Austin, Texas $99.12 August 3, 2010 • Exxon Houston, Texas $120.44 August 5, 2010 • What action will you (the credit company) take and why? • How the scenario can be detected?

4. CRM and Data Mining (BI)Example • A Grocery store in U.K. with the following “patterns” found: • Every Thursday afternoon • Young Fathers (why?) shopping at store • Two of the followings are always included in their shopping list • Diapers and • Beers • What other decisions should be made as a store manager (in terms of store layout)? • Short term vs. Long term • This is an example of cross-selling • Other types of promotion: up-sell, bundled-sell • IT (e.g., BI) helps to find valuable information then decision makers make a timely/right decision for improving/creating competitive advantages.

5. Q/A • Can the “patterns” in the grocery store example be produced from its Database? • Y/N • Why? • It only can be produced from its “Data Warehouse” using a kind of “data mining” software.

6. Study Questions Q1: How do organizations use business intelligence (BI) systems? Q2: What are the three primary activities in the BI process? Q3: How do organizations use data warehouses and data marts to acquire data? Q4: How do organizations use reporting applications? Q5: How do organizations use data mining applications? Q6: How do organizations use BigData applications? Q7: What is the role of knowledge management systems? Q8: What are the alternatives for publishing BI? Q9: 2026?

7. Q1: How Do Organizations Use Business Intelligence (BI) Systems? • Information systems generate enormous amounts of operational data that contain patterns, relationships, clusters, and trendsabout customers, suppliers, business partners, and employees that can facilitate management, especially planning and forecasting. • Business intelligence (BI) systems produce such information from operational data. • Data communications and data storage are essentially free, enormous amounts of data are created and stored every day. • 12,000 gigabytes per person of data, worldwide in 2009

8. Why do organizations need business intelligence? • BI systems are computer programs provide valuable information for decision making. • Three primary BI systems: • __________ tools read data, process them, and format the data into structured reports (e.g., sorting, grouping, summing, and averaging) that are delivered to users. They are used primarily for assessment. RFM (Recency, Frequency, and Monetary Value) is one of the tool for reporting. • ____________ tools process data using statistical, regression, decision tree, and market basket techniques to discover hidden patternsand relationships, and make predictionsbased on the results • ___________ _________ tools store employee knowledge, make it available to whomever needs it. These tools are distinguished from the others because the source of the data is human knowledge. Reporting Data-mining Knowledge management

9. Q1: How Do Organizations Use Business Intelligence (BI) Systems? Components of Business Intelligence System Fig 9-1: Components of a Business Intelligence System

10. Tools vs. Applications vs. Systems • BI ____ (e.g., decision-tree analysis) is one or more computer programs. BI tools implement the logic of a particular procedure or process. • BI __________ is the use of a tool on a particular type of data for a particular purpose. • BI _______ is an information system having all five components (what are they?) that delivers results of a BI application to users who need those results. tool application system

11. How Do Organizations Use BI? [4] [3] [2] (Decision Support Systems) [1] • Fig 9-2 Example Uses of Business Intelligence

12. What Are Typical BI Applications? • Identifying changes [or patterns] in purchasing patterns (data warehouse) • Important life events cause customers to change what they buy. • BI for entertainment • Netflix has data on watching, listening, and rental habits, however, determines what people actually want, not what they say. • Predictive policing • Analyze data on past crimes, including location, date, time, day of week, type of crime, and related data, to predict where crimes are likely to occur.

13. Just-in-Time Medical Reporting • Example of real time data mining and reporting. • Injection notification services • Software analyzes patient’s records, if injections needed, recommends as exam progresses. • Blurryedge of medical ethics.

14. Q2: What Are the Three Primary Activities in the Business Intelligence Process? • The primary activities in the BI process are: • 1. Data __________ • The process of obtaining, cleaning, organizing relating, and cataloging source data. • 2. BI ________ • The process of creating BI analysis: reporting, data mining, and knowledge management. • 3. Publish _______ • The process of delivering BI to the knowledge workers who need it. acquisition analysis results

15. Q2: What Are the Three Primary Activities in the BI Process? [1] [2] [3] • Fig 9-3 Three Primary Activities in the BI Process

16. Using Business Intelligence to Find Candidate Parts at Falcon Security • Identify parts that might qualify. • Provided by vendors who make part design files available for sale. • Purchased by larger customers. • Frequently ordered parts. • Ordered in small quantities. • Used part weight and price surrogates for simplicity.

17. Acquire Data: Extracted Order Data • Query Sales (CustomerName, Contact, Title, Bill Year, Number Orders, Units, Revenue, Source, PartNumber) Part (PartNumber, Shipping Weight, Vendor)

18. Sample Extracted Data: Part Data Table

19. Analyze Data

20. Sample Orders and Parts View Data

21. Creating Customer Summary Query

22. Customer Summary

23. Qualifying Parts Query Design

24. Publish Results: Qualifying Parts Query Results

25. Publish Results: Sales History for Selected Parts

26. ________ is the process of obtaining, cleaning, organizing, relating, and cataloging source data. A) Data entry B) Data acquisition C) Data mining D) Data encryption Answer: ______ B

27. ________ requires the user to request business intelligence (BI) results. A) Push publishing B) Pull publishing C) Desktop publishing D) Accessible publishing Answer: ______ B

28. Q3: How Do Organizations Use Data Warehouses and Data Marts to Acquire Data? • Functions of a data warehouse • Obtain data from operational, internal and external databases. • Cleanse data. • Organize and relate data. • Catalog data using metadata. Current Data Base contains ________ information (up-to-dated) Data Warehouse = Data Base + _____________ Historical Data

29. Components of a Data Warehouse • Fig 9-14 Components of a Data Warehouse

30. Possible Problems with Source Data Curse of dimensionality

31. Data Warehouse vs. Data Mart subset Data Mart is a _________ of Data Warehouse • Fig 9-15 Data Mart Examples

32. Data Base, Data Warehouse and Data Marts • Data base: An organized collection of logically related (current) data files. • Data Warehouse: A data warehouse stores data from current and previous years (historical data) that have been extracted from the various operational and management database of an organization. • Data mart: a subset of data warehouse that holds specific subsets of data for one particular functional area or project.

33. Data marts: Mini-warehouses, limited in scope L T E Separate ETL for each independent data mart Data access complexity due to multiple data marts Independent data mart data warehousing architecture Legacy System: Operational database 33

34. Which of the following statements is true about operational data? A) It is always better to have data with too coarse a granularity than with too fine a granularity. B) If the data granularity is too coarse, the data can be made finer by summing and combining. C) Purchased operational data often contains missing elements. D) Problematic operational data is termed rough data. Answer: _____ C

35. Examples of Consumer Data That Can Be Purchased • Fig 9-13 Examples of Consumer Data that Can Be Purchased

36. Possible Problems with Source Data Curse of dimensionality • Fig 9-14 Possible Problems with Source Data

37. Q4: How Do Organizations Use Reporting Applications? • Create meaningful information from disparate data sources • Deliver information to user on time • Basic operations: • Sorting   • Filtering • Grouping   • Calculating • Formatting

38. What are typical reporting applications? • RFM Analysis allows you to analyze and rank customers according to purchasing patterns as this figure shows. • __________:How recently a customer purchased items? => leads and opportunities • __________: How frequently a customer purchased items? => retention • __________ Value: How much a customer spends on each purchase? => profitability • RFM Analysis • Sort the data by date (for recency), times (for frequency), and purchase amount (for money), respectively • Divide the sorted data into five groups • Assign 1 to top 20%, 2 to next 20%, 3 to the third 20%, 4 to the fourth 20% and 5 to the bottom 20%. • The _______ the score, the better the customer. Recency Frequency Monetary lower

39. RFM Analysis: Example RFM Scores R F M lower The ______ the score, the better the customer, and, consequently, the more profit the company will be. Fig 9-16 Example of RFM Scores Organizations can find their most valuable customers through “RFM”: • Recency: How recently a customer purchased items? => leads and opportunities • Frequency: How frequently a customer purchased items? => retention • MonetaryValue: How much a customer spends on each purchase? => profitability _ecently _requently _oney

40. Interpreting RFM Score Results • Ajax has ordered recently and orders frequently. M score of 3 indicates it does not order most expensive goods. • A good and regular customer but need to attempt to up-sellmore expensive goods to Ajax • Bloominghams has not ordered in some time, but when it did, ordered frequently, and orders were of highest monetary value. • May have taken its business to another vendor. Sales team should contact this customer immediately. • Caruthers has not ordered for some time; did not order frequently; did not spend much. • Sales team should not waste any time on this customer. • Davidson in middle • Set up on automated contact system or use the Davidson account as a training exercise 80/20 Rule (Pareto Principle)

41. RFM Analysis Classification Scheme

42. A sales team should attempt to up-sell more expensive products to a customer who has an RFM score of ________. A) 311 B) 551 C) 113 D) 542 Answer: ______ C

43. OLAP and its Applications • Online Analytical Processing (OLAP), a second type of reporting tool, is more generic than RFM. • OLAP provides you with the dynamic ability to sum, count, average, and perform other arithmetic operations on groups of data. Reports, also called OLAP cubes. • What software and function that enable you to create OLAP and its applications? • ANSWER • EXCEL with • Pivot table

44. Example of Grocery Sales OLAP Reporthttp://dwreview.com/OLAP/ http://www.tableausoftware.com • Online Analytical Processing (OLAP) cubes, use • Measures which are data items of interest. In the figure below a measure is Store Sales Net . • Dimensions which are characteristics of a measure. In the figure below a dimension is Product Family. OLAP Product Family by Store Type • Fig 9-17 Example Grocery Sales OLAP Report

45. Example of Expanded Grocery Sales OLAP Report Drill down • Fig 9-18 Example of Expanded Grocery Sales OLAP Report

46. Example of Drilling Down into Expanded Grocery Sales OLAP Report • Fig 9-19 Example of Drilling Down into Expanded Grocery Sales OLAP Report

47. OLAP servers are special products that 1) read data from an operational database, 2) perform some preliminary calculations, and then3) store the results in an OLAP database Role of OLAP Server & OLAP Database Third-party vendors provide software for more extensive graphical displays

48. On-Line Analytic Processing (OLAP) • Enables mangers and analysts to interactively examine and manipulate large amounts of detailed and consolidated data from different dimensions. • Analytical Processing: • Drill-up (Consolidation)– ability to move from detailed data to aggregated data • Profit by Product >>> Product Line >>> Division • Drill-down – ability to move from summary/general to lower/specific levels of detail • Revenue by Year >>> Quarter >>>>Week >>>Day • Slice and Dice– ability to look across dimensions • Sales by Region Sales • Profit and Revelers by Product Line

49. REGION CUSTOMER • Slicing a data cube

50. Which of the following observations about RFM and OLAP reports is true? A) RFM reports are more generic than OLAP reports. B) OLAP reports are more dynamic than RFM reports. C) RFM reports have measures and dimensions. D) RFM reports can drill down into the data to a greater extent than OLAP reports. Answer: _______ B