Mastering 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 incrementally when data might be modified

Handling deletions from the source system

Handling situations where there is no modify date

Partially reloading only one part of the data model

Replacing a table

Adding new rows to a table

Managing script execution in partial reloads

Loading the content of another QVW

Using QlikView Expressor for ETL

Introducing Expressor

Understanding why to use Expressor for ETL

Understanding workspaces, libraries, projects, and artifacts

Creating a workspace

Managing extensions

Working with libraries and projects

Understanding artifacts

Configuring connections

Configuring a File connection

Connecting to a database

Creating a QVX Connector Connection

Configuring types and schemas

Adding additional Atomic types

Creating Composite types

Configuring a schema

Creating and packaging a basic dataflow

Understanding the dataflow toolbox

Inputs

Outputs

Transformers

Utility

Creating the dataflow

Configuring a Read File operator

Adding a Transformation operation

Creating a QVX output

Packaging the dataflow

Summary

4. Data Governance

Reviewing basic concepts of data governance

Understanding what metadata is

Structural metadata

Descriptive metadata

Administrative metadata

Establishing descriptive metadata

Adding document-level information

Documents without any additional metadata

Document Properties

Management Console

Naming and renaming fields

Guidelines to rename fields

Dimensions

Key fields

Measures

Renaming fields using As

Using Qualify

Renaming fields using Rename

Using a mapping table to rename fields

Tagging fields

Using the Tag statement to tag a field

Tagging fields using a mapping table

Hiding fields

Hiding fields automatically based on prefix or suffix

Using tagging to hide fields

Adding field comments

Renaming and commenting on tables

Commenting in charts

Commenting dimensions

Entering an expression comment

Automatically renaming qualified fields

Extracting metadata

Exporting the structure

Extracting from QVD files

Extracting from QVW files

Deploying the QlikView Governance Dashboard

Managing profiles

Configuring the Dashboard options

Reviewing operational information

Analyzing application information

Summary

5. Advanced Expressions

Reviewing basic concepts

Searching in QlikView

Searching for text

Wildcard search

Normal search

Fuzzy search

Associative search

Advanced search

Searching numeric fields

Numeric search

Automatic interpretation of searches

Multiple values search

Searching in multiple listboxes

Understanding bookmarks

Saving a bookmark

Managing bookmarks

Using variables in QlikView

SET versus LET

Using variables to hold common expressions

Using variables with Dollar-sign Expansion

Limiting calculations

Sum of If

Flag arithmetic

Calculations using variables

Data islands

Set Analysis

Explaining what we mean by a set

Set identifiers

Set modifiers

Understanding Dollar-sign Expansion

Following the two-step process

Following the steps in the script debugger

Following the steps in a chart expression

Understanding when the steps happen in chart expressions

Using parameters with variables and Dollar-sign Expansion

Using variables in expressions

Using advanced Set Analysis

Identifying the identifiers

Understanding that modifiers are sets

Set arithmetic

Using searches in Set Analysis

Using Dollar-sign Expansion with Set Analysis

Comparing to other fields

Direct field comparison

Using Concat with Dollar-sign Expansion

Using the P and E element functions

Set Analysis with Alternate States

Using Alternate States as identifiers

Comparing fields between states

Calculating vertically

Using inter-record and range functions

Applying the Total qualifier

Creating advanced aggregations with Aggr

Using Aggr to calculate a control chart

Calculated dimensions

No to nodistinct

Summary

6. Advanced Scripting

Reviewing the basic concepts

Using Table Files Wizard

Using relative paths

Delimited files

Fixed width files

XML files

HTML files

QVD/QVX files

Connecting to databases

Using the Connect button

Understanding the Connect To statement

Explaining the Force 32 Bit option

The Select wizard

Counting records

RecNo

RowNo

FieldValueCount

NoOfRows

NoOfColumns

Loading data quickly

Understanding compression settings

Optimal loading from QVD

Using an Exists clause

Preloading fields into QVDs

Applying variables and the Dollar-sign Expansion in the script

Examining common usage

Holding dates

Holding paths

Examining variable values during reloads

Nesting Dollar-sign Expansions

Passing parameters to variables – macro functions

Subroutines

Using control structures

Branching with conditional statements

If … Then … ElseIf

A note about conditional functions

Switch … Case

When and Unless

Looping in the script

AutoGenerate

For … Next loops

For Each … Next loops

FileList

DirList

Do … Loop

Exiting

Exiting the script

Exiting other constructs

Using variables for error handling

ErrorMode

ScriptError

ScriptErrorCount and ScriptErrorList

Examining advanced Table File Wizard options

Enabling a transformation step

Garbage

Fill

Column

Context

Unwrap

Rotate

Using the Crosstable wizard

Looking at data from different directions

Putting things first

First

FirstSortedValue

Looking backwards

Previous

Peek

Reusing code

Summary

7. Visualizing Data

Reviewing the history of data visualization

Beginning the story

Analyzing geometry

Grecian influences

French discord

Telling stories with diagrams

Educating with charts

Inventing new charts

Creating infographics

Using data visualization to persuade

Bringing the story up to date

Following the leaders

Edward Tufte

Few

Robert Kosara

Alberto Cairo

Andy Kirk

Enrico Bertini and Stefaner Moritz

Mike Bostock

Understanding the audience

Matching patterns

Counting numbers

Estimating numbers

Understanding picture superiority

Drawing conclusions

Designing effective visualizations

Understanding affordances

Grading your screen's real estate

Nielsen's F

The Gutenberg diagram

Preference for the right

Positioning screen elements

Charts on the left

Listboxes on the right

Dates on top

Using the layout grid

Thinking quantitatively

Understanding the SFW question

Designing dashboards

Choosing charts

Categorical comparison

Trend analysis

Comparing measures

Low cardinality, part-to-whole comparison

Tabular information

Using color

Color should have meaning

What does RAG mean?

The ink-to-data ratio

Color blindness

Using maps

Summary

Index