This case study focuses on using a derived table to identify cohorts or audiences used for course enrolment in Lambda Analytics.
Cohorts and audiences are commonly used to enrol users in courses, using the cohort or audience sync enrolment method. To identify the cohorts or audiences that have been used to enrol learners into courses, you can use the following query to create a derived table.
To add the cohorts or audiences derived table into an existing data source:
1. From the Lambda Analytics homepage, click View Existing under Data Sources on the top navigation bar.
2. Find your desired data source, right-click the corresponding title, and select Edit to access the Edit Data Source page.
3. Underneath the Data Source Design heading, click Edit with Data Source Designer to open the Display tab of the Data Source Designer.
4. Navigate to the Derived Tables tab, and add the following SQL statement to retrieve the cohort or audience enrolment information.
Derived Table Query ID: cohort_audience_enrolments
SELECT c.id as cohortid, cm.userid as userid, e.courseid as courseid, c.name as cohort
FROM mdl_cohort c
INNER JOIN mdl_cohort_members cm ON (cm.cohortid = c.id)
INNER JOIN mdl_enrol e ON (e.customint1 = c.id)
5. In the Query ID field, enter the table name as 'cohort_audience_enrolments.'
6. In the Query field, copy and paste the aforementioned SQL statement.
7. Once you have added the SQL statement, click Run Query to validate the query.
8. Navigate to the Joins tab, and connect the 'cohort_audience_enrolments' table to both the 'mdl_course' table and the 'mdl_user' table, using inner joins for both.
After the joins are put in place, the cohort or audience information can be used in any Ad Hoc views that you would like to build.
9. Navigate to the Display tab.
10. In the Resources panel, select to View as: Join Tree.
11. Expand JoinTree_1, then expand the 'cohort_audience_enrolments' table.
12. Select the cohort field and drag it into the appropriate set in the Sets and Items panel.
13. Click OK to save your changes in the Data Source Designer and return to the Edit Data Source page.
14. Once you are complete, click Submit to save your changes to the data source.