Building for the Future Now

1. Building for the Future Now Sakai Lessons Learnedat Indiana UniversityDan McKee and Rob Lowden

2. Building for theFuture Now “When you’re busy cutting, shaping and polishing the gems, it is difficult to keep the ring setting in mind.”-- Anonymous

3. Building for the Future Now At Indiana University, Sakai development faces two enterprise infrastructure challenges: • Integration into our enterprise • A broader vision of the long term use and maintenanceof each Sakai module thatgoes beyond its primaryfunction. This would includeplanning for : • archive/purge processes • performance • scalability • data recovery due to user error

4. A Few Things that IU Infrastructure is Recommending to our Sakai Developers • Specialized Administrative Data • Avoidance of Parsing • Use of Human Recognizable Names

5. Specialized Administrative Data: The Issues • Archive/Purge – currently a daunting task • Lack of Time Stamped Rows • Recovery of Data due to user error is resource “Intensive” • Three Approaches: • Early-In-Development • Auditing • Hybrid • Each would require the addition Specialized Administrative Columns into each row: • Create_Date • Mod_Date • Mod_User • Mod_Type • Mod_Active

6. The Early-In-Development Approach: Overview • Recommended approach when a Sakai Module is early in its development • Specialized Administrative Columns would be integrated into the application code • Deactivate the rows instead of deleting • Queries need to include/exclude De-activated Rows accordingly • Specialized code to purge of De-activate Rows

7. The Early-In-Development Approach:Pros and Cons Pros: • Integrated – not retrofitted • Facilitate archive, purge, partitioning, auditing and/or recovery of deleted data • Impact user response time the least of the three approaches Cons: • Requires application code to populate special administrative columns • Requires application code to exclude/include deactivated rows • Requires application code to deactivate parent and child rows

8. The Audit Approach: Overview • Requires Triggers to populate the specialized administrative columns • Triggers copy data into Shadow Tables • Update or Delete copies prior tomodification • Insert goes into both Sakai Tableand its Shadow Table

9. The Audit Approach: Pros and Cons Pros: • No Modification of the existing application code • Facilitates easier, faster and more accurate Integration, Analysis and Reporting • Eases Recovery of Data lost due to use error • Provides a complete record of activity for a defined period of time Cons: • Requires the addition of shadow tables • Requires the addition of Triggers to populate Shadow Tables and Special Administrative Columns • Requires Shadow Table cleanup routine

10. The Hybrid Approach: Overview • Combination of Early in Development and Audit Approaches • Requires Shadow Tables • Shadow Tables contain only deleted rows

11. The Hybrid Approach: Pros and Cons Pros: • No Modification of the existing application code • Facilitates easier, faster and more accurate Integration, Analysis and Reporting • Eases Recovery of Data lost due to user error Cons: • Require the addition of the Specialized Administrative Columns • Optionally require the addition of Shadow Tables if copying rows prior to deletion • Requires Triggers to populate the Specialized Administrative Columns • Optionally requires a Shadow Table cleanup routine

12. Specialized Administrative Data: Business Impact • REDUCED Computing Resources- • FEWER DATA = Smaller storage, processing and memory requirements, ultimately leading to reduced ownership costs. • IMPROVED System Performance- • PURGED DATA = Reasonably sized transactional systems improve system response time and more importantly, the intangible measure of end user satisfaction. • DECREASED Administrative Overhead- • ORGANIZED DATA = Less time spent managing system growth, storage allocations, etc. Fewer data recovery operations.

13. Specialized Administrative Data: IU’s estimated Resource Usage for Archive / Purge 100 represents the currentlevel of resource usage Status Quo Audit Hybrid Early In Dev.

14. The Avoidance of Parsing: The Issues • Parsing the datum is breaking it into smaller (discrete) pieces • Sakai has several instances where data isstored in non-discrete chunks thatmust be parsed • This parsing: • adds computer processing time (slows response to user) • requires “extra” application code to be written and maintained • requires data integrators to (write) use equivalent code to the desired parsing

15. The Avoidance of Parsing: One Example • A single field in the SAKAI_EVENTS table contains strings like this:  /MessageCenter/site/11116bee-2d41-4ba4-801d- cc9f92140f6f/Message/2947064/01b25775-dec3-436b-8007- f4a2e9f3e7b6 • Each of these items, separated by a '/' character, is an individual data element that may be of interest for report generation. • This particular string contains many items: • The name of the Sakai tool • the site id\ • the message forum id • the message id

16. The Avoidance of Parsing: Business Impact • REDUCED Custom Coding Cost • FEWER STRINGS = Improved ROI, as repeated custom coding is not cost effective. Eliminating the complexity of string parsing will afford better use of staff and Open Source BI resources as less coding time is required. • IMPROVED Utilization of Computing Resources • AVOIDING STRINGS = Better normalized and modeled data design, which in turn facilitates improved performance using fewer computing resources. Fewer cycles spent parsing typically results in improved end user experiences. • BETTER Data Manageability • DISCRETE DATA = Simpler data manipulation in the event of human error, software issues or the like. Updating and repairing individual data elements is a much less time consuming then with embedded strings.

17. The Avoidanceof Parsing: IU’s EstimatedResourceSavings

18. The Use of Human RecognizableNames: The Issues • HUMANS do not cope well withgeneric, abbreviated and/orobscure strings of characters • While the table and columnnames used in Sakai are“pretty darned good”,there is a little room forimprovement

19. The Use of Human Recognizable Names:Overview • Some Naming Recommendations • Views end with V • Materialized Views end with _MV • Shadow Tables end with _X • Indexes end with _I<nn> • Columns that measure/indicate the same thing should be named the same • ID is used in 115 different tables. Not all of these columns measure/indicate the same thing • The generic use of ID has confused report writers and data integrators

20. The Use of Human Recognizable Names:An Example The diagram to the left illustrates the use of the generic ID column. Non-Sakai Developers would have a “hard time” knowing the relationship amongst the tables The diagram to the right attempts ease the ability to determine the relationship amongst the tables by re-naming the generic ID column to something that better communicates what it measures or indicates. Therefore, more humanly recognizable

21. The Use of Human Recognizable Names:Business Impact • REDUCED Development Effort • Unique Naming = Improved cross-module Integrations and reduced development effort. Uniquely identifying database elements make system relationships obvious, ultimately leading to reduced costs and increased productivity. • ENHANCED Long-term Database Viability • Adopting Standards = Enhanced long-term viability of the database model. Ostensibly, Sakai will only grow in complexity. Such Growth must be managed in a standardized approach, if reasonable database troubleshooting of performance and data integrity issues is to occur.

22. The Use ofHumanRecognizableNames:IU’s EstimatedCoding TimeNeeded forReport Writersand DataIntegratorsBefore andAfter Renaming

23. Contact: damckee@indiana.edu or rlowden@iu.edu White Paper and Presentation: http://bugs.sakaiproject.org/confluence/display/CONF09/Conference+Presentations