-: Good morning from Vancouver, Canada
Bonjour. Ohayou gozaimasu. - おはようございます。
Welcome to the Canadian Higher Education Tableau User Group.
This session is presented by Plaid Consulting, a Tableau partner. 5
Plaid helps universities, colleges,
polytechnics, and governments
with data and analytics solutions,
business process improvement, and training.
My name is Andrew Drinkwater.
I'll be presenting today.
Nice to meet you.
We're pretty excited
that we'll be presenting at Canadian Institutional Research
and Planning Association Conference on October 26th
in partnership with Sam Hannah
from Capilano University.
we'll be focusing on building a data warehouse
and Tableau analytics in four months
as part of our presentation.
This is on October 26th, 10:30 AM Pacific time.
You can still register for the conference
visit our website, plaid.is/cirpa if you're interested.
we're also sponsoring the DATAcated conference.
It's happening on October 27th on LinkedIn live.
You can learn more about the conference
and register at plaid.is/datacated
Today is a pretty exciting day for Plaid.
So we are launching a new video
that talks about who we are and what we do.
And, you get to be the first to see it.
If you're on a slow connection, it may work better if
you view it directly on YouTube.
The link will be in the chat shortly.
(soft music)
(upbeat music)
Right, so this is pretty exciting for us
and we appreciate all the support
of those who've contributed along the way.
I will just go back to PowerPoint now,
and we can talk about the topics for today.
(soft music)
Or we can hear the video twice.
Okay.
So this webinar is being recorded.
So if you have colleagues who couldn't make it today
we expect to have that recording posted
to our website in about a day or so.
And that'll also include transcriptions as well.
So today we're here to talk
about Tableau's new relationship data model.
In terms of our agenda we're going to to talk
about some background techniques.
So what is a join and what is a blend,
then we'll talk about what are relationships
and what can we use them for?
We'll walk through some conceptual examples first,
and then we'll get into some practical examples
using Tableau.
We anticipate that this session will be about 30 minutes.
So we thank you very much for joining us today.
All right.
So some background knowledge
for those who haven't used databases a lot
or who haven't used much of Tableau.
Joins are a way of combining two different input tables
in order to create a third table.
We'll talk a few bit about these today.
I wanted to put this right up front.
so we have a chance to talk about it together.
Imagine we have table A and table B,
and we want to bring this information together.
We do this using a concept called a join in a database
or in Tableau or in many other tools.
Joins are based on a common key,
which we'll talk about in a minute.
They result in essentially a third table.
So this is a effectively a flat file
of the combined results from table A and B.
It may be narrowed down with WHERE conditions
and we may narrow which fields we bring in
by specifying which fields we care about
from table A and table B.
But ultimately we end up with one result in table C.
Typically we come across row level joins.
So the idea to a row level join
is that every row in one table
joins to every row in the other table
we can imagine if we have a table
of institutional students looking like the one you see
on the top right of the screen there.
This is a very condensed form of enrollment
in Canadian higher education,
courtesy of the good people who maintain Wikipedia.
And then we want to compare it to say provincial population.
So we have another table with populations,
courtesy of Wikipedia as well via the Canadian Census.
We can combine these and create something to the effect
of provincial participation.
So maybe we could calculate participation rates,
things like this once we have this combined table.
Our combined table looks like this when we run a query,
we can see that we have all five of our institutions
across the three different provinces.
And the number of students is correct.
The population is also correct
when we display this at the row level.
Where row level joins become a bit problematic, however,
is when we want to perform aggregations.
So let's say that I didn't really care about
the specific enrollment at a particular institution
but rather that I was more concerned
with enrollment province-wide.
Using just these five institutions
and aggregating this up to just the province level.
We can now see that our population
is out of alignment for Alberta and Ontario.
It's double.
The reason for this is that we have two institutions
in our list from each of those provinces
versus for Quebec we only have one institution.
So its population is not doubled.
You can also handle this problem
with what are called aggregate joins.
So if the tables are at a different level of detail,
they may need to be aggregated
before being joined effectively
using the same data we had before
the way that we would deal with this
would be to aggregate the institutional students
up to provincial level, which looks like this.
We can now easily join our student's data set
and our population data set together at the row level,
using provinces, as the common key.
This results in a data table that looks like this.
Which is accurate, but maybe a little bit less helpful.
For example, what if I wanted to know
what proportion of those 95,000 students in Alberta
or at the Southern Alberta Institute of Technology?
With this table I could not answer that question.
I'd need to make a different table to answer that question.
So when we aggregate, depending on how we choose to do it
we may lose the context here.
So to kind of summarize this
and admittedly this is selling short
about 10 years of my career where
I continued to hone my knowledge of joins and SQL.
So this is a really condensed summary.
Row level joins essentially result in a fixed output.
So we can only answer a certain number of questions
with each join that we make.
Aggregate joins on the other hand
depending on how we choose to aggregate
can result in more flexibility.
So if we aggregate table B as an example
or change the shape of it,
we result in a different table C.
Aggregates can be more variable
in terms of their output.
If we have the nimbleness to define them on the fly.
So, you may be thinking to yourself all right,
so he's talked about joins a whole bunch.
What is the relationship?
That's what this webinar was supposed to be about.
And quite possibly your first thought
was something like this
maybe Homer and Marge in their slightly interesting
and dysfunctional relationship
or Aunt Viv and Uncle Phil
from the Fresh Prince of Bel-Air
or David and Patrick
from the Emmy award-winning Schitt's Creek.
When we talk about relationships in Tableau,
we're not talking about those kinds of relationships,
we're talking about data relationships.
Relationships in Tableau
are a dynamic and flexible way to combine data
from multiple tables and locations.
In a nutshell, you can think of a relationship in Tableau
as a contract between two related tables.
It tells you how they're related
and Tableau uses this information
when you're building a visualization
in order to come up with the appropriate joins
at the time that you need them.
That's a lot to absorb.
Let's talk through some conceptual examples
and then we'll get into the nitty gritty with Tableau.
So why relationships are really helpful?
Largely this comes down to three major reasons.
Reason number one
is that you need to spend less time doing data preparation
and more time understanding what your data is telling you
with relationships Tableau automatically combines
the relevant tables at the time of analysis,
preserving the right level of detail.
You no longer need to pre aggregate
your data using custom SQL
or creating views in the database.
Hearkening, back to our example with provincial population
and students, this is no longer necessary.
You also get the advantage
of more use cases per data source.
So you remember how I said
that role level joins only answer a few questions
at a time and you need to make new data sets
for different kinds of questions.
Relationships handle this challenge with aplomb.
They preserve all the detailed records
for multiple different tables
in a single data source in Tableau.
So say goodbye
to different data sources for different scenarios.
Relationships can handle more complex data sources
or more complex data models in one place.
Last, but especially not least
is that this can help build trust over time.
So joins depending on how you've constructed them
They can sometimes toss out results
or they can duplicate results.
As we saw in our previous example.
Relationships on the other hand
preserve all the measures that are in your data.
So you won't lose important values like tuition money
because you made the wrong choice of your join upfront.
So how does this look?
It's similar in some ways to what we saw previously
with the standard joint examples,
the difference is that
we can combine many more tables this way
much more flexibly
and Tableau will decide on the fly
what kind of joins are are needed
which you can modify through the interface of course.
So just a couple of other terminology differences here.
So when we talk about joins,
joins are performed in Tableau at the row level.
As we saw earlier, the input to a join is a physical table.
We'll talk about this concept in a minute,
but the short form is that a physical table
is something that exists in your database
or other data source like Excel.
The output of a join is what we now call a logical table.
We'll talk about that again, coming up,
the join type of a join has to be defined upfront.
You have to say it's an inner join.
So keep all the matches from both tables
or it's the left join so keep everything from the left
and only things that match from the right
or right as the opposite end full is key.
Basically everything including the matches.
The queries for a join are executed
every time you change your query.
So when you add something to rows or columns
or the marks card, your query changes
you need to execute a new query for this purpose.
Extracts can be pre-computed
because you already know all the information
that's in the tables.
Relationships on the other hand
are related to each other at an aggregate level
which would be more obvious in the diagram I show shortly.
The input to them is logical tables
which again, we'll talk about coming up.
Rather than having it be a specific flat table
as the output, we instead define
how a query should be run when used individualization.
So the conditions under which it should do something.
The join type is flexible so Tableau will help you decide.
And you can modify that if you need to.
To me, one of the biggest things is actually the queries.
So in a relationship only the tables
that you use in a visualization are included in the query.
And this has changed
on each visualization that you're looking at.
So if you think of something like
a simple table with students, it's pretty small.
You've got, you know, 20,000 students or something,
but they all take four to five courses.
And so then you have another table
of student courses that has maybe 80 to 100,000 records
to account for all those courses that they enrolled in.
And it gets much more complicated from there.
Only the tables you actually use are included.
So if you want to do the sum of students
which we'll talk through later, you can do that.
If you want to know how many courses
these students enrolled in, you can do that too.
It's much more of performance.
We'll show you some examples to come.
Extracts for relationships cannot be pre-computed.
But to be clear, this
does not mean that you cannot create an extract.
You can, but it stores the tables separately
so that it can maintain how relationships work.
One other concept of bringing data together in Tableau
that we're not going to talk about too much today
but I know there's a few people on the line
who are familiar with it is called data blends.
Data blends work at the worksheet level.
So every worksheet within your workbook
could have a different blend hypothetically,
between two different data sources.
The downside to this method is that
you cannot publish a blended data source to Tableau server.
You have to publish the primary and secondary data sources
separately and blend them in the workbook that uses them.
So there's a few extra steps.
And from personal experience it's really easy
to miss a linking field in data blending
because it's unique to each worksheet.
The only join type that you can do with a blend
is the left join because you have a primary and secondary
no ifs, ands, or buts. Relationships, on the other hand,
because there are data source, not at the worksheet level
they can be published to Tableau server or Tableau online.
Additionally, and this to me is really key.
All the tables are considered equal.
There's no primary.
There is no secondary.
You can put them together in whatever way you want
that join type, all joined types are supported
and the performance is part of the query.
So it generally is a lot quicker than blends.
All right.
So let's get into some examples now.
Imagine that you work in strategic enrollment management.
And one of the key questions that you have to answer
among many particularly this year
is are we on track to meet our admission targets?
So let's talk about comparing applicants versus targets
using Tableau's new data model.
So when we talk about applicants and targets
there's a relationship between these two things.
Now, applicants is a pretty broad term
and targets is a little bit narrower
depending on who you ask.
So, we'll define this as the logical layer.
So these are the different concepts
that Tableau wants to relate together
based on a contract that we define.
So, the logical table targets is made up
of a single physical table called the intake targets.
This is part of the physical layer.
Intake targets actually exists in our database.
Now those look kind of the same
but let's go to the more complicated one.
Applicants is made up of four physical tables
and at your institution
it's probably made up of a lot more than that.
So we've got a table for applications,
and then we join it to a table
to say what program has this student applied to?
In what term did they apply?
And are they domestic or international?
We roll these up from the physical layer
up to the logical layer to create a logical table
called applicants that has all the information
about applications, programs they apply to,
terms they apply to, and whether they're domestic
or international.
We can then say that there's a relationship
to the logical table targets.
Now those of you who work
in the strategic enrollment management world
are probably very familiar with intake targets
are typically at a high level.
So it's usually something like
national status and basis of admission
and faculty may be program, but it is certainly not
at the individual application level like we see on the left.
So our applications data is way more detailed
than our targets.
And this may look familiar
from the examples we talked about earlier.
We have a relationship between them.
More generally speaking
we have logical tables at the logical layer.
And when we demonstrate this in Tableau
you'll see kind of the difference
in visual design between the two.
Our logical tables can consist of one
or more physical tables
that is tables that actually exist
or views that actually exist
are considered physical for Tableau's purposes.
Here's where they consist of many tables.
There's a relationship between them, which is a contract.
One thing to note that I missed in the previous slide,
logical tables are not merged.
So each of logical table A in this context
and logical table X retain their original level
of detail and domain.
So if we reverse backwards here
to the applicants and targets,
we would keep all the applications.
So we could drill into any individual student
and see their records and we'd keep every individual target.
So we could drill into individual programs
and see how many students were targeted as an example.
But we can compare these two because of the relationship.
So we don't lose that level of detail
like we would in a standard aggregate join.
All right,
so moving forward a couple of other conceptual examples
then we'll get into Tableau.
All right.
So say that you work in fundraising
and post-secondary institutions
typically employ people to encourage donations
from interested stakeholders.
It's often called fundraising development or advancement.
Now Tableau's relationships can be as simple
or as complex as we'd like.
This is a little bit more complex.
So let's say we were trying to answer a question
focused on what proportion of our gifts
are considered major.
This would be defined by our targets,
which you see on the right-hand side.
What proportion of the donations come from our alumni?
Well, if we wanted to answer that,
we'd need to know first off our donations,
which we can see with gifts.
And then we need to know of those who donated
who graduated at some previous point from our institution.
Well, we can do that with a relationship model
and then last but not least, we can combine these
with our campaign goals to determine what proportion
of our campaign goals were met by major gifts from alumni.
Another example would be the proportion
of building energy targets
that are attributable to each student.
So we've got the physical layer for our rooms
that consists of classrooms,
which are located in buildings,
which are scheduled on particular days for example.
we've got energy targets, which is very high level.
We've also got courses that are offered in particular rooms.
And then we have students that take courses
that are offered in particular rooms.
So bringing these all together means
that we can compare this kind of
fairly complex question really easily
but we could also just say which room is attributable
for most of our building energy usage.
So relationships are much more nimble
than standard joins because you can change on the fly,
which parts of the data layer you're looking at.
These examples got a bit pressed for time. Additional versions on www.plaid.is Okay, so let's talk about some actual data in Tableau.
So imagine if you will
that your government has decided to set targets
based on the number of students, completing credentials
who say that work integrated learning,
help them find a job after graduation,
based on the national graduate survey.
Now, this sounds a little far-fetched,
but there's always new and interesting ways
that governments measure targets.
So maybe this will come at some point in the future.
This is the national graduate survey.
It comes from statistics Canada.
I use Tableau prep to import this data
and do some reshaping of it in particular,
to deal with
sort of a nested hierarchy that is here.
So the Tableau prep part will be the focus of
a future webinar (see www.plaid.is)
which we can see here.
So you can sign up for that.
You can scan this QR code with your phone if you want.
And this link will be displayed again
towards the end of the webinar.
So if you're interested
in using Tableau prep builder to modify this data
and write it to a database
that's going to be the topic of our November 26th webinar.
Example number two is on LMS interactions.
These number of students is actually wrong
Because I found a better dataset - apologies for the oversight
and let's get into Tableau.
So example number one
is going to focus on the government targets.
So let's see what that would look like.
And bear with me just a second
while I open one more instance of Tableau.
Okay.
Okay, so they look almost the same,
but on the left-hand side of your screen
is Tableau 2019.4.
And on the right side is Tableau 2020.3.
So the one on the left
does NOT have the relationship data model.
The one on the right DOES have the data relationship model.
Okay,
All right.
So step number one is we want to bring in some data
about our targets.
Now, as mentioned, I created this using Tableau prep.
So my data file is, or sorry.
My two data files are the national graduate survey
which are the actual results and the NGS government targets
which are my fake targets that I invented
for the purpose of this demo.
They're hyper files
which are highly optimized for Tableau's purposes.
So what I'll do first is I am going to drag the graduate
onto both Tableau 2019.4 on the left
and Tableau 2020.3 on the right.
What you'll see right away is that
there's some subtle differences in design.
So on the left is what we call the physical layer.
So in this context, the national graduate survey exists
on my local machine as a Tableau extract.
On the right this is the logical layer.
But if we want to get back to the physical layer,
we can either double click on the table
that we're looking at,
or we can hit the dropdown and say open
and then we get something that's almost the same.
So I'm going to back out of that
and go back to the logical layer.
So now the next thing that I want to do
I missed a very important step.
Apologies, let's go back.
Let's go back one step, see if,
see how well this will work.
Okay, so one thing we're going to do is to try
and show what's happening behind the scenes in Tableau.
So if we click on the Help menu
and select Start Performance Recording
for both of our workbooks,
so help menu settings and performance
start performance recording.
We'll see how this goes for this example
I'm now going to drag extract onto each view.
So Tableau will read from it.
That part I worked fairly well
'cause this has already been done once
I'm then going to drag my targets in
to both the left and the right.
What we can see now
is that there'll be slightly different behavior.
So on the right
On the right nothing is dragging for me at the moment.
There we go.
Sorry. Okay.
So it works.
All right.
So on the right, we need to tell Tableau
how our two extracts are related.
And so I'll do the one on the right first
for the newer version of Tableau.
So we have a field of study,
is the first part.
Geo is the second,
and I believe it's not level of study.
Okay, so it should be that
if I made a mistake I'll come back to it.
Oh, and we can add DGUID just to make sure that, you know
none of our data has the same name across provinces
but it's pretty accurate.
So I don't think we have to worry too much.
All right.
So now we have these three relationship keys.
And on the right, we can do the same.
And now we go to worksheet number one,
and we can see that our layout is a little bit different.
So let's quickly put this together.
I'm not going to create the DNA chart
just in the interest of time.
I see we're almost at the end already.
So if we just create a
government target as an example
I actually will create this as a data table.
So we can say geography for each province
and field of study.
And then we can add our government targets.
What we can see here.
If we look just at Alberta for that first row,
is that Alberta has got way more data than we intended.
And this is effectively the by-product
of the physical model.
So Tableau is essentially multiplying the target
for agricultural in Alberta,
by the number of students
that responded to the survey more or less.
So if we add in the number of students
just as a sake of comparison.
So for sake of comparison,
what we see as happening here
is that we're getting multiple entries across all of this.
So we're getting lots of data duplication.
So the relationship model is getting us the accurate target
which enables us to compare.
We can say, Oh, it looks like Alberta is
above government target in architecture.
In fact, it looks like in this case,
Alberta is above target for,
all of our disciplines,
but there are other places that are below target.
So that is example number one.
Now our performance recording
has been running in the background.
I'm going to skip over that in the interests
of the simpler example for it.
So we'll just stop that
but what happens is Tableau produces a workbook.
You can see it easily.
Okay next example here,
we're going to connect to a,
to a LMS set of data.
So in this example, we can look at students and courses.
So if we drag our student info
onto both versions of Tableau,
and we turn on our performance recording
this time I'll show you the difference.
For those who have to run
to another meeting, this is recorded.
You can see the rest, if you want.
I'll wrap this up in about five minutes
for those who are to stick around.
Okay, so we now have our student info.
We're going to join it to our course info.
Okay, sorry.
Which for this purpose will be student registrations
and on the right we're going to
relate it to student registrations.
Okay, so student ID and then code module.
And code presentation, just to be on the safe side.
So now we have that relationship
and our join on the left is on code module
and code presentation.
So now we can go to our worksheets.
So what I'm going to show here is actually
a really simple example.
So if I want to know how many students do I have
By the particular course that they're registered in,
On the left I can use number of records
and then I can divide it
based on their course code.
On the right Tableau now does account by table.
So I'm going to use the count from student info
and then I'll use the code module as appropriate.
Okay, so,
on the right-hand side
we'll look at our performance recording.
And in the interest of time,
I'll skip over the other one,
but I'll describe what the difference is.
So this is Tableau's performance recording
for sake of demonstration, I'm going to say, keep anything.
And I'm only going to keep the query executions.
So our first query as an
Okay, here we go.
All right.
So this is probably the first query,
but what we can see is
that it's doing a count
from the student info table
In particular, the part to focus on
is that even though I have a relationship
to the student registration table
because I didn't use registration on the view
for my initial bar chart, before I added the course module
onto the screen,
Tableau doesn't need to join to that table.
The opposite would happen in the old version of Tableau.
So if I show the performance recording there
what it's going to have in addition is
basically the same query
that it's going to say, join student registration
on whatever the keys were in between the two tables
even though I'm still using the same count.
So this means you're hitting your database
way less hard to answer your questions in particular.
Now in the interest of time,
I'm just going to move back to the presentation
and talk about a couple other key concepts
and move forward from there.
Okay.
So last but not least,
All right, last but not least.
We did a previous webinar
focused on the BC higher education
accountability dataset redesign.
And in particular, it made use
of a technique to draw curved lines across the map
which was really just for the purposes of learning.
Now in Tableau, in order to create curved lines like this,
you need to do what's called densification.
It means add a bunch of data points
for any record that I actually have.
If we use densification
we can have the many data points for our curved lines
but we can have only the one data point
for our enrollment by institution.
If we used standard joins for this purpose,
this is what would have happened.
This is what it would look like.
Come on,
there we go.
What would have happened
if we used a standard join without additional conditions
is that we'd be multiplying our enrollments by 100,
because I have 100 data points per point in my data,
which has a bit of a problem.
There's workarounds for it, but they are all work arounds
versus the relationship data model.
Got it right out of the box.
There's a ton of other examples as well,
but in the interest of time, we'll wrap this up.
Pat, if you can open up the poll
for the future topics one, that'd be awesome.
Okay, so on your screen now is a multi-select question.
If I did it right in zoom
we'd love to hear your feedback on what topics
may be of interest to you at some point in the future.
Okay, so in summary relationships
and Tableau in my view are a game changer.
The reason for this is that they're more versatile
than conventional joins.
They answer many questions
with one data source and these two pieces together
in particular will keep your it department more happy.
So in my own life, as an institutional analyst
it was fairly common that I would hear feedback
like can't you use the data warehouse properly.
Why are you making one massive data source
to answer all your questions?
When you could be making 10 smaller data sources
it'll be easier on the database.
You'll get better performance.
And I went, well, I don't have the time for that.
Tableau relationships help with that.
Now you can set up a data sources,
optimized for your data warehouse
among many other tasks like actuals versus targets.
It is so, so much easier.
And you can answer myriad questions with them.
You also won't have the problem
of missing or duplicated data because you made a mistake
with your join and you never realized it.
So I would highly recommend it.
It looks like we got some poll responses, so that's awesome.
Thank you all for your thoughts.
So it looks like a deeper dive
onto Tableau calculated fields as popular
set and parameter actions are popular.
And those are kind of followed up by
integrating Tableau with Python
and split between beginner and advanced dashboard design.
So our winner is set in parameter actions,
but of course, we'll ask you for feedback
after this session.
-: It also appears that the poll
only allowed for one response.
So feel free to send us a chat message
or an email at info@plaid.is
if there's additional topics
beyond what you could choose on the poll.
-: Thank you, Pat.
Okay, so Andrew's rules of thumbs
for Tableau relationship use relationships
whenever you can.
The only time you need to use joins in the physical layer
is if relationships do not work
for the problem you're trying to solve
in my own experience this is really unlikely.
A distant third is if joins don't work, use data blends.
The use case that I can see for data blends
is when you deliberately want to have a different relationship
on many different worksheets, then it might be helpful.
But as a general rule, use relationships
Use relationships, use relationships.
As mentioned, we have another webinar focused
on Tableau prep builder coming up November 26th,
same time, same place.
You can scan the QR code that's on the right
or register at the URL provided.
This one is focusing on writing to a database
using Tableau prep
and it's going to use similar data to what we use today.
Tableau prep builder recently got the capability
to write to a database.
So this may save you from creating tons and tons
of CSVs or hyper files.
You can actually publish straight to the database.
So cool new technology.
All right, last but not least.
Thank you all so much.
I have tried to keep it in 30 minutes.
I failed slightly and my apologies on that,
but I am working on it.
I'd very much appreciate your feedback on the session today
and things that you think we could do in the future.
Feel free to respond
to the form that we'll send out or to email me directly.
Andrew@plaid.is any and all feedback is welcome.
The other part that I'll mention is we need you.
So this is intended to be a community-driven
higher education Tableau user group.
We need people that aren't just me presenting.
So if you would be interested
or even curious about what it's like to present,
reach out to me, I'll walk you through
every step that you need
and I'll even give you some tips
that will keep you on time in ways
that I sometimes struggle with.
So with that, I will say, thank you so much.
Merci. ありがとうございます
Thank you for being here.
And have a great day.
Stay safe, everybody.