What IS ETL Exactly ?

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.