What IS ETL Exactly ?

Paul Scotchford
A free video tutorial from Paul Scotchford
Data Analyst and Qlik Development Specialist @ awari
4.5 instructor rating • 6 courses • 91,674 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: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 :)