Mastering QlikView

Stephen Redmond

• Publisher: Packt Publishing
• Year: 2014
• eBook ISBN: 9781782173298
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Modelling & Design
• Index: Computer Science

QlikView and its new sister product, Qlik Sense, are the leading tools for BI and data discovery. They both feature the ability to consolidate relevant data from multiple sources into a single application, an associative data model to allow you to explore the data the way your brain works, and state-of-the-art visualizations, dashboards, analysis, and reports.

The book starts by reviewing the best performance-tuning techniques and then advances to help you discover strategies to improve performance and test scalability with JMeter. You will also learn dimensional data modeling and creating best-practice ETL techniques using the QlikView script and QlikView's graphical ETL tool, Expressor. Following this, you will deploy QlikView Governance Dashboard to import multiple data sources and view all the information in a single location. Finally, you will learn why virtualization is important and what are the best practices for virtualization in QlikView.

Mastering QlikView

---

Table of Contents

Mastering QlikView

Credits

About the Author

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

Qlik Sense

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Performance Tuning and Scalability

Reviewing basic performance tuning techniques

Removing unneeded data

Reducing the number of rows

Reducing the number of columns

Replacing text keys with numbers

Resolving synthetic keys

Reviewing the basics

Generating test data

Generating dimension values

Generating fact table rows

Understanding how QlikView stores its data

A great primer

Looking at things from a simple level

Exporting the memory statistics for a document

Strategies to reduce the data size and improve performance

Optimizing field values and keys

Optimizing data by removing keys using ApplyMap

Optimizing performance by removing keys by joining tables

Optimizing memory by removing low cardinality fields

Testing chart performance for different load options

Turning the cache off

Examining the chart calculation time for different scenarios

Optimizing performance by creating counter fields

Optimizing performance by combining fact tables?

Optimizing your numbers

Optimizing chart calculation times

The QlikView calculation engine

Creating flags for well-known conditions

Sorting for well-known conditions

Using Direct Discovery

Direct Discovery restrictions

Direct Discovery syntax

Looking at an example Direct Query

Testing scalability with JMeter

Obtaining the scalability tools

Installing JMeter

Installing the scalability tools

About the scalability tools

Running an example execution

Summary

2. QlikView Data Modeling

Reviewing basic data modeling

Associating data

Automatically associating tables

Understanding synthetic keys

Creating composite keys

Using string concatenation

Using one of the Hash functions

Using the AutoNumber function

Realizing that facts are calculated at the level of their table

Joining data

Understanding Join and Keep

Inner joins

Left and right joins

Outer joins

Cartesian joins

Understanding the effect of duplicate key values on joins

Understanding Keep

Concatenating rows

Reviewing Concatenate

Differentiating Concatenate and Join

Mapping data with ApplyMap

Reviewing the basic functionality of ApplyMap

Mapping numbers

Using ApplyMap instead of Join with duplicate rows

Dimensional data modeling

Differentiating between facts and dimensions

Understanding the grain

Understanding star schemas

Summing with facts

Discovering more about facts

Transaction fact tables

Periodic snapshot fact tables

Factless fact tables

Dealing with nulls in fact tables in QlikView

Designing dimension tables

Denormalizing dimensions and conformed dimensions

Understanding surrogate keys

Dealing with missing or late arriving dimension values

Defining Kimball's four-step dimensional design process

Selecting the business process

Declaring the grain

Identifying the dimensions

Identifying the facts

Learning some useful reusable dimension methods

Creating a calendar dimension

Unwrapping hierarchies

Creating leaves with Hierarchy

Creating parent associations with HierarchyBelongsTo

Creating dimensional facts

Handling slowly changing dimensions

Taking the most recently changed record using FirstSortedValue

Using IntervalMatch with SCDs

Using hash to manage from/to dates

Dealing with multiple fact tables in one model

Joining the fact tables together

Concatenating fact tables

Changing the grain of a fact table

Linking fact tables of different grains

Drilling across with document chaining

Summary

3. Best Practices for Loading Data

Reviewing data loading concepts

Getting data from anywhere

Loading data from QlikView

Loading similar files with concatenation

Loading dissimilar files with Concatenate and For Each

Understanding QlikView Data files

Storing tables to QVD

Using QVD files

Understanding why you should use an ETL approach

Speeding up overall data loading

Reusing extracted data in multiple documents

Applying common business rules across multiple documents

Creating conformed dimensions

Provisioning a self-service data layer

Using an ETL approach to create QVD data layers

Creating a StoreAndDrop subroutine

Extracting data

Creating an extractor folder structure

Differentiating types of scripts

Executing the extractors

Transforming data

Creating a transformer and model folder structure

Executing transformers

Loading data

Creating a UserApp folder structure

Executing the load step

Mastering loading techniques

Loading data incrementally

Establishing the script for the basic process

Running an incremental load when data is only added

Loading increm...

Table of contents

• Mastering QlikView