This case study focuses on using derived tables to show LMS roles in a data source on Lambda Analytics.
This case study will cover adding and configuring a derived table to a data source that identifies a user's Moodle or Totara role (e.g., student, editing teacher, etc.). After adding this derived table, you can include a Roles field to your views that displays the LMS role of your users.
For the purposes of this case study, we will add the Roles Derived Table to the Course Completions Data Source. To edit the data source, you will need to copy it from the Public Artifacts of the Repository to your Private Artifacts. Copying the data source required you to have the Zoola PUBLIC_READ role.
To copy the Course Completions data source for editing:
1. From the Lambda Analytics homepage, click View Existing under the Data Sources link on the top navigation bar.
2. Right-click the Course Completions data source and select Duplicate. An editable copy of the Course Completions data source now exists in your Private Artifacts.
Editing the Course Completions Data Source
To edit the Course Completions data source and configure the Roles Derived Table:
1. Right-click your newly copied Course Completions data source and select Edit to access the Data Source Designer.
2. Hover over the + icon, and click Create Derived Table.
3. In the Derived Table Name field, enter the name "roles."
4. In the Query field, you will need to add an SQL statement. Use one of the following formulas:
SELECT DISTINCT ra.userid, ctx.instanceid AS courseid, ra.roleid, if(length(r.name) > 0, r.name, r.shortname) AS role
FROM mdl_role_assignments ra
JOIN mdl_context ctx ON (ra.contextid = ctx.id and ctx.contextlevel = 50)
JOIN mdl_role r ON ra.roleid = r.id
5. Then, click Run Query. If your query runs successfully, you will need to select from the returned results. Select the entire list by holding Command, Shift, or CRTL, and selecting each field.
6. Click Create Derived Table. Your new derived table will appear in the Available Objects panel with the unique derived table or calculated field identifier.
7. Navigate to the Joins tab and create the following joins:
Courses: From the Data Structure panel, select and drag the course field from the mdl_course_completions table to the JoinTree_1 section. Then, select and drag the courseid field from Roles derived table to the empty option box beside the course field. Then, select Inner Join from the drop-down menu.
Users: From the Data Structure panel, select and drag the userid field from the mdl_course_completions table to the JoinTree_1 section. Then, select and drag the userid field from Roles derived table to the empty option box beside the userid field. Then, select Inner Join from the drop-down menu.
8. Navigate to the Data Presentation tab.
9. Under the Data Structures panel, expand JoinTree_1, then expand the Roles table.
10. Select the relevant field(s) and drag it into the appropriate JoinTree_1 set in the Sets and Items panel.
11. In the Sets and Items panel, select the newly added role item.
12. Hover over the field label, and rename it to "Role" for presentation purposes.
13. Once you are complete, hover over the Save icon, and click Save Data Source.
14. Under the Required Information heading, rename the duplicate Course Completions data source to avoid confusing it with the Public Course Completions data source. We will call this data source the "Course Completion Data Source - Roles."
15. To save your changes to the data source, click Submit.