Moraine Valley Community College Office of Research and

Moraine Valley Community College Office of Research and Planning Nil Shah Aaron Roe

Free Tableau Daily Enrollment Dashboard with Granular Filters M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Presentation Contents: ▫ ▫ ▫ ▫ ▫ The Enrollment Problem The Solution Tableau Public MVCCs Daily Enrollment Dashboard Table Transformations to the Final Dataset Tableau Features Automated Daily Updates Where we are Today Questions M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

About Us: ▫ MVCC is located in Palos Hills, southwest suburb of Chicago. ▫ Approximately the second largest community college in Illinois. ▫ As of Fall 2017 our enrollment is 14, 620 unduplicated headcount and 132, 282 credit hours as of census day. ▫ We use Colleague as our ERP and SAS as our main reporting tool. M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

But enough about us… M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

The Problem: • MVCC had a daily enrollment count. However, this report created more questions than it answered. If there was a drop compared to the same day in the previous year it caused chaos as leadership staff wanted to know immediately where the problem was occurring.

The Problem: Questions we received: Faculty Coordinators - Wanted to know how enrollment was trending in the classes in their areas on a daily basis. Leadership Staff - Wanted to know where exactly there was a decline in enrollment on any particular day leading up to the start of the semester and all of the details. Deans - Wanted to know which sections were not growing at a pace that would allow them to reach the minimum number to run that section. Staff - Off campus locations wanted a daily enrollment count for their particular campuses. Faculty - Wanted to know how the enrollment was progressing in their individual sections. Admissions - Needed to know which types of students were not registering at the expected pace on a daily basis in order to prioritize phone calls to newly admitted students. M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Declining Enrollments in the State of Illinois: • Why should everyone care about providing this enrollment data to all departments on campus daily?

M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

The Solution: • We needed to provide data on a daily basis to show the daily enrollment was changing at various different levels: Campus Location Course Type Prefix, Course, Section • Furthermore: ▫ A solution for staff and faculty to see exactly where the differences in enrollment existed with no extra effort. ▫ No restrictions on how many staff or faculty could access the data (we have about 2000) ▫ 5 years worth of data in order to allow staff to determine if the differences existed for only a year or if there was a longer term trend. ▫ Analysis by Seatcount, Headcount and Credit Hours. ▫ Cheap / Free (our budget for tools is under $1000 for the academic year). ▫ No IT assistance required. M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

and enough of the background… M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Where we were then: Daily Headcount Seatcount Differences Daily Seatcount

But the pivot tables weren’t always enough and we felt like this… M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Where we are today: M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

What is Tableau Public (Server)? ▫ Tableau Public is a forum that shares millions of data visualizations from all over the world for free. Examples: Viz of the Day Nil’s Profile: Vizzes Texas State: 20 Vizzes featuring a lot of IR data (where we would like to be) Search for “Institutional Research” on Tableau Public reveals dozens of IR departments that are using Tableau Public to share data M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Tableau Paid Options vs Free Stuff Paid Options Free Stuff

Tableau Public Pros and Cons @ MVCC: • Pros: Free Easy to Share (no downloading required) Can handle larger datasets (15 million rows) – we were limited to a little more than a million rows in Excel Parameters and filters allow for manipulation of data Visuals • Cons: Links to Public Forum (more on this on next slide) Dataset needs to be refreshed manually (a free solution requires more work) Tableau needs to see data in very specific way, so a lot of data prep work is needed IT could/would not assist so we had to learn to do it using our own tools Can only open Tableau Public Workbooks (Not. TWBX or. TWB files) M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Tableau Public (Server) – Constraints • Inability to restrict access by using a sign in process. Anyone with the link to the dashboard can view it. Even if links are shared only with employees, links can be forwarded. • Underlying Data ▫ As of August 2017, Tableau introduced the option to hide data

Tableau Public Desktop (Constraints) • Cannot connect to most databases (Professional Desktop can connect to almost anything. • Cannot save to local machine; can only save to Tableau Public (server)

Demonstration: MVCC Tableau Public M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

The figure representing data dashboards: Diagram Source: Southern Cross Design & Drafting M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

How we got there: M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Tools Used 1. SAS – We mostly use PROC SQL which can be done through My. SQL for free. Also R package sqldf allows the use of SQL statements through R 2. LAG and Retain Functions – These are available in R for free; also available in SQL Server (Transact-SQL) if you have it 3. Tableau Public – Our first version was created completely on Tableau Public Desktop and shared via Tableau Public Server entirely for free 4. VBA – Execute program that updates the dataset daily; available through MS Excel 5. Windows Task Scheduler – Daily Automation for Free by Scheduling VBA script *Handout contains code samples for table transformations from the very base table available in any ERP system.

The Base Table

The Counts

The Final Table

Tableau Formulas • Formulas within Tableau are used to produce the final counts from the table:

Differences • Differences between counts in different semesters are also done through Tableau Level of Detail calculations:

Tableau Parameters and Filters Parameters are used to allow users to choose Measures, Semesters, Days and Count Types: • Filters are used to allow users to choose Location / Course Type / Credit Type / Section / Course and Prefix

Automated Macro to Update Data Daily • As mentioned earlier since we are using Tableau Public and we do not have our own Tableau Server we cannot automatically connect Tableau to our data • Instead we refresh the data on a daily basis using the Windows Task Scheduler in combination with a VBS Script and manually update data from Tableau M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Where we are today: M O R A I N E V A L L E Y C O M M U N I T Y C O L L E G E

Training Users • We found “Data Champions” who were our early adaptors • One from administration and one from faculty • We brought them on during training demonstrations to show other faculty and administration how to integrate this data into their processes

Questions?

Office of Research and Planning Nil Shah Email: Shah. N 29@morainevalley. edu Phone: (708) 974 -5283 M O R A I N E V A L L E Y Aaron Roe Email: Roe. A 6@morainevalley. edu Phone: (708) 974 -5413 C O M M U N I T Y C O L L E G E