What IS ETL Exactly ?

A free video tutorial from Paul Scotchford
Data Analyst and Qlik Development Specialist @ awari
4.4 instructor rating •
5 courses •
99,385 students
Learn more from the full course
The Complete QlikView Boot CampLearn QlikView for Data Analytics - Designer & Developer step by step. Real-World Data Analytics. Real data included.
15:15:50 of on-demand video • Updated April 2020
- 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
[INTRO MUSIC] Report CC errors, thank you. 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 an Enterprise Server environment so these are really
about running jobs in a scheduled environment on a regular basis so this
could be every 10 minutes, every hour half daily, end of day and so forth and
that is controlled through 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 QVW ; a QlikView document and that document itself is what will run on
that regular basis on the server so you'll develop on a desktop for example
and then push it up through the various test and deployment stages within your
organisation onto the server and ETL itself is about extracting the data
transforming it and loading it. Now you notice this very basic high level
diagram where we have a number of different data source types 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 QlikSense apps so we don't have to rewrite our load scripts just
because we're using QlikSense in another part of the organisation. Very
often an organisation will have the two together. At my last organisation we ran QlikSense
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 QVD's and of course later
on the QVW's in other words those documents that the users are interacting
with and indeed the QlikSense apps can consume the data from these QVD's 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'll 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's so a
good example is dimension conformation I don't mean confirmation just conformation I
did mention it briefly in the previous section it's about making sure that the
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 qvw 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
USA all right so why don't we now get into the desktop and
start writing some ETL. See you in there :)