1 / 48

Business Intelligence on a Budget

Business Intelligence on a Budget. 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

melosa
Download Presentation

Business Intelligence on a Budget

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related