Mastering QlikView

Mastering QlikView

by Stephen Redmond
Released November 2014
Publisher(s): Packt Publishing
ISBN: 9781782173298

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Unleash the power of QlikView and Qlik Sense to make optimum use of data for Business Intelligence

In Detail

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.

What You Will Learn

  • Tune your Qlik applications and review basic performance-tuning techniques for scalability
  • Model your data correctly using industry best approaches
  • Get to know the best practices for data loading and understand QVDs to implement an ETL approach
  • Discover a range of techniques to manage metadata including the QlikView Governance Dashboard
  • Use advanced expression techniques to get the right answers
  • Write great scripts to load data in different ways
  • Visualize data using a set of analytical and advanced charting techniques

Table of contents

  1. Mastering QlikView
    1. Table of Contents
    2. Mastering QlikView
    3. Credits
    4. About the Author
    5. About the Reviewers
    6. www.PacktPub.com
      1. Support files, eBooks, discount offers, and more
        1. Why subscribe?
        2. Free access for Packt account holders
        3. Instant updates on new Packt books
    7. Preface
      1. Qlik Sense
      2. What this book covers
      3. What you need for this book
      4. Who this book is for
      5. Conventions
      6. Reader feedback
      7. Customer support
      8. Downloading the example code
        1. Errata
        2. Piracy
        3. Questions
    8. 1. Performance Tuning and Scalability
      1. Reviewing basic performance tuning techniques
        1. Removing unneeded data
          1. Reducing the number of rows
          2. Reducing the number of columns
        2. Replacing text keys with numbers
        3. Resolving synthetic keys
        4. Reviewing the basics
      2. Generating test data
        1. Generating dimension values
        2. Generating fact table rows
      3. Understanding how QlikView stores its data
        1. A great primer
        2. Looking at things from a simple level
        3. Exporting the memory statistics for a document
      4. Strategies to reduce the data size and improve performance
        1. Optimizing field values and keys
        2. Optimizing data by removing keys using ApplyMap
        3. Optimizing performance by removing keys by joining tables
        4. Optimizing memory by removing low cardinality fields
      5. Testing chart performance for different load options
        1. Turning the cache off
        2. Examining the chart calculation time for different scenarios
        3. Optimizing performance by creating counter fields
        4. Optimizing performance by combining fact tables?
        5. Optimizing your numbers
      6. Optimizing chart calculation times
      7. The QlikView calculation engine
        1. Creating flags for well-known conditions
          1. Sorting for well-known conditions
      8. Using Direct Discovery
        1. Direct Discovery restrictions
        2. Direct Discovery syntax
        3. Looking at an example Direct Query
      9. Testing scalability with JMeter
        1. Obtaining the scalability tools
        2. Installing JMeter
        3. Installing the scalability tools
        4. About the scalability tools
      10. Running an example execution
      11. Summary
    9. 2. QlikView Data Modeling
      1. Reviewing basic data modeling
        1. Associating data
        2. Automatically associating tables
          1. Understanding synthetic keys
          2. Creating composite keys
            1. Using string concatenation
            2. Using one of the Hash functions
            3. Using the AutoNumber function
        3. Realizing that facts are calculated at the level of their table
        4. Joining data
          1. Understanding Join and Keep
            1. Inner joins
            2. Left and right joins
            3. Outer joins
            4. Cartesian joins
            5. Understanding the effect of duplicate key values on joins
            6. Understanding Keep
          2. Concatenating rows
          3. Reviewing Concatenate
            1. Differentiating Concatenate and Join
          4. Mapping data with ApplyMap
          5. Reviewing the basic functionality of ApplyMap
          6. Mapping numbers
            1. Using ApplyMap instead of Join with duplicate rows
      2. Dimensional data modeling
        1. Differentiating between facts and dimensions
        2. Understanding the grain
        3. Understanding star schemas
        4. Summing with facts
        5. Discovering more about facts
          1. Transaction fact tables
          2. Periodic snapshot fact tables
          3. Factless fact tables
        6. Dealing with nulls in fact tables in QlikView
        7. Designing dimension tables
          1. Denormalizing dimensions and conformed dimensions
          2. Understanding surrogate keys
          3. Dealing with missing or late arriving dimension values
        8. Defining Kimball's four-step dimensional design process
          1. Selecting the business process
          2. Declaring the grain
          3. Identifying the dimensions
          4. Identifying the facts
        9. Learning some useful reusable dimension methods
          1. Creating a calendar dimension
          2. Unwrapping hierarchies
            1. Creating leaves with Hierarchy
          3. Creating parent associations with HierarchyBelongsTo
        10. Creating dimensional facts
      3. Handling slowly changing dimensions
        1. Taking the most recently changed record using FirstSortedValue
        2. Using IntervalMatch with SCDs
        3. Using hash to manage from/to dates
      4. Dealing with multiple fact tables in one model
        1. Joining the fact tables together
          1. Concatenating fact tables
        2. Changing the grain of a fact table
          1. Linking fact tables of different grains
      5. Drilling across with document chaining
      6. Summary
    10. 3. Best Practices for Loading Data
      1. Reviewing data loading concepts
        1. Getting data from anywhere
        2. Loading data from QlikView
        3. Loading similar files with concatenation
        4. Loading dissimilar files with Concatenate and For Each
        5. Understanding QlikView Data files
        6. Storing tables to QVD
        7. Using QVD files
      2. Understanding why you should use an ETL approach
        1. Speeding up overall data loading
        2. Reusing extracted data in multiple documents
        3. Applying common business rules across multiple documents
        4. Creating conformed dimensions
        5. Provisioning a self-service data layer
      3. Using an ETL approach to create QVD data layers
        1. Creating a StoreAndDrop subroutine
        2. Extracting data
          1. Creating an extractor folder structure
          2. Differentiating types of scripts
          3. Executing the extractors
        3. Transforming data
          1. Creating a transformer and model folder structure
          2. Executing transformers
        4. Loading data
          1. Creating a UserApp folder structure
          2. Executing the load step
      4. Mastering loading techniques
        1. Loading data incrementally
          1. Establishing the script for the basic process
          2. Running an incremental load when data is only added
          3. Loading incrementally when data might be modified
          4. Handling deletions from the source system
          5. Handling situations where there is no modify date
        2. Partially reloading only one part of the data model
          1. Replacing a table
          2. Adding new rows to a table
          3. Managing script execution in partial reloads
        3. Loading the content of another QVW
      5. Using QlikView Expressor for ETL
        1. Introducing Expressor
        2. Understanding why to use Expressor for ETL
        3. Understanding workspaces, libraries, projects, and artifacts
          1. Creating a workspace
          2. Managing extensions
          3. Working with libraries and projects
          4. Understanding artifacts
        4. Configuring connections
          1. Configuring a File connection
          2. Connecting to a database
          3. Creating a QVX Connector Connection
        5. Configuring types and schemas
          1. Adding additional Atomic types
          2. Creating Composite types
          3. Configuring a schema
        6. Creating and packaging a basic dataflow
          1. Understanding the dataflow toolbox
            1. Inputs
            2. Outputs
            3. Transformers
            4. Utility
          2. Creating the dataflow
            1. Configuring a Read File operator
            2. Adding a Transformation operation
            3. Creating a QVX output
          3. Packaging the dataflow
      6. Summary
    11. 4. Data Governance
      1. Reviewing basic concepts of data governance
        1. Understanding what metadata is
          1. Structural metadata
          2. Descriptive metadata
          3. Administrative metadata
      2. Establishing descriptive metadata
        1. Adding document-level information
          1. Documents without any additional metadata
          2. Document Properties
          3. Management Console
        2. Naming and renaming fields
          1. Guidelines to rename fields
            1. Dimensions
            2. Key fields
            3. Measures
          2. Renaming fields using As
          3. Using Qualify
          4. Renaming fields using Rename
          5. Using a mapping table to rename fields
        3. Tagging fields
        4. Using the Tag statement to tag a field
          1. Tagging fields using a mapping table
          2. Hiding fields
            1. Hiding fields automatically based on prefix or suffix
            2. Using tagging to hide fields
        5. Adding field comments
        6. Renaming and commenting on tables
        7. Commenting in charts
          1. Commenting dimensions
          2. Entering an expression comment
        8. Automatically renaming qualified fields
      3. Extracting metadata
        1. Exporting the structure
        2. Extracting from QVD files
        3. Extracting from QVW files
      4. Deploying the QlikView Governance Dashboard
        1. Managing profiles
        2. Configuring the Dashboard options
        3. Reviewing operational information
        4. Analyzing application information
      5. Summary
    12. 5. Advanced Expressions
      1. Reviewing basic concepts
        1. Searching in QlikView
          1. Searching for text
          2. Wildcard search
            1. Normal search
            2. Fuzzy search
            3. Associative search
            4. Advanced search
          3. Searching numeric fields
            1. Numeric search
          4. Automatic interpretation of searches
          5. Multiple values search
          6. Searching in multiple listboxes
        2. Understanding bookmarks
          1. Saving a bookmark
          2. Managing bookmarks
        3. Using variables in QlikView
          1. SET versus LET
          2. Using variables to hold common expressions
          3. Using variables with Dollar-sign Expansion
        4. Limiting calculations
          1. Sum of If
          2. Flag arithmetic
          3. Calculations using variables
          4. Data islands
          5. Set Analysis
          6. Explaining what we mean by a set
            1. Set identifiers
            2. Set modifiers
      2. Understanding Dollar-sign Expansion
        1. Following the two-step process
          1. Following the steps in the script debugger
          2. Following the steps in a chart expression
          3. Understanding when the steps happen in chart expressions
        2. Using parameters with variables and Dollar-sign Expansion
        3. Using variables in expressions
      3. Using advanced Set Analysis
        1. Identifying the identifiers
        2. Understanding that modifiers are sets
        3. Set arithmetic
        4. Using searches in Set Analysis
        5. Using Dollar-sign Expansion with Set Analysis
        6. Comparing to other fields
          1. Direct field comparison
          2. Using Concat with Dollar-sign Expansion
          3. Using the P and E element functions
        7. Set Analysis with Alternate States
          1. Using Alternate States as identifiers
          2. Comparing fields between states
      4. Calculating vertically
        1. Using inter-record and range functions
        2. Applying the Total qualifier
        3. Creating advanced aggregations with Aggr
          1. Using Aggr to calculate a control chart
          2. Calculated dimensions
          3. No to nodistinct
      5. Summary
    13. 6. Advanced Scripting
      1. Reviewing the basic concepts
        1. Using Table Files Wizard
          1. Using relative paths
          2. Delimited files
          3. Fixed width files
          4. XML files
          5. HTML files
          6. QVD/QVX files
        2. Connecting to databases
          1. Using the Connect button
          2. Understanding the Connect To statement
          3. Explaining the Force 32 Bit option
          4. The Select wizard
      2. Counting records
        1. RecNo
        2. RowNo
        3. FieldValueCount
        4. NoOfRows
        5. NoOfColumns
      3. Loading data quickly
        1. Understanding compression settings
        2. Optimal loading from QVD
          1. Using an Exists clause
          2. Preloading fields into QVDs
      4. Applying variables and the Dollar-sign Expansion in the script
        1. Examining common usage
          1. Holding dates
          2. Holding paths
        2. Examining variable values during reloads
        3. Nesting Dollar-sign Expansions
        4. Passing parameters to variables – macro functions
        5. Subroutines
      5. Using control structures
        1. Branching with conditional statements
          1. If … Then … ElseIf
          2. A note about conditional functions
          3. Switch … Case
          4. When and Unless
        2. Looping in the script
          1. AutoGenerate
          2. For … Next loops
          3. For Each … Next loops
            1. FileList
            2. DirList
          4. Do … Loop
        3. Exiting
          1. Exiting the script
          2. Exiting other constructs
        4. Using variables for error handling
          1. ErrorMode
        5. ScriptError
          1. ScriptErrorCount and ScriptErrorList
      6. Examining advanced Table File Wizard options
        1. Enabling a transformation step
          1. Garbage
          2. Fill
          3. Column
          4. Context
          5. Unwrap
          6. Rotate
        2. Using the Crosstable wizard
      7. Looking at data from different directions
        1. Putting things first
          1. First
          2. FirstSortedValue
        2. Looking backwards
          1. Previous
          2. Peek
      8. Reusing code
      9. Summary
    14. 7. Visualizing Data
      1. Reviewing the history of data visualization
        1. Beginning the story
        2. Analyzing geometry
          1. Grecian influences
          2. French discord
        3. Telling stories with diagrams
          1. Educating with charts
          2. Inventing new charts
          3. Creating infographics
          4. Using data visualization to persuade
        4. Bringing the story up to date
          1. Following the leaders
            1. Edward Tufte
            2. Few
            3. Robert Kosara
            4. Alberto Cairo
            5. Andy Kirk
            6. Enrico Bertini and Stefaner Moritz
            7. Mike Bostock
      2. Understanding the audience
        1. Matching patterns
        2. Counting numbers
        3. Estimating numbers
        4. Understanding picture superiority
        5. Drawing conclusions
      3. Designing effective visualizations
        1. Understanding affordances
        2. Grading your screen's real estate
          1. Nielsen's F
          2. The Gutenberg diagram
          3. Preference for the right
        3. Positioning screen elements
          1. Charts on the left
          2. Listboxes on the right
          3. Dates on top
          4. Using the layout grid
        4. Thinking quantitatively
          1. Understanding the SFW question
        5. Designing dashboards
        6. Choosing charts
          1. Categorical comparison
          2. Trend analysis
          3. Comparing measures
          4. Low cardinality, part-to-whole comparison
          5. Tabular information
        7. Using color
          1. Color should have meaning
          2. What does RAG mean?
          3. The ink-to-data ratio
          4. Color blindness
        8. Using maps
      4. Summary
    15. Index

Product information

  • Title: Mastering QlikView
  • Author(s): Stephen Redmond
  • Release date: November 2014
  • Publisher(s): Packt Publishing
  • ISBN: 9781782173298