IT&F: BUSINESS INTELLIGENCE

1. IT&F: BUSINESS INTELLIGENCE Stefano Grazioli

2. Critical Thinking • Doing well • Extra lab 2-330 on Friday #395, Homework help • Easy Meter

3. Business Intelligence The processes, technologies, and peopleto turn data into informationin order to drive profitable business action. - Wayne Eckerson, TDWI Source: B. Wixom

4. BI and Analytics Analytics is “the extensive use of data, statistical and quantitative analysis, explanatory and predictive models, and fact-based management to drive decisions and actions” (Davenport and Harris – Competing on Analytics) “BI refers to the general ability to organize, access and analyze information in order to learn and understand the business.” (Gartner)

5. Business Value from Data Strategy • Usable data • High quality data • Usefuldata (big)DataUse • Accurate • Timely • Valid • Awareness • Access • Usefulness • Security • Privacy • Meaning • Scope • Sharing Business Value Based on work by B. Wixom

6. Why is Data Bad? No one gets up in the morning and says“I’m going to make lots of errors today” Source: T. Redman, Data Driven, 2008

7. Data Quality Benchmarks • Analysts cannot find what they need 50% of the times • 10-25% of the records have inaccuracies or missing elements • Data frequently misinterpreted • Known data loss and theft • Most databases implement inconsistent definitions • 50% of the stored data is never used • 10x duplication of data Source: T. Redman, Data Driven, 2008

8. Approaches to Data Quality • Find and Fix • Prevent at the source • Do nothing (3M)

9. Homework Business Scenario:Google’s Daily Cagr

10. You are an analyst at a broker firm. Daily Cagr for Google Many of our customers invest for short amounts of time on Google. They sell their shares within a few weeks…. I wonder: do they make any money out of it?

11. file with~800customers whobought and sold GOOG within thelast two months. Three steps (and two homework) Clean data: phones, dates Compute Daily Cagr = [(final price/initial price)1/days ]-1 Report the Average Daily Cagracross all customers. Daily Cagr for Google

12. Cleaning Phone Numbers • From:#2-345-3-48565 • To:(234)-534-8565

13. UML Activity Diagram - Daily Compound Average Growth of a Security (part I) When the user presses a button labeled “start”, a file selection windows pops out. The user selects a .csv file. The file is shown starting at “A1”. The start button becomes invisible. Three more buttons appear: “Clean phone numbers”, “Format Dates”, and “Compute Daily CAGR”. Select the next column and/or date [is a date] Format asmm/dd/yyyy & clear highlight if any Highlight the cell in yellow A Next homework [Compute] [Format Dates] [Clean ph.no] [No More Dates in this column] Select the next phone no. Count its digits [Exactly 10 digits] [No more columns] Format as(xxx)-xxx-xxxx & clear highlight if any Highlight the cell in red A A [No More Ph.No]

14. ' store the address of the current active sheet, i.e., the ‘target’ DimmyActiveS AsExcel.Worksheet = Application.ActiveSheet ' select a file DimmyFile AsString= Application.GetOpenFilename() 'get the data in a new temporary workbook Application.Workbooks.OpenText(myFile, , , Excel.XlTextParsingType.xlDelimited, , , , , True) ' store the address of the temporary workbook DimmyActiveWB As Excel.Workbook = Application.ActiveWorkbook ' copy the content from the temporary to the ‘target’ sheet myActiveS.Range("A1:J1000").Value = Application.ActiveSheet.Range("A1:J1000").Value ‘ close the temp workbook myActiveWB.Close() Reading a File into EXCEL

15. Dim lastRow As Integer = Cells(Rows.Count,1).End(Excel.XlDirection.xlUp).Row Finding the Last Non-empty Row

16. Suggestions • Video available • Come to the Lab

17. WINIT What Is NewIn Technology?

18. Strings and Dates

19. DimmyString AsString=“This is a sample string" DimmyString2 AsString = "s" DimmyChar AsChar = "s"c Strings and Characters

20. DimmyString AsString = "#2344-234-33-3" Dimtemp AsString = "" ForEach x AsCharIn myString If IsNumeric(x) Then temp = temp + x EndIf Next Testing Numbers

21. Dim myS AsString = "This is a sample string" myS = myS.Insert(4, "xyz") myS = myS.Remove(4, 3) 'starting where, how many myString = myS.Replace(" is", " was") myS = myS.Substring(0, 9) + “ another" + myS.Substring(10, 13) + "." Inserting and Removing

22. Dim myS AsString = "This is a sample string" DimmyPosition AsInteger = 0 myPosition = myS.IndexOf("s") Finding

23. myLenght = myString.Length myNewString = myString.Trim() myNewString = myString.TrimEnd() myNewString = myString.TrimStart() myNewString = myString.PadLeft(50) myNewString = myString.PadRight(20) Trimming and Padding Total length of the result

24. Name, major Learning objectives Things you like about the class Things that can be improved Strengths / Attitude towards the Tournament You do the talking

25. Dim myDate AsDate = "11/14/2002“ Year = myDate.Year Month = myDate.Month Day = myDate.Day DOW = myDate.DayOfWeek DOY = myDate.DayOfYear ... ... Dates MyDate 2002 Year 11 Month 14 Day 45 Week .... ...

26. DimmyDate1 AsDate DimmyDate2 AsDate DimmyTS AsTimeSpan myDate1 = Range("A1").Value myDate2 = Range("A2").Value myTS = myDate2 - myDate1 Range("A3").Value = myTS.Days TimeSpan A TimeSpan represents the elapsed time between two dates. Date1 Date2 TIMESPAN

27. mySpan.Days gives you the total number of days mySpan.TotalDays gives you the total number of days, plus a fraction of day based on the hours TimeSpan