What IS ETL Exactly ?

[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 :)