00:06: Hello and welcome to the Denodo Administrator and Developer Training guide, which
00:10: will serve as a centralized location to get trained on the basics of Denodo from an
00:14: administrator's and developer's perspective.
00:16: During this video, we'll show you how to accomplish administrative tasks such as
00:21: requesting changes to your virtual database, or VDB, and how to adjust row and column
00:26: level security settings for users,
00:29: as well as development tasks such as setting up data sources and base views and how
00:34: to integrate data together.
00:36: There will be links throughout the video to specific training documents and videos
00:39: about the different tasks,
00:40: so feel free to pause and jump out to those
00:44: you'd like more information about.
00:46: I'll start by mentioning the Denodo request form, which can be used to request
00:50: changes to your VDB,
00:52: like safe listing servers and scheduling jobs in Denodo.
00:58: Please use the links on the next screen for more information on how to fill out the
01:01: request form and a link to the form itself.
01:05: Next, we'll discuss how to log into Denodo using the web application Solution
01:10: Manager.
01:11: The link to the Solution Manager login page is on the next screen, along with a link
01:16: to the DataHub website you
01:17: see here, which you can also use as a landing page to log into the Solution Manager.
01:21: manager.
01:25: You'll begin by logging in with your NetID and Active Directory
01:28: password. You can enter that info and click sign in, or you can simply use the single
01:33: sign on button, which uses the university's SSO solution.
01:37: Note that you need to be connected to a VPN that requires two-factor
01:41: authentication or connected to the university network to log into Denodo.
01:45: Also, if you want to use a service account to log into Denodo, there is some setup
01:49: involved and a different method used to log in.
01:52: For more information on this process, including the front-end setup work required, see
01:56: the document linked in the top right of this screen.
02:04: Once you've logged in, you'll see a drop-down that displays the environments you can
02:08: use. The choices follow the software development lifecycle
02:10: approach to doing development work in the development environment, testing in
02:14: the QA environment, and finally promoting to the production environment when they're
02:18: ready.
02:20: You'll also see some helpful resources on the right side.
02:22: For more information about these resources available from the Denodo Community
02:26: website and for a quick walkthrough of the DataHub website, see the Denodo Resources
02:30: walkthrough linked on the next screen. Also
02:32: see the video titled Denodo Courses for a detailed walkthrough on how to register
02:37: for the courses Denodo
02:38: offers at no charge to you or your unit.
02:43: Next, we'll open the Design Studio and discuss its functionality.
02:49: In this walkthrough, I'll stick to the development environment.
02:53: You'll know which environment you're in by the first VDB listed in the Explorer
02:56: panel on the left side of the screen. Any VDBs you have access to will be listed
03:00: below that.
03:04: This typically includes the Logical Data Warehouse, which is the virtualized version
03:08: of the Enterprise data warehouse that we'll talk more about later and the VDB you or
03:13: your unit created.
03:16: When your VDB was created it was given a basic folder structure suggested by
03:20: Denodo
03:20: to keep your work organized. For more information on this structure
03:24: see the document linked in the top right.
03:28: The VQL Shell should be open by default.
03:34: If it isn't, click Tools and select the first option, VQL Shell.
03:40: Virtual Query Language, or VQL, is the SQL dialect Denodo uses. This VQL
03:46: Shell can be used to query data in Denodo, as well as perform almost every
03:49: administrative and development task.
03:51: There is a graphical user interface to perform these tasks as well, which is what
03:56: I’ll focus more on in this walkthrough.
03:58: For more information on the VQL coding language and how to use it,
04:02: see the VQL Guide document in the top right.
04:06: One of the first things you'll want to do in your VDB is to create data source
04:10: connections to your data.
04:11: The data can be housed in CSV files in a cloud environment like Box or SharePoint,
04:16: Come from a vended applications API, be stored locally in a MySQL database, etc. To
04:22: create a new data source, choose File in the toolbar along the top, hover your
04:26: cursor over New, and choose Data Source.
04:33: You can scroll through the list or use the search bar to find a certain type.
04:41: The basic steps are similar no matter which type the data source is.
04:44: The first step is getting access to the source data and being able to authenticate
04:48: as a user with access.
04:51: For JDBC connections we typically use OAuth 2.0 which requires a client identifier
04:56: and a client secret to authenticate into the application.
05:01: File sharing platforms like Box, SharePoint, and Amazon require that you create a
05:05: custom app or role to accomplish this.
05:09: For documentation on how to connect to different types of data sources,
05:12: see the links on the next page.
05:15: Once the data source connection has been established, the next step is to create a
05:18: base view of the data.
05:20: The base view is the virtualized version of the data you're trying to get at.
05:24: Base views can be created at any point following the creation of the data source.
05:30: Simply click the Create Base View button from the data source.
05:41: If the data you're virtualizing is flat, such as a CSV file,
05:45: Denodo will simply virtualize it as a view.
05:55: If the data isn't flat, such as a JSON format, you'll need to stream the output at a
06:00: certain level to get the data you're looking for.
06:06: For more information about creating and editing base views, as well as how to ingest
06:10: table and column metadata
06:11: from the data source while creating the base view, which will then allow the
06:15: definitions to flow to all integrated views built from them in the future,
06:18: see the documents listed on the next page.
06:22: Once you've got base views of your data, the next step is to combine that data
06:25: together to create integrated views that can be queried at any time.
06:30: Integrated views are also a way to incorporate business logic into raw data
06:34: so it's more meaningful to end users.
06:37: There are a few ways to create integrated views, but here we'll focus on using the
06:41: graphical user interface to create them.
06:45: Click File in the toolbar along the top,
06:47: hover over New, and choose the type of integrated view you want to create.
06:51: Denodo can create joins, unions, and many other types of integrations from different
06:56: base views or other integrated views.
07:03: For more information on the different types of integrations Denodo can handle, see
07:07: the document linked in the top right.
07:12: To combine data from two different sources,
07:13: all that's needed is a common field you can use to join the data on.
07:23: For example, a UIN can be used to join tables about a student's academic history
07:27: from the Logical Data Warehouse with survey data
07:30: your unit may have in Qualtrics or another survey platform.
07:48: We can see the condition that we just created.
07:52: We can further configure the join conditions, add where conditions, and add group by
07:58: clauses,
08:00: On the Output tab, define which fields we want in the output along with the name of the
08:04: new view.
08:09: We can also add column descriptions.
08:12: And then we just need to pick which virtual database and which location within that
08:17: database it's going to go.
08:22: From here, we can query this data and see the results.
08:40: One main benefit of Denodo that I just mentioned is the Logical Data Warehouse, or
08:45: LDW, which is a virtualized version of the entire Enterprise Data Warehouse, or EDW.
08:55: Security is synced between the EDW and the LDW,
08:58: so users in Denodo will see the same tables and views they have access to in the
09:02: EDW.
09:05: The tables and views in the EDW were created as integrated views with the same names
09:10: in Denodo,
09:10: so navigation should be familiar.
09:14: Users can use these views like any other views to create their own integrated views
09:18: that combine LDW data with external data.
09:23: Any query you can currently run against the EDW can be slightly modified to run
09:27: against the LDW in Denodo.
09:31: For example, a query that pulls all of the academic data for all students of an
09:35: academic advisor can be virtualized very easily in Denodo.
09:56: For more information on how to use the LDW in Denodo,
10:00: see the documents linked on the next page.
10:05: Active Directory groups control which users have access to your VDB and their
10:09: permission levels - admin, developer, or consumer.
10:14: After creating integrated views, you can adjust security settings for a user or
10:18: group of users, for all or some, of the views you've created.
10:25: Column level security limits the columns a user can query, and row
10:29: level security allows an admin to reject certain rows or mask data for users.
10:35: For more information on how to set up and adjust column and row level security, see
10:39: the documents linked on the next page.
10:41: The last step in this process is to connect your end users or data consumers to the
10:45: views you've created. There are several ways to do this depending on the desired look
10:50: and functionality of the data.
10:54: For example, if a user wants to be able to query the views using a BI tool like
10:58: Tableau, Power BI, or DBeaver, they can create an ODBC or JDBC connection to your VDB.
11:03: VDB.
11:05: If you're simply wanting to surface this data so consumers can view and search
11:09: through it in a web browser, you may want to use Denodo's built-in REST service.
11:14: For more information on how to connect your data consumers to the views in your VDB,
11:17: see the documents linked on the next page.
11:21: If you need help with anything Denodo-related, please see the resources on the last screen. The
11:25: Denodo Request
11:27: Form can be used to contact AITS Decision Support directly. Thanks for watching!
11:32: Thanks for watching.
