Andrew: Good morning from Vancouver, Canada.
Good afternoon and good evening
to those joining us from around the world.
Thank you very much for joining the workshop,
The Rise of the Logical Data Fabric
in Institutional Research.
It looks like most of our participants are here,
so we are ready to begin.
Today's workshop is intended to be hands-on
and interactive, but it will be recorded.
We'll send this to you following the session,
and you're welcome to contact us
if you need a chance to go through some things again.
All right, without further ado, here we go.
Our presenters today are primarily Patrick Lougheed
from Plaid Analytics.
Pat is our Chief Technology Officer
and the data guru behind what you're going
to experience today.
We also have Suntosh Murthy
and Andy Kaizmaier from Denodo who will be helping
in the background, questions that come up
with the platform, et cetera.
And my name's Andrew Drinkwater.
I am mostly hosting the webinar
but I'll try and help
with questions where I can as well.
A tiny bit of housekeeping.
For those of you familiar
with the Zoom platform, this is probably old hat,
but the webinar is being recorded for your reference.
We'll send this out as soon
as is available following the session today.
There is a question and answer button
which you'll discover at the bottom of your screen.
This would probably be the most ideal place
to ask your questions,
and we'll try and answer them as best we can.
In the event that we're not able to answer
through text responses for this
or through the live presentation,
Suntosh and Andy will follow up
with you directly on how to connect
with them individually for help.
If you want to chat
with the other attendees or have a message
that's valuable for everybody, feel free to use the chat.
Plaid Analytics helps educational institutions gain
insight from their data.
We provide data
and analytics solutions, business process improvement,
and training and workshops.
And this is an example in a condensed form
of one of those workshops.
I will turn it over to my colleague, Suntosh from Denodo
to talk about data virtualization
and what Denodo is.
Suntosh: So if you're not familiar
with data virtualization,
it is essentially a logical data layer
that helps integrate various data sources
as a single unified view.
Makes data easier to consume, whether you're in a BI tool,
a web portal, a data science tool, or whatever.
So does this all
without physically moving data around.
The way it works is there's really three main capabilities
and the first is really connecting.
So the first thing we do is we connect
to various data sources.
We have hundreds of out-of-the box connectors.
We take that, the metadata,
and we combine them
and transform the data into a single unified view
that can then be consumed
by these various data consumers I was just mentioning.
Again, they can be any of your consumption tools
that can access data via ODBC, JDBC, REST, OData, et cetera.
So the value that it provides over say other alternatives
is you don't have to physically move data around.
You're not risking issues of data breaches
and sets of data being copied across the enterprise.
And it does it in an agile more self-serviced capability.
Andrew: And I will turn it over to Pat to talk about
what we're going to go through in today's workshop.
-: Great.
Thank you.
We'll go through a couple of the different ways
of interacting with Denodo
and talk about a number of its capabilities today.
So we'll start
with talking about the Design Studio
which is where you create different views
of data that you can then use in other tools.
We'll look at connecting to a couple of different kinds
of data sources.
These would be data bases, spreadsheets,
and a cloud based data source in our case.
And look at executing queries as well,
how to actually get data out of those views
that we've created.
Denodo has some fantastic capabilities around providing data
as a web service,
and we'll take a quick look at that,
and then look at the Data Catalog,
which is a great data governance tool
and a data discovery tool
that can be used through the enterprise.
We'll also do a quick demo around connecting
to Denodo data using Tableau.
If you have Tableau, you can do this yourself,
or if you don't have Tableau installed
on the machine that you're using today,
you can just kind of follow along
with me as I do that.
We'll also have a little bit of a discussion
about some other capabilities
of Denodo around other Business Intelligence tools,
caching, security, that sort of thing.
But we'll try
and keep it pretty simple for the actual workshop portion.
And then of course questions, either at the end
or if you have any that come up as we go,
we are happy to answer those.
Let's talk a little bit about the data
that we're going to be working with today.
What began as an idea, that is,
can we fake up some data that we can use
in workshops like this ended up transforming
into a full-fledged imaginary university.
We want to introduce to you today Plaid University.
This is a institution that is now 11 years old,
launched in 2010 or so.
It's had a huge growth trajectory.
It's about 23,000 learners now,
and offers a really wide variety of programs.
So we have undergraduate and graduate studies.
We have professional
and graduate studies in both law and medicine.
And we also do a variety of trades.
So we really run the whole gamut
in a way that many institutions don't,
which gives us an opportunity looking
to all sorts of different aspects of institutional data.
As a startup, we chose to use a variety
of information systems suited to purpose
rather than just one monolithic enterprise
resource planning system.
As our student information system we have Banner,
and because our IT folks are crazy, crazy hackers
and doing things differently, we don't run it on Oracle.
We run it on PostgreSQL,
which you don't have to worry too much about today,
but it's just worth mentioning.
As our learner relationship management system
we have a Microsoft Dynamics-like tool.
For our learning management system we use Canvas.
So both of these being cloud-based.
You can see where getting information
out of the cloud would be very important to us.
We have a data warehouse that lives
in Amazon Web Services on PostreSQL.
And then of course, like everyone,
we have spreadsheets where we keep various information.
In particular the spreadsheet that we're dealing
with today has information on our addition targets
set for us by our senate.
As a data driven institution,
we really needed a way to bring together
all of these different data pieces into one place.
And that's where we can leverage the capabilities of Denodo,
which really helps bring data
from all of those disparate data sources into one place.
We think with that, we will start on the actual demo itself.
We're going to start
with logging into the Denodo Data Studio,
Design Studio, sorry.
You should've received an email from us this morning
that provided not only the URL to access this
but also a username
and password that you can use.
What we've done here is we've actually
created a number of different databases
within our Denodo server,
and each one of you will get,
essentially gets your own database.
So anything you do in there
for which you have full permission, you can,
if you don't want to follow along
and try your own thing, you can do that.
You can create views or what have you.
But nobody else can see that.
If you didn't get a chance
to see that email this morning,
I would suggest you go find it now
because it will have the username
and password that is unique to you.
The URL to log in here is
denodo.demo.plaid.is/denodo-design-studio.
If you enter that into your browser,
then you'll get a log in screen that looks like this.
So we can enter the username
and the password provided to you in that email.
And I'm going to log in as
one of these workshop users myself.
Then you can leave the virtual data port server listed.
It may say local host here,
in which case, just to make something a little bit easier
down the road, rather than leaving it as local host,
I'd like you to enter denodo.demo.plaid.is
so that ultimately your virtual data port server
looks like this.
Okay, I'm going to sign in here.
And what I'm going to enter into here is the,
as mentioned the Design Studio.
In may ways, this will look like the web based version
of a fairly typical desktop based tool.
On the top up here we have a menu that has a variety
of capabilities,
and some of that we'll talk about,
but most of that we don't need to worry too much
about today.
Over here, on the left-hand side, we have the ability
to browse through the different components of our server.
We start here
with a couple of databases that our workshop user
has access to.
You'll see the admin database,
which in our case is largely used for authentication
and caching only.
We won't look into that too much today.
But there's also a workshop database, and this
should correspond to the username that you're provided.
So for workshop 01
you should see a workshop 01 database here.
I've logged in as workshop 20.
And beside, to the left-hand side
of that database there's a little plus sign.
And if you click on that, you can see that there's a number
of folders here.
There's a little folder icon.
And these function in a way similar to folders,
whether it's on your Mac or Windows machine,
that allow you to organize pieces within Denodo.
We've setup here a way of separating
out the different types of elements that we have in Denodo.
If we click on the plus
button beside data sources,
we can see that there's a couple
of different items listed here,
a couple of different elements,
and these are all data source connections
that we have to different places
where our data lives at Plaid University.
Let's just have a look at one of those quickly.
There's three vertical dots beside.
I'm going to look at ds_banner.
So this is the data source connection
to our Banner database.
And if I click on those three dots, then I get a menu here.
I can also get into it just by double clicking on it.
And this will open a brand new tab for me, telling me this
is the data source I'm looking at, and providing me
with some information.
So we can see very quickly here
that we've got a connection to a Postgres database,
where that database lives.
In this case we have similar to our Denodo hosting,
we have postgresthedemo.plaid.is.
Can I increase the size of the screen?
Yes.
I hope that works better.
Let me know if we still need to go a little bit bigger
and now of course I need to redo my arrows here.
We have a Postgres database
that connects to a particular server,
and we're using just a Banner user
and it doesn't show us the password actually
for security purposes,
but we can see that's how we're going to go.
We can...
Okay.
So I will just step back here quickly.
What I did to get here was that I clicked on the plus button
beside workshop, whatever the appropriate number is,
and then clicked on the plus button
beside where it says 1-data sources.
And you should see three data elements
within data sources.
There's a banner, a target,
and then open house registration
which we'll get to in a minute.
And then for banner, you can either double click on it
which will open this tab,
or you can click on the three vertical arrows
and then select open.
They'll get you to the same place.
All right.
This just tells us about the kind of connection information.
It doesn't actually give us the data or show us any
of the data components to them.
We have two other connections.
And we can see by these icons here
that these are actually different kinds of connections.
As Suntosh mentioned, there's lots of different kinds
of data that Denodo can connect to.
Our banner data source here as we saw here is a connection
to a database.
Our targets which has this quotation marks around it
connects to what we've got
is a comma-separated values file, so a text version
of a spreadsheet.
And I would show you this in my browser,
except my browser likes to download CVSs
and won't actually show it to me.
But if you look at the data behind it,
I'm sure everyone's at least seen CSV files before.
The same connection type that Denodo supports can connect
to other kinds of flat files.
So if you have a tab-separated file
rather than comma-separated,
Denodo is perfectly happy to do that.
If it's a structured flat file like many of us
are used to when reporting data for example
to Canada or some
of our provincial data warehousing systems
that need specific fields to be specific lengths,
Denodo can do that as well.
We've also got another connection here,
the curly braces shown as open_house_registrations,
and this connects to a JSON data source.
This is one of our cloud data sources.
So I'm just going to do the same thing here.
I'm going to double click on ds_open_house_registrations
and I can see here that my data route rather than being
to a database I'm actually telling it
to use a web client that ultimately this data needs
to come from somewhere on the web.
And similarly too with our folders
and our databases over here,
we can see some of the configuration is
within these different panels that we can open.
So if we click the plus button beside configuration,
then we get the actual URL where we can get this data from.
Now, assuming I've done everything properly,
this is already configured for you
and you can just verify here that we are in fact
getting this off of a webpage.
But you can also see here there
is a bunch of other functionality that Denodo supports.
And in the interest of keeping this pretty simple,
we're not getting into this today,
but we can do things like authentication
where if a specific username
and password or a different kind of authentication,
for example, many cloud-based data sources
now require something called OAuth 2.0.
This is no problem for Denodo.
It can handle that out-of-the-box.
There's also some cloud data sources out there
that actually do what's called pagination of results.
So if you say for example from a system,
give me information on every person
that's in your system, whether that's a student
or a staff or a faculty member, what have you,
and it'll say here's the first thousand.
Ask me again for the next thousand
and then the next thousand.
Again, Denodo can handle that out-of-the-box.
We just have to configure it
and tell it exactly how that system needs that information.
So all of this is really out-of-the-box,
Denodo can handle nearly any system
that we can throw at it in terms of where our data lives.
Okay, so those are connections.
Let's look a little bit at the actual data.
I'm just going to close these two tabs.
Similar with many other tab-based systems,
there's little X icon here,
and we can close those.
So what I'm going to do to actually start looking
at a little bit of data is I'm going
to press the plus button here beside 2-base views.
And the base view in Denodo terminology is simply where,
it's a direct connection to data
in our underlying data source.
It's not data that Denodo needs putting together.
And we'll come back to what are called derived views
in just a minute.
So you'll see here what we've got is a variety
of different banner tables.
For those of you that use Banner at your own institution,
some of these may look familiar to you.
If you don't run Banner at your institution,
you can kind of pick one of these tables to look at.
There's a question about virtual views of Banner tables.
So if there's a view that exists
within your Banner database, Denodo can connect
to that as well.
So it doesn't have to be a table in the database.
It can be a view.
It can be a stored procedure.
Basically anything from your database
that returns data, Denodo is happy to connect to.
I'm going to look at,
and feel free to open one of these tables.
It doesn't really matter which one.
I am going to pick one just in the interest
of being a little bit quicker, this.
So I'm going to look at our
college table.
Again, I picked the table that I wanted to see.
You can pick any one of these ones such
with bb_banner_.
I picked the stvcoll
which holds validation information on our,
in our case our faculties, so what Banner calls colleges.
And again, similarly, I could double click
or I could click on the three vertical dots and select Open
and I'll get to the same place.
So this base view gives us information
on where this information is coming from.
So again, we can see that this is coming
from our Banner data source.
And we can see the different fields that are associated
with this.
One of the things that you'll notice here
is that there's a element here that
has a little key beside it in the PK column.
And what Denodo has done is it's asked the database
to tell it as much as possible about the actual data.
And part of this involved saying what are the primary keys
of this data.
Now one of the things about Banner
and a number of other student information systems
is that these, they don't always
have primary keys defined at the database layer.
I have, we have at Plaid University chosen to define these
at the database layer in our Postgres version of Banner,
but if connecting to a real Banner database,
you might not always see this.
But you can always define them in Denodo,
even if they don't get defined at the actual database there.
And that goes not only for database data sources,
but you can do that for cloud data sources
or your spreadsheets or what have you.
You can say, this is the primary key.
And Denodo will pass that information on to other tools
like Tableau or a SQL client or what have you.
We can also see the different kinds of
or the different fields that we've got
and then what kind they are.
So we have some text fields here.
We have some date fields.
We have a numeric field, an integer.
We have numeric ones that are more specific than that
or they're decimal fields.
So all of that information came directly from our database.
If we need to, we can actually edit some
of this information.
And within our tab here, we can actually click on Edit.
Ah, so the question that just came
in on the Q&A actually is just where I'm,
the start of that question I'm about to answer
and we're going to come back to that.
The question is from a data governance perspective,
how can we augment or enhance the data view
with meaningful business descriptions?
The first thing is by clicking on Edit here.
One of the things that we have here
is actually a description field.
Most databases aren't going to have,
in fact, I'm not sure that any database
will actually have descriptive information
about the different columns.
Pam, there is no need to be sorry here.
I appreciate the question.
It's good
and suggests that we're delivering the workshop
on the right topics given that you're asking about it.
Okay.
So description.
What you'll see here
is a little pencil icon besides description.
If you click on that, you'll get a box
that will pop up where
you can actually edit the field description.
So we can say Banner code for faculty.
If we hit OK, that will show up there.
Now the really,
one of the really neat things about Denodo is
if we define that on a base view
and then later on when we'll talk about derived views
in a minute,
but when we create other data tables that are based on this,
that information will actually flow
through those derived views as well.
So they'll pick up the fact
that we've put a description here
and that will show up in a derived view as well.
Okay.
So next.
Exactly.
That data then becomes part of our data lineage.
So what we can do from here, let's just quickly look at,
I'm going to save my changes here.
And I didn't give myself enough privileges.
Okay.
Apologies.
I'm not going to be able to save that,
but we'll come back to the data governance piece
a little bit more in a bit,
so let's hit Clear Changes instead
and then we will go over to summary.
The other piece here, let's click on Execution panel.
And this will bring up a panel
where we can actually run a query.
Part of the reason that I picked this
is particularly the table is that
there's not a ton of data in here.
So if I press Execute,
we should get an answer back
from our database pretty quickly.
What Denodo has now done is taken that connection
and gone out to the database
and said, "Please give me all the information
that's in that table."
This is similar to what we see
with a standard SQL client here.
You can see we've got nine different faculties in our setup.
Various ones around.
We've got business
and education, engineering, science, law,
continuing studies, et cetera.
Okay.
So that's a base view from a database.
How about we look at something
that's a little bit different?
Let's go with our targets.
If you scroll, there's many tables in our database.
I think I created 58 or so.
So you might have to scroll down a bit,
but if you do that, you should find bv_targets.
And again, if you double click here
or if you click on the three vertical arrows,
or sorry, three vertical dots
and you press Open, we're going to get some data here.
Or sorry, some metadata about our targets based view.
And our data source here is our targets data.
So this is our connection to a CSV file,
a comma-separated values file.
And we can...
Ah, okay.
Depending on how many tabs you have open here,
sorry.
Depending on how many tabs you have open here,
so I'm just going to open a couple more,
just so you can see how this...
What it will do is it will start collapsing tabs
because we've got too many open.
On the last tab there's a little down arrow here.
And if you click on that arrow, you'll see all the ones,
all the additional tabs that you may have opened.
So depending on your screen resolution,
this will happen at a different number of tabs.
So for me, beyond four tabs, I get this little down arrow
and I can go see the additional tabs.
For you it might be less or it might be more.
Totally dependent on your resolution
and your zoom level.
So you can always get back to
whichever other one you were looking at
by using that little down arrow.
Okay.
So our CS...
because a CSV file inherently doesn't
have primary keys, Denodo actually allows us
to define those primary keys.
In this case we set our targets or our senate
or general faculty's council
or whatever it may be in your particular jurisdiction
and institution have set our targets by term,
by level of study, so this would be trades, undergrad,
law or medicine.
Our senate chooses not to do this for graduate studies.
Our faculty, a particular program
and then we have two national statuses,
one for domestic students
and one for international students.
And then we've got a particular target.
For those of you that come from an institution
that has PeopleSoft,
you may notice some PeopleSoft like fields here.
And that's because our crazy,
crazy hackers at Plaid University
are also potentially looking at moving from Banner
to PeopleSoft.
So we've set up our target data source
so that it's got the appropriate fields
for both of those systems.
As a side note,
essentially when we created all the fake data
for Plaid University,
we created in both data schemas, one for PeopleSoft
and one for Banner.
We're using Banner for the workshop today,
but we could similarly take this out
and we can do the same workshop
with PeopleSoft like data pretty easily.
Okay.
Similarly to before, we've got the same set of options.
I'm not going to look at editing or anything like that here.
But if we press the Execution panel here,
make it a bit bigger, and execute here.
We can see we get a bunch of data back from our CSV file.
Let's see, at the end here we've got targets
and we've got national status
and programs and all of these various different pieces.
So this is coming straight out of a CSV file.
But what Denodo is doing for us is setting it up
so that we can create data that comes out of a database,
or data that comes from anywhere else.
Okay.
So let's look at data that's a little bit different.
One of the other data sources
that we've created here bv_open_house_registrations
actually comes from a cloud based system.
So this is actually an extract
from our learner relationship management system
where we set up the events, the different kinds
of events that our prospective students might get to attend.
In this case, what we've done is we've taken out
just the open house registrations that we've done.
As an extract, data coming out of our system,
it looks like...
Here we go.
It looks like this.
We can see we've got a student ID either
in the form of a PeopleSoft one or a Banner one.
And then a student can have registrations
for different open houses.
And we've kind of indicator for whether someone
has checked in or not, and the date
and the particular event.
So if we look a little bit further down here,
we've got a prospective student here who registered
for two open houses
and then checked in for the first one
and decided not to attend the second one.
If you're not familiar
with JSON data or cloud based data,
JSON is a little bit different
from what we're used to working
with in a database.
In particular, a JSON document like this
that comes out of most cloud based systems
is in fact treated as a single, it's a single row of data.
And in this case, what we have is one row of data that has
within it, actually, it's a single object
and it's got an array.
So there's hundreds or potentially thousands
of different entries within this array,
but in the JSON document itself
it just comes back as a single object.
And so in Denodo, that's what,
essentially that's what we'll see in the base view here.
So I'm going to open up my bv_open_house_registrations here
and I'm going to go to the execution panel
and I'm going to press Execute.
And we'll see what comes back here is not hundreds
of rows of data or thousands, but one.
It doesn't show as any data here,
but we get a little I icon, an information icon.
If we click on that, we'll get a pop up
that shows us all of the different elements of this array.
So if we look at the fourth one here,
I'm just going to press the plus button beside before item,
and we can see that our prospective student here
and if we click on the plus besides registrations,
we can see they had two registrations,
one of which they attended
and one of which they didn't.
So it's the same prospective student we saw
in our JSON file just now represented
as an object in Denodo.
Okay.
Well, that's all well and good,
but this doesn't look like traditional database data,
and some of our end tools SQL clients, Tableau,
Power BI are going to look at this, go, "What do I do
with this?"
This is where Denodo's capabilities
around data transformation really come into play
and where what are called derived views
in Denodo terminology come from.
A derived view is essentially any view in Denodo where we
are doing some transformation to data from a base view.
What we'll show you today is a couple of different ways
that we can transform that data.
But what we're going to start with
is by looking at how we can get this data
into a form that's going to be much more helpful to us.
So I'm just going to scroll up here on the left
and I'm going to close my data sources
and base views folders,
just because they take up a lot of space,
and I am going to go into the derived views.
So I've got a couple of different ones here.
But the one I want to look at is open_house_registrations.
Similar to before, I can click on the three vertical dots
and Open or I can just double click on that.
This looks very similar to what we saw before
with the base views.
But if we press Edit here, we can see that instead
of just getting a list of fields that come
from our base data, we actually
see a bit of a data model here that allows us
to tell Denodo how we want our data to be transformed.
In this case, what we're doing is what Denodo
refers to as flattening.
So we are flattening out an array.
So each element of the array becomes its own row of data.
We don't need to worry about how to do that now.
If we go back to Summary
and press Execution panel
and we are going to,
apologies, I might be going a little quick here.
That was, I went back to Summary,
and then I pressed Execution panel,
and now I'm going to press Execute over here.
And that data that before came to us as one object
with a varied array into it has now been flattened out.
So now, the derived view that Denodo was providing to us,
looks much more like data that we'd be used to working
with in Tableau, Power BI, SQL Tools, wherever.
They're all going to look at this data
and they go, "I know what to do
with this now."
And we can go further from here if we need to.
We can create derived views on top of derived views
to ultimately get to a place
that we've got all the data together in a format
that we need ultimately to be able to do
whether it's our analysis or our reporting or what have you.
Did I lose anyone?
Yes, exactly.
This is,
in just a second here, I'll show you another derived view
or two that will utilize this data
that we've created here in derived view
in another derived view.
So yes, it's like multiple passes
with SQL.
You're kind of taking these components
and you're building them together to ultimately you end up
with data looking the way that you'd like to.
Speaking of that,
let's look at the applications derived view here.
Similarly, I'm just going to press on my three vertical dots
and press Open.
And in this case, I'm going to press Edit again.
This one we can see is a little bit different.
This rather than being a flattening is actually a join.
So what we're doing here is
to bring together different Banner tables
that have information about a applicant
who has applied to study at Plaid University.
So we start
with a Banner table that tells us about applications.
We join in other Banner tables that tell us
about the decisions we made about that application.
Did we admit the student?
Did they ultimately register?
Did we deny them?
Pieces like that.
And then we bring in various validation tables
that give us things like descriptive information
of what faculty, what program, what term did they apply to.
And then you'll notice down here
that we've got that open_house_registration,
that derived view that we just looked at
is part of join here.
Rather than only having to use base views
to create derived views, we're utilizing other derived views
and ultimately building up to a data element
that we can use in our analysis.
We can of course, as with any join,
we can get pretty granular
about the particular join conditions,
whether it's a left join or an inner join,
all of those different components.
But Denodo and Design Studio in particular allows us
to do that fairly simply.
Now, I've already defined all the join conditions here.
I don't need to change any of this.
But if I did need to change things, Denodo provides me
with a drag-and-drop way to do it.
All we need to do is tick the appropriate field here
on the left or on the right,
and you can track that over to the field that
should be joined to on the right.
I'm going to completely mess everything up
if I actually do that, so I'm not going
to make any other changes.
So I'll show off the join conditions here.
So we can define here what kind of join we want.
If we want to get really particular, we can tell,
even tell Denodo what kind of join method to use.
Generally, we suggest not touching any of those
because Denodo will determine those dynamically.
But you can also directly edit the join condition.
So if you just want to type them in by hand,
you can press,
sorry, I think I did that very quickly
so I will do that again.
If you just press the Edit button here,
rather than using the drag-and-drop interface,
you can also directly type in elements here.
For example, I have one condition in this particular
join that is looking for a static value
rather than comparing two different fields.
And so that one was typed up manually.
Anything that you can do in a normal SQL client
to create a join you can do in Denodo.
You control all of that of course.
Okay, let's look at one more derived view.
And that is going to be applications metrics.
And what this particular derived view does
is a Group By.
We can see that if we press Edit here.
We can see if we go to the Group By tab,
we can see we have our grouping conditions here.
So in this case what we're doing
is we're taking all of that application,
the information that we pulled together in one spot,
and we're grouping it by term
and by faculty and by program,
and the descriptive versions of those,
and ultimately coming up we're calculating
some metrics here.
So if we go to Output here,
so one of the things I haven't mentioned yet
is Denodo's capability to create calculated fields.
Everything that we've been playing with
so far showing so far would be around data
that came straight out of basics.
But we can also do calculated fields.
We can create calculations here
within Denodo that then get passed
in through our analysis chain.
We can see we've got a couple of fields here
that weren't part of our Group By.
So I'm just going to look at admitted here
and, sorry, I'm going to too fast.
If you click on the little pencil beside admitted,
we can see that we've actually
got a calculated field here that's doing a sum
of all of the admitted field from the applications table.
Nested select statements, I'm going to,
yes, that is beyond what we will do in this particular demo.
Looks like Andy is responding to that one.
Andy or Suntosh.
The short version is yes, you can do it in a WHERE clause.
I have found it may be easier to create kind of layers
of derived views just so that you understand exactly
what it's doing,
but it is entirely possible to do things
like have nested select statements.
So yeah, maybe I'll ask Suntosh
and Andy just to respond to those questions
in the Q&A if you could.
Okay.
I can see here we've got our Group By.
We've got our aggregation fields.
Let's go back to summary for a second.
And then we'll click again on execution panel
and let's make it bigger.
And we're going to execute them.
Now you'll notice here that this one takes a second to run.
And there's a couple of reasons for that.
One is that we're actually pulling data
from all three of those different data sources.
No, sorry.
We're pulling data from two different data sources here
and then we're putting it through a couple
of different commands.
We are pulling together data from our database
and from our JSON file.
So if you scroll to the right here,
you'll see that some of the metrics here
involve our open house, how many open houses,
how many prospective students registered
and attended those open houses.
And we didn't run open houses until 2019,
so in all likelihood these metrics that you're seeing
are actually for 2010 when we don't have open house data,
and so they'll be zeros.
But we're pulling information from our Banner database
and we're connecting that
with our JSON data
and we're joining that
and then we're aggregating that.
We're doing Group By's.
In terms of performance,
one of the things I'll note for demo purposes here,
we don't have a particularly ScoopM database
or a ScoopM web server, or even a ScoopM Denodo server
for that matter.
These machines that you're utilizing today
are not tuned for the kind of performance
that we would expect in an institutional environment.
That's one piece.
And there are some kinds of data sources
like cloud data source, JSON data sources
that just inherently aren't quite as quick as a database.
They're not really intended to replace the database.
So getting data algorithms can sometimes
be a little bit slow.
What we'll show in a minute here is
how we can speed that up.
Bear with me one moment.
Yes, it is definitely not a demo
unless you manage to do something that breaks up.
Okay.
So this portion may or may not work for you.
So I apologize
and perhaps you can just follow on along on this part.
If I had done this right, when you press the plus button
next to web_services, 5-web_services,
you should have seen an application_to_target element here.
What we've done here is we have taken one
of our derived views.
So I showed you the application metrics.
We then have one other derived view
that takes the data from the metrics
and then joins that with our targets.
We can then publish that as a web service.
What that allows us to do is to expose data,
not only to tools that can talk in a database language,
not just our SQL clients,
not just our Business Intelligence tools.
We can actually expose this if our institution
wants to create like a web front end.
So many websites now, when they grab data
from something that's behind the scenes,
they're not connecting directly to a database.
They're connecting to a web service.
Denodo can create those for us.
So I'm going to, here I'm going to click
on three vertical dots.
If this appears for you, feel free to follow along.
If not, apologies.
In our menu here, we can launch a web service URL.
What that's going to do here is it's going
to open another window for me, another tab,
and here I've got my web service.
What this allows me to do is not only
for the web front ends,
but I actually have a web front end here built
into Denodo that I can use to navigate my data.
So I don't need to load up my SQL plan.
I don't need to load up Tableau if all I want
to do is look at my data quickly.
So I'm going to click on applications_to_target here,
and it's going to pull data out.
And then of course this was the one I didn't turn caching on
and this is what I get when things blow up on me.
So we'll just have to give this a second.
Andrew: You're doing great Pat.
Don't be too hard on yourself.
(Pat laughing)
-: Okay.
So what we can see here is the data that we have
in our applications to target,
we've got this all through a website now.
Even better, Denodo allows us
to define relationships between our data piece,
and I'm going to come back to that in a minute
and show you what that looks like,
particularly from a data governance perspective.
But one of the things that allows us to do is we do it
and web services,
Denodo actually builds in some navigation for us.
I'm going to scroll to the right of my web page here.
In addition to all the data that I've got,
I can also click on various links to other data that lives
within Denodo.
So those base views that I brought in from Banner
that do things like define my validation
or my possible values for levels of study
and terms of entry and programs
and faculties, I can click on those.
So I'm going to click on faculty first.
So it shows me what
is the definition of that particular faculty.
For that row of data I just press back there.
That row of data was for a program that was in our Trades
and Apprenticeships program.
So when I clicked on stvcoll,
it showed me the definition of our Trades
and Apprenticeships faculty.
If on the other hand I went, I've got one here that's
in the faculty of engineering.
So I'm going to click here on stvcoll
and I get my faculty of engineering.
Similarly, now data like that is again,
that's similar to a foreign key or a primary key
in a database.
And I can define those relationships.
But I can also define other kinds of relationships.
My metrics here are actually rolled up
from that underlying application's table that I showed you,
the one that had all those different joins.
I've defined a relationship here between my metrics
and my actual application's table.
So if I click here on applications, what it's going
to do is actually going
to pull an underlying application data for me
without me having to do it.
So you can see the various conditions here
that it's showing us on the data that it's pulling,
but essentially this is the underlying data
that's rolling up to that metric data that we've exposed.
So this really allows us to navigate, it allows us
very, very quickly, kind of dig into data,
simply by defining all these pieces
that Denodo gives us right out of the box.
We can see how different data is related to one another
and then we can actually dig in to that data.
Okay.
From here, we're going to switch tactics a little bit.
What we largely been talking about
is the Denodo Design Studio.
And Design Studio is a fantastic tool,
but it's largely an administrative tool.
This is where you define different kinds of data.
This is not what most people,
many analysts will use to interact
with Denodo.
They'll have a SQL client
or their Business Intelligence tool.
So how would they find data?
How would they know what we made available to them?
And that's where the Denodo Data Catalog comes in.
So I logged into this an hour ago
and it shut off.
Okay.
So this part you can follow along
with as well.
This should not be affected by the error that I had there.
The Denodo Data Catalog is at a slightly different URL.
The email that you've got this morning
should have that URL as well.
But just in case it didn't,
this is denodo.demo.plaid.is/denodo-data-catalog.
It will automatically add the slash login piece for you.
Okay.
Same username
and password that was in that email for you this morning.
The virtual data port server here,
you can either leave as Plaid Demo
or you can pick the particular one for your username,
and you'll see pretty much
the same data more or less.
So I'm just going to leave this as Plaid Demo
and I'm going to sign in here.
No, I'm not.
It's going to make me do that again
because it timed out.
Sorry.
Ta-da.
Okay.
All right.
What do we have here?
What we have here is our Data Catalog tool.
This allows us to do various things like search
and it starts us off at the search page.
Or we can also browse our different databases
and folders.
We can also create categories and tables.
So we can tag.
We can categorize.
We can even create kind of custom properties for tables.
And we'll come back to that.
My derived views were mostly around the applications.
I'm going to search here for applications.
That's the data that I'm interested in here today.
I'm just going to hit applications
and go return.
We can see that there's a couple
of different things that came back.
We've got some categories here.
Okay.
Apologies.
When I searched for applications here,
I got some information about a category that I'd created,
but it didn't show up,
and I'm going to have to figure out why that is.
It didn't show me the application's view that I created.
Instead, let's click on Browse
and then click on Databases and Folders.
Here, if we look on the similarly
to Design Studio we can look at the various
different data elements here.
What you'll notice here is the Data Catalog shows you
the tables, not the,
or sorry, the base views
and the derived views and the web services.
It doesn't show you kind of the underlying elements.
It doesn't show you the actual data connections.
That's not,
we've got governance information that we need
through the base views and the derived views.
So what I'm going to do here is I'm going
to click on applications_to_target.
So this is the one that we were just talking
about a minute ago.
And you'll notice that that descriptive information,
and I didn't spend too much time on this
in the Design Studio,
but the descriptive information that we provided
about the different fields that are
in this particular derived view are showing up
in my Data Catalog.
I've got a description here both
of the particular table itself
and then we've got descriptive information for each one
of our underlying tables.
Now an administrator can come in here
and edit this and augment this
and provide additional information.
We get information in here,
what's the name of the field, what's the description.
That's great.
But if we want to see where the data
is actually coming from, we can see that too.
We've got another tab here.
We've in fact got four tabs.
One is summary and that's where we started with.
Another one is to actually query data.
We'll come back to that in a sec.
And then relationships,
and we'll come back to that as well.
But the one I want to show next is around data lineage.
So if we click on the data lineage tab,
this is probably going to also,
actually super duper small through Zoom,
so I'm just going to zoom in on this a bit.
What this does is it shows us the flow of data
from those base views that exist in our Banner data source.
We can see what data source is the data coming from,
what table, what's the base table,
where does the information ultimately live,
and we can see that we've got elements like,
so we've got joins here.
It tells us what kind of join or the conditions.
And our additional data elements,
things like our JSON data that goes
through our open house registrations
and gets flattened into a derived view,
and our target data that comes out of CSV.
We see here in one view where all of our data has come from,
and what ultimately what Denodo is presenting
to us in this final view,
this applications target view
where it's all flowing up from.
From a data governance perspective,
we can see the end result
but we can also trace that back.
Okay, where did that actually come from?
We can also see those different relationships
that we have to different data elements
and we can also explore those.
If we click on the relationships tab,
we can see in our Denodo server
that we have defined these relationships.
And I didn't actually show you in the Design Studio
how we defined these just in the interest of time.
But one thing worth noting is that
from database data sources,
Denodo can collect a lot of this information automatically.
Again, assuming that your database system
has defined these at the database layer
as appropriate primary keys and foreign keys.
And if it has, then Denodo can just,
can grab that information for you there.
But we can also define these manually.
Our applications to target here,
we can see that we've got relationships to other data.
And if you looked very closely
at the web service that we had a couple minutes ago,
you'll see that these are similar.
We've, sorry.
Not similar.
They're the same in fact.
So we've got our level of study data, our term data,
our program data,
and our faculty data.
Those are all relationships.
And then that connection to applications
that the underlined data, that's also there as well.
What's really cool about this
is that we can also explore from here.
If we click on,
double click on, sorry, the program data,
Denodo actually digs in
and says, okay, well, what are the relationships
for the program table?
And we can see that we've got relationships
to other tables like
what degree level does
this particular program provide to us.
I mean we'll stop here in just a second.
What faculty offers the program?
So we can actually see here
that both of our applications metrics
and our program, they're both related to our faculties.
We got various other relationships here.
So we can actually navigate
through the different relationships
between our tables in Denodo.
We can also from here query...
Can that stop rotating?
Yes.
Normally it does stop rotating.
I don't know what it is that I've got here.
It will eventually kind of sort itself out.
Normally it does not rotate quite this long for me.
Here we go.
Almost stopped.
Okay.
We can also query data directly here if we wanted to.
I have managed to skip over one of the pieces
that I wanted to show you.
So we'll do that in just a second here.
I'm going to click back on the summary piece here.
One of the other things that I wanted to mention
and we didn't set up this workshop
is you can also add different properties to base views
and derived views through the Data Catalog.
One of the examples that I've set up is
in other context, not for this particular workshop,
is like a data contact.
For this particular data we
could define properties that say who the steward is,
who the data contact is, what the data quality is,
various set of pieces.
And basically augment that descriptive information
that we provided here
with other different kinds of properties.
And we can say maybe it's a text field,
like for a data contact we want to be able to put in a name
and an email address, we can just make that a text field.
But for something like data quality,
we could say we have to pick from don't use, very poor,
good, excellent, that sort of thing.
We can have kind of a drop box.
And again, administrators,
people with appropriate permissions can come in here
and they can set those properties on different views.
From a data governance perspective, this really allows us
to bring all the information together
that we need to have quite robust data governance.
One caveat to that, that I will say,
is Denodo only knows about the data
that you bring in to Denodo.
One of the things we really suggest is tell Denodo
about as much of the data
throughout your organization as you can,
because the more do you have in there,
the more you can use Denodo for the data governance piece.
One of the things that Denodo allows us to do is to connect
with all kinds of different tools.
And I've mentioned this before as part of the workshop.
Whether that's a SQL client or Business Intelligence tools,
or web services, we can get the data out of Denodo in lots
and lots of different ways.
Tableau as an example has a native connector for Denodo.
But Denodo also particularly the Data Catalog
here actually makes connecting even easier.
There's a button here,
and I'm just going to press this,
and this should work for you if you have Tableau
on your desktop, you can follow along here.
If you press the Tableau button,
what is actually going to do is it's going
to download Tableau data source file for you.
I'm going to save this to my Downloads,
and then I am going to pop over to Tableau
and I am going to take that file
that I just downloaded, once my computer
decides that it wants to
show that to me.
And I'm going to drop that in Tableau.
What Tableau is going to do here is it's going
to ask me for the password
because that's not embedded
in this data source connection information.
But it's pretty much got everything else it needs
in order to be able to connect to this data.
My username here is workshop20
or whichever workshop username
you were provided in the email.
And then the same password.
You have to press OK.
And this is going to take a sec to get the data back.
But what we can see here is the data that we have
in that table both as our what Tableau calls dimensions
to measures, we've got that information right
at our fingertips here in Tableau.
I'm going to,
I want to see how many people are registered
so I'm going to click on Register
and I'm going to double click on Term Code
and what you see here is information
on our registered metric by term.
And we got that with a hiccup,
but basically clicking on that Tableau button
straight in the Data Catalog,
opening it up in Tableau
and all we had to do was tell it
what our password was to connect to Denodo
and we can immediately start
with our analysis.
There's no intermediate steps.
There's not a whole lot we need to do in terms
of clean up in Tableau.
It's doing it for us right here.
Okay.
One other element that I intended to show
and then managed to skip over too quickly.
Let's see if this works.
I'm just going to go back to Design Studio
and let's see.
I'm going to pick on workshop01
because I made this one more clear.
I'm going to...
One of the options that we have here,
and this is where I may have scuppered myself
by turning this on
and not having it load properly,
is the ability to cache data.
One example of where this
could be really,
really useful is obviously around performance
when you're pulling in data from different data sources
that might not be as performant.
I mentioned JSON
and many cloud data sources just aren't as performant
as a database.
Denodo allows us to pull that information in out
of those different data sources
and store it in its own database as a cache.
The other place this can be really useful is in many cases,
we don't want to be queering our production systems.
So whether that's a student information system
like Banner or another database.
We don't want to impact the performance
of the production systems.
In many cases, IT units are set up like a reporting version
of a database.
Sorry, I clearly made a mistake there.
The cache is on Denodo's server.
This is something that has to be enabled by an admin
and it's all of Denodo's standard security also applies
to what's in the cache.
This is not on my local machine here.
But it's typically when we set up a cache, it's something
that's more local to the Denodo server
and so typically it will get better performance out
of the cache.
The trade off there is the information
that you're going to get is not quite as real time.
Turning on the cache however,
we can get much quicker results.
If for example we look at application metrics here,
and I haven't turned on caching for this particular view.
We give it a sec.
It will take a few seconds to run.
One of the other awesome things
about the Denodo Design Studio is it
can tell us exactly what Denodo is doing
and the different components that go
into creating the data for us.
By looking at the query results here,
and I opened the applications metrics,
and then I pressed the Execution panel
and the Execute button.
Now I'm going to press on Execution Trace.
What this does is shows me the different components
that Denodo used to get that data for me.
I can see the,
similarly to the data lineage,
I can see where the data came from,
the different data sources,
the different transformations that it went through,
and ultimately where it got to.
But I can see if I click on the final execution plan here,
I can see how long that took me.
In this case, it took me 13 seconds,
which is not fantastic,
but I'm sure we've all written SQL queries
that run a lot longer than that.
But my applications_to_target
which relies on applications metrics, I've turned on caching
and I preloaded this information
and clearly I broke something.
Let's...
Can I do this on the fly
and not have it blow up in my face?
By caching I can bring that 13 seconds down
to fractions of a second once I've loaded the cache,
which is what this is doing right now.
Okay.
Let's run that again.
Out of the cache.
This time it's using the cache.
Part of what you can see is that our execution
is much simpler.
But the other thing you can see is my 13 seconds
there is now 31 milliseconds.
In terms of performance, just turning on cache
and loading that data into the cache can really help us
bring down our time to analysis drastically
and leveraging Denodo's performance capabilities.
With that, we've got about seven minutes left.
I realize I've thrown a lot of information at you.
Oh, can you schedule refreshes to cache?
Yes, sorry, I should have mentioned that.
Denodo also ships
with a scheduler and that scheduler
which I haven't shown here today at all,
but that scheduler allows you to set the,
if you want to cache a full table,
it allows you to say do it at two a.m., six a.m.,
what have you,
and you can schedule as many tables into the cache
as you want.
There's also other caching options where it actually does
it on the fly if you would prefer that,
if the table is used less often.
But there's lots of different ways that you do it.
Are there any other questions, anything anybody
is wondering about that I haven't already touched on?
And of course,
I should mention that we are quite happy to follow up.
I know not only have we kind of thrown a lot
of information at you,
but this is kind of generic.
We've talked about a database
and we've talked about a spreadsheet
and we've talked about JSON data
and pulling it all together.
We also know that every institution kind of
has their own set of data living in different places,
and we are certainly happy to talk
with you directly about how Denodo can work
to your specific environment
and how it can provide you additional capabilities.
A couple of the things that we didn't touch on
but are worth mentioning are elements like security.
Denodo can tie into your existing authentication.
I suspect many of you are using
Active Directory throughout your organization
to log in to machines
and we control access to pieces.
Denodo as an example can leverage Active Directory.
It can also do both row
and column level security.
One of the things as an example is maybe somebody
who's a budget manager, they've got finance data in Denodo,
somebody who's a budget manager for a faculty or department
should only see finance information for their faculty
or their department.
Rather than having a layer on top of Denodo, or sorry,
on top of your data, so something like a web service
or what have you, Denodo can enforce that,
which is a capability that most database systems
don't have or at least require some customization.
So with Denodo you can set those up so that people
can only see particular rows
or they can only see particular fields.
If most people, most of your analysts
shouldn't see social insurance number
for one of your folks who's been hired.
I know we have a few people here
from the United States today.
This would be the social security number in the US context.
If most people throughout your organization
shouldn't have that,
but some people should,
you can setup that kind of column level security
within Denodo as well.
And then
there's one other piece that I wanted to talk
about that is now,
oh, yes.
And I did kind of mention other BI tools.
We happen to be Tableau partners.
So we use Tableau
and that's what we showed here.
But if you happen to have another Business Intelligence tool
in your organization, something like a Power BI,
then Denodo is quite happy to work with those
and those tools are quite happy to work Denodo as well.
So even though I showed Tableau today,
it's not something that is specific to Tableau.
Are there any other questions?
Anything that I have missed?
All right.
Oops.
Went back a little bit too far on my slides.
Well I need to turn this off.
Okay.
If you did want to learn more about data virtualization,
the folks from Denodo have provided us
with access to, it's about 60 or 65 pages.
It's Data Virtualization For Dummies.
And you can download that from our website.
There's a QR code
and link's here at the bottom.
And hopefully that will get that pasted
in the chat momentarily.
And if you did want to have any further conversations,
we're happy to talk about how Denodo can work
within your particular environment.
So our contact information is there.
Suntosh or Andy, is there anything you'd like to mention,
anything that came up that I may have missed
in the questions that we
should address live before we sign off?
Suntosh: No.
The only thing I was going to mention is to reach out
to Plaid for a potential data virtualization assessment
if you want to learn more,
and understand how data virtualization can fit
into your architecture
and support your upcoming business initiatives.
Andrew: Pat, there was one question from very early on
and that was how long will we leave the server online
if people want to try this on their own time?
-: Right.
We'll leave it online until end of the day Friday.
And then from there, one of the-
If there's not enough time, drop us an email
and we can bring it back at other times as well.
Andrew: Pat, I think your audio cut out there.
You want to try that one more time?
-: Of course.
We will leave it up to the end of the day Pacific time.
So on this coming Friday,
so that would be I guess February the 26th.
But it's also a demo environment.
We can bring it kind of up
and down at will.
So if somebody needs more time or they'd like
to schedule a particular time to poke around the server,
feel free to drop us an email
and we are happy to bring it up for that purpose.
All right.
Thank you very much for your time today.
Recording will be available
within the next couple of days
and we'll send you an email when it's available.
And yeah, feel free to reach out
if you have any additional questions.
We welcome that.
Or more in-depth conversation.
Thanks very much.
(upbeat electronic music) Learn more at www.plaid.is