Processing, Storing, and Viewing Use Data

1. Processing, Storing, and Viewing Use Data Tansy Matthews Electronic Resources & Libraries 2010 Austin, TX January 31, 2010

2. Agenda • Background information • Storing use data • Processing use data for storage • XML • CSV/Excel • Presenting use over time

3. Background Virtual Library of Virginia is the consortium of the nonprofit academic libraries within the Commonwealth of Virginia. (www.vivalib.org) VIVA's mission is to provide, in an equitable, cooperative and cost-effective manner, enhanced access to library and information resources for the Commonwealth of Virginia's nonprofit academic libraries serving the higher education community.

4. Background • Comprises 39 state-assisted colleges and universities (at 55 campuses) • 6 doctoral institutions and 9 4-year comprehensive colleges and universities • 24 community and 2-year branch colleges (40 campuses) • 33 of Virginia's independent (private, not-for profit) colleges and universities • Other educational and research institutions

5. Background • Challenge: Develop an efficient means of processing use data that would: • Continue providing monthly use data updates for our members, on an institutional and product level • Facilitate long term storage of data • Permit the display of data to track trends and to demonstrate the consortium’s contribution to higher education in Virginia

6. What we realized Concatenate • Keys to manipulation AND storage • Consistent formatting • Columnar data

7. Hey, Tansy! What do you mean by columnar data?

8. Background • Challenge revised: Develop an efficient means of processing use data that would: • Combine all of our disparate data reports into consistently formatted, columnar data.

9. Project COUNTER Project COUNTER helped by creating a standard format for use data reporting. http://www.projectcounter.org/

10. What we came up with • Receive data as CSV, Excel, or XML files • Import into Excel • XML • direct import and formatting • CSV/Excel • combine files using SobolSoft’sImport Multiple Text Files tool ($29.99) • Develop macros to reformat • Store in an Access database

11. What this lets us do

12. Agenda • Background information • Storing use data • Processing use data for storage • XML • CSV/Excel • Presenting use over time

13. What it looks like

14. Storing data • Access/MySQL • Space • Flexibility • Easy import/export

15. Agenda • Background information • Storing use data • Processing use data for storage • XML • CSV/Excel • Presenting use over time

16. XML 101 Extensible Markup Language similar to HTML XML data tags label the structure (not the appearance) of the document Human-readable markup language XML documents must be translated by a separate program or application (e.g. Excel) - otherwise, they’ll appear as marked up text.

17. In English please… HTML <img> Hey! That’s me! </img>

18. In English please… <animal> XML Am not! </animal>

19. In English please… <mammal species=“groundhog”> <role = “weatherforcaster”> XML Much better. </role></mammal>

20. In English please… <mammal species=“human”> <role = “weatherforcaster”> XML Taught him everything he knows. </role></mammal>

21. What does it look like? • Example file: http://www.vivalib.org/xml_sample.txt

22. Why XML? • XML facilitates the importation of data into Excel. • Allows the user to determine where the data “lands” when it’s imported – imperative for maintaining a consistent data format over time. • One step import/formatting.

23. How do I get it into Excel? • Open Excel • Create a new workbook • Select Developer>XML>Import • Example: • Download http://www.vivalib.org/sjp_07.xml • Import into Excel

24. Using XML in Excel • Select data to be imported • Assign data positions (map elements) • Change field names

25. Adding new data • Data>XML>XML Map Properties • For example: • Overwrite (http://www.vivalib.org/sjp_07_march.xml) • Append (http://www.vivalib.org/dup_07.xml)

26. Great, but what if there’s no XML? • For COUNTER compliant data: • Compile COUNTER reports into a single Excel workbook (SobolSoft tool) • COUNTER processing macros to format: http://www.vivalib.org/stats_training/counter_processing.xlsm

27. SobolSoft tool

30. Let’s try it Open counter_processing.xlsm Open http://www.vivalib.org/jr1_compiled.xlsx Select Developer>Macros Select compile_counter_journal_reports Click Run

31. Let’s try it Select month. Paste month in first empty column.

32. Data Collection Over Time • Consistent format allows data to be easily concatenated. • Charts can be generated directly from PivotTables. • Example: http://www.vivalib.org/extended_report_example.xls

33. Agenda • Background information • Storing use data • Processing use data for storage • XML • CSV/Excel • Presenting use over time

34. Creating PivotTables • Excel • Data>PivotTable and PivotChart Report • Choose source of data & data range • Select a static range • Import • Place data headings • Example file: http://www.vivalib.org/journals_example_sourcedata.xls

35. Creating PivotCharts Click on your PivotTable Go to PivotTable Tools>PivotChart Create your chart. To “detach” your chart from the PivotTable, delete the PivotTable

36. Help! • Some resources: • lib-stats listserv: "Lib-Stats hosts information useful for the analysis of usage statistics in academic (and other) libraries. Current issues are debated on the lib-stats mailing list." To sign up for the mailing list, send an email to lib-stats-request@ncl.ac.uk. • microsoft.public.excel: From Google groups. A general discussion group for Excel users. More specialized groups are also listed in Google groups. A good source for VBA macros. • OzGrid Forum: A good source for Excel tips and VBA macros.

37. Additional Information • http://www.vivalib.org/stats_training/stats_resources.html • Stats resources • Example files • Also potentially useful: • PivotTable tutorial • http://www.vivalib.org/pivot_table_examples/pivot_table_tutorial.html

38. Questions? www.vivalib.org tmatthe6@gmu.edu