We've completed the "Seeing is Believing" phase with big success. We've shown HighCloud Airlines the potential value that QlikView can bring to their business and how they will be able to give their raw data the meaning their business requires to make everyday decisions. Now, the natural question that arises after seeing what QlikView can do on the frontend is: What type of database does QlikView require to work?
The straight answer to this question is, simply, that QlikView does not necessarily requires a specific database or
Data Warehouse (DWH) to pull data from. It could benefit from using a DWH, but it is not required. However, the data must reside somewhere, in order to be able to pull it into QlikView, visualize it, discover patterns in it, and build all kinds of charts with it. That somewhere can be almost any standard database, flat file (for example, .xls or .csv), web page, and so on, or even any combination of the above.
When building the data model for...
Using ODBC and OLE DB drivers
First, what the acronyms mean:
You may already know what these are, and we will not go into detail about how these drivers work on the inside but, in general terms, we can think of them as "query translators", which enable the communication between an application (such as QlikView) and the DBMS. Since they have been in use for a long time, almost all major DBMS vendors provide access via ODBC and/or OLE DB drivers.
When you use a printer, it requires you to install a driver on your computer so the documents you send to print can be received and printed properly. The same is true with the DBMS drivers. You need to install the corresponding driver on the machine you will be sending queries from in order for them to be accurately translated and properly processed by the DBMS, which will, in time, respond to it by sending the requested set of data.
Accessing custom data sources
OLE DB and ODBC are the most common types of connectivity you will find in corporate environments. However, there are certain data sources that cannot be accessed naturally via any of these standards. For these few (but increasing) scenarios, QlikView provides the ability to integrate what is called
Custom Data Sources, and extract data from them and manipulate it as any other source.
We can access custom data sources just as we access any other common database: with a connector or a driver. In this case, we can either build our own custom provider, or buy it from a third-party. The former typically requires using C or C++ code to create the communication architecture between the custom data source and QlikView. QlikTech provides a Software Development Kit (SDK) to facilitate the construction of these programs, sometimes even including sample code.
An example of a custom data source would be Salesforce.com. The connectivity for Salesforce.com is provided by QlikTech...
The third type of data source you will find consists of the most common table files, such as Excel, CSV, TXT, XML, or even HTML. For these types of data sources, the one requirement would be that their content is in a readable, understandable structure. It will be easier to extract data from them if they are constructed in the form of a traditional table, that is, only rows and columns (like any table in a database). However, sometimes these files could contain extra information that is not actually part of the core table (such as headers or footers) and, therefore, additional transformations via script are required.
Note
In Chapter 9, Basic Data Transformation, we will talk about some techniques for dealing with unstructured table files.
The ability to read table files is especially useful when we want to mix information from the DBMS and data generated by the business user that might not be stored in a database. For instance, budget forecasts, external market indicators...
Extracting data—two hands-on examples
In this section, we will go through the steps required to
extract data into a QlikView document. The extraction process through which we pull data into the QlikView document consists of:
Connecting to the database
Querying the database
Reloading the QlikView script
We will provide two examples of data extraction using two different data sources:
Extracting data from MS Access
Our first example will demonstrate how to extract
data from an MS Access database. It will be a good example since the connection process is very similar to that used when connecting to most major DBMSs. We will be using one of the drivers discussed in the previous section, and covering the steps required in the entire process.
Note
Before continuing, make sure a database file named Dimension Tables.mdb is in the Data Files\MDBs folder. If not, proceed to create the folder, if necessary, and copy the file.
We have now gone through the process of loading data from traditional databases and simple table files. In this section, we will take a deeper look at the QVD and QVX file types, which are used by QlikView to store and read data in an optimized format.We will discuss a little more about both of these types, and the benefits and uses of each of them.
QlikView Data (QVD) files are used to extract and store data into and from QlikView. This means that whichever table you read, from whichever database, you can store it in the QVD format before or after any transformations you perform on the table. The special characteristics of this file type are:
It contains only one logical table.
It uses a special algorithm to compress the data, achieving compression rates of up to 90 percent, depending on the fields' cardinality of the underlying data.
When reading a QVD table file in QlikView, the loading speed is anywhere from 10 to 100 times faster than when loading from a database...
There is yet another way of adding a table to a data model, and it's one that is especially useful for small tables that do not necessarily reside in a database. For example, those that contain a custom description of an entity. With an Inline table, the data is entered directly into the Edit Script window. The process to input an Inline table is outlined here.
From the Edit Script window, go to the Insert menu and select Load Statement | Load Inline. The
Inline Data Wizard will appear as shown in the screenshot below:
The window is similar to a spreadsheet, with rows and columns.
Note
To enter heading labels, double-click on the header cell.
We can start typing data into the cells, and after we are finished entering the content of the table, we can click on OK.
Tip
Importing document data to the Inline table
It is possible to enter, within the Inline wizard, a list of values contained in a field that is already stored in RAM. This is done via the Import Document Data...
We have covered the most basic extraction capabilities that QlikView provides. We have also described the different data sources you can access from QlikView and provided a few tips for dealing with input tables.
At the same time, the technical team at HighCloud Airlines has been able to assess that QlikView can load data from disparate sources, and see the different options they will have at their disposal with QlikView regarding data sources. They have also been presented with one of the most important differentiators of the QlikView software; that is, its ability to read and process data stored in disparate sources, and combine them, associate them, link them, and store it all in one location (the QlikView document), to enable a complete insight and analysis for QlikView users.
In the next chapter, we will learn how we can turn our extracted data into a proper data model.
