Tags

,

Screen Shot 2020-01-27 at 8.57.48 PM

Storage in Blackboard Learn need to be monitored and managed.  The choice of instructional design techniques may cause content to accumulate in unintended ways.  For example, when students submit video assignments directly to the LMS, this creates extremely large course sizes.  This in turn becomes a complication during backups, archiving, and course restoration.  Another way a course may grow beyond expectations is uploading uncompressed video files as instructional content directly to the LMS.  Instead, both for student submissions and instructional use, best practice is to utilize video systems such as Panopto, Echo360, Sharestream, Kaltura, or YouTube.  The advantage of using such systems is in streaming videos, video navigation, video quizzes, and most of all compression.  This means students pay fewer cell data fees when viewing video content and their device batteries last longer.

In order to help instructors choose the right tools for distributing video content, administrators need to monitor course storage.  Blackboard Learn offers storage reports.  These can be located under Administrator panel -> System Reports -> Disk Usage.  The report lists the course information including the type of storage.  Protected Files are student submissions such as homework.  Course Files include content uploaded by the instructor.  [ tip: the course copy system in Blackboard Learn may multiply unnecessarily Course Files if during course copy the option to copy course home folder is selected ].

In order to communicate the Disk Usage report, we are sharing a few tricks that help to export the data into Tableau.

Step 1.  Export the data into Excel

This report, or a similar one, can be exported directly into CSV by using Blackboard Data or other admin SQL access.  However, for administrators with UI access only, the export involves copy and paste.  Simply run the report, sort it by Total Size, and the top results will include the largest courses in your system.  In our scenario there were 72k courses and we used the top 10k as the course sample.  The report will display 1000 courses per page if you adjust the paging size.  Next, copy and paste the data into Excel, 1000 records at a time.   Make sure to Paste Special, and select Text only.

The headers of your spreadsheet will be:

Course ID, Course Name, Status, Course Files, Protected Files, Legacy Filesystem, Total Size.

Step 2. Convert disk storage units into a unified expression.

Since the report lists 100KB, or 50MB, as units, the column is not numerical.  It is necessary for sorting or comparison to convert the column into an equalized value.  We are going to use GB as the common unit.  In your spreadsheet, create a new column called “Total size in GB” with the following formula:

=LEFT(H2,LEN(H2)-2)/10^((MATCH(RIGHT(H2,2),{"PB","TB","GB","MB","KB"},0)-3)*3)

This is going to convert 500MB into .5 and 12GB into 12.  This means you can now sort and compare all your courses in a single data set.

Step 3. Allow for B (byte) values

A downside of the above Excel formula is that byte values generate an error (VALUE).  This is not common for the Total size column, but for Protected, Legacy, or Course Files the storage may actually be very small, totaling in bytes.  So, you may want to use IFERROR wrapper to convert such values into 0.

=IFERROR(LEFT(TRIM(D2),LEN(TRIM(D2))-2)/10^((MATCH(RIGHT(TRIM(D2),2),{"PB","TB","GB","MB","KB"},0)-3)*3), 0)

Step 4.  Separate TERM or YEAR data

If your COURSE_ID is made out of standardized fields, you can use Excel to extract TERM or YEAR data in order to provide grouping in reports.  This allows you to see how much storage is used per TERM or YEAR.

Let’s assume your COURSE_ID structure is similar to ours.  If not, make sure to adjust the formula.  We have as example: 2017.fall.comm.100.36318.  This means year, term, course_code, course_number, section_number.  To extract the term data, ex: 2017.fall, use this formula:

=IFERROR(IF(ISNUMBER(LEFT(A2, SEARCH(".",A2)-1)*1),LEFT(A2, SEARCH(".",A2)-1) & "." & MID(A2, SEARCH(".",A2) + 1, SEARCH(".",A2,SEARCH(".",A2)+1) - SEARCH(".",A2) - 1),"site.work"), "site.work")

This formula also will catch any errors and if the first number is not numerical (2017, 2018, etc) the default will be applied: site.work.  We use site.work as continuous courses or development courses.  If the COURSE_ID structure doesn’t look like it is term based, the field defaults to site.work.

Step 4.  Use the resulting spreadsheet as a data source for Tableau.

If you have not yet tried it, download the public version of Tableau and setup an account.  With the free account you can make your reports unlisted.  It is also possible your organization already owns a Tableau server license.  Inquire with the Institutional Research department.

In Tableau import the data as Microsoft Excel file.

Screen Shot 2020-01-28 at 10.35.30 AM

You can preview or download my Tableau project or use the sample spreadsheet used in the project and generated from the Disk Usage report to build your own.  The spreadsheet contains all the above formulas.