ODBC stands for Open Database Connectivity. ODBC is a widely used standard through which various applications can connect to databases. Some examples of these applications are Microsoft Access, MySQL Workbench, and Sequel Pro.
We refer to an ODBC Connection at Leo when you want to access your own data that is stored within the Leo Database by creating/running queries to generate customized reports that are different from the UI-level Reports that are already easily available.
- In order to do this, you need to request access to the database and Leo has to authorize the connection. This is because you are often accessing live data in your Production System and could unknowingly jeopardize the system.
- Also, in order to help you protect your own User data, we allow read-only access so that you can only generate queries and obtain data from the system but you cannot overwrite any existing data. Again, this is in your best interest!
In Leo, the vast majority of reports that have resulted in a tabular format will actually show you the SQL (Structured Query Language) that generated the report. You can view this by clicking on this icon:
These queries are a good place to start in cases where a report contains most of the data you want but you may just need to add a few data points.
Remember, you are running these queries on your Production System!
We strongly suggest that you use the SQL best practices such as:
- Avoid * wildcards in order to keep results clean and for best performance. When returning a large number of results, each field multiplies the transaction cost of your query.
- Limit your results. Certain tables in the system can get quite large if your institution uses Leo for multiple years. Once you have your query set the way you want it, then you can expand/remove this limit if it is appropriate.
- Use table aliases when defining your SELECT statement. This will help you reduce keystrokes as you create your statements.
In general, it is best to start small then build on your queries. For example, if you want to add 12 fields to a report that are contained in 5 different tables, you should add a table’s worth of fields at a time to prevent from causing an incorrect query with multiple errors.
This will help you more efficiently identify the errors and the cause, saving time and effort in fixing it.
In this example, we want to see all of the faculty that teach within a 6-month window:
SELECT
# Now we define our resulting fields first name, last name, full name, and email address
user.cFname AS fName,
user.cLname AS lName,
CONCAT(user.cFname, space(1), user.cLname) AS fullName,
user.cEmail AS email
# We identify the table in which we are looking for records, in this case the Events Table
FROM dcLearningEvent AS le
# Now we join 2 additional tables: Learning Event Instructor (to determine who the faculty are) and
# for each Event and the User Table to get the faculty names and email addresses.
JOIN dcLearningEventInstructor AS faculty ON faculty.iLearningEventID=le.ID
AND faculty.bDeleted=FALSE
JOIN dcUser AS user ON user.ID=faculty.iInstructorID
AND user.bDeleted=FALSE
# Now we need to set our criteria for which results we want, in this case between 2 dates.
# We also want Group by User so we only see each faculty name once
# Finally, we order by the last name then the first name
WHERE
le.dDate BETWEEN '2018-07-01' AND '2018-12-31' #put your dates here
AND le.bDeleted=FALSE
GROUP BY user.ID
ORDER BY lName, fNameThat’s it! You may have noticed in our JOINs we typically referenced a bDeleted field. This is generally used in most tables to denote if a record is deleted or not. Make sure to include this in your JOIN and WHERE clauses so that you don’t pick up deleted data in Hour Reports.
Some of the top used tables in the system are:
- dcUser = This table lists all the Users in the system and the fields that are directly tied to their User Record.
- dcLearningEvent = This table lists all regular Events in the system.
- dcMilestone = This table lists all the Long Events in the system.
- dcCourse = This table lists all the Course Sections
- dcCourseMaster = This table lists all the Course Templates
For additional questions, please feel free to contact us via the Help Desk.