Author: Chandraish Sinha
Publisher: Packt Publishing
Audience: QlikView newbies
Reviewer: Ian Stirk
This book aims to introduce you to QlikView, covering the whole application lifecycle, how does it fare?
QlikView is an increasing popular tool for data visualization. This book is aims to introduce you to the “...basics of QlikView, and then dive into loading data, performing transformations, creating visualizations, and deployment of the application”. The book is relatively small, having around 140 working pages spread over 8 chapters.
Below is a chapter-by-chapter exploration of the topics covered.
Chapter 1 QlikView Fundamentals
The book opens with a brief overview of the QlikView components, namely:
QlikView Desktop – development tool
QlikView Server – analytic engine
QlikView Publisher – loads data, and distribution service
Details are provided on where to download, and how to install a personal edition of QlikView Desktop. This is followed with an overview of QlikView basics (data is held in RAM, associations used to discover table relationships based on common field names, development is via QlikView Desktop).
The chapter continues with an outline of the steps in the development of a QlikView application. These steps form the other chapters of the book. The example used throughout the book is based on the well-known Adventure works 2012 database. There’s a brief overview of the user requirements for the example QlikView application, before showing details of the various tables, their columns, and relationships. There’s a brief look at star and snowflake schemas, stating they are particularly amenable to reporting since they require less joins.
The chapter ends with details on how to download a QlikViewEssentials zip file from the publisher, this is unzipped, and its files used in the development of the subsequent QlikView application.
This chapter provides a useful overview of what QlikView is, its components, and how to install it. The lifecycle of a QlikView application is outlined before introducing the application that will be built in the remainder of the book.
This chapter is easy to read, well written, with a good flow, and has plenty of helpful diagrams. These traits apply to the whole of the book.
Chapter 2 Extract, Transform, and Load
Here we look at various data sources to design a data model. This model is used in subsequent dashboards. The chapter opens with an overview of QlikView scripting, with its major features explained.
Next, details are provided on how to connect to different data sources (MS Access database, Excel, and text files), and then loading the data into QlikView. QlikView automatically connects different tables based on common field names (e.g. OrderID). Details are given on how to add additional fields to the tables. Having loaded the fact (detail) tables, instructions are provided on how to load the related dimension (reference) tables, again automatic linkage occurs. This is your first data model.
Some typical problems are discussed (synthetic keys, circular references, loosely coupled tables) together with some solutions.
This chapter provides a very practical step-by-step walkthrough of creating data models using data from various sources. Everything is explained very clearly.
Chapter 3 Optimizing Your Data Model
The aim here is to reduce the current data model to one more akin to a star schema (fewer tables, less joins, better performance). The chapter opens with a look at mapping tables, their uses include: data cleansing, renaming fields, and commenting.
There’s a step-by-step walkthrough on how to reduce the number of tables, by adding the required columns of the ‘unwanted’ tables to a table that you want to keep, via a mapping table. As an example, the TerritoryDesc field is added to the Orders table – via a mapping table, and then the Territories table is removed from the model. The end result is a simplified data model.
Concatenation is another method of reducing the number of fact tables (a single fact table is optimal), this is similar to using the UNION ALL statement in SQL. Examples are provided of the various types of concatenation.
The chapter briefly discusses JOINS, explaining the different types (inner, left, right, and outer). Various joins are shown, with the aim of reducing the number of tables in the data model. One example shows the joining of the two fact tables (Orders and Order Detail). The chapter ends with a review of the latest data model, having significantly fewer tables.
This chapter provides an overview of the methods that can be applied to a data model to reduce the number of tables, aiming for a star schema. Fewer tables mean less joins and better performance.
Chapter 4 Data Modeling Challenges
When processing data from different sources there are often challenges in: data formats, having multiple fact tables, and performance. These problems are examined, and some solutions given.
Sometimes tables contain useful data in repeating fields (e.g. year_2006, year_2007), making this data more difficult to aggregate, these are known as crosstables. Ideally this data should be represented as row values. Details are provided on how to achieve this, however it produces two fact tables, which is undesirable. Details are given on how to combine the two fact tables using a link table.
Next, several miscellaneous features are discussed, including: using variables – useful for expressions, keeping date/time information outside fact tables, and using the IntervalMatch function (match a single value with an interval).
The chapter next looks at the QlikViewData Files (QVD), which contain a table of data exported from QlikView, it can be read and written much faster than other data sources. Details are provided on storing all the tables of the data model in QVD files. It is possible to load these QVD files via the much faster optimized load - providing certain conditions are met (e.g. no transformations).
The chapter ends with a very useful list of data modelling best practices (e.g. use of associations, script organization, use of comments, and use of QVDs).
This chapter provides a miscellany of useful QlikView features, some of which you’re sure to come across when developing your own applications.
Chapter 5 Creating Dashboards
Having created a usable data model, this chapter aims at providing visualization via the use of dashboards (or documents or applications). The chapter opens with an overview of the aims of dashboards (e.g. show KPIs). This is followed with a look at various dashboard related concepts (e.g. highlighting associations, binary load - to hide script details, menu options).
Documents can consist of multiple sheets, the various sheet objects are briefly outlined (e.g. List Box). Details are given on how to create a multi-tab application. Various objects are created, including bar chart, straight line, and scatter chart. The use of straight tables and pivot tables is explained. The end result is a very useful and detailed dashboard. The chapter ends with a brief discussion on QlikView functions.
This chapter provides a very useful step-by-step tutorial on how to build dashboards based on the data model created previously.
Chapter 6 Comparative Analysis
Dashboards can be made more powerful by applying Set Analysis, this is typically used for comparing this year to last year etc. The syntax of Set Analysis is described (i.e. Identifiers, Operators, and Modifiers), before an example gives a year-by-year comparison of sales. Next, Alternate States are discussed, these provide another means of making comparisons, and step-by-step details are given.
The chapter next looks at What-If Analysis, always popular with business users. Typically, using sliders and input boxes to provide variable input, its effect can then be viewed as various calculations/expressions are applied. A helpful example is provided.
This chapter provides a useful overview of Set Analysis, Alternate States, and What-If Analysis, all useful in making comparisons.
Chapter 7 Securing Your Application
Security is an increasingly important topic. Security in QlikView is achieved either by protecting access to your dashboards (via the use of roles and groups) or by protecting access to the underlying data using Section Access. Various features of Section Access are discussed, before providing a step-by-step walkthrough on how to implement it.
The chapter provides a useful, if short, overview on how to implement security via Section Access. I’m not sure why security via roles and groups wasn’t discussed.
Chapter 8 Application Deployment
Having created an optimized data model, and interactive dashboard, which was then secured, here we look at how to deploy this application to a server for central access. The chapter opens with a brief overview of the QlikView multi-tier architecture (Server, Publisher, Console, Access point), illustrated with a helpful diagram. The chapter then looks briefly at the different types of Client Access Licenses (CALs), which controls access to the application. The chapter ends with a very brief look at application deployment.
This book aims to introduce you to QlikView, covering the whole application lifecycle – and certainly succeeds. The book is easy to read, well written, has a sharp focus, with lots of helpful diagrams, and an easy flow between chapters. The book is very practical, with plenty of hands-on step-by-step walkthroughs.
If you’re new to QlikView, buy this book. Highly recommended.
D3.js By Example
To keep up with our coverage of books for programmers, follow @bookwatchiprog on Twitter or subscribe to I Programmer's Books RSS feed for each day's new addition to Book Watch and for new reviews.