We recently aired a special webinar for Colleague® by Ellucian users who are planning to or are currently in the process of migrating from a Multivalue database to Colleague SQL. This presentation reviews the steps in this process and offers suggestions and advice on how to understand SQL data structures and simplify the migration process.
Below are some highlights from the webinar. Click here to download the entire recording.
About the databases:
- A SQL database schema is more complex than UniData; there are 6,200 tables in Colleague SQL (3 times the # in UniData)
- Multivalues are normalized in UniData – all fields go into separate tables. There are two table types: unassociated Multivalue tables, and associated tables that have associated names in the U2 set)
- An ODS (operational data store) simplifies schema, flattens data & presents it in a single table, but also limits the number of columns to put multivalues in. Because the data isn’t live, users have to “refresh” whenever they need up-do-date information not stored in the ODS since the last refresh. The benefit to not using an ODS is that Informer will run reports directly from the Colleague database, meaning the data will always be current, but Informer will also connect to an ODS.
- When migrating to a SQL ODS, users may need to tweak the URL to add responsive buffering, adaptive to JDBC URL (MSSQL)
Informer Conversion Utility
- Informer offers a free migration tool, or conversion utility, to those migrating to SQL. The tool helps convert live UniData reports to live SQL (with limitations). Some specific criteria (like calculated columns) must be converted manually. The tool is a great starting point for your migration, and will usually successfully convert 50-75% of reports.
- Visit the Informer Help Center to download the Utility by navigating to “SDKs and Utilities.”
Reporting on SQL – things to note
- Computed columns = SQL functions
- Multivalue computed columns do not exist on database, so users can’t reference when reporting
- Can create links with any shared column value directly
- Can define link/join type (left/right, inner/outer) (Informer version 4.5)
- Links can include scalar values (Informer version 4.5)
- Can create savelists (Informer version 4.5 plugin)
- SQL reporting is very different – normalized, spread out
- Users have to rethink how they approach the data with SQL. Often times there may be a different, more direct, or more efficient way of doing something than how it was done with UniData.
- Users can create SQL reports with a Multivalue table format – “MV view” in Informer
In response to a request for additional support, we are offering a special SQL Migration Consulting Package to Ellucian Colleague® users through June 30th. This consulting package includes a walk-through of the entire migration process utilizing our migration utility and two hours of conversion review to identify and troubleshoot errors. The regular cost is $2,000, discounted to $1,700 (a 15% savings) if purchased by June 30th, 2015.