There’s more to Assessment than testing! Data Warehouse 101

1. There’s more to Assessment than testing! Data Warehouse 101 Crans Computing Services lcrans@centurytel.net

2. R. Kipling's Honest Serving-men What, Why, When, How, Where, Who

3. Why are we here? To learn about Data Warehouses……. • What are they…. • How do they work…. • Should we want one….. • Why do we need one….

4. What is a Data Warehouse? * “A copy of transaction data specifically structured for query and analysis.” Ralph Kimball, The Data Warehouse, P. 310. * “A collection of integrated, subject-oriented databases designed to support the DSS function where each unit of data is relevant to some moment in time…”Inmon, Imhoff & Sousa, The Corporate Information Factory. * “Data warehouses combine data from all types of sources and have the following characteristics: subject oriented, integrated, time variant (has a time component), and non-volatile (no data aredeleted…)” Walton and Cline, Data Warehouses/Data Marts: Repositories for Data Mining.

5. What is a Data Warehouse? Why not use the Student Information System? • SIS = OLTP: On Line Transaction Processing • Describes processing at operational sites • DW = OLAP: On Line Analytical Processing • Describes processing for decision making

6. What’s the difference? OLTP OLAP • Data oriented • Day to day operations • Application-oriented • Current, Isolated • Detailed, Flat relational • Structured, Repetitive • Short, Simple transaction • Information oriented • Decision support • Subject-oriented • Historical, Consolidated • Summarized, Multidimensional • Ad hoc • Complex query User Function DB Design Data View Usage Unit of work

7. What’s the difference? OLTP OLAP Access Operations # Records accessed #Users Db size Metric • Read/write • Individual Records • Tens - hundreds • Thousands • 1 MB-GB • Transaction throughput • Read Mostly • Lots of Scans • Millions • Hundreds • 100GB-TB • Query throughput & response

8. What’s the difference? OLTP OLAP

9. What is Data Warehousing? The types of data warehousing management issues that need to be addressed are:

10. Why use a Data Warehouse? {Functional Reasons} Improve information access. Combines all the little files and different systems. Bring the user in touch with their data Easily accessed on the net; queries & reports are easy. Enhance the quality of decisions All data is “cleansed” and “conformed” for accuracy Provide cross-functional integration All data sources can be analyzed in combination

11. Why use a Data Warehouse? {Educational Reasons} Schools that engage in data-driven decision making reap a number of benefits. Overall, they have the information they need to- 1. assess the current and future needs of students, parents, staff, and the community; 2. decide what to change and how to institutionalize changes; 3. determine if goals are being met; 4. engage in continuous school improvement; 5. ensure that students "don't fall through the cracks;" 6. evaluate how well current programs and activities meet clients' needs; 7. guide curriculum development and revision; 8. identify the root causes of problems;

12. Why use a Data Warehouse? {Educational Reasons} 9. improve instruction; 10. judge the effectiveness of their reform efforts; 11. know which programs are producing the results the school wants; 12. meet state and federal requirements; 13. measure program; 14. monitor students' progress in meeting standards; 15. promote accountability; 16. select education programs and expertise that will solve problems and position the school for the future; 17. understand ways in which the school and community is changing.

13. Why use a Data Warehouse? {Educational Questions require answers} How did my 4th grade students do on the last assessment? Can I see the results broken out by gender, ethnicity and economic status? What are the attendance and grade patterns of those 4th graders who did not meet the standard? What are the patterns of discipline infractions among individual schools? Can I see the results represented by a graph? Among the 8th graders who did not meet the standard on the recent assessment, what are the skill areas that need improvement? How effective are the changes I made in our remedial reading program?

14. Why use a Data Warehouse? {Educational Questions require answers} How does student performance compare on different tests ? How can I develop an accurate way of equating standards across different assessments? How can I get a list of students who almost met the standard on a particular assessment? What steps are most effective in helping these students meet the standard? How do personal factors such as living situation and extracurricular activity affect performance? Is there a specific combination of factors that most effects school performance? Is there any correlation between a certain pattern of courses and assessment performance? Is there a relation between teacher certification and assessment performance? Is there any way to produce state reports easier and more accurately?

15. But, there may be a MAJOR problem….. What about a system-wide student number? For long-term analysis, data must follow the student in the school, in the district, and in the 9-12 areas. (Soon to be in the state as well) Why? Possible Solutions: • Forget the whole thing.. • Develop a numbering system that all districts will agree on…AND USE. • Let the Vendor do it!

16. Where will the Data Warehouse reside? The vendor can be an Application Service Provider (ASP), and host the Data Warehouse on the Internet. Or, as in the case of OPI, the DW can reside on the users’ servers. This requires extensive hardware and database expertise.

17. Who will control the Data Warehouse? Software maintenance will be the vendor’s responsibility. Hardware will be the users’ responsibility (unless vendor hosts the DW) Data accuracy, integrity and security will be the users’ responsibility, using vendor tools; and System growth and expansion will be a shared vendor/user responsibility.

18. How does a Data Warehouse work? What are the components? • Current Detail • Systems of Record • ETL - Transformation Programs • Summarized Data • Archives • Metadata • User Analysis Tools

19. How does a Data Warehouse work? What are the components? • Current Detail • The heart of a data warehouse • Comes directly from operational systems • Represents the entire enterprise • Snapshot, at a moment in time • Refreshed as frequently as necessary • Lowest level of data granularity

21. How does a Data Warehouse work? What are the components? • Systems of Record • Actually, part of your “legacy” systems… • The source of the best or most pertinent data • Most timely, complete, accurate • Usually closest to the source of entry into the district

22. How does a Data Warehouse work? What are the components? • ETL - Transformation Programs Different operational systems represent data differently, use different codes for the same thing; squeeze multiple pieces of information into one field. • Reformat, recalculate, or modify data structures • Add time elements • Identify default values • Supply logic to choose between multiple data sources • Summarize, tally, and merge data from multiple sources (ETL setup is shared by vendor and technical staff.)

23. ETLProcess Extract Transform Load SIS ITBS Data Staging Area DW MP ??? OLTP

24. ETL & CleansingProcess • Data cleaning is important to warehouse – there is high probability of errors and anomalies in the data: Inconsistentfield sizes, inconsistent descriptions, inconsistent value assignments, missing entries and violationsof integrity constraints. Optional fields in data entry are significant sources of inconsistent data.

25. ETL & Refresh Cycles • Periodically (e.g., every night, every week, quarter, semester, annual) or after significant events • On every update: not warranted unless warehouse data require current data, e.g., daily attendance. • Refresh policy set by administrator based on user needs and traffic • Possibly different policies for different sources

26. How does a Data Warehouse work? What are the components? • Summarized Data and Archives • Large, complex, very active, & “smart” DWs will: • store summarized/aggregated data for commonly asked questions; and • store separately or differently the low-access or obsolete data.

27. How does a Data Warehouse work? What are the components? • Metadata Metadata is data about the data. Needs to be developed for all data fields. Essential to document the use of the DW: to work with loading new data; for users to know contents of DW; for control of data security.

28. Sample Metadata page

29. How does a Data Warehouse work? What are the components? • User Analysis Tools • Pre-designed reports, primarily for print. • Pre-designed reports, with drill-up/drill-down capability • Ad-hoc queries, typically with OLAP cubes • Data extract & download for Excel/Access • Data mining.

30. Who will be the users? • 70% casual users, who make frequent use of the warehouse, but prefer static or parameterized reports. • 25% active users, who make frequent use of standard reports, and sometimes require assistance with ad hoc requests. They are usually comfortable with interactive reports but still use static and parameterized reports. • 5% power users, who prefer interactive reporting and regularly create their own ad hoc queries. They are often expert spreadsheet users, and regularly extract data for further analysis. Most technical teachers and staff probably fit into this category.

31. How does a Data Warehouse work? What are the components? • User Analysis Tools • Pre-designed reports, primarily for print.

32. “Traditional” report sample

33. How does a Data Warehouse work? What are the components? • User Analysis Tools • Pre-designed reports, with drill-up/drill-down capability, generally with interactive requests

36. Chart & Report Sample

37. How does a Data Warehouse work? What are the components? • User Analysis Tools • Ad-hoc queries, typically with OLAP cubes Very important functionality, so we’ll look at this more closely…

38. Multidimensional Data: A Portion of an OLAP Cube Test scores as a function of grade, year and subject. 5th 6th 7th Math SocSt Rdng Sci. 72 68 77 81 ’98 ’99 ’00 ‘01 Year Measures Dimensions

39. Visualize Pivoting the Cube 5th 6th 7th Year Math Soc. Rdng Sci 10 Grade 47 30 12 Subject ’98 ’99 ’00 ‘01 Slice Year Dice

40. What is…..Drill down? Soc. For 2001 Adams, John Bacon, Fran Carter, Jim Darwin, Chuck ….. ….. Wilson, Woodie Soc. For 2001 Adams, John Brown, Sally Carter, Jimmy Darwin, Charles ……. Math Soc. Rdng Sci 5 3 8 2 9 7 ’00 ’01 ’02 ‘03 Year

41. So, what is “Drill-up” & “Drill-down”? • Require “dimensional hierarchies”. State Region County School District School Subject Section Student Test Subtest Score Student Gender Ethnicity Birth date Residence Disability Grade level Important when considering data elements & granularity!

42. On-screen representation of OLAP cube

43. OLAP screen to paper report

44. How does a Data Warehouse work? What are the components? • User Analysis Tools • Data extract & download for Excel/Access Since no program is all things to all people, there is usually the capability to download selected fields for use in Excel, Access, or more sophisticated statistics programs like SPSS.

45. What about Data Mining? • OLAP answers questions you do know how to ask. • Data mining tries to answer questions you don‘t know how to ask. Data mining uses Probability and Statistics techniques such as: Regression analysis Classification trees analysis Neural Networks Cluster analysis “Fuzzy Logic” This is a good ‘phase 2’ topic.

46. “Digital Dashboard” Example

48. Data in.... Teachers Tests Schools ? - ? - ? Students ETL Internet DW Server Reporting District Office Principals ....Information out Teachers Counselors

49. PowerPoint - Finis