Loading data already stored in QlikView

The first lesson in advanced data transformation will be about optimizing loads when processing data. If you remember from Chapter 4, Data Sources, we discussed the various ways in which we can pull data from different sources into QlikView. We also described how we can take advantage of the QVD file format to store and read data in super-fast mode. Now, we will describe yet another way of reading source tables, but this time the "source" will be QlikView itself. There are different cases in which this approach will prove useful and we will describe two scenarios to perform it:

  • Accessing data already stored in a QlikView data model (QVW file) from a separate QlikView document. We will call this approach Cloning a QlikView data model.
  • Accessing data from the same QlikView document in which the data model resides. We will call this approach Loading from RAM.

Cloning a QlikView data model

This concept refers to the ability of replicating the data model of an already created QlikView document and placing it into another QlikView document without accessing the original data source. In technical terms, it's a Binary load. Once the data model is cloned by the second QlikView document, it can be manipulated further, integrated into a bigger data model (that is, adding more tables to it), or even reduced by removing some of its tables or data.

Suppose we have a QlikView file, with an already constructed data model and all of the composing tables properly associated. We now want to use this same model in another QlikView document, adding just a few more tables. The process for binary loading a QVW is as follows:

  1. Create a brand new QlikView document and save it to the disk.
  2. Open the Edit Script window (Ctrl + E or File | Edit Script…)
  3. Click on QlikView File… button, located in the Data tab.
    Cloning a QlikView data model
  4. Browse to the QlikView file we want to read and click on Open.
  5. A new script statement will be created at the top of the active script tab, which will be something like Binary [file name.qvw];

    Note

    The Binary statement must be the first statement to be executed in the script, so it has to be always at the top of the first (left-most) script tab. Also, only one binary load is allowed in a QlikView script.

  6. At this point, we can add more tables to the already-loaded data model as we would normally do. After reloading the script, the data model will now be in the new QlikView document, along with any other added tables.

    Note

    A Binary load statement is the fastest way to load data into a QlikView document.

In the environment we've been working throughout the book, can you find a use case for a Binary load? Which would it be?

I can think of one. For example, we have an initial QlikView document with the Airline Operations data (the one we constructed in Chapter 3, Seeing is Believing). Based on this initial data model, we can create a new QlikView document, to which we will only add the Employment data (used in Chapter 9, Data Modeling Best Practices). As a result, we would have one Airline Operations document accessed by certain users, and another with the same data but with additional information about airline employment, which might be treated as confidential and accessed by another group of users.

As a side note, we must point out that binary loads are used in yet another approach to data architecture. We will not discuss it in-depth, but suffice to say that the new layer is composed of QlikView documents consisting of only a data model without any frontend objects, referred to as "QlikMarts". These QlikMarts then become the source for the QlikView documents in the Presentation Layer.

Loading from RAM

In some cases, we will need to read the same table more than once in a single script execution. This means, querying the database (or QVDs) and pulling data from it, and then reprocessing that same data after the first read in order to make it adequate for our data model. Since the data is being stored in RAM after each query during the script execution, we can use that RAM-stored data instead of going directly to the original data source. This is accomplished via a Resident load.

Resident load

The keyword Resident can be likened to the keyword From in a query. The difference is that the Resident keyword is used to reference the data in RAM model, that is, all the tables that have been previously read in the preceding queries of the same script. The process for achieving this is as follows:

  1. First, we must load data from a data source (any database or table file described in the previous sections), so we create the corresponding query in the script. An example would be:
    SalesData:
LOAD
    InvoiceNumber,
    Date,
    SalesPerson,
    Department, 
    Amount as InvoiceAmount;
SQL SELECT * FROM DataBaseName.dbo.Sales;

    Note

    Note that we have defined a table name, at the beginning of the query, so that we can use it to reference the table later on. We have also renamed the Amount field to InvoiceAmount.

  2. Next, we add a subsequent query, in the same script, to access the table already in RAM using the Resident keyword. In this case, we will also aggregate the data using a Group By clause, which is a data transformation technique explained later in this chapter.
    SalesTotals:
LOAD
  Department,
  Sum(InvoiceAmount) as TotalAmount
Resident SalesData
Group By Department;

Note that, when referencing a table that is now part of the QlikView data model, we must use the field names with which they have been defined, which might not necessarily be the same names as in the source table. In this case, we are using InvoiceAmount, a name that was defined in the previous query. The same applies for table names.

As a result, we will have two tables in our data model; one with all the data at an atomic level, the product of the first query, and the other as an aggregated version of the SalesData table with totals by Department, the product of the Residentload we constructed in conjunction with the Group by statement.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset