Adventures in Systems Integration 3 rd Party EDMS Integration with Banner

1. Adventures in Systems Integration3rd Party EDMS Integration with Banner Paul Drobny Divisional Manager of Systems Technology Student Affairs, UC Davis

2. In this Session: From Concept : Design to Delivery and Beyond Basics of EDMS Unstructured Data Using Contractor Integrator Services Supporting Distributed Customers in the Campus Data Center The Importance of Project Communications Minimizing Dependence Matrix Effects on Users Treating 2 Enterprise Systems as One Tight vs. Loose Integration – What works?

3. The Back Story Goal : Extend Banner ERP Software with Unstructured Data Buy Sungard’s ERP Extender (tightly integrated)? Buy a ‘better’ 3rd Party tool Provide the difference in the fit/gap results locally? Can Microsoft Co-exist with Oracle?

4. EDMS Basics • Photos of Documents in Tiff format • Docs stored in Microsoft Directories • MetaData stores pointers to docs, not photos • Search feature supported by MetaData attributes • Distributed scanning, centralized storage • Workflow enables office “routing” and status • Rich Security environment for docs, everything • Microsoft-centric solutions • Automatic Document Retentioning

5. As Delivered SQL Server Production Instance Only Scanners in Registrar, Fin Aid & Admissions EDMS core in UC Davis Data Center “The DC” Dedicated Microsoft Domain Integrated to Banner via Text File Extraction One “DataStore” with 230 Attributes in Metadata DB Heterogeneous Microsoft environments 3rd Party Support Contractor in Detroit

6. As Administered 3 Dept Agreement + Central Support Purchasing, Licensing, Hardware Equally Shared Staff in Dept + Data Center Ante into funding pot (by 3, DC T & M) Rotating staff (about .5 FTE Total) 30% Manager from Student Affairs Staff in Data Center (1 Architect, .5 Admin) Single Master (Mistress?) Securities Admin Shared Admin Responsibilities (Microsoft Model)

7. Issue #1Test Instance • Firewalls • Subnet Proximity • ‘Neighbors’ on your subnet • Search features supported by MetaData attributes • Microsoft Domain Controllers • Prod is Test

8. DC Architecture

9. DC Change #1

10. First Changes Duplicate Hardware for Stage/Test Instance Remove SISDocs from Riff Raff channel (first) Move Adjacent to SIS – same subnet Protect behind firewalls of SIS Dedicated Microsoft Domain Duplicate Firewalls and Domain Controllers Proliferation of Microsoft Boxes Service Level Agreements Multiply

11. Issue #2 MetaData • Document Storage Dependent on Indexing • SIS : System of Record protected by Stewardship • Shuffle text files via FTP then run Upload • Move 220 Attributes for Admissions • Move difference data, or all data? • Oracle SIS, SQL Server SISDocs

12. Issue #3 Heterogeneous Departmental Environments • One has Microsoft Domain, Exchange Services • One uses Obsolete OS – Win 2k, No Domain • Different Integrations, Fileservices • Problems present differently in all environments • Email Client “corrupts” Tiff files? • Upgrades take 4 months with current staffing

13. Solutions #2 Dump SQL Server in favor of Oracle MetaData Instances on SIS Homogenize OS support with Terminal Services Subnet Control Remains in Department Speed Upgrades by Solving Problems Once Require Secure Scan Stations – DC Exposure Eliminate Some Hardware, Add Some More!?!

14. Next Iteration – “SIS/Docs”

15. DC Change #1

16. Problem : Performance Strange Application Error Escalation License Management an Issue Application dependence Datastore to Datastore Meta Loads of Huge Data Sets MetaData on a Diet? System of Record – Autofill & Real Time Indexes Business Springs from the SIS

17. Problems of Other Sorts • SIS uses Token Access • How Many Places is a User Managed? • Microsoft Centric Apps Easily Integrate with Like • Vendor Blues • Remote Support, but what do you Pay For? • Help w/o Onsight Familiarity? • Distinguishing among internal and external problems • When Firing a Partner Becomes Necessary

18. Last Iteration DC Architecture

19. DC Change #1

20. Advantages Reduced Box Count On the Same Subnet as SIS Run Oracle instance on SIS Hardware Behind Firewalls Maintained for SIS DB to DB transfer – high speed, no text Firewalls Pass Microsoft Domain Info Leverage SIS Token through Xeda work

21. Advantages 2 Adjacent to both SIS & Xeda Xeda Integration “automagic” Programming within DB, not Inline Code Troubleshooting tools familiar to Depts SSN Encryption Oracle function used in SIS DB Refresh Sync easier to co-ordinate SIS Team Crossover Support

22. The Next Set of Issues Real Time DB Lookups for Indexers How Many Primary Keys Do you Need? Migration – Shouldn’t Hardware Match? Update MetaData Only As Necessary Triggers from SIS to SISDocs – DB/DB Xfer A Word About Web Clients & Services Virtualize the Hardware and $ave

23. Source Code & Programming “Scripts” are more prevalent than Sources Designed with critical stubs among the toolkit options (insert script here), stored in MetaData Limits your ability to modify (that might be good) It Helps to Have .Net Experience even if not programming (for Architecture Sake) APIs for Everything .. Program Control Calls

24. A Word About Teams

25. A Word About Teams

26. Loose vs Tight Coupling Characteristics Loose Less than 10 Attributes in MetaData Primary Key Only Maybe – Student ID? Use ODBC to talk to Database Use Real Time System of Record Access Create Interfaces Between Systems In Short Service Oriented Architecture Tight Configurations Similar Depend on Same DB for Data Transfer Developed in Same Tools Informed Understanding of All Business Process Dependency Matrix Managed by Vendor Installations Easy

27. The Winner : Loose Users Benefit from Purpose Built Product Choose DB Based on Staff Familiarity Workflows Interface Generally Instead of Specifically Institution Needs Integration Level Knowledge Anyway Ultimately B to B will Use SOA Design Manageable Issues: Dependence Matrix Security User ID Management?

28. Glossary • ERP – Enterprise Resource Planning (Program) : Associated with management dogma of the late 80’s, the concept of Enterprise Planning alludes to overall control of a business as a single entity, not a collection of vertical disciplines. Applied to automation in the late 90’s, the concept speaks to using computers to holistically manage the entire enterprise as if it were a single computer program, though this is generally not the case in any modern institution, certainly not institutions in Higher Education • Fit/Gap or Fit/Gap Analysis : Examination of a business process to determine which parts are critical to business success, and which parts may be expendable or done in another way. The process is used by wise institutions when defining where automated support is required, and how to communicate that activity to potential vendors and stakeholders. • Domain – In Microsoft parlance, this refers to a group of systems, users and their privileges to use system resources. The concept is at the center of their securities and access controls list. It defines a hierarchical structure and system architecture designed to support a wide range of organizations from the very small to the very large. Larger structures are hierarchal in nature. Domain Controllers are used as the authorization and authentication services so that many users can access many resources . • EDMS – Electronic Document Management Systems are generally an enterprise level conglomeration of automated services that organize and manipulate a large number of photographs of paper documents. They use database applications, standardized file formats and large disk spaces as core technology. • Metadata - The database at the center of an EDMS is referred to as metadata. This is generally a database that has millions or billions of records, but stored only a few attributes about each document like where that document is on disk space, a primary key that allows the document to be indexed to another often legacy database, and any other keys to help index the documents. • SOA – Stands for Service Oriented Architecture, but conceptually is the design of large, complicated computer programs as loosely coupled services that are able to talk to each other over the network. In this way, the data structures (entity relationship models) are passed from service to service to accomplish operations. Because the services communicate, it makes it easy to connect and re-connect the services in different ways, to more accurately model the way your institution works. It is also easier to make major changes when major business projects are pursued. • Systems Architecture – The basic layout of automation, and the standards that define how modifications to that automation are made, and what infrastructure uses. This is similar to the foundation of a house. Choosing a flexible foundation allows for the house to be modified with minimal cost to greatest benefit of the homeowner. • Systems Integration – When commercial software companies design new products, it is common practice to buy some pieces that represent working parts of the final product, then make those pieces work together by adding code where functionality is missing and using all sorts of engineering tools to make the parts work together. • Primary Key – When an entity relationship model has an attribute that is unique for each and every record in a table, the table has a primary key. Occasionally a primary key has to be the combination of 2 or more attributes which when taken in sum constitute a primary key

29. Questions or Comments? • Paul Drobny psdrobny@ucdavis.edu