Now that Jim is ready with the database connection, he can start with the extraction process.

To extract the data tables from the data source, we will start creating the new Qlik Sense application. For this, you need to open Qlik Sense Desktop.

Once Qlik Sense Desktop is open, you can create a new application by clicking on the Create new app button. The option is shown next:

We can see how Qlik Sense hub can be opened from the following screen shot.

Give a name to your application and click on the Create button. The new application will be created, which can be seen in the hub.

Then click on Open app to open the application. You will be given two opens: one, Add data from files and other sources-this option will allow you to load data using the drag and drop options, and second, Script Editor, namely using manual scripting.

Click on Script Editor to open the script window to start writing the script.

First thing to do is to connect to the data source using the ODBC connection created in the previous section. To do this, click on Create new connection:

Once you click on Create new connection, you will be asked to select the driver using which you want to create the new connection:

In our example, we are going to use ODBC, so you need to click on ODBC. Once you do that, it will open a window where you will be shown the available DSN connections, as shown in the following screenshot:

In the previous section, we had created User DSN, thus we will click on User DSN. Once you click on that, you will see the AdventureWorks DSN that we had created. Click on AdventureWorks and check the box Use 32-bit connection; this is because we had created DSN using 32-bit configuration. Give a name to the connection and click on the Create button.

Once the connection is created, you will see it under Data Connection on the right hand side, as shown in the following screenshot:

This connection was to connect to the data source, but we would need one more connection to connect to folder for storing the QVD files.

To do this, you again click on Create new connection and then click on the Folder option. It will ask you to give the path of the folder where you want to create a connection and a name to the connection. You should give such a name that you can easily identify the use of that connection. Let’s assume we give a name to the connection called QVDs.

Now that we are ready with every connection, let us start writing the code.

We will start by entering the connection string of the data source which we had just created, namely, AdventureWorkDW. To do this, click on the first option, Insert Connection String available, under the connection name on the right hand panel, as shown in the following screenshot:

Once you click on that, you will see the following auto generated script line on the scripting window:

LIB CONNECT TO ‘AdventureWorkDW’

Now there are couple of ways through which you can fetch the data: one is using separate script for each of the tables, and second is using For Loop to extract all the tables. In our example, we will use the second option to load the tables in one go; we will use For Loop.

To run For Loop, you need to know the number of tables that need to be extracted from the data source. For this, we will create a temporary table which will have the list of the tables which need to be extracted.

We will start by creating a separate tab to write this code and we will name this tab Load Table List. The script is shown in the following image:

You can see in the preceding script that we have created an inline table that has the list of tables which need to be extracted. One thing to note here is that we have not listed the transaction table names, that is, fact tables, because those tables should be loaded using the incremental load, which we will see later.

The script to load this table is shown in the following image:

You can see in the preceding image that we started with finding the number of tables that need to be extracted from the data source using the NoOfRows() function and stored that value in a variable, so that we can use that in for loop.

Then we started with For Loop , one thing to note here is that we started loop from 0 to minus 1. This is because the table stores the data from index 0, so when we fetch individual table name using the peek function, we can refer to the correct value of the table.

Then within for loop, we used the SQL command to extract the table, and then used the Store statement to store the extracted table in the QVD.

Later, we dropped the table, because the purpose of this application is to extract the data and store in QVD and not to keep the extracted data in this application.

Now let us see the script for incremental load. In our example, we have used two fact tables, namely FactInternetSales and FactResellerSales.

Following image shows the first part of the incremental script:

>

You can see in preceding screenshot that we started with listing the required fact tables in temporary table and used for loop to run the script for each table.

Here, we will first find if the QVD is created or not; if it is not created, then full data should be extracted for the table, else incremental load logic should be applied. For this, we have used the function QvdCreateTime(), to know the created datetime of the QVD. If it doesn’t exist, then it will return Null() value. Using the IF statement, we define which scripts to run when QVD exists and when it does not.

If you look at the script, we have created %SalesKey in the preceding load. This is to make a primary key in the table so that we can use it in incremental load.

Following screenshot shows the second part of the script, which takes care of incremental load:

The script in the preceding screenshot shows incremental load in the else part of the IF statement.

If you look at the script, we started by finding the last fetched sales order from the QVD data and stored the same in variable. While storing in the variable, we removed the text part of the sales order by using the purgechar() function.

Then we fetched the incremental data from the table using the where condition in the SQL script and concatenated the same with the existing QVD with the Exists() function. This would take care of the insert and update of the data in the QVD. To remove the deleted data from QVD, we used the inner join condition, as shown in the preceding image.

At the end, we stored the data in the QVD and dropped the table.

This ends the extraction of all the required tables from the data source. Now we will see how to create a data model using this QVD and creating a dashboard on top of it.