(Andrew) Okay, we are ready to begin.
So welcome to the FME in education webinar.
The second in a series
and this one's focused on combining data from web based
and on-campus systems as you'll see coming up.
For your information, this webinar is being recorded.
We will have it up on our website
at the URL shown in a couple of days.
You'll also get a feedback form at the end of the webinar.
And if you choose to subscribe to updates,
you'll get an email notification
when the recording is ready.
For those who haven't used a Zoom webinar before,
there's a few housekeeping items just for you information
at the top left of your screen,
it'll indicate that this webinar's being recorded
as mentioned.
Feel free to use the Q&A,
or the chat if you have questions for the presenters,
we'll keep an eye on both of them.
In some webinars, but not this one we'll have a poll.
There is one interactive step
that we'll ask you to participate in during this webinar.
And we'll post the link for that one in the chat.
The host of this webinar is Plaid Consulting.
We provide data integration, research
and analysis services to educational institutions
to help them improve policies, services, and processes.
Our agenda for today is we will do a brief review
of what is the FME platform for those
who may not be familiar with it.
And then we'll spend most of our time together
on an example of piloting
a conceptual learning management system,
integrating it with a handful of other systems.
So in the on-campus context,
we'll talk about an enterprise resource planning system,
such as Banner or PeopleSoft
with particular emphasis on the student information
and HR information systems side of that.
And then we're going to integrate it
with a handful of cloud systems,
which includes Microsoft Forms,
Canvas Learning Management system, Google Sheets,
and IT requests and documentation systems,
including JIRA and Confluence.
It's a bit of an ambitious agenda.
So we apologize in advance if we talk quickly,
we'll try and keep it,
keep it constrained properly as we go.
My name is Andrew Drinkwater.
I'm a co-founder of Plaid Consulting,
Plaid launched in 2016.
So we're just over four years old now.
And we're pretty excited.
We're based on the west coast of Canada
and we primarily help higher education institutions,
but that's starting to expand into the K-12 sector as well.
My co-presenter today is Patrick Lougheed.
He is also a co-founder conveniently enough,
of Plaid Consulting.
We've both got backgrounds working
in the higher education system in Canada
in a number of different roles,
spanning from academic advising to recruiting,
through to business and policy analysis
where we work together to institutional research
and a number of others in between.
So before we talk about the FME platform,
I thought I'd mention what is data integration,
which is sort of the meta level topic
of what we're talking about today.
And data integration involves combining data
that resides in different systems or sources
and provides users with a unified view of them.
In order to achieve our goals of data integration.
We're using a tool called FME,
FME stands for feature manipulation engine,
for those who are curious,
and it's the data integration platform
with the best support for spatial data worldwide.
We note that most of today's data
is actually non spatial and FME does an excellent job
with this as well.
So the mission of Safe Software,
the company that makes FME is to help users
maximize the value of their data,
no matter where it's stored,
what applications you use
or who you need to provide access to.
Safe was founded in 1993,
by two co-founders named Don Murray and Dale Lutz.
They recognized that local companies were encountering
a lot of data challenges and they set out to help.
It's funny how far things have come
and yet how many companies
still have similar challenges today.
The data integration platform FME,
enables over 10,000 organizations worldwide
to use and share their data where, when and how it's needed.
FME is known for its ability to address complex challenges,
which you'll see today, no doubt,
including those that are posed by location information
and provides the best support for spatial data.
FME is a software vendor.
So they make the tool and sell licenses
and the like services are provided by a network
of over 150 partners, which includes Plaid Consulting,
delivering the webinar today.
Safe has three major products in the FME family,
FME desktop, where you build and run workflows,
which is what we're going to focus on today.
And FME server and cloud allow you
to automate your workflows,
which we're gonna focus on in a future webinar.
The idea to FME is to allow you to connect data
from multiple data sources
so that you can power business decisions
with all the information that's available.
FME supports geospatial data, structured data,
unstructured data, linked data, time series,
and many, many more. There's over 400 different connectors.
You can also extend FME with custom connectors
using REST which we'll see today with the HTTPCallers
using R, the statistical programming language
or Python as well as other technologies.
This slide is intended to show how FME has grown
and advanced since the early 1990s,
they began in the geographic information space
and quickly moved into computer aided drawing,
tabular and database systems,
towards the 2000's.
Closer to today, they've made major investments
in indoor mapping so that FME can support maps
of your campus for example,
augmented reality and virtual reality
and AI machine learning.
And of course everything in between,
and they're not slowing down yet.
There's lots of investments on the horizon
and new research and development for new features
for the products.
FME server and FME cloud allow you
to automate your workflows.
This will save you time and ensure that data
arrives when it's needed.
With FME you can create all of your automations
for transformations and event based workflows
in a graphical user interface.
You'll see some of that coming up today,
you can set up business rules that trigger workflows
to run on schedules or in response
to specific kinds of events,
such as when a new applicant applies to college.
You can also automate these workflows
so they send notifications, for example,
via SMS or email or various web protocols.
So you could, for example, set up a data upload service
that provides validation and the detailed pass-fail report.
Performing complex manual tasks is a thing of the past,
your staff can now focus on areas where they add more value
than just fetching data and combining it
in different spreadsheets for example.
FME has cross platforms so they support Windows and MacOS,
which you'll see today.
Also support Linux, FME server can run in Docker
and Kubernetes.
And of course, FME cloud is the FME server version
that's fully hosted on the AWS cloud by Safe Software.
Part of what we're here to discuss today is
FME has a new education subscription model
that launched this year.
The idea to the education subscription is
it allows you to use more FME at a lower price,
leverage your operational expense funds
because it's an annual predictable cost,
reduce the time lags so you don't have to wait
for new licenses because this is an all you can eat model.
And on the same token,
it allows you to expand the footprint of FME
beyond say a single department
like information technology services
into many others as the need arises without additional cost.
Budgets are predictable,
they're based on the number of students at your institution
or school district, et cetera,
and reduce procurement costs
because of the simple licensing model.
We don't have a specific pricing page
as part of the webinar today
because we have attendees from around the world.
So feel free to reach out to Plaid Consulting
or to Safe directly if you'd like a specific price sheet
for your country.
But the rough idea is the license model
is based on the number of students that you have.
And in our view, it's an eminently affordable option
compared to many other competitors in this space.
So we highly encourage you to have a look at that,
and we'd be happy to chat about that specifically with you.
Next up, we're gonna move towards the live demonstration.
So for our live demo,
we're gonna engage in an experiment called skunk works.
So historically this has been known
as a laboratory or department of a company
or institution that's typically smaller and independent
of the rest of the organization.
This term originated
from Lockheed Martin's advanced development programs,
which ultimately led to the development
of the XP-80 aircraft in a way, 140 days.
This group has designed several military aircraft to follow,
and the term has become widespread
in business technology and engineering.
They're also well known for things like
coming to an agreement based on a handshake
and figuring out the paperwork later.
So what are we going to attempt to achieve today with this?
Well, we're going to try and pilot
a learning management system,
and we've got a few goals at a high level.
So in order to achieve our pilot,
we wanna get some instructors to volunteer
a hint in advance for the attendees. That'll be you.
We'll post the link in the chat soon,
we want to enroll some students.
And we're gonna simplify this and say,
if we have an instructor,
we assume that the course they teach
is in demand enough that it'll have students.
So we don't need any student volunteers for today.
We wanna document the features that IT worked on
related to this and any work-related service requests
that they worked on as well.
We wanna calculate a rough cost for our pilot,
and we wanna create a project report
that includes interactive tableau dashboards.
Pat posted the link in the chat coming up in a few minutes.
We'll get you to fill that in, but for now just hang tight.
So imagine I am a business analyst. Maybe I work in IT,
or maybe I work in one of the business areas,
nominally, I am leading this project.
And so I also have a handful of secret goals
that I don't really want management to know about.
So number one is I have zero desire to code the interfaces
between all the systems that we're gonna look at today.
Even though I might know some code,
it's not my first choice for this.
And I really don't want to have to manually
enter the same data in multiple places.
So I wanna leverage a data integration tool
to minimize any data entry that I have to do.
So we're gonna look at two systems at a very high level.
We're gonna talk about database systems.
These are often hosted on campus,
but there's also options like Amazon web services
or Microsoft Azure that hosts them in the cloud,
but they behave much like the ones you would host on campus.
We're also gonna look at cloud systems.
I should note database systems
are often accessed through structured query language or SQL.
This is relatively straightforward for many people
in this space, or they have tools
that lay over top of structured query language,
such as Tableau or power BI.
Cloud systems on the other hand are often accessed through
what's known as an application programming interface or API.
These are a less common set of skills.
They structure the data very differently
and you can't get direct access to a cloud database
under typical circumstances.
So we need a new way of looking at them.
Our fundamental goal, much like having a family dinner
over the holidays is to just get these
people to talk to each other.
So in much more detail, this is what we're looking at.
The left hand side of the screen shows
on campus hosted systems.
So a student information system and an HR system
on the left, for example.
We also have an institutional data warehouse,
but that will be a webinar topic unto itself. In the future.
On the right hand side are a variety
of cloud hosted systems.
These range from relatively simple, like Microsoft Forms
and Google Sheets to a fair bit more complex,
like Canvas learning management system
and Atlassian, JIRA and Confluence.
We're also gonna use Tableau for our data analysis,
and we're going to publish a report for leadership
to a HTML page.
As mentioned, our team's comprised of a business analyst,
that's me, instructors and their students and you.
You'll also be an instructor
if you're willing to volunteer for us.
I should note that all of the data used in this demo
is entirely fake.
It is made up in FME or entered by users in a survey
like you'll see shortly.
We should note that any vendors mentioned
in this presentation do not necessarily
endorse the conclusions drawn or the way the data is used.
All right. So this is where you come in, before we begin,
we're wondering if you can fill in the form for us.
This was posted in the chat moments ago,
and we'll ask that you make your responses semi-realistic.
It's a really easy survey.
We're gonna ask three questions.
The first is, what course would you like to teach?
Just give us a title. It can be anything really.
And then the second and third questions are about
how much you think you'll get paid.
So the first one is, how much do you think
you should get paid?
And the second one is, how much do you think
you actually will get paid?
We know that we've got international attendees.
So the exact numbers
for Canadian dollars don't really matter,
but that's the scale we're thinking of, just so you know.
The URL itself is posted in the chat,
or you can scan this QR code with your phone
and it'll pop up in a browser as well.
Okay, so let me just switch tools here,
away from PowerPoint and into the more
interactive part of our demo.
So the first step is we wanna look at our Microsoft form.
And so if you went to the URL provided,
which is in the chat, if you're just catching up now,
you'll get a survey that looks like this.
So the first question is about,
please give us a course name
and the second ones are about money.
So let me just move this side by side
so that you can see kind of how the responses work.
All right. So this Microsoft form
is designed to input data automatically
into an Excel sheet that is stored on One Drive.
So this is perhaps the one step of the process
I'm not really using FME for
because Microsoft has built this integration automatically.
And it's just a little bit simpler.
So if you can fill in that link in the chat now
that would be excellent.
I'm gonna do it for sake of demo, just so we can see this.
I've also provided a number of sample responses previously.
Thank you for intro to data integration, that's appreciated.
And so this is an example response.
And so I'll hit submit and we'll give One Drive
a couple of seconds to catch up
and I should have a new course momentarily.
So my example is row number 23 here,
intro to not making typing MedSeq CS in live webinars.
We can see here that the email is anonymous
because this is a open survey
and we're not requesting such information.
We've got the course title,
we've got the range and that's basically the idea
and our SharePoint agent is filling this in for us.
Okay. So that's step one,
let's get some responses in there.
Thank you to our volunteers for providing
some additional information beyond what we had before.
And now we're going to move into the live examples.
So first off we want to retrieve the Microsoft forms data.
So moving over to FME,
I've got the workspace already set up.
This one's fairly large
given the number of examples we're going through today,
but we'll post this following the webinar
once we've done some cleanup to it.
So our first step is let's import our data.
I can hit FME's, "Run just this" just so I can make sure
that we've got the most up to date data.
And then I'll talk you through the next steps.
So I can see I've got down in the bottom here.
I can see I've got IDs, 11 through 23,
and that's probably good enough for this purpose.
So let's run the first step up to here.
So what we're gonna do
is we're taking a couple of steps here.
We do some field name cleanup
and get rid of some additional fields
from the data source that we don't really need.
And then we're using string searchers
to look through the salary range.
And basically the first one takes
the left half of the salary range.
The second half takes the right half of the salary range.
We then do a little bit more field cleanup
and ultimately turn these into numbers.
So what we're doing is we're taking,
because each range is $2,000.
We're saying pick the left side,
plus a thousand and the right side, or sorry.
And for the second question, same story.
So if you picked a 2000 to 4,000 range,
your response here would be 3000.
We're also assigning course names and things like that.
These are entirely random.
So all of you are teaching FME 100
and something depending on how many volunteers we got.
Step 1A, which is a little bit irrelevant right now
is to check whether any of these submissions already exist.
So we've already cleared out the database in advance,
but I'll show you how this works,
just because it's really helpful sometimes.
So FME has a transformer called a change detector.
And what it's doing right now
is it's reading from our PostgreSQL database, hosted in AWS.
And it's checking if any of the information
from the form is already available.
What we can see here is that all 13 records
are considered inserts. So they're new.
We don't have any that are in the unchanged
or in the deleted category.
So assuming all our records are new,
we can then assign class sizes.
I have assigned needs to be between four and eight,
largely because of time, the more students we invent,
the slower our demo goes.
So we know class sizes of four to eight
are wildly unrealistic.
So our next step after this
is we want to make our instructors
look a little more realistic.
So we're using the United States census names dataset
that's posted at the URL shown,
and we are working with surnames that occur 100
or more times in census 2010.
For this purpose, we're taking surnames
and assigning them to both first and last names.
So the names might look kind of funny when you see them.
This is used under an open data license.
U.S Census Bureau does not endorse this use
or conclusion necessarily.
So what we're doing here is
we assign the last name and the first name.
And in both cases, we have a random number
that gets assigned to them as well.
This allows us to make use of these in the sorters
to ensure that we don't have the same
or likely ensure that we don't have
the same first and last name, for example.
So I'll run that whole step.
That takes a minute to import all the names
that are popular
in the U.S. and then we'll go to what happens from there.
As that's loading, what we do next is we clean up the names
and we assign things like email addresses.
So we're saying that all of our professors
have a doctoral degree and that their email address
is in first_name.last_name@PlaidUniversity.Plaid.IS,
for example.
We're then going to assign those names that we just created
to the instructors that initially came
from the Microsoft form.
So we're just joining the data based on the row ID.
Once we have that,
we can now see that we have 13 instructors,
they got course titles based on the Microsoft forms.
We've got some mid points for how much they'll get paid,
et cetera.
We've got course subject and number as discussed.
The source of this data is new.
So there might be some existing records in the database,
hypothetically.
We've got really small class sizes
and we have names assigned and email addresses
that are related to those.
So that's what happens there.
Our next step is we wanna make it seem real.
So we want rather than just an aggregate number
of students like eight in that first class,
we wanna actually have eight students
related to that first class.
So let's see how we do that.
I am going to run this to where we assign the student names,
and then while that's going,
I'll walk through what's happening here.
So the first step that we're doing
is just a little bit of cleanup.
And then we say that the first student in the class
is one basically, we then use a loop in FME.
So this is a custom transformer.
You can do a number of different things with them,
but in this case, we're just looping.
What we're doing is we're saying add one
to the base number of students within the class
as our first step.
Our second step is to test whether
our new base number of students
remember that was the original one plus one
is less than or equal to our class size.
If it's true and it passes, we want to do two things.
One is we want to write the student IDs
out to our main workflow,
because even if it's less than the intended total,
they still count.
The second is we wanna loop back to the beginning.
So we wanna go from here to here, basically,
if it's less than or equal to the total class size,
and then we wanna repeat the process.
So we're gonna repeat this over and over and over again,
eight times for that first class, for example,
going back to our main work space,
we can see the output of this
because of the order of the looping
I'll sort this by class number, just so that we can see.
So FME 111, for example, has a class size of four.
And we can now see that we have four rows of data,
one for each of these base student number within class,
and the exact numbers here don't matter,
but there's four rows. And that's the goal.
We then do a quick check.
So feature merger is similar to a join
in that we're bringing together another data data set.
So this one actually comes from our database
to check whether there's any existing students.
We already know that there aren't.
So we're gonna skip that.
We've got some conditions to allow for that basically.
From there, we create an actual student ID number,
and this is, you know, very simplified.
It's just an incremental number.
And then because we use basically the same method
for instructors, we're going to offset this
by a thousand, sorry, at the beginning of the columns,
just to make sure that we don't end up with the same name
for students and instructors,
even though we know this happens in the real world.
We can then generate a anonymous ID strings.
So for example, like this one
just because it's safer and more secure
than an auto incrementing number.
And then we do some cleanup
and then just like we did with the instructors,
we're creating student names and email addresses,
and the student email addresses are slightly different
in format, but it's the same basic idea.
Okay, so that is how we invent students
out of aggregate data,
it's really great for testing your systems too,
on another topic.
Our next major step is we wanna create some tables
in an enterprise resource planning system.
So ERPs are very common in education.
Huge players in this space include Oracle with PeopleSoft,
Ellucian with Banner, Workday is coming on board.
There's a number of others as well.
PowerSchool, I think is the other big one
in the K-12 sector.
And there's a number of others as well
that I'm glossing over.
So this schema is based very loosely
on how Ellucian banner sets up their main tables.
We've cheated a little bit
to make the demo go a little faster.
It's very simplified compared to
what the database actually looks like.
So let's walk through together,
what's happening here and then let's run it.
The first thing we're gonna do
is we're gonna use a random number generator
to assign a credential.
So an ID between zero and four,
and then we're gonna join that to
some data that we invented to say,
these are the programs we offer.
We'll join that to our actual students.
We're gonna write both students and instructors
to a table called Spriden,
and we'll do some cleanup around there as well.
We also have a student based table.
I don't know how to pronounce
the banner acronym for this one,
but it's got some information as well.
We'll write that. We've also got a registration table,
which you can see here.
We've cheated a little bit on this.
There's a separate enrollment table in banner,
which has the CRN,
which is a unique identifier within a particular term.
We've just put them both on the same registration table
to simplify the demo.
Additionally, we have a course table
that identifies which courses we offer
and some information about them as well
at the section level.
Last but not least on the students side,
we have instructor assignment.
So we say these people are teaching these courses
that have these students in them more or less.
On the HR side, we're not doing too much with this
for demo today but the information's there.
You'll see how it comes into play
a little bit later in Tableau.
So we take all of our instructors and write them
to the SIS part, is one step here.
Another step is we have an employee based table.
So all of our staff get added here.
So the instructors from our Microsoft form,
as well as an IT staff member that we invent down here.
The IT staff members me as a business analyst.
We also have a payroll table that we're gonna write to
and we're gonna invent nine pay dates
for the pay period we're talking about
for the particular term.
So that's what's happening here.
So I'm gonna run these all at once
by going to the database writer.
So the feature writer allows us
to write to several tables at once.
So I've got mostly the ERP tables here,
plus a couple of tables to say,
who are our students and instructors.
So let's run to here.
And so that'll take a minute
because it's generating all the data as we go
but we should be just about done here.
There we go. Now we're done.
So we could see, for example, if we look in our database.
I promise you, you won't have to write code for this,
but just so you can see that there's information.
If I click on say see error sign,
I can see a little bit hidden.
I can see the pidome and the CRM
and which table name it is and what term code it is.
This is a great example,
but Spriden has all our students
and all our instructors in it.
So we can see instructors up to ID 23
and students starting at 1,001, for example.
So now that we have written this out to the database,
I will turn this over briefly to my colleague Pat,
who's gonna demonstrate
the starting point of our LMS workspace.
After that I'll turn it back to myself
and we'll talk about some of the IT related parts
of this puzzle.
So without further ado, Pat,
over to you for the beginning part of Canvas.
(Pat) Perfect. All right.
So what we have is a Canvas installation
running inside of Docker.
So this is basically just set up from scratch.
We have a default term that Canvas
has helpfully provided for us.
We have one person that being myself, the administrator,
who set up during the installation process
and no courses.
So what we're gonna do is we are going to run
a workspace that pulls data out of our ERP tables,
such as the Spriden table
that Andrew just talked about.
And we're gonna put that information into Canvas
and to do this we're going to use
Canvas' application programming interface
through a combination of HTTP colours.
And what these will do is essentially to
provide data in the particular format
that canvas requires in order to create everything.
Just to be on the safe side, we're going to
pull existing data out of Canvas.
So we're gonna check for what users
already exist there, terms, courses
and enrollments and well we're essentially
starting with a clean Canvas install
so we're not gonna find anything.
We wanna set this up so that in future,
if we run this and we end up with the same students
enrolling in multiple classes
that we're not going to attempt to recreate them
again and again.
So we are going to run this workspace.
We're gonna make sure that we've got the right API key
and just give it a little bit of information
on where to pull data from.
As it starts, it's going to pull information
out of canvas,
and we'll then start doing the comparison,
making sure that those people don't already exist in Canvas
and then push them in.
So we can see we're pulling information out of Canvas.
We're creating 87 odd instructors and students,
and while this is running we'll turn this back
over to Andrew to finish off some of the IT side of things,
and then we'll come back and see how Canvas does.
(Andrew) Great. Thank you, Pat.
Yeah. So next up we're going to have a look at
some of the IT systems that we wanna demonstrate today.
There's two of them
and they're both delivered by the same company.
So you may have heard of Atlassian
and they make a number of different IT oriented tools.
The two that we're gonna look at is Confluence and JIRA.
So Confluence is a documentation tool,
it's used by IT staff to document the work
that they're doing, and new features and this kind of stuff.
It's also used by non-IT staff
for similar kinds of purposes.
The other one is JIRA.
JIRA is often used by both IT and project staff
to manage their workflows.
So we'll show you what this looks like in just a second.
We're using what's called a REST API.
And so this is a way of getting data
out of these cloud systems.
They're not quite the same across every company,
unfortunately, but they're pretty realistic to work with.
The part that's probably worth knowing here
is just a couple of really brief concepts.
So for example, the first part has the word get
and get basically means retrieve data
from this particular spot.
So in the case of what we're looking at,
we're looking for content.
And so it's got, what's called a URL endpoint
as the second bit here.
And so this is saying, get all content
based on this particular URL.
There are also a number of others.
So we use post to create content for example,
but otherwise we have the same URL
and this can get all manner of more complex
depending on how the API is structured.
Atlassian is pretty good as far as documentation goes,
in my view.
They've also got a similar API for Agile
and one for JIRA as well.
So let's see what this looks like.
So in confluence, for example,
this is a documentation system.
And just to show that
there's no kind of funny business going here,
I'm gonna hit refresh and you can see
that the only page we have in this space
based on what's on the left hand pane here
is feature requests.
And so I've got some very basic text
written here that says, these are gonna be summarized,
click the links below, which don't yet exist.
So we're gonna create these with FME.
JIRA involves a number of different pieces.
Today we're mostly gonna talk about
project management perspective. So we have a backlog,
a backlog is where we create new issues to be worked on.
And then we have a current sprint.
So we see sprint one here, the current sprint is like,
right now, what are our staff working on? That kind of idea.
We can also display this on a board.
So we have a to do in progress and done.
And so we'll come back to that. It's all empty right now.
So going back to FME,
I'm gonna start populating some of this stuff.
So we can see here that term end point.
So there's a number of different end points
that I've set up in an Excel sheet
that are gonna get imported into here.
And then I filter them as I need them.
This is perhaps more complex than what we need for today,
but that's sort of the idea.
Okay, so what we're gonna do first
is we're going to populate JIRA
and I already showed you the web version
so I'm gonna skip through to about here.
So what we're intending to do
is we wanna make a service ticket
for every new instructor that gets created.
And we're gonna say it's worth between
one and three story points. Story points is a term
that many people in the Agile space use to say,
roughly how much work is this?
Okay. So now that we've done that,
if I go back to my web browser and I hit refresh,
I can now see that I have 13 issues
in the backlog to be worked on.
And I can see that each of these
has a different number of story points assigned
somewhere between one and three.
Our next step is to say, okay, well,
let's actually put these on the board of the current sprint.
That would be this one so that our IT staff
could be assigned to work on them this week.
So I'll run that one as well and just wait for it
to finish writing the output.
And then I'll go back to my web browser.
And now I have all of the tickets.
And so if I wanted to, I could move
a specific task to in-progress or over to done.
If I verify the instructor ID.
We note that you probably
wouldn't manually verify all these instructors,
but there might be some steps
that your service desk needs to handle
when you launch a new LMS.
So we wanted to demonstrate this right up front.
Now that we have all of them on the board, we wanna say,
Our IT staff got through some number of them
and I'm gonna pick about half the issues.
And so now if I refresh,
I can see half plus the one that I moved
are now in the done category.
So that's the first step.
Oops, sorry, too fast.
The next thing I wanna do is
I wanna write some information to Confluence.
So what I'm gonna do here
is I'm going to look for a specific end points.
And in the case of what we're talking about today,
I wanna make content within particular spaces.
I use a loop for this one as well,
which for what we're doing today
is probably a little bit overkill, but it's really helpful
if you wanna go through all your issues.
The reason I chose to use a loop for this particular tool
is because it's paginated.
So it'll only return 25 content pieces at a time,
for example.
So we need to loop through many pages, potentially
if we have a big site.
So let's get those pages onto Confluence.
So the other thing we did,
was we made a really basic data source in Excel.
That looks something like this.
So what we have here
is a set of pretty common feature requests.
So we wanna make sure that it works with our
campus' central authentication.
We want to make it look like our brand,
a number of other things. We want zoom integration,
because these are the times we live in and so on.
So let's get those features into Confluence.
There you are.
So I'm gonna write these out to Confluence
and I should have seven pages momentarily.
It's nice to see that none of them were rejected.
So if I go back to Confluence,
I can see that there were no pages and now I hit refresh.
Now I can see that I have all of my feature requests
documented in Confluence as though my IT staff
had written them themselves.
So I've written what the feature name was,
how long we think it'll take.
I've given an image of what it looks like.
Our central authentication system
is based on trees as it turns out.
And I've got a description of what the feature was.
So my IT documentation is more or less complete.
So that's kind of the first step.
We now have this in Confluence.
The last major piece that we wanna talk about
is we wanna get this information out to,
both a report that our executives can view
and to Tableau desktop.
But first, before I demonstrate that part,
I'm gonna turn it back over to Pat
to show how our data ended up in Canvas.
(Pat) Okay. So looking at FME
we've run to the end of our process.
We have put all of our data into Canvas.
So adding users, for example,
we can see that all of our users were successfully
outputted, none of them were rejected.
In turn we ended up creating terms,
courses and both student and instructor enrollments.
So we go back to Canvas, have a look at
let's start with our terms.
We can see that we've added a summer 2020 term
that runs from May 1st to September 1st.
And we've created the courses
that live within that particular term.
We've got the various courses
that we had in our Excel sheet on our form.
And we can see the instructors
that have been successfully assigned to those courses.
I can also see that we've got a wide swath of people here
that represent both our instructors and our students.
As soon as Canvas wants to finish loading, there we go.
And we can dig into these students
and we can see what they're enrolled in.
So for example, the student was enrolled in
Intro to Physics.
So there we go, Andrew, back to you.
(Andrew) Awesome. Thanks Pat.
Okay. So to wrap up our demo,
we'll go back to FME and we'll continue on
to grab a little bit more content
and put it into both Tableau and an HTML report.
So one step of that, and I'll just show you this one,
because it's what we're looking at currently
is to run the HTTPCaller that looks at Confluence
to pull the content of all of those pages
that we just created.
So I'm gonna say run between the selected
and then we'll have a look what the content came back as.
Okay. So I think this one's probably a good enough example.
So, oops.
So we can see here that we've got,
we've got our table that says what the feature name was.
So this is integrate
a login with central authentication system,
how long it took an image and a description and so on.
And so all the pages are kind of like that.
So that's one step that'll feed into our report momentarily.
The other piece is now that we have some data,
we wanna try and write this out to Google Sheets.
So this is a convenience function,
it's so that we can put
this data onto Tableau public easily.
So Tableau public can read directly from Google Sheets.
So let's see what that looks like.
So currently we have Google Sheets already set up,
but you could create this from scratch just as easily,
and we have Tableau public.
So for example, right now
you can see that our skunk works summary
has basically no data.
We have zero courses, zero instructors,
zero students, et cetera, et cetera,
our IT costs are zero and so on.
Pat, if you can put the Tableau public link in the chat,
that would be great.
So now let's get some data into our Google spreadsheet
as our starting point.
So I'm gonna write this and I'll just take a second,
cause it has to kick back and find
some of the earlier steps of the process,
but it looks like, it looks like we got data
for all of our important tables.
So we got 20 rows for the JIRA issues,
117 for instructional costs, 13 for instructors,
180 for IT costs and 70 for students.
So if I go back to the web version of my Google Sheet...
Anyways, I've now got my 13 instructors, for example,
I've got information about my IT costs
broken down by weekly, my instructional cost and so on.
So now if I go back to Tableau public,
and as the owner of this workspace,
I can request an update and that'll take just a second.
While that's updating, I should note Google Sheets
will update in Tableau public overnight, automatically,
but if you want it done live
you have to push the button at this point in time.
So while that's happening,
let's talk about the final step in our process.
So the final step in our process here
is to create an HTML report.
And so we're gonna take the content
that we had from Confluence,
at least the pages for feature requests,
which is what that test filter does.
And then we're gonna join it to some additional content,
which includes some more information about the new features.
So we've got image URLs directly rather than via Confluence
where it would require authentication.
We've got the Tableau embedding as well.
And then we've just created
some kind of very basic HTML here
to fill out the top part of the report.
I will say the report doesn't lack or does lack
a little bit of shine and polish at this point.
So thanks for your patience on that particular part.
Okay. So let me just...
Too fast. Okay.
So there's gonna be a report template.
You'll see what it looks like in a second.
So what we do now is we run through to our create webpage
and what's gonna happen here
is we're gonna combine the eight pages
that came from Confluence and we're gonna combine it
with that basic HTML we created for our template.
We're gonna have a sorter.
So we have basically nine or so records here
that have HTML content in them.
The sort is just to make sure
that things like project summary end up at the top,
they probably would but just to be on the safe side,
we've done that anyways.
And then we use an aggregator to combine all of our HTML.
We're concatenating it here.
And the separator character is text-based.
I think it's just a line break. Yeah.
So LF means line feed in FME.
So it's just a line break
to separate things out a little bit.
And then we write a single file to the HTML page.
And so that HTML page in turn looks like this.
So what we can see now
is that we've got some plain texts that we wrote.
So overall, our project was a success.
It costs us a little over $80,000.
We had 13 instructors and courses, 74 students,
201 hours of IT time, and we can see how that breaks down.
We can then scroll down and we see additional dashboards.
So we have some instructional costs, by course,
we can see that FME 116 was our most expensive course.
We can also look at the pay expectations.
So when you filled in our survey earlier,
the reason we asked you what you thought you would get paid
versus what...sorry,
what you wanted to get paid versus
what you thought you would get paid
was to be able to produce this chart.
So what we can see is that as volunteers,
you expected on average $7,300 to teach this course,
and our imaginary institution is only paying 5,462,
which is a difference of 1,846.
So this is good for our executive to know
that we may have to shift how we do our hiring
in order to find people that will work for our price point,
or we need to change our price point
in order to find the right people.
We can also see a breakdown of our IT costs.
So most of them were devoted to
building new features as expected,
and about 10% were devoted to service tickets.
And we can see what these all look like.
Ah, that's my testing from earlier, apologies
that should actually have a proper ticket name.
And I wrote it over it with tests
to make sure the Tableau public would update. And it does.
So my mistake there, it should have an actual name,
but nevermind that, we can move on.
And then we have our feature requests, including,
the table that came from Confluence,
the image that came from the URL shown here
our feature descriptions.
And on we go and from here
we could put it onto a nicer template.
So they'll be on our webpage as well.
And our leadership could actually interact with it.
So if they wanted to just keep a particular one,
they could do that.
And additional filters would obviously have to be set up
to make this work, fully the way we'd want it to.
But that in a nutshell
is how we can do a skunk works project
of integrating LMS data, Microsoft Forms, Google Sheets,
an ERP, Atlassian, Confluence and JIRA.
And we did this with pretty much no code.
There's a couple places
where knowing a little bit of code was helpful.
So I was able to cheat a little bit here
and type some basic HTML,
but you could have an editor that would pop out HTML
for you as well. And FME can also create HTML.
The other part where knowing a tiny bit of code is valuable
is when it comes to writing JSON objects.
So JavaScript Object Notation is how most of these APIs
accept inputs for things like creating pages.
So as an example, we have this JSON templater here
that shows kind of a structure.
So for this particular page to create a page on Confluence,
we need to know the title,
which comes from the FME feature or column known as feature.
We need to tell it what type it's gonna be.
So that's page, we need to know what space it belongs to.
So this was the skunk work space,
as opposed to Plaid's actual planning spaces.
Ancestors is optional but to make sure
it nests itself underneath feature requests,
I pulled from the feature request page here.
I pulled this ID.
And so that's what that one is.
And then a little bit of information about the body.
That's just Atlassian's notation to say, the HTML is this.
And so that's how we did it. It wasn't very hard,
but it does take a tiny bit of practice.
And Atlassian as I mentioned is pretty good with this stuff.
So when we create content, if I scroll down,
I basically copied their code and modified it
for the purposes of what I needed an FME.
So I didn't really type any of this manually,
except for that one little bit of HTML.
Everything else is done through a visual interface.
This was much easier than me coding all of these interfaces
between systems manually.
So that's kind of our workspace.
So thank you all very much for taking in the demo.
We have about five minutes for questions.
If anybody on the line has any, you are welcome to.
Feel free to...
I'm gonna click allow to talk.
If you would like to unmute yourself, you are welcome to.
And just a couple other housekeeping things.
Let me go back to the slides,
for the tail end of this.
So I'll skip over all of those.
There are great learning resources available at safe.com.
I would not have been able to present this webinar
if it weren't for me spending time with these resources
over the last couple of years, since I started using FME,
as an example.
There's free online training, there's free tutorials.
There's an excellent knowledge center.
And people in the community will help you if you need it.
As mentioned, this webinar was recorded.
It will be available on our website
in the next couple of days.
But if you subscribe to the updates,
which you'll be prompted about when the webinar ends,
you'll get that notification in your email.
This is how you can reach us.
And now I'll turn it over to Q&A.
So feel free to post in the chat or in the Q&A box
or unmute your microphone. You're now allowed to do that.
If you have any questions and on behalf of Plaid Consulting
and our friends at Safe Software
who are supporting this webinar,
thank you so much for attending.
We really appreciate your time.
This is kind of a new thing for us. It's our second webinar.
So if you'd be so kind
as to recommend them to any colleagues, if you enjoyed this,
we would really appreciate it so that we can help
spread the word about how useful this tool
can be in education.
Thanks very much for attending. If you have any questions,
please feel free to stay on the line.
I will stop the recording here.