Business Intelligence on a Budget

1. Business Intelligenceon a Budget

2. Christopher Frost • Born in Texas, Raised in Oklahoma • Undergrad from OU • MBA from Texas A&M • Married with 4 Kids, 2 Boys and 2 Girls • Ranging from 19 Years Old to 7 Years Old • 7 Years Working at Collin College • Before That, 2 Years at the University of Central Oklahoma • 14 Years of Combined Experience with BI, CRM, and SFA Solutions About Me

3. My Life as a Series of Graphs Kids About Me

4. My Life as an Infographic About Me

5. Drowning in a Sea of Data • What stories does your data tell about you? • Digital Footprint • What stories do you want data to tell you? • Student success factors • Outcome metrics • Interest in programs and courses offered About Data

6. IT as a Librarian PROS • Central Repository of Information • Secure • Backed Up • Available to Authorized Users CONS • Data Might Be Hard to Access • Limited QA • IT Might Be Tracking Data You Don’t Need & Ignoring Data You Do Need About Data

7. Power of Stories • Wikileaks • School Performance http://schools.chicagotribune.com/ • Comparative School Data http://projects.propublica.org/schools/ About Data

8. Rules are Changing… • End users are more sophisticated • Want more access to the raw data • Want to tell their own stories About Data

9. Moving from this… About Data …to this

10. From Reports to Dashboards • “I don’t know what I want, but I’ll know it when I see it.” • People are wired to react to visuals • Things are moving from static reports to interactive dashboards • OLAP cubes allow users to dig into the total numbers and look at underlying trends • This ability to dig deeper allows us to tell better, more accurate stories. About Data

11. Sophisticated BI Systems • Pentaho • Microsoft’s SharePoint / SSRS • Oracle’s OBIEE / Oracle Answers • SPSS • Cognos Data Tools

12. Budget-Minded BI Solutions • Tableau Public Data Tools

13. Tableau Public Demo • Did anyone bring a spreadsheet to share? SHORTCOMINGS OF TABLEAU PUBLIC • Free version means the data is public for everyone. • Don’t share if you care about privacy. • Limited to 100,000 rows. Data Tools

14. Budget-Minded BI Solutions • Tableau Public • ??? Data Tools

15. Most Powerful BI Tool • Is the One Used Most Often - Microsoft Excel Data Tools … and PowerPivot Can Make It Much Better

16. Budget-Minded BI Solutions • Tableau Public • Microsoft Excel • PowerPivot Data Tools

17. Setting Up PowerPivot Data Tools

18. Setting Up PowerPivot Data Tools

19. Setting Up PowerPivot Data Tools

20. PowerPivot Features • Work with Large Data Sets • Combine Data from Different Data Sources • However Only Inner Joins Allowed • Easy to Set Up Dashboard-Style Views • Slicers Make It Easy to Visually Filter Data • Use DAX to Create Calculated Columns on the Fly • Working with Dates (i.e. YEAR(H2)) Data Tools

21. PowerPivot Features Data Tools

22. PowerPivot Features Data Tools

23. PowerPivot Demo • Did anyone bring a spreadsheet to use? Data Tools

24. How Can I Get to the Data? • With the Data Tab and Get External Data Data Tools

25. How Can I Get to the Data? • Can Use Excel to Access the Following Data: • Access Databases • SQL Server Databases • CSV Files • Text Files • XML Files Data Tools

26. How Can I Get to the Data? • Can Also Use Excel to Query Data in: • Oracle Databases • mySQL Databases Data Tools However, They Require Special Drivers and Client Installs Before They Can Be Used

27. Mining the Metadata • Metadata is the data about your data • Can use this as a roadmap to identify those elements that you are interested in • Metadata already exists inside of your database as column comments, datatypes, and table comments. • Best to document metadata as you create new tables or modify existing ones. Data Tools

28. Budget-Minded BI Solutions • Tableau Public • Microsoft Excel • PowerPivot • Oracle Data Modeler 4.0 Data Tools

29. Mining the Metadata • Oracle Data Modeler 4.0 Demo Data Tools

30. Mining the Metadata End Result: • Glossary of Available Data Sources • Security Defined via an Object’s Associated Roles • Data Element Dictionary BEST OF ALL • It’s easily updatable since it sits along side your data. Data Tools

31. Roadblocks to DataAccess • Biggest issue isn’t technical. It’s functional. • Data owners are justifiably protective of their area’s data. • Need assurances and safeguards in place to prevent data from being misused. Data Processes

32. Roadblocks to Data Access • Student needs assurances that Personally Identifiable Info isn’t going leak out • Finance needs confidentiality on cost center authorizations • HR needs assurances that performance-related comments don’t become public For Example: Data Processes

33. IT as Matchmaker • IT works with a wide variety of areas • Can facilitate two-way conversations between departments on shared data • What data do you want? • What data are you willing to share? • What concerns do you have about sharing data with others? • Start small and build trust between user communities over time. Data Processes

34. Balancing Access with Security • Can Set Up Views of the Data that Filter Out Sensitive Info • Use a Surrogate Key for Granularity • Associate Views with Security Appropriate Roles and User Accounts Data Processes

35. Balancing Access with Security PUBLIC Registration Reports Enrollment Reports Course Schedule Section Info Outcomes and Retention Info Program Effectiveness Early Warning Systems Learner Info Access to Everything ADMIN ASSISTANTS Data Processes DEANS VPs

36. Balancing Access with Security • Can Set Up Views of the Data that Filter Out Sensitive Info • Use a Surrogate Key for Granularity • Associate Views with Security Appropriate Roles and User Accounts Data Processes

37. Build a Data Warehouse? • Views against Source Data and Working with Spreadsheets will only Get You So Far • Building a Data Warehouse Takes Planning, But Isn’t Rocket Science • Data Warehouse = Source Data through Time • Clumping Data Together in Denormalized Structures • Basically, Turning 4000+ Tables into a Dozen Tables that Fit Together Data Warehousing

38. Advantages • Greater Performance since Totals are Often Pre-aggregated at a Granular Level • Tables Can Be Optimized via Range or Hash Partitioning • Calls to the Source System are Reduced by Having a Reporting System Available Data Warehousing

39. Data Warehousing Data Warehousing

40. Data Warehousing Data Warehousing

41. Data Warehousing Data Warehousing

42. Data Warehousing • Facts are your numeric data, your measures • Level of detail on the fact table is the grain • Facts are joined to dimensions in the star schema • Dimensions provide context for facts • They are used for filtering queries or reports • Dimensions control the grouping of facts when you run the totals Data Warehousing

43. Questions to Ask • How will you deal with slowly changing dimensions? • Will you.. • Overwrite the data • Or load the new value while “inactivating” the old value • Most have a mixture, depending on the importance of that historical info. Data Warehousing

44. Lessons Learned • Start Small, Add On When You Can • Avoid Inner Join Conditions • Be Careful with NULL Values • Look at Count Comparisons and Time Values to Validate the Accuracy and Timeliness of the Data • Don’t Be Afraid to Start Over. Things Change and Sometimes the Data Warehouse Needs to Reflect That Data Warehousing

45. Further Reading • The two great masters of data warehousing are Ralph Kimball and William Inmon. Any of the books below are great starting points. Final Points

46. Further Reading • I would also highly recommend Lawrence Corr and Chris Adamson. Both represent the best of the new ideas in data warehousing. Final Points

47. Or Training • Tim Smith teaches a great BI Certificate Course at our Courtyard campus. However, there are lots of good BI programs out there. Final Points

48. Thanks for Attending • Have a great day! • A copy of this presentation will be available on www.savvybi.com. Final Points