One Report, Two Data Sources: Unidata and SQL Server

1. One Report, Two Data Sources Reporting from Unidata and SQL Server in a single report PRESENTER: Jared Carter | jcarter@tesc.edu | October 11, 2010

2. Quick Facts about Thomas Edison State College Primarily an online distance education institution Approximately 18,735 students Approximately 45% of our students in the military Datatel (Unidata) Blackboard (SQL Server)

3. Challenge Certain military contracts required TESC to notify the military when a student fell behind in a course. Manually reporting from Mentors was: Labor intensive Inconsistent and confusing Resulted in potential contract violation A decision was made to use the date the student last accessed a course to indicate tardiness in the course. This information will be reported regularly to the military.

4. Solution Create a report that pulls live data from both Datatel and Blackboard Steps 1. Identify and prepare data sources, tables, and fields needed for reporting. 2. Define the selection criteria (Filter) 3. In Informer, map data source, files, and remote links. 4. Create Informer Report.

5. Identify your data sources, tables, and fields SQL Server: Tables, Views, Fields Unidata: Entities/Files, Attributes, computed columns/i-descriptors Searching through 3 rd party software, databases, and documentation is time consuming, and at times, very frustrating. Solicit help.

6. Blackboard Data Sources USERS pk1 user_id COURSE_MAIN pk1 users_pk1 course_id batch_uid COURSE_USERS crsmain_pk1 last_access_date Datatel Data Sources PERSON @ID SSN, LAST.NAME, FIRST.NAME STUDENT.COURSE.SECTION SCS.STUDENT X.SCS.SYNONYM SCS.REG.METH COURSE.SECTIONS SEC.TERM SEC.SUBJECT, SEC.COURSE.NO, SEC.START.DATE, SEC.END.DATE, SEC.NAME COURSE.SEC.FACULTY CSF.FACULTY

7. STUDENT.COURSE.SECTION STUDENTS PERSON COURSE.SECTIONS COURSE.SEC.FACULTY FACULTY PERSON USERS COURSE_MAIN COURSE_USERS This will not work because you need a single linked key and your associations must be a hierarchy.

8. STUDENT.COURSE.SECTION STUDENTS PERSON COURSE.SECTIONS COURSE.SEC.FACULTY FACULTY PERSON NEW VIEW: BEHIND_PACE_vw COURSE_USERS In SQL, create a new view that has a unique key field called ID In Unidata, create a unique computed column in the entity (file) that will be your key to the ID field in your SQL view.

9. Blackboard Data Sources BEHIND_PACE_vw BB_SCS_KEY user_id course_id batch_uid last_access_date Datatel Data Sources PERSON @ID SSN, LAST.NAME, FIRST.NAME STUDENT.COURSE.SECTION SCS.STUDENT X.SCS.BBKEY X.SCS.SYNONYM SCS.REG.METH COURSE.SECTIONS SEC.TERM SEC.SUBJECT, SEC.COURSE.NO, SEC.START.DATE, SEC.END.DATE, SEC.NAME COURSE.SEC.FACULTY CSF.FACULTY

10. Define the selection criteria (Filter) When using two data sources, only one data source can have a filter. (However, if you are using a view, you can add criteria to the view.) For this example: SCS.REG.METH = EARMY SEC.TERM = <>

11. Map Datasource Tables

12. Add Properties to your Tables

13. Add Links to your Tables

14. Add details for Remote Link

15. Create Informer Report

16. Add selection criteria

17. Add Fields

18. Add Fields Sample Report

19. Whats Next? More reports! More data sources!

20. Thank you! Any questions?

21. Supporting documentation