Database tables and Tableau

Database tables and Tableau

by Damion Martin -
Number of replies: 17

I'm working on integrating the Moodle database tables within my Tableau environment.  I'm having a hard time finding which tables to use when needing to figure how much time a user spends taking a course.  I can see the mdl_log table which seems it would include everything tracked within the log, but no data is returning.

Can anyone recommend the tables to use?  Currently everything that deals with time is showing in unix time.

Also, is there an updated list of all of the tables and what each table includes?

Thanks for your assistance.

Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Here is at least one place to go to get a sense of what is contained in Moodle's tables.

Moodle tracks clicks, not time.  Tracking "time" is a common question.  Unfortunately, only specialized computer software is capable of tracking time. What do you want to know?
Average of ratings: -
In reply to Rick Jerz

Re: Database tables and Tableau

by Damion Martin -
Thanks for the response. I'm looking to find out how long each user takes to complete a course. Will then figure an average time for all users for a different report that is used within our company.

The Moodle log will track when a user has launched an assigned course, the scoring and exit of the course. I'm looking to get that information into Tableau, but when going through the tables I've had a hard time getting that information.
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
As Rick mentioned, Moodle tracks clicks and when they occurred which has nothing to do with how much "time" was spent working in a course. As an example, today I logged in to my Moodle site account this morning and made a Diary entry in a course that took about thirty seconds. Over the next four hours, in another tab, I went into one other course and in a third tab I also checked a couple of admin settings, just enough activity that my Moodle site did not automatically log me out. I just now exited the Diary course tab. Do I get credit for working in that course for four hours or the thirty seconds. There is NO way to tell from any table in Moodle how much time I spent "working" on the Diary activity.

As a matter of fact, the only Moodle activity I can think of offhand that does track time to a certain extent the way you want, is the MooTyper typing practice/instruction activity, and it shows only a partial picture of time spent. In it's case, it checks what you've typed every four seconds to calculate your accuracy and words per minute rate. Let's say a student completes a lesson that contains 10 exercises and you look at their grade results. It might show they spent 22 minutes total typing time completing the 10 exercises. However, if they were in the classroom for an hour, what were they doing for those other 38 minutes? No way to tell, especially now that so many people are doing courses from home. Those same ten exercises may have been completed over the course of a whole day, instead of a face-to-face class of an hour.
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
So, it appears that you want the difference between the first course login and the last. Yep, this data is in the mdl_logstore_standard_log table. This table keeps track of the userid, courseid, and time of click. You just need to find the minimum and maximum times for each student and each course. If you want more than just the userid and courseid, you will have to join this table with others.

There are other tables that contain more, and different kinds of data.

You will have to decide if you are more efficient doing this in Tableau, which can do SQL queries, or before bringing the data into Tableau.
Average of ratings: Useful (1)
In reply to Rick Jerz

Re: Database tables and Tableau

by Damion Martin -

Not looking for the difference between two courses.  I'd like to know when a user goes to a course, launches the course and then when they complete the course.  All courses are SCORM 1.2.

I've attached a screenshot of a user completing a SCORM course and what is shown within the logs.  I want to know when the the "Sco launched" is triggered to let me know they opened the course and "Submitted SCORM status" to when they closed the course and received a score.

The mdl_log table seems to be the table to capture this information, but no data is returning.

Attachment SCORM Course.png
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
You have me confused. When you say "course" do you mean the SCORM activity? I see that in your screenshot, as an example, that the student did something even after "Submitted SCORM raw score."

Let's assume that your screenshot represents everything this student has done. Then their time in this "course" would be the difference between the top and bottom record, 12:02PM and 1:19PM, which is 77 minutes.
Average of ratings: -
In reply to Rick Jerz

Re: Database tables and Tableau

by Damion Martin -
Yes when saying course, I meant a SCORM course activity. After the SCORM course is completed it submits the score and takes them back to the "course viewed" page where the user would launch the course.

My hope was the above information or something similar would be available through the tables to populate within Tableau. I've tried exporting the logs into an excel document, but was unsuccessful when inside of Tableau to get what I was looking for.
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Okay, not a Moodle course. Instead, a SCORM activity.

Unfortunately, I don't use SCORM activities in my Moodle courses. You will first need to explore if your SCORM activity keeps track of its own time, since Moodle is only linking to it. Or, does the student click on something to complete the SCORM activity? Moodle (an most computer applications) only keep track of clicks. For example, how much time would you want to calculate if a student begins a SCORM activity, leaves for 10 hours, and then comes back to it?

To get to the data that you want, you would need to "work" on it (meaning pre-process) prior to bringing it into Tableau, or learn how to use Tableau to filter the data to what you wish to see. Remember, Moodle uses a relational database to keep track of information. Tableau can connect to relational databases. But one needs to understand relational databases, and probable SQL, to produce results.

Perhaps the presentation that I posted for Howard will help.
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Randy Thornton -
Picture of Documentation writers
You will find some of what you need over in the mdl_scorm_scoes_track table. See the following two queries which show how to use that data and what other tables are connected: https://docs.moodle.org/310/en/ad-hoc_contributed_reports#SCORM_Activity_Reports.

That table contains what for most other activities is called the user "attempt". It's the main source of the "Basic" Report tab in the Reports section of the scorm, while the interaction and objectives report will also use the related tables for _scoes, _scoes_data, and objective (if your package is tracking multiple internal scoes.) The main mdl_scorm table holds the settings for each scorm activity.

Basically, those scorm tables hold the events that the student does inside the package, while the logs hold events that mostly happen outside of the package, such as entering it and getting a score. There's a bit of overlap, to be sure, but usually it is quicker to use the data in the scorm table if you can, since it will be smaller than the log table.

All dates and times in Moodle are stored internally as unix timestamps. You will have to convert those to the format you want elsewhere, eg in Tableau.
Average of ratings: -
In reply to Randy Thornton

Re: Database tables and Tableau

by Damion Martin -
Thank you for this. I actually just came across the mdl_scorm_scoes_track table and did see that "attempt" included all interactions within a course. I'm now working in Tableau to try and return what I'm looking for.

Thank you all for your assistance. Hopefully I can get a result and will update what was done.
Average of ratings: -
In reply to Damion Martin

Re: Database tables and Tableau

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
This may be obvious to everybody except me... what's "Tableau"?
Average of ratings: -
In reply to Howard Miller

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Tableau is a "data visualization" product, perhaps a leader in its field.

I gave the following presentation at a MoodleMoot.

MoodleMoot2018 (Spain), "Using Tableau with Moodle for Visual Analytics"
Average of ratings: Useful (2)
In reply to Rick Jerz

Re: Database tables and Tableau

by AL Rachels -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Wow, Rick! I have always enjoyed your videos and especially liked the one included in your MoodleMoot2018 (Spain) presentation. Thanks for sharing.
Average of ratings: -
In reply to AL Rachels

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
Glad that you enjoyed this, Al. I want to do more in the area of "visual course analytics."  Maybe I'll see you in Spain this year!  😀
Average of ratings: -
In reply to Rick Jerz

Re: Database tables and Tableau

by Howard Miller -
Picture of Core developers Picture of Documentation writers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers
Ahhh..... well outside my responsibility.

We have a whole department who worries about that stuff. We just give them huge amounts of data to do whatever they do with it big grin
Average of ratings: -
In reply to Howard Miller

Re: Database tables and Tableau

by Rick Jerz -
Picture of Particularly helpful Moodlers Picture of Testers
I understand, Howard. Yes, this is outside of Moodle. However, what is important is that if one has access to the Moodle database, one can create all kinds of graphics (with external products.)
Average of ratings: -