Originally published April 22, 2019.
Commonly, new users of Data Hub become comfortable using Advanced Data Sets, but don’t venture beyond downloading them regularly. Brightspace Data Sets can feel daunting for those not used to working with raw data tables, but it doesn’t have to feel that way. There is so much more knowledge available in the Data Hub once you begin to use Brightspace Data Sets and combine them with other data sets.
One of the limitations of only working with Advanced Data Sets is that you are limited to only the fields included that data set. If you want to filter on another field, or add another column to the table, you are unable to do so without connecting the data to another data set.
This is where Brightspace Data Sets adds significant value – there is far more data available then in Advanced Data Sets, and you have the flexibility to come up with infinitely different structure tables for reporting. The key is that you must ‘join’ the data sets together to access this information in a legible way.
Brightspace Data Sets are based on relational database principles. Items, such as a User or a Course, have specific attributes (e.g. Name, Start Date) and also relationships to each other (e.g. a User can be enrolled in a course). For efficiency in data storage and handling, these items are represented by unique integer values throughout (generally referred to as ‘Ids’). If you have a User named ‘ABC Learner’ with a UserID of ‘12121’ you can then look for them in the Enrollments and Withdrawals Data Set using that ID to find the OrgUnitIds for the courses they have enrolled in.
A full reporting implementation using data sets would involve setting up a database and potentially combining many data sets, as needed, for your reporting requirements. However, to get you started, we have an example using Excel that demonstrates how the different data sets can be combined.
If you are ever unsure about the relationship between two data sets, please ask a question in the Brightspace Community and we’d be happy to clarify the relationship.
Example of Leveraging a Data Set Combination
Though we often treat Advanced Data Sets (ADS) and Brightspace Data Sets (BDS) as separate tools, there is no reason you cannot use both, and therefore leverage the strengths of both to meet your reporting needs. The example below uses two data sets you will need to download from your Data Hub – the ADS ‘Enrollment and Withdrawals’ and the BDS ‘Course Access’ to build a report that lists students that have been enrolled in a course more than 14 days ago but have not logged into the course yet. This way, an advisor would have a list of students that likely need some intervention. Note that we are intentionally avoiding using macros in order to keep the example simple and assuming that withdrawals are not used for currently active courses. Additionally, we assume Data Hub is already enabled and you have full access to it.
Step One: From the Data Hub, run the ‘Enrollments & Withdrawals’ Advanced Data Set, with parameters to include all currently active courses (depends on your enrollment process). Once it has generated, download it, unzip it, and open it. Also download, unzip, and open the current full ‘Course Access’ Brightspace Data Set.
Step Two: Copy the Course Access spreadsheet into the Enrollments & Withdrawals workbook.
Step Three: Highlight the columns pasted from Course Access (OrgUnitId, UserId, and DayAccessed), then, from the Data tab, click Remove Duplicates. In the Remove Duplicates dialog box that appears, uncheck the ‘DayAccessed’ column, then click OK. You will be given a summary of the number of rows removed.
Step Four: On the Home tab of the Course Access spreadsheet, insert a new column to the left of the current columns and name it “Course and User Ids”.
Enter the following formula in the second cell of the first column (A2) and duplicate it down the rows by double clicking on the square in the bottom right corner of the cell: =CONCAT(B2, “_”, C2)
This will create a combination ID of the course and the user that will make the ability to match to the Enrollments and Withdrawals data easier.
Step Five: On the Enrollments and Withdrawals spreadsheet, using a blank column on the right, title it “Course and User Ids” and enter the following formula to recreate the combination ID of OrgUnitID and UserID in this tab: =CONCAT(B2, "_", F2).
Step Six: In the next column in the same spreadsheet, enter the title “Course Accessed”. In this column we will be matching the combination ID to the list of course accesses on the other tab using the following formula: =IFNA(MATCH(O2, CourseAccess!A:A,0), "None"). This formula will look for a match on the of the “Course User ID” on the Course Access tab, and if there is a match, return the row number. If there isn’t a match, it will return N/A, which is changed to “None” by the IFNA function. Make sure to copy the formula down the column.
Step Seven: Title the next blank column to the right: “Formatted Log Date.” It will hold a version of the date and time value that Excel can understand and format, so we can then filter it. Enter the following function into the first cell of the column: =DATEVALUE(MID(N2,1,10))+TIMEVALUE(MID(N2,12,8)). Ensure to copy the formula down the column, and that the N column in the formula is the Log Date. This will convert the date/time value to the decimal value Excel prefers to use.
Step Eight: Format the Formatted Log Date column (right click on the column > Format Cells…> Date) to the date format of your choice. No need to include time because we are looking for individuals enrolled more than 14 days ago. Your new columns should look something like this:
Step Nine: Filter the Course Accessed column (Using the Sort & Filter > Filter button on the home ribbon if filters are not already enabled) to only show the “None” values. These are the individuals who have never accessed the course.
Step Ten: Filter the “Formatted Log Date” column to only show dates more than 14 days ago.
You now have a list of all the individuals who were enrolled in a course more than 2 weeks ago but have not yet logged in. Although this is a simple example, you should be able to see the potential impacts of combining data sets.
Feel free to share any combinations you found particularly useful or are struggling to achieve in the comments section. We’d love to hear from you!
Need Further Help?
Need further help working with data? Stuck on any of the above steps? Lacking one of the skill sets needed? D2L provides flexible levels of Data Solutions Consulting services that can assist with any or all steps in the process detailed in this post. If you are interested, please contact your D2L Customer Success representative.
