Case Study: Adding Custom User or Course Fields to Data Sources

This case study will cover the process of adding custom user profile fields or course profile fields to your data sources. For the purposes of this case study, we will add a derived table to the Course Enrollments Data Source.

To begin, you will need to copy the Course Enrollments Data Source from the Public Artifacts into your Private Artifacts. To copy the Course Enrollments Data Source:

1.  In the Lambda Analytics homepage, click View Existing under the Data Sources link on the top navigation bar. 

2.  Once you have found the Course Enrollments Data Source, Right-click on the corresponding title and select Duplicate. An editable copy of the Course Enrollments data source now exists in your Private Artifacts. 

Now that you have an editable copy of the Course Enrollments Data Source, you can edit it to add the derived table. 

To add the derived table to your newly copied Course Enrollments Data Source:

1.  Right-click the duplicate Course Enrollments Data Source and select Edit to access the Data Source Designer page.

2.  Hover over the + icon, and click Create Derived Table.

3.  In the Derived Table Name field, enter the name "der_custom_user_fields" or "der_custom_course_fields," depending on which custom profile field you intend to add. 

4.  In the Query field, you will need to add an SQL statement. Use one of the following formulas: 

Customer User Profile SQL

SELECT d.userid AS userid,
        MAX(CASE WHEN f.shortname = 'Field1 Short Name' THEN d.data ELSE '' END) AS FieldName1,
        MAX(CASE WHEN f.shortname = 'Field2 Short Name' THEN d.data ELSE '' END) AS FieldName2
FROM mdl_user_info_data d
JOIN mdl_user_info_field f ON d.fieldid = f.id
GROUP BY d.userid

Custom Course Profile SQL

SELECT d.courseid AS courseid,
       MAX(CASE WHEN f.shortname = 'Field1 Short Name' THEN d.data ELSE '' END) AS FieldName1,
       MAX(CASE WHEN f.shortname = 'Field2 Short Name' THEN d.data ELSE '' END) AS FieldName2
FROM mdl_course_info_data d
JOIN mdl_course_info_field f ON d.fieldid = f.id
GROUP BY d. courseid

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 one of the following joins, depending on your query:

Custom User Field: From the Data Structure panel, select and drag the ID field from the mdl_user table to the JoinTree_1 section. Then, select and drag the userid field from 'der_custom_user_fields' derived table to the empty option box beside the ID field. Then, select Left Outer Join from the drop-down menu.

Custom Course Field: From the Data Structure panel, select and drag the ID field from the mdl_course table to the JoinTree_1 section. Then, select and drag the courseid field from 'der_custom_course_fields' derived table to the empty option box beside the ID field. Then, select Left Outer Join from the drop-down menu.

8.  Navigate to the Data Presentation tab.

9.  Under the Data Structure panel, expand JoinTree_1, then expand either the 'der_custom_user_fields' or 'der_custom_course_fields' table.

10.  Select the relevant field(s) and drag it into the appropriate JoinTree_1 set under the Sets and Items panel. 

11.  Once you are complete, hover over the Save icon, and click Save Data Source.

12.  Under the Required Information heading, rename the Course Enrollments Data Source in the Name field, in order to avoid confusing it with the Public Course Enrollments Data Source. We will call this data source the "[Company Name] Course Enrollments Data Source."

13.  Once you are complete, click Submit. 

Alternative Solution

The first method of getting access to custom profile or course fields is the best approach if you have have many custom fields and you need them all as part of your Ad Hoc view.

If you only require one or two custom fields, the below alternative solution may provide slight better performance.

Replace the FieldName# place holder with the information associated with the custom fields you intend to use.

All other instructions are the same as the original custom user profile and course fields, including the requirement to Left Outer Join the mdl_user table to the 'der_custom_user_fields" or "der_course_user_fields" derived table. 

Custom User Profile SQL

SELECT u.id AS userid,
       d1.data AS FieldName1,
       d2.data AS FieldName2
FROM mdl_user u
LEFT OUT JOIN (
       mdl_user_info_data d1
       JOIN mdl_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Programs'
) ON d1.userid = u.id
LEFT OUTER JOIN (
       mdl_user_info_data d2
       JOIN mdl_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Programs1'
) ON d2.userid = u.id

Custom Course Profile SQL

SELECT u.id AS userid,
       d1.data AS FieldName1,
       d2.data AS FieldName2
FROM mdl_user u
LEFT OUTER JOIN (
       mdl_course_info_data d1
       JOIN mdl_course_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'Programs'
) ON d1. userid = u.id
LEFT OUTER JOIN (
       mdl_course_info_data d2
       JOIN mdl_course_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'Programs1' 
) ON d2.userid = u.id