What IS ETL Exactly ?

A free video tutorial from Paul Scotchford
Data Analyst and Qlik Development Specialist @ awari
7 courses
136,525 students
Learn more from the full course
The Complete QlikView Boot Camp
Learn QlikView for Data Analytics - Designer & Developer step by step. Real-World Data Analytics. Real data included.
15:02:04 of on-demand video • Updated November 2024
Handle interview questions with ease
Start developing solutions for your company as soon as you have finished this course
Receive an achievement based certificate. This is based on your test results so you can show the world your skill levels
Understand and apply Associative Data Modelling concepts
Learn about different Model Schema types
Learn to code Joins & understand how they work
Learn how the Associative Data Model is stored internally
Learn how to load and connect to different data sources (e.g. SQL, Web )
Code Scripts for ETL data processing
Learn to code calculations using Set Analysis
How to code What-If analysis
14 Assignment projects for hands on practice, followed by Quizzes testing your knowledge
Learn charting best practices
Learn about advanced calculation functions like Aggr() , Rank() , IntervalMatch()
Create Charts such as Gauges, Pie, Bar, Combo, Line, Tornado, Heat Maps, Scatter
Learn performance tuning for charts and scripting
Add actions and conditions to charts and other objects
Apply many of your new skills to QlikSense e.g. Modelling, Set Analysis
Over 150 Quiz test questions for you to check your learning progress
Real world sample data available for download and analysis
Install QlikView 12 (and SQLServer express if you want to connect to sample SQL databases)
English [Auto]
Hi there. Welcome to this section. We're going to talk about ETL. ETL is extract transform and load. The process of ETL mostly will be done on enterprise server environment. So these are really about running jobs in a scheduled environment on a regular basis. So this could be every ten minutes, every hour half daily end of day and so forth. And that is controlled through the administrator inside the enterprise environment for us. We need to write the scripts that will do the ETL that can run on the enterprise environment. And predominantly you'll develop these within a chvt, a qlikview document. And that document itself is what will run on that regular basis on the server. So you'll develop on your desktop, for example, and then push it up through the various test and deployment stages within your organization onto the server, and ETL itself is about extracting data, transforming it, and loading it. Now you'll notice on this very basic high level diagram, we have a number of different data source types here. And I'm going to discuss all of these in this section. But predominantly the extract stage is about grabbing the data we need from these disparate or indeed a single source of data. The scripting that we write will ultimately load that data into Qvd files. These qvd files are qlikview data files, and in fact they can also be consumed by Qlik Sense apps. So we don't have to rewrite our load scripts just because we're using Qlik Sense in another part of the organization. Very often an organization will have the two together. My last organization ran Qlik Sense for a lot of its mobile apps, and then we use Qlikview for the more complex apps or documents that we wanted to present to the user. That was a medical environment as well. So in this instance, our script layer is what's going to populate our cvds and of course later on the qvd. In other words, those documents that the users are interacting with, and indeed the quintessence apps can consume the data from these cvds. Now, of course, there's always going to be a security layer here, and we'll discuss security a little later on. But the enterprise is where that management of security is taking place. Now, what we do here is we'll go through the process of building extract scripts, transformation scripts. So the transformation is about if the data or the format of the data needs to change as it moves from the source to the target, in this case our qvd. So a good example is dimension conformation. I don't mean conformation, just conformation. I did mention it briefly in the previous section. It's about making sure that a code, for example, is consistent across all of the data sources as they come in. We don't want to present different codes for the same thing within our data. For example, the US might have an abbreviation of USA in, say, some SQL database data. It could be US of a in one of the binary loads we're going to execute on a QV W, and it could be something else here. Now the user is going to get quite confused if we represent it in different forms. So the transformation stage is about conforming that data to a point where it's consistent for the user. So we only have one source of truth. In other words for us a. All right. So why don't we now get into the desktop and start writing some ETL. See you in there.