This case study demonstrates how to add proper activity names to data sources on Lambda Analytics.
TOPICS
- Introduction to Adding Proper Activity Names
- Copying the Activity Completion Data Source
- Adding the Proper Activity Name to the Data Source
Introduction to Adding Proper Activity Names
Data sources configured to track activity-related data have, in their natural state, an Activity Name field. However, this field simply names the basic form of activity ("Quiz," for example). This case study will cover the process of adding a derived table that allows you to include the proper name of the activity ("User Management Quiz," for example).
For the purposes of this case study, we will add a Derived Table to the Activity Completion Data Source. This derived table is not natively included with this data source, as you will require a select statement for each activity you intend to track.
Copying the Activity Completion Data Source
To begin, copy the Activity Completion Data Source from the Public folder into a private organizational folder tree.
To copy the Activity Completion Data Source:
1. In the Repository, navigate to Public > Moodle/Totara > Data Sources.
2. Right-click the Activity Completion Data Source and select Copy.
3. Navigate to the folder in your private organizational tree where you would like to copy the Data Source.
4. Right-click the folder and select Paste.
Adding the Proper Activity Name to the Data Source
Now that you have an editable copy of the Activity Completion Data Source, you can edit it to add the Derived Table.
NOTE
Before opening the Data Source Designer, ensure that you have noted the following things for your LMS activities.
To add the Derived Table to your newly copied Activity Completion Data Source, complete the following steps:
1. Right-click the Activity Completion Data Source and select Edit. The Edit Data Source page open.
2. Underneath the Data Source Design heading, click the Edit with Data Source Designer link. The Data Source Designer opens to the Display tab.
3. Navigate to the Derived Tables tab.
4. In the Query ID field, enter the name "proper_act_name"
5. In the Query field, you will need to enter a series of select statements based on the following formula:
select m.id as moduleid, m.name as moduletype, t.id as instanceid, t.name as instancename, t.course as courseid from /*LMS TABLE*/ t join mdl_modules m on m.name = ' /*NAME*/ '
union
NOTE
The two variables highlighted in this select statement are:
/*LMS TABLE*/ : In place of this variable, insert the proper LMS module table name. For example, 'mdl_lesson' or 'mdl_quiz.'
/*NAME*/ : In place of this variable, insert the name of the activity. For example, 'lesson' or 'quiz.'
6. To start, you can begin by entering the following sample Derived Table. You will likely find this series of select statements to miss some of your custom modules and name several that you do not use. Enter the following query into the Query field:
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
union
select m.id as moduleid, m.name as moduletype,
7. Click Run Query.
8. If there are table referenced in this query that do not exist in your LMS, you will receive an error. If you receive this error, simply remove the offending statement from the query and run it again.
9. If your query runs successfully, you will need to select from the returned results. Select the entire list by holding Command/Shift/CTRL and select all of the fields.
10. Click Save Table. Your new derived table will appear in the Available Objects panel with the unique derived table/calculated field identifier.
11. Navigate to the Joins tab and create the following joins:
From the Left Table, select the Course field from the mdl_course_modules table, then from the Right Table, select the courseid field from the proper_act_name derived table. Click Left Outer Join.
From the Left Table, select the Module field from the mdl_course_modules table, then from the Right Table, select the moduleid field from the proper_act_name derived table. Click Left Outer Join.
A modal window appears, notifying you that the tables have already been joined, and that a composite key will be created. Click OK.
From the Left Table, select the instance field from the proper_act_name derived table. Click Left Outer Join, then click OK.
12. Navigate to the Display tab.
13. In the Resources panel, select View As: Join Tree.
14. Expand JoinTree_1, then expand the proper_act_name table:
15. Select the instancename field and drag it into the Activity Completions set in the Sets and Items panel.
16. In the Sets and Items panel, select the newly added instancename item.
17. In the Properties panel, click Edit. The property fields of the instancename item become editable.
18. In the Label field, rename the instance item to Proper Activity Name, for presentation purposes. Then, click Save.
19. Click OK to save your chances in the Data Source Designer and return to the Edit Data Source page.
20. Under Required Information, rename the Activity Completions data source in the Name field, to avoid confusing it with the original data source.
22. To save your changes, click Submit.