-: Okay, it seems like we've got some attendees now.
Welcome everybody,
thank you for joining us today.
Today's webinar focuses on connecting to APIs.
The webinar is presented by Plaid Analytics.
And we help educational institutions gain insight
from their strategic enrollment management data.
We provide data and analytics solutions,
business process improvement, training and workshops.
Plaid is a proud partner of Tableau, Safe Software,
both of which will be shown in today's presentation
and Denodo as well.
We're based in Vancouver, Canada
and recently had our fifth birthday as a company
and have helped institutions across North America
during this time.
Our presenters today include me, I'm Andrew Drinkwater.
I'm a co-founder and president at Plaid Analytics
and my background is entirely in higher education.
So I started my career as an academic advisor,
helping students through an interesting transition
of closing down a university, merging it into another one
and changing the program top to bottom.
At the time I got my start in data
by trying to solve some issues
where suddenly we had a whole bunch of students
and nobody to teach them,
a kind of by-product of both market interest in the program,
but also layoffs during the merger and acquisition process.
So I stumbled my way into data then to try
and help students be able to get the right courses
when they need them
and help us figure out who we needed to hire
as we ramped up the new operations in the new university.
Most recently prior to Plaid,
I was responsible for tuition analytics
and data warehousing related to admissions
at the University of British Columbia
in Vancouver, Canada.
And I will turn it over to my colleague Pat now
to introduce himself.
-: Good morning and good afternoon,
depending on where you are.
My background is also pretty much exclusively
in higher education.
I started as a systems administrator
and in information technology services
for a academic unit at Simon Fraser University,
many, many moons ago.
But also spent 10 years in our registrar's office
and student services
where I was responsible for business data
and policy analysis,
which including doing things like building data warehouses
for strategic enrollment management
and a variety of other data projects
before launching Plaid with Andrew.
-: Thanks very much Pat.
Okay, so in terms of what we're going to cover today,
we are going to start with talking about
from a conceptual perspective, what is an API
and how do I connect to it.
And then we'll get into some live examples.
So the first one that we'll look at
is using Tableau's built in web data connector
and focusing on geothermal wells in California.
And we'll talk a little bit more about
why we chose that data set
when we get to the example.
The second one,
we'll focus on the Canvas Learning Management System
and the API for that.
And we're going to demonstrate how to use that
using two different tools, one of which is FME
and one of which is Python.
And then our third example will be
the US Department of Education's College Scorecard
and we'll connect to this using FME
and show some examples from that as well.
And then questions are welcome through the chat at any time,
but we'll have some time at the end for those as well.
So an API, for those who've never heard of it
is known as an application programming interface.
And essentially this is an intermediary
between two different systems
that allow them to talk to each other in a secure way.
So you can think of this a little bit like a contract
between something that provides information
and something that needs information, like a user.
The big advantage here
is you don't have to open up your database systems
to outside access.
This provides a much better foundation
for security control and authentication.
So there's a few different kinds of APIs that exist
the most common one, these days anyways,
is called REST or Representational State Transfer.
And this is essentially an architectural style
with a series of constraints
that is built for web services.
So basically any app that you use on your phone
and most modern web services will rely on an API like this
to communicate between the data systems
and the application that you're using.
There's another standard that you may come across called
the SOAP or Simple Object Access Protocol.
This is a little bit less common in the market,
but they do exist there.
Today we're focusing on REST APIs.
So how does this work at a conceptual level?
If you look at the diagram on the right,
there is a client which we're picturing as a laptop there
and there's a server,
which we're picturing as a cloud on the right.
And somehow they have to talk to each other.
And remember the client,
does not have direct access to that server.
So they can't just go in
and run a SQL query against whatever information
that server is storing.
It doesn't quite work that way.
Instead, using an API,
the client sends a request to that server using one
of between four and six,
depending on the service methods that you see here.
So the two most common are get and post.
Get means retrieve information,
post means send it or write it.
Typically the format that the client either sends
the request in or receives a response in
is in what is known as JSON or JavaScript Object Notation.
And the communication is done over HTTP,
Hypertext Transfer Protocol.
The same thing that your web browser runs on.
There are different response formats.
You could receive your response in HTML
or XML or Python or PHP,
but the most common ones these days is JSON.
Oftentimes you'll need to provide some header information,
which we'll show you a couple examples of later today.
But header information
usually includes things like authorization tokens,
may include things like pagination or filters.
A little bit more detail
on what those different methods are.
So as mentioned, get and post are the most common.
So get allows you to request information from a service
or read data, post allows to send it or write it.
Not every service will allow you to post information to it.
So for example,
today we'll show you how to post to the Canvas API
because we're allowed to write to that one
in the demo environment that we're using anyways,
and you can also get information from that.
Conversely, the College Scorecard
run by the Department of Education,
only allows you to use get.
You're not allowed to post information back
to the College Scorecard.
Unless you work for the Department of Education,
then maybe it's a little different.
There's a few other methods as well, they're less common.
So put is another way of writing data to a service
and in some ways it's a little bit more robust.
So if you send the same request multiple times,
you're always going to get the same result.
This is not always the case with post,
but post is much more common.
Head includes headers and parameter information.
Delete allows you to delete data from a service.
Many services offer this,
but you should use it with a little bit of caution.
And patch is like an update.
Most modern APIs leverage what are called endpoints.
And so these are essentially communication channels.
So if we think of a learning management system like Canvas,
you could think of things like accounts and users
and courses and announcements for courses,
those kinds of things.
In a way, if you think of themes
related to what data might be stored in that service,
those are good starting points
for what end points may exist.
Each provider structures them a little bit differently.
And so you want to look through their documentation
in order to understand what is available
so that you can retrieve data from that particular endpoint.
So, sorry that's the same text.
My apologies for that.
The example that's on the right though
is an example of a very simple response
that comes from the Canvas Learning Management System API.
So what you see on the right here is a very simple JSON file
with four pieces of information within it.
So we have an ID and this is typically auto-generated.
So it'd be like a row number, essentially.
We have a name.
So Canvas has a default term built in
that happens to start in 2012, June 1st
at six in the morning or sorry,
not six in the morning,
six hours off of Greenwich Mean Time.
And there is no defined end date for this default term.
And so we have four different fields or columns
for our data, but they're arranged as plain text
as you can see here.
We'll show some other examples of this coming up as well.
And we use different systems
to be able to interpret that kind of information
and turn it into data that we can use in a tool like Tableau
for visual analysis.
There's a number of different API systems
that are available in higher education,
well more than I could hope to name,
but here's a few examples.
So ones that we see often are Workday or Salesforce.
You can also connect with SharePoint
and Teams using Microsoft's Graph API among others.
Raiser's Edge for those in fundraising,
Canvas that we're going to show today
and then if you work in facilities and building management,
things like iAuditor and EnergyCap
help you manage your physical plant infrastructure
on campus.
There are many, many tools
that you can use to connect to an API.
One of the advantages of an API
is it's a relatively open standard that anybody can use.
So there's hundreds of tools that can work with it.
Here's a small list.
The ones that we're going to show today
are Tableau's Web Data Connector, Python and FME.
So I think most of you heard about this
through Appalachian State's Analytics Day,
which was phenomenal.
If you didn't have a chance
you should check out the recording.
And as such, I assume you already know what Tableau is,
so I'm going to fly through this.
But just for a sake of argument,
Tableau is a visual analysis platform.
There's a number of different products that live within it.
So Tableau Desktop is where many people author dashboards
and visual analytics.
Tableau Prep helps people prepare their data
while staying in the flow of analysis.
It sings and dances really nicely with Tableau Desktop.
You can move back and forth seamlessly
to be able to you know, embark on your analysis
and then reshape your data
and then continue on your analysis.
It's really good for flow.
They also have the ability to share your analytics
with other users.
So there's a few different ways you can do this.
Tableau Online is hosted by Tableau.
Tableau's server is hosted by your institution
or in the public cloud or similar.
And these allow you
to securely share your analytics with others.
The other sharing product that's not shown here
is Tableau Public.
So this is open to the world.
It is inherently not secure from that perspective,
so don't put confidential data on it.
But it's a great way
to share your university's information with the world,
such as with a fact book
or other key information about your institution.
We are also going to show the FME platform.
And I imagine many of you have not heard of this previously,
so I'll talk a little bit more about this as we go.
FME is made by Safe Software,
which is headquartered in Vancouver, Canada
and their goal is to help you maximize
the value of your data.
So as in many organizations, typically universities
and colleges have many different silos where data exists,
but they have a tough time communicating with each other.
FME is one way to solve this problem.
So it's a data integration tool
and consists of a number of different components
that can help you with this challenge.
The key products within the platform,
similar to how Tableau works,
they've got a desktop product where you build
and run integration workflows.
They've got a server product, where you can automate those
and schedule them to run.
So examples of that might be,
we want the script that populates our data warehouse
to run every morning at four o'clock
and then we want an automation to pay attention for students
who apply and requested assistance filling in
the financial aid application.
Maybe we want to trigger an automation
that notifies our advisors through Microsoft Teams or Slack
to say, hey by the way,
this student needs a hand, can you reach out to them?
FME Cloud is the same idea as Server,
but it's hosted by Safe Software
instead of your institution.
We mentioned Safe here as well
in part for the example that we're going to show today,
but they also have a very compelling education discount
where you can get an enterprise license
for a very reasonable price point.
So if you're interested in learning more about that,
let us know, we'd be happy to reach out to you
with more information.
Okay, so our first example is a very simple one.
What we're going to look at here
is Tableau's Web Data Connector.
So web data connector
is a way of connecting to information services
that exist on the web.
Okay, so Tableau has a fair amount of documentation
on their website about the web data connector.
You can read that kind of when it suits.
There's also a community portal that's linked.
And so on the community portal,
there are a number of different web data connectors
that you can see here.
They do have different versions
and they work with different versions of Tableau Desktop.
So there's a little bit of trial and error to make sure that
the version of Tableau Desktop that you are using
corresponds with the version of the web data connector
that you want to use.
So part of why we chose the geothermal wells in California
as an example is because we have vetted it
and we know that it works
with the latest versions of Tableau.
But there are other examples on here that do as well.
So if you click on this link for the geothermal wells,
it directs you to a specific URL,
which is what we're going to use in Tableau.
To connect to a web data connector,
for those who've never used it,
it'll typically be under to a server under the more menu.
And if you've never used it before,
it'll be located here towards the top right.
Because I've used it,
it's located in the quick links on the left as well.
So I'll click web data connector
and I will paste in that URL that we saw before.
And once I've done that, then I can hit enter
and I can click the green button
and Tableau is going to execute a query using an API
over the web.
And so this has populated the California geothermal wells.
And if I hit update now, it'll take a second
to generate the extract for me,
but now I have the information.
And so if I go to my worksheet,
it will load the information.
And sometimes web data sources have varying performance,
which we will undoubtedly see today.
So, while that's thinking about running,
I'll show you how the data ultimately looked in Tableau.
So for example,
we've got a map of well locations
in the State of California.
And in particular, this data set has both latitude
and longitude provided at a highly granular level.
So this would be much more granular than say
if we use city with Tableau's generated latitude
and longitude.
So here we can see the different counties in gray
and then the different types of wells in multiple colors,
as you can see there.
But this works essentially the same
as any other Tableau data source.
So if I were to make a new worksheet
and I double clicked on latitude and longitude,
I get a basic map.
And then if I put well type on the color,
this gets us really close to what I had built previously.
And last but not least,
a feature that was released a couple versions ago
in Tableau, but is relatively new,
is the concept of map layers.
So if I wanted to put counties on this,
I can drag the county name up towards the top left
of the sheet
and then this pop-up will appear for add a marks layer.
If I drop county onto there,
I now get a second set of marks for county,
which I can change to a field map, for example.
Now, inevitably for me I tend to get this order backwards.
So I actually wanted
the circles to appear above the counties.
So the easiest way to do that
is to drag county below a latitude.
And then that flips the order of them
and now my circles are appearing above the gray background.
And so this is the basic idea
of how I would connect to data
if I was using a Tableau Web Data Connector.
As shown on the web here,
there's a number of different examples
that the community has built,
but there's only a very small number within here
for education.
And so there exists an opportunity
for creating new web data connectors
that would help people in higher education
be able to connect to more web services.
So for those who are interested in building this,
I personally think there's an opportunity here
that could help.
The web data connectors are built in JavaScript.
So you need to know a little bit about JavaScript
in order to create it, but by all accounts,
it's not a terribly difficult paradigm to use.
So that's the,
if there's a connector available
through the web data connector,
that's one of the easiest ways to get data into Tableau.
Our next few examples show other ways using FME and Python,
that we can get the data out of particular services
and into Tableau.
But they involve creating a couple of steps ourselves,
either visually or with code.
So we'll show those next
and I'll turn it over to my colleague Pat,
to talk about Canvas.
-: Okay, so Canvas is a learning management system
used by a variety of higher education institutions.
So it's really about having courses,
instructors can put course materials into Canvas.
Conversely, students can consume materials through Canvas.
And what we have done is we just have a Canvas instance
running in a locally on my machine in Docker,
which is a system that allows you
to run container applications
or basically smaller groups of applications
blocked off from one another on your system
and eventually if you want to in cloud systems.
So we have, once it decides it wants to load,
we have a local Canvas with a number of courses.
If it'll go in it.
Again, once the sites load, there we go.
The data for this Canvas system comes from a banner
that we, a banner system student information system
filled with a fake data that we have created
for Plaid University.
So for those of you familiar with Ellucian's Banner SIS,
some of the tables that you're about to see here
may look familiar.
But what you're looking at is a workflow
in Safe Software's FME product to get data out of banner
and ultimately writing it to an API,
in this case, Canvases API.
And there's a few different pieces here,
but essentially what we're doing is we're creating terms
that's the component down here
and we are creating courses within Canvas
based on the courses
that exist within our demo SIS environment.
What's important when you're trying to write data
into an API, is understanding how the API wants data
to be provided to it.
So again, oftentimes this is in a JSON format,
but in other cases it can be in XML or plain text
or even binary files,
occasionally you can upload to APIs.
What we're doing here,
because we're doing a bulk data load into Canvas,
we're actually pulling data out of Canvas first
and comparing that to the courses
that we've got in our SIS to see whether
or not we have already created the courses in Canvas.
If we have, we don't,
there's nothing further that we need to do,
if there isn't, we need to create the data into Canvas.
So I'm going to, I've pre-run a part of this.
So at the moment, I will zoom in here,
we've got 1,955 courses that we are about to try
and create in Canvas.
And this may take a few minutes,
so we won't necessarily let the entire thing run.
And what we've done here is we've parameterized
where our Canvas server is as well as the API key.
Andrew mentioned authorization
that's often required for APIs.
So in Canvas's case
and in many other tools do this as well,
is they allow you to create a special key,
like a password that allows you
to have access to the system.
And of course you want to take all best practices
with this key, keep it secure, rotate it occasionally,
those sorts of things.
In this particular case,
because I'm destroying everything pretty much
right after the webinar,
I'm not particularly worried about this key
being out in the open.
So assuming that I have done this all right,
I am going to start writing data into Banner
and while it's doing that,
let's just take a little bit of a look at what it takes
to put data into the correct format here.
So in FME,
we've got this particular transformer called an HTTPCaller,
which basically allows us to say,
we want to do something with a endpoint
over the HTTP protocol.
We can indicate what our URL is,
what kind of HTTP method we want to use,
in this case we're using post as Andrew talked about.
We've got our API key
that we need to pass in with our request
to tell it that we're allowed to do these things.
And finally we have some JSON that we're putting together
that tells Canvas what it is that we want to do.
We have a name for our course,
we're putting together a course code,
in which case, in our case comes from a subject code
and a course number.
We have a particular term that that course exists within.
And then we have a special code that exists between Canvas
and our SIS, so that they can use the same key
so that we know which course is which.
This course is not an open enrollment course
and students aren't allowed to self enroll in it,
they have to enroll through our SIS.
Now, how do I know
that this is how we need to structure this data?
Many APIs,
particularly those publicly accessible,
will have online information in terms of what they expect,
both in terms of data that they will provide back to us,
but if we want to write to the API,
what the data needs to look like.
So in this case, this is Canvas's REST API documentation.
And I'm looking at courses here
because this is the particular end point that we're using.
And so they have a function here to create a new course,
so I'm just going to click on that.
And when I do that, it gives me the URL.
In this case, I need an account ID.
Canvas allows us to host multiple different accounts.
This could be different institutions
that are sharing a Canvas server,
it could be different faculties or colleges
within an institution, et cetera.
And it also tells us what request parameters
it wants to see.
So our course name
and it, Canvases API also tells us okay,
if you don't provide this data,
here's what we're going to do instead,
we're just going to call it unnamed course.
It doesn't necessarily need to have a course code.
We don't need to tell it when it starts or end,
none of these are listed as requested or sorry, required,
but we can provide this information if we want.
So back in FME,
as we're writing all of these new courses into Canvas,
it's translating all of those different components
into the JSON as we've just seen on the webpage.
And so Canvas is quite happily creating these new courses.
All of these new courses
that I'm creating right now are in the fall 2021 semester.
And when we looked at Canvas,
we can see those new courses appearing for us.
Okay so that's helping us get data into Canvas.
Now, how do we get data out of Canvas with FME?
So I'm just going to stop this.
We don't need that running in the background anymore.
We're going to switch over to a different workspace
that we have here
that is intended for reading data out of Canvas.
And it's pretty similar here.
We've got a HTTPCaller to get data out of Canvas.
And when Canvas, I'm just going to run this too,
when Canvas gives us data back, it gives it to us
in this JSON format,
which is not particularly human readable
and while Tableau can read some JSON files,
it's not its favorite format to read in.
One of the things that Andrew mentioned
is that many APIs have what's called pagination.
So if you say,
I would like to know what courses as we're doing here,
what courses are in the system.
It's going to say here's the first 10
and Canvas in particular will give you 10 back at a time.
And then it will say if you want the next 10,
here's a URL to use to get that next set of 10.
And then when you go and get that next set, it'll say okay,
if you want 20 to 30, here's another URL.
If you want 30 to 40, here's another URL.
And so essentially you have to create a loop
to get all of the data back.
And what we've done here is we've created a color here
or a custom transformer in FME
that we can use again and again and again,
that does that work of figuring out okay,
is there another page worth of data that I have to get?
And then sending that back as part of the loop here
to get more data.
So ultimately,
depending on exactly how many courses got written,
we should be...
Ah, there we go.
So we had 986 courses that ultimately got written
and it took us 99 different calls to Canvas
to get those 986 courses back.
One of the nice things about a visual tool like FME is that
we can write that logic once
and it'll use it again and again.
The second caller here,
we've gotten information on our terms.
And then we take the JSON
that is returned to us by Canvas,
which looks something like this.
It's a very long string of JSON.
It's got individual components with FME.
We can use JSONFragmenter
to turn those into individual rows of data.
So we can see we have an unpublished course here,
for you know, this particular course.
And I have apparently messed up the code portion.
Always the fun of demos,
when you do these things on the fly,
there is inevitably something that you miss.
Ah course code, okay.
So sorry, I'm just going to fix this on the fly
so we can get better.
Okay, so we've got our courses.
When Canvas returns the data to us,
it just tells us this is a term ID.
So we've also got information on the terms
that we're coming back separately,
so we have an actual term name to go with it.
Once you've got the data out of Canvas
and into FME, you can write it to many different formats.
You can write it directly to Tableau's hyper format
if you wish, but in our case,
we are just going to write it as a CSV file,
so we can very quickly bring that into Tableau.
So there's our file that we just wrote.
I'm going to drag my CSV into Tableau
and we can see we've got all this information
that has come out of Canvas just now.
And we can see how many different courses we have
in different terms.
Oops, I think that's cached from...
There we go, okay.
There was in fact some caching going on there
because I had run this earlier
just to make sure it all worked.
So now we've got 106 fall courses that we've created
because I stopped it part way.
And then we have 880 summer courses that already existed.
So that's one way we can do this.
Let's look at another.
And what we're going to use is Python.
And one of the things about APIs,
particularly relatively well used APIs,
is that different tools will be created
in different programming languages
that are specific to that particular API.
So in this case,
the University of Central Florida
has created a Python module
that is specific to the Canvas API
and has a whole bunch of functionality
already built into it,
that takes care of the things like the pagination,
sending the API key in the proper format,
all of these components for us.
And so we can leverage that.
So I'm just going to run my Python here.
So here we're basically saying okay,
we want to use this Canvas module.
Here's the URL that we're going to use,
again, this is my local Canvas.
Here's the API key, the password that we're going to use.
It's telling me to use a secure format,
which I'm not again because it's local and it's a demo
and I can get information on the different accounts.
Again, the multi-tendency that Canvas has.
And so I want to look for the account ID
for my Plaid Analytics account.
Finally, what I'm going to do is I'm going to get all
of the courses associated with this account.
So that's at 986 ish courses and we'll run that.
And then same thing that I did in FME,
I'm going to write this all out to a CSV file.
And again,
this will just take a minute to run
as it does those 99 different calls
to get information out of Canvas.
It does take a second to run.
So the thing about modules like this
that have been written by others
in this case, the University of Central Florida,
it's fantastic that we can leverage
the work that others have put into this
and that they're allowing us to leverage that
by making this open source.
But it's not perfect.
When somebody like the University of Central Florida
builds these components,
they're building it for their particular needs.
And there may be some other cases,
some other data that you need to get out of the API
that maybe they don't need,
so they haven't built that functionality yet.
One way to do that
is to create that functionality within the module
and then ask for it to be included within the Python module.
In our particular case,
what we wanted to do was get all the courses
for a particular account.
This wasn't built into the Python modules,
so we had to read the code
and figure out how to leverage
what they had already written into getting us
that information.
Okay, and that's all written.
So now we have another course's CSV here telling us
that the data's from Python.
And we haven't done quite as much data cleanup
on the Python side here,
so there's a few additional...
Oops, I went one step too far.
There we go.
So for example, in this case,
I grabbed the enrollment term ID
it didn't bring in the term names,
but we can see that we've got our 880 summer courses
and our 106 fall courses,
as well as some, basically we've dumped all
the information out of Canvas.
-: Our next series of steps
is going to focus on the College Scorecard
by the US Department of Education.
And for this example,
we will also use FME to connect to it.
And I will walk you through a couple of things
related to that.
Okay, so where we're headed here
is a really simple visualization,
which you can access on Tableau Public.
And I will post in the chat momentarily,
the relevant links to all of this.
But this was the basic idea.
I thought well, you know,
the College Scorecard has a bunch of information
on first-generation students,
where is it most common
for first-generation students to exist?
And so, as an example,
the EDP school in New York
has 96% first-generation students,
which was fascinatingly high to me.
Conversely, if we go to the low end of the spectrum,
I think that's probably not quite the lowest,
but 9% at Oberlin College in Ohio.
And if we look at the map,
we see that there's a huge variety,
but as a general, very basic trend,
a little bit further south,
the more likely we find higher proportions
of first-generation students at institutions.
So how did we get this information
and how do we get it into Tableau?
And of course, by now I'm sure you know,
the answer is we used an API
in order to get this information.
So let me just post this bit to the chat
so those who want to follow along have it.
I think my Tableau link was wrong there, sorry.
Okay, so on we go.
So we're going to make use of the College Scorecard's API.
And you can find this information,
the links are now in the chat on the USDOE website.
The API in my view is not quite as well documented
as what we saw from Canvas for example,
but the information is available and it is workable.
So it takes a little bit of practice
to get used to this one,
but it's got a wealth of information
that can be used in really interesting ways.
I will state for the record
that I am a rank amateur with this data,
it's very new to me.
And I would highly encourage you
to follow Higher Ed Data Stories, Jon Boeckenstedt,
who's at Oregon State University,
who does really interesting work with some of this data
among many others in the US who do really interesting work.
But how this works essentially is we start with a URL.
So this is the starting point for what we're going to use
when we use that HTTPCaller
and FME to pull data from this particular service.
And then the end point,
I'll say quote unquote, is V1/schools.
So this API is structured a little differently than others
but this is where basically all the information lives.
So we start with that.
In order to use this API,
you need to register for a key,
which is a really easy process.
You put in your email address, you hit enter,
you get an email about 30 seconds later with a key
that you can use.
So quick, easy process.
There is a data dictionary in particular,
that's quite useful when you're looking at the API.
So I've already downloaded this, it's an Excel format.
And what it looks like is this one.
Let me just clear away some of my filters
and make this big enough that a human can see it.
Okay, so within the data dictionary,
there's a number of different tabs in the Excel sheet,
some of which are more useful than others for our purposes.
So for the purpose of what we're showing today,
the institutional data dictionary has a bunch of information
that we need.
So this is arranged
based on the concepts of the data elements
and then within them certain values.
So by way of example, at the very top,
we have an ID field that is available for every institution.
And as far as I can tell, this is a unique ID
but there's also a federal school code
which should be unique
and the eight digit and six digit OPE ID.
So between all of these different ID fields,
we can establish a unique institution at the very least.
Some of the other information,
in the data dictionary is more what I would call a crosswalk
or a lookup table.
So for example the FIPS code for state
that I've highlighted here, sorry off by one row,
has Alabama as first, Alaska as second
and so on through the alphabet towards the end.
So these might be useful in a tool like Tableau,
so that rather than showing this value of one for Alabama,
we could actually display the state name,
which could be quite helpful as we go.
The other pieces that are somewhat useful to us,
both the dev category and developer friendly name
that we see here,
these are used mostly in the API.
So for example, under the category of school,
we can find the name, which is the school name.
And we're going to use that momentarily.
So this is a basic idea.
There's a bunch of other information in here
that's valuable as well,
but that's the high level of what we're going to use today.
So now let's see how this works.
So I'm going to show you first a really simple workspace
so that we can attempt to get some information live
from this.
And then I'll show you a little bit more complicated one
just for your reference.
So the way that it works in FME is visual drag and drop.
So the basic concept for most workspaces,
I'll just show this at the bottom here,
just so that we have kind of a conceptual underpinning
of how this works.
Most workspaces start with what's called a reader.
So for example,
we could search for Excel
and we could pick the Microsoft Excel reader
and we could tell it where to...
The basic idea is that we would have a reader
and then we'd have flow
and then we'd have some transformers, maybe multiples
and then we would have a writer.
That's the basic flow.
And I'll skip connecting to that Excel file for now.
Okay, so from here,
let's walk through the workspace that I actually built.
So HTTPCaller is technically a transformer
rather than a reader,
but for sake of argument, we could consider that to be the,
kind of the key starting point where we get our data from.
We're reading it from the web,
rather than from a local file or a database.
So FME has a transformer that's called a Creator.
If we wanted to add that,
we just start typing the word and we could add another one.
And then what we're going to do from that,
so Creator creates as many records as you want.
So we're inventing data here.
And in this case, I want to create a single record
or a single row of data,
just so I have a starting point for my workflow.
We then have an attribute manager,
which allows us to populate certain values.
So we use the base of the API that we saw on the website
and then we use the end point
that we also saw on that website
and then we're going to turn those into a URL
by essentially concatenating them.
We then have our API key,
which I'm storing in a parameter and then our JSON query.
So this tells us that within the JSON file,
the pieces that we really care about live within results,
which I'll show you in a minute.
It'll make more sense when you see
the structure of the file.
But take my word for it,
that's a good starting point.
We can then also specify parameters.
So I talked about these earlier in the presentation
as header items.
So for example, we're going to say,
bring in page number zero.
And in this case, all the pages start at zero
and they count their way upwards.
And then I want to bring in 50 results per page.
Most APIs have a limit on how many results per page
you can bring in.
I have not found it for the DOE,
but I imagine it's somewhere between 100
and 500 at the most.
My experience in putting together this webinar,
is that anything much more than 50 records per page
results in very hit and miss performance.
So I found when I tried a larger number,
I think it was either 100 or 250,
one out of about four or so pages would return an error,
which I think was basically the service saying,
you're requesting too much stuff all at once.
And if I repeated it, it would work just fine,
but it was very sporadic.
So what I would suggest
is that having a small number per page
is actually optimal for this
and then looping through your requests
with a little bit of a delay,
which I'll show you in the other workspace.
So those are kind of the key parameters we've set up
and then we use the HTTPColor.
And so, I copy and pasted the URL.
This is the same thing as the variable that I just created,
but this is the URL that you get from the website.
And then for our header information,
sorry, for our query string parameters,
the way that this API works is it requests the API key
as part of the query string.
So essentially what's happening here is
after the word school is in the URL,
we're putting in a question mark
and then our API key equals that long value
that they sent me by email.
And then we're going to put an ampersand
and we're going to say how many records per page.
And then we're going to put in an ampersand
and say, which page are we looking at.
The order of these doesn't matter,
but they all should be there.
Okay and we haven't specified any other filters in here.
So this means
we're going to get every element of the data available
on the particular page that we're looking at.
So page zero in this case.
So let's run this and see what we get back.
So I'm going to tell it to run between my Creator
and my HTTPColor.
And that'll take almost no time and return.
When I look at what comes back from the HTTPColor,
I can see you know, my original variables
that I had created in the attribute manager as an example,
and then I can see a couple of new ones that came in.
So as Pat showed here, we've got this response body
of a really long string of metadata and then a status code.
So depending on the service you're using
the status codes may be a little bit different.
But 200 generally is a good sign,
it means it was successful
and other numbers may be a bad sign,
it means they were not successful.
So as we saw before,
we have all this well information
in a not very human readable form,
the way it comes back from the web service.
FME does have a JSON formatter transformer
to make this a little bit more human friendly.
So, as an example if I add that,
I can tell it that I want the response body
and I'll leave all the other defaults
and then let me run this.
Okay and then if I looked at my formatted field,
now this is, I don't know if I want to go
as far as human readable,
but it's much more human readable than what we had before.
So what we can see is in the metadata section,
we got page number zero
and there's a total of 6,806 institutions in this data set.
And we're bringing in 50 of them per page.
So ballpark, we're going to need 100 to 150 iterations of this
in order to get all of them,
if we wanted to loop our way through it.
What we can see is that most of the information we need
lives within results.
And then within latest
or if you query this a different way, a particular year,
depending on what you're looking for.
So we've got information on the student
and then we've got information on aid, et cetera.
I'll show this in a different window,
'cause it's a bit easier to see in Atom.
So here is an example of the very first record.
So same thing that we just downloaded.
So this tool is called Atom.
And what I'm going to do here
is just I collapsed a few things.
So metadata just is information on the page.
Results is much more interesting stuff.
So within latest for example, we have student
and we have aid and we have cost and so on.
There we go, student, aid, costs, earnings,
completion, repayment, academics and admissions.
And then we have a school one as well that we make use of
and a little bit off the screen is location.
So let's see what that looks like in terms of our data.
So now just as Pat did,
we're going to use what is called the JSONFragmenter
to split out the relevant pieces here.
And the ones that are highlighted in blue
are the ones that I've gone through and actually vetted.
And the ones that are in gray are my homework assignments
for the future.
So as an example,
if I run this to the student piece, what I have done
and I'll show this to you in the next workspace in a second,
I have used FME to populate all this information for me
from another workspace.
But basically I'm looking for within the latest,
I want student
and then I want the number of FAFSA applications
and then latest student average dependent income,
et cetera, et cetera and on we go like that.
I broke these out into what I'm loosely calling entities.
So one for each of the high level topics
of student aid costs and so on.
And then I have what's called a feature writer.
And so I can write this out to a CSV file
at the end of the process.
All of the other transformers you see there
are basically a little bit of cleanup.
Okay, so that is the basics of how we would run it
and how we get information.
Sorry, I forgot to show this part.
So once we have run this through the JSONFragmenter,
now rather than a single response body field,
we now have all of these different columns worth of data.
Some of which are nulls
'cause not every institution reports everything
or doesn't have a value for it,
but we get some useful information, basically.
All right, from here I will show you
the slightly more advanced workspace.
Just bear with me a quick second to open that.
Okay, so the slightly more advanced workspace will open.
And what you'll see is something that looks pretty similar.
So the difference here is we made a loop
using what's called a custom transformer.
So same as what Pat described,
we run the HTTPColor,
we get some information like page numbers
and then we check for, is this the last page
or is it not the last page?
If it's not the last page, repeat the process,
but with a new page number,
is basically what's happening here.
This last part, the decelerator before the loop,
where we go all the way back to the beginning
with a new page number,
the decelerator essentially inserts a pause.
So I found adding .1 seconds in between calls,
meant that I didn't get any errors from this API
versus if I ran them as fast as it could,
I tended to get some errors.
So that's what's going on here.
The rest of it is exactly the same as before,
but one other thing that I did
that might be interesting to people
is I took that institutional data dictionary Excel file
and I told FME
to help me figure out a bunch of information about it.
And so I split it into what I call base and look up tables.
So you remember that state lookup as an example,
I said, let's do something with the lookup tables
and essentially write them out to an Excel file
so that each of them gets its own tab
so that I could later join them in Tableau.
So for sake of argument
and in the interest of time,
I will just show you the end result of that.
So for example, FME took that single data flow
and turned it into many tabs worth of lookup tables.
So if I go all the way,
oh no, it's alphabetical.
Anyways, so for example,
there is a field called main campus
and it has a value of zero or one
and the label's are main campus or not main campus.
And so we kind of repeat this,
but for every combination of lookup tables,
which we could then use in our joins and Tableau later.
So that's coming soon as part of my homework assignment.
So let's see how we use this in Tableau.
So if I edit my data source,
what we can see here is that I've got a series
of Excel files in the pane on the left.
I've started with the school
and I've dragged all the other ones on.
And what I did in my workflow is I made sure
that the ID field and fed school code
and so on were present in every one of these data sources
so that I could make an easy relationship in Tableau
on the ID field.
So the idea of a particular school is present
in every one of these files.
And so now when I go to make my dashboard, for example,
if I remove my search,
I can now see every one of these tables kind of separately
and grouped by topic, which is really helpful.
So I can get like school city or school name, et cetera.
And I could discover the costs related to those and so on.
I chose first-generation students
just because it was interesting,
but this is how we put together an example related to that.
So we wrote the data out to a CSV,
and then we connected Tableau
using the relationship functionality to those CSVs.
The advantage of using a relationship rather than a join
for something like this is largely around performance.
If I'm only focused on first-generation students,
I don't for this example sorry,
if I'm only focused on the first-generation student metric,
say the aid data source isn't particularly relevant to me,
but the location is because I'm displaying a map.
So if I'm not using a particular data source like aid,
Tableau's not going to query it.
It's going to be much faster using a relationship
than using a join.
Okay, so this on Tableau Public.
The link is in the chat
and I will wrap up here in the interest of time.
But we'd love to hear from you,
feel free to reach out at any point in time
if you have questions.
Thank you so much for attending our webinar today.
And we would very much appreciate any suggestions
on future topics that would be of interest to you.
So with that, I will say,
thank you very much from Vancouver.
We appreciate you taking the time today to join us.