Identify datasources dependencies in dashboard provisioning

[quitterie-lcs]

Purpose

Grafana dashboarding architecture allows to cross mutliple datasources. That was at first an asset for our solution is now a drawback as we want for the same dahsboard templates, connect several identical datasources on the same input.

Proposal

The solution is to connect a single datasource, the lrs one, that contains all necessary data for dashboarding. What is computed with Grafana with the multiple datasources will be computed for the single datasources. In the existing dahsboards, we need to identify all the data dependencies to datasources other than LRS.

edx_app

• EdX Course Key
• School
• Course
• Session
• Course title
• Course start date
• Course end date
• Number of course enrollments

marsha

• Video title
• Video UUID

[wilbrdt]

Associated queries :

edx_app

EdX Course Key:

SELECT DISTINCT %(course_id)s FROM student_courseaccessrole WHERE (%(user_id)s = (%(query_user_id)s) AND %(role)s IN ("staff", "instructor"))' % { course_id: 'student_courseaccessrole.course_id', user_id: 'student_courseaccessrole.user_id', query_user_id: 'SELECT id from auth_user WHERE email=${__user.email:sqlstring}', role: 'student_courseaccessrole.role', }

School:

edX Course Key + regex

Course:

edX Course Key + regex

Session:

edX Course Key + regex

Course title:

SELECT title FROM courses_course WHERE key="${EDX_COURSE_KEY}"

Course start date:

SELECT DATE_FORMAT(start_date, "%d/%m/%Y") FROM courses_course WHERE key="${EDX_COURSE_KEY}"

Course end date:

SELECT DATE_FORMAT(end_date, "%d/%m/%Y") FROM courses_course WHERE key="${EDX_COURSE_KEY}"

Number of course enrollments:

SELECT DISTINCT COUNT(user_id) FROM student_courseenrollment WHERE (is_active=1 AND course_id="${EDX_COURSE_KEY}")

marsha

Video title, Video UUID:

SELECT 'uuid://' || id AS \"object.id.keyword\",title FROM video where id IN (${COURSE_VIDEOS_IDS:sqlstring})