Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Save more on your purchases! discount-offer-chevron-icon
Savings automatically calculated. No voucher code required.
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Newsletter Hub
Free Learning
Arrow right icon
Mastering QlikView
Mastering QlikView

Mastering QlikView: Let QlikView help you uncover game-changing BI data insights with this advanced QlikView guide, designed for a world that demands better Business Intelligence

Arrow left icon
Profile Icon Stephen Redmond
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (10 Ratings)
Paperback Nov 2014 422 pages 1st Edition
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial
Arrow left icon
Profile Icon Stephen Redmond
Arrow right icon
Free Trial
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5 (10 Ratings)
Paperback Nov 2014 422 pages 1st Edition
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial
eBook
S$12.99 S$71.99
Paperback
S$88.99
Subscription
Free Trial

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing
Table of content icon View table of contents Preview book icon Preview Book

Mastering QlikView

Chapter 2. QlikView Data Modeling

 

"It is a capital mistake to theorize before one has data. Insensibly one begins to twist facts to suit theories, instead of theories to suit facts."

 
 --Sherlock Holmes (Arthur Conan Doyle), A Scandal in Bohemia

In data warehousing and business intelligence, there are many approaches to data modeling. We hear of personalities such as Bill Inmon and Ralph Kimball. We talk of normalization and dimensional modeling. But we also might have heard about how QlikView can cut across all of this—we don't need to worry about data warehousing; we just load in all the data from source systems and start clicking. Right?

Well, that might be right if you want to load just a very quick application directly from the data source and aren't too worried about performance or maintainability. However, the dynamic nature of the QlikView script does not mean that we should throw out all of the best practices in data warehouse design...

Reviewing basic data modeling

If you have attended QlikView training courses and done some work with QlikView modeling, there are a few things that you will know about, but I will review them just to be sure that we are all on the same page.

Associating data

QlikView uses an associative model to connect data rather than a join model. A join model is the traditional approach to data queries. In the join model, you craft a SQL query across multiple tables in the database, telling the database management system (DBMS) how those tables should be joined—whether left, inner, outer, and so on. The DBMS might have a system in place to optimize the performance of those queries. Each query tends to be run in isolation, returning a result set that can be either further explored—Excel pivot tables are a common use case here—or used to build a final report. Queries might have parameters to enable different reports to be executed, but each execution is still in isolation. In fact...

Dimensional data modeling

There are several methodologies for implementing a data warehouse or data mart that might be useful to consider when implementing QlikView in an organization. However, for me, the best approach is dimensional modeling—often called Kimball dimensional modeling—as proposed by Ralph Kimball and Margy Ross in the book The Data Warehouse Toolkit, John Wiley & Sons, now available in its third edition.

Some other methodologies, most noticeably that proposed by Bill Inmon, offer a "top-down" approach to data warehousing whereby a normalized data model is built that spans the entire enterprise, then data marts are built off this to support lines of business or specific business processes. Now, QlikView can sit very readily in this model as the data mart tool, feeding off the Enterprise Data Warehouse (EDW). However, QlikView cannot implement the normalized EDW.

In my opinion, Kimball dimensional modeling, on the other hand, is right up QlikView...

Handling slowly changing dimensions

For many dimensions, we are not usually worried about changes being made in the underlying system. If a salesperson gets married and their surname changes from "Smith" to "Jones," we just reload the QlikView document and the new surname will appear in the selectors. However, if the same person changes from the inside sales team to the northwest sales team, just updating the data means that sales attributed to that salesperson will no longer get attributed to the correct team.

These changes to the dimensions do not happen very frequently and are called slowly changing dimensions (SCDs). Kimball defines eight different methods of handling SCDs, from Type 0 to Type 7. The first example discussed previously, the change of surname, is an example of Type 1—simply update the value (Type 0 says to use the original value). The second change, where the sales team is updated, should be handled by Type 2—add a new row to the dimension...

Dealing with multiple fact tables in one model

In data models designed around business processes, we will often have just one source fact table. If we have additional fact tables, they tend to be at a similar grain to the main fact table, which is easier to deal with. Line-of-business documents may have fact tables from lots of different sources that are not at the same grain level at all, but we are still asked to deal with creating the associations. There are, of course, several methods to deal with this scenario.

Joining the fact tables together

If the fact tables have an identical grain, with the exact same set of primary keys, then it is valid to join, using a full outer join, the two tables together. Consider the following example:

Fact:
Load * Inline [
Date, Store, Product, Sales Value
2014-01-01, 1, 1, 100
2014-01-01, 2, 1, 99
2014-01-01, 1, 2, 111
2014-01-01, 2, 2, 97
2014-01-02, 1, 1, 101
2014-01-02, 2, 1, 98
2014-01-02, 1, 2, 112
2014-01-02, 2, 2, 95
];

Join (Fact)
Load * Inline...

Drilling across with document chaining

One of the basics of dimensional modeling is the ability to drill between models to answer questions. There are a few situations in QlikView that make this an important consideration, for example:

  • We might have multiple data models, with some shared dimensions, that might be difficult technically, or even excluded by license, to associate within one QlikView document.
  • Most analysis for most users can be performed on an aggregated, low-memory-footprint data model, but for some users on some occasions, they need to drill down to a lower level of detail.
  • In some situations, the number of applications is not a consideration, and we create multiple applications within different business areas but want users to have some options to link between them.

QlikView handles this quite well with the document chaining function. As with any other system where you need to drill across, the ability to do so is entirely dependent on the use of conformed dimensions.

To enable...

Summary

This chapter has had a lot of really important information. We started by reviewing what you should already know about associating data. You learned important information about keys and autonumbering and the level of calculations used in QlikView. We also reviewed the different methods of stitching data together—join, concatenate, and mapping.

We then moved on to talk about dimensional data modeling, fact and dimension tables, and best practices from Ralph Kimball. You learned how to handle SCDs and multiple fact tables and how to drill across tables.

The previous chapter dealt with loading data for performance. The next chapter will help us continue our learning of how best to load data, building QVD layers, to support a dimensional modeling approach.

Left arrow icon Right arrow icon

Description

If you are a business application developer or a system analyst who has learned QlikView and Qlik Sense and now want to take your learning to a higher level, then this book is for you. It is assumed that you are aware of the fundamentals of QlikView and have working knowledge of development and in-memory analytics.

What you will learn

  • Tune your Qlik applications and review basic performancetuning 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

Product Details

Country selected
Publication date, Length, Edition, Language, ISBN-13
Publication date : Nov 26, 2014
Length: 422 pages
Edition : 1st
Language : English
ISBN-13 : 9781782173298
Category :
Tools :

What do you get with a Packt Subscription?

Free for first 7 days. $19.99 p/m after that. Cancel any time!
Product feature icon Unlimited ad-free access to the largest independent learning library in tech. Access this title and thousands more!
Product feature icon 50+ new titles added per month, including many first-to-market concepts and exclusive early access to books as they are being written.
Product feature icon Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.
Product feature icon Thousands of reference materials covering every tech concept you need to stay up to date.
Subscribe now
View plans & pricing

Product Details

Publication date : Nov 26, 2014
Length: 422 pages
Edition : 1st
Language : English
ISBN-13 : 9781782173298
Category :
Tools :

Packt Subscriptions

See our plans and pricing
Modal Close icon
$19.99 billed monthly
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Simple pricing, no contract
$199.99 billed annually
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just S$6 each
Feature tick icon Exclusive print discounts
$279.99 billed in 18 months
Feature tick icon Unlimited access to Packt's library of 7,000+ practical books and videos
Feature tick icon Constantly refreshed with 50+ new titles a month
Feature tick icon Exclusive Early access to books as they're written
Feature tick icon Solve problems while you work with advanced search and reference features
Feature tick icon Offline reading on the mobile app
Feature tick icon Choose a DRM-free eBook or Video every month to keep
Feature tick icon PLUS own as many other DRM-free eBooks or Videos as you like for just S$6 each
Feature tick icon Exclusive print discounts

Frequently bought together


Stars icon
Total S$ 244.97
Mastering QlikView
S$88.99
QlikView 11 for Developers
S$88.99
QlikView for Developers Cookbook
S$66.99
Total S$ 244.97 Stars icon
Banner background image

Table of Contents

8 Chapters
1. Performance Tuning and Scalability Chevron down icon Chevron up icon
2. QlikView Data Modeling Chevron down icon Chevron up icon
3. Best Practices for Loading Data Chevron down icon Chevron up icon
4. Data Governance Chevron down icon Chevron up icon
5. Advanced Expressions Chevron down icon Chevron up icon
6. Advanced Scripting Chevron down icon Chevron up icon
7. Visualizing Data Chevron down icon Chevron up icon
Index Chevron down icon Chevron up icon

Customer reviews

Top Reviews
Rating distribution
Full star icon Full star icon Full star icon Full star icon Half star icon 4.5
(10 Ratings)
5 star 70%
4 star 10%
3 star 20%
2 star 0%
1 star 0%
Filter icon Filter
Top Reviews

Filter reviews by




Neno Jan 02, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I bought separately both types of this great book, the printed version and the e-book version.This is only my second review as I am harsh to myself and then consequently to others and hard to get thrilled. The first one was about "QlikView 11 for Developers" written by Barry Harmsen and Miguel Garcia.So, this is a "must" for every serious QlikView developer. A book that will give you unexpected insights evevnthough you might went through all classical QlikView classroom training sesssions and have read 5-7 books about QlikView. When I noted this title I was a bit sceptical about real need to have it after having available great books, like previously mentioned "QlikView 11 for Developers", and "QlikView for Developers Cookbook" by Stephen Redmond, but then still decided to increase my personal Qlik library. I have read it over Christmas holidays and was very much surpirsed with the knowledge and increased understanding of QV and then decided to write this review.If you are or plan to be serious about QlikView you need to read these books, in this order: already mentioned "QlikView 11 for Developers", "QlikView for Developers Cookbook" and "Mastering QlikView".Not to mislead potential buyers and readers, I would only remove the Qlik Sense name from the cover as this book really does not deal with this new product, a true self-service BI tool. Of course, when it comes to scripting than it does, but then, there is no difference between QlikView and Qlik Sense from the scripting point of view. The difference lies in the front end and only the geneal thoughts and suggestions in the last chapter about visualisations are not enough to show the difference between the two products.Finally, I would really recommend this book to any QlikView developer.
Amazon Verified review Amazon
Vassya Sep 01, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Brilliant book for every QilkView-practitioner. A lot of valuable insights and useful technics available in this book will help reader grow in level.
Amazon Verified review Amazon
swarup Oct 27, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I found this book to be very useful .Well written ,easy to understand and implement
Amazon Verified review Amazon
Neno Jan 02, 2015
Full star icon Full star icon Full star icon Full star icon Full star icon 5
I bought separately both types of this great book, the printed version and the e-book version.This is only my second review as I am harsh to myself and then consequently to others and hard to get thrilled. The first one was about "QlikView 11 for Developers" written by Barry Harmsen and Miguel Garcia.So, this is a "must" for every serious QlikView developer. A book that will give you unexpected insights evevnthough you might went through all classical QlikView classroom training sesssions and have read 5-7 books about QlikView. When I noted this title I was a bit sceptical about real need to have it after having available great books, like previously mentioned "QlikView 11 for Developers", and "QlikView for Developers Cookbook" by Stephen Redmond, but then still decided to increase my personal Qlik library. I have read it over Christmas holidays and was very much surpirsed with the knowledge and increased understanding of QV and then decided to write this review.If you are or plan to be serious about QlikView you need to read these books, in this order: already mentioned "QlikView 11 for Developers", "QlikView for Developers Cookbook" and "Mastering QlikView".Not to mislead potential buyers and readers, I would only remove the Qlik Sense name from the cover as this book really does not deal with this new product, a true self-service BI tool. Of course, when it comes to scripting than it does, but then, there is no difference between QlikView and Qlik Sense from the scripting point of view. The difference lies in the front end and only the geneal thoughts and suggestions in the last chapter about visualisations are not enough to show the difference between the two products.Finally, I would really recommend this book to any QlikView developer.
Amazon Verified review Amazon
Jerry DiMaso Dec 29, 2014
Full star icon Full star icon Full star icon Full star icon Full star icon 5
Stephen Redmond is a QlikView expert, as indicated by his book Mastering QlikView in which he clearly illustrates myriad advanced techniques from data modeling and performance tuning to expression writing and visualization best practices. While there is a disclaimer in the beginning of the book that the content caters to more advanced QlikView developers, I believe that Mastering QlikView is a must-read for anyone endeavoring to create QlikView applications. Though having in-depth knowledge of the tool does help to provide more context to the optimization portions, Redmond does an excellent job of giving the reader a basic overview of each chapter’s content prior to delving into more detail; it doesn’t read at all like a technical manual, but more like a tutorial.Mastering QlikView starts with a very technical-heavy performance tuning and optimization guide with detailed instructions on how to debug any QlikView application for slow behavior; the section on using JMeter for user simulation is particularly well-written and explains each step carefully without assuming foreknowledge of the tool itself. Redmond also explains how to most efficiently acquire test data and provides links to sources he uses when testing a QlikView environment. He then goes on to detail the process of data modeling, theories on data warehousing, and performance testing on the front end – he even includes a guide on how to use Expressor to optimize the ETL process.What struck me most about this book is that it not only answers the question of “How?” but also the question of “Why?”. Redmond gives sound reasoning and empirical proof of the benefits of using a variety techniques for each topic he covers. The section on data visualization is imperative for any developer to have in his or her back pocket in order to understand how and why users interact with QlikView applications (or really anything) the way that they do – he uses an example of a door with a handle that says “PUSH”, a situation to which I’m sure all of us can relate.Mastering QlikView is a great addition to any QlikView developer’s bookshelf – even the most sophisticated developer would certainly come away with something new or at the very least a better understanding of QlikView. I will certainly be referring back to this book in my future engagements and would recommend that any QlikView developer, novice or expert, keeps Mastering QlikView close at hand while creating QlikView applications.
Amazon Verified review Amazon
Get free access to Packt library with over 7500+ books and video courses for 7 days!
Start Free Trial

FAQs

What is included in a Packt subscription? Chevron down icon Chevron up icon

A subscription provides you with full access to view all Packt and licnesed content online, this includes exclusive access to Early Access titles. Depending on the tier chosen you can also earn credits and discounts to use for owning content

How can I cancel my subscription? Chevron down icon Chevron up icon

To cancel your subscription with us simply go to the account page - found in the top right of the page or at https://subscription.packtpub.com/my-account/subscription - From here you will see the ‘cancel subscription’ button in the grey box with your subscription information in.

What are credits? Chevron down icon Chevron up icon

Credits can be earned from reading 40 section of any title within the payment cycle - a month starting from the day of subscription payment. You also earn a Credit every month if you subscribe to our annual or 18 month plans. Credits can be used to buy books DRM free, the same way that you would pay for a book. Your credits can be found in the subscription homepage - subscription.packtpub.com - clicking on ‘the my’ library dropdown and selecting ‘credits’.

What happens if an Early Access Course is cancelled? Chevron down icon Chevron up icon

Projects are rarely cancelled, but sometimes it's unavoidable. If an Early Access course is cancelled or excessively delayed, you can exchange your purchase for another course. For further details, please contact us here.

Where can I send feedback about an Early Access title? Chevron down icon Chevron up icon

If you have any feedback about the product you're reading, or Early Access in general, then please fill out a contact form here and we'll make sure the feedback gets to the right team. 

Can I download the code files for Early Access titles? Chevron down icon Chevron up icon

We try to ensure that all books in Early Access have code available to use, download, and fork on GitHub. This helps us be more agile in the development of the book, and helps keep the often changing code base of new versions and new technologies as up to date as possible. Unfortunately, however, there will be rare cases when it is not possible for us to have downloadable code samples available until publication.

When we publish the book, the code files will also be available to download from the Packt website.

How accurate is the publication date? Chevron down icon Chevron up icon

The publication date is as accurate as we can be at any point in the project. Unfortunately, delays can happen. Often those delays are out of our control, such as changes to the technology code base or delays in the tech release. We do our best to give you an accurate estimate of the publication date at any given time, and as more chapters are delivered, the more accurate the delivery date will become.

How will I know when new chapters are ready? Chevron down icon Chevron up icon

We'll let you know every time there has been an update to a course that you've bought in Early Access. You'll get an email to let you know there has been a new chapter, or a change to a previous chapter. The new chapters are automatically added to your account, so you can also check back there any time you're ready and download or read them online.

I am a Packt subscriber, do I get Early Access? Chevron down icon Chevron up icon

Yes, all Early Access content is fully available through your subscription. You will need to have a paid for or active trial subscription in order to access all titles.

How is Early Access delivered? Chevron down icon Chevron up icon

Early Access is currently only available as a PDF or through our online reader. As we make changes or add new chapters, the files in your Packt account will be updated so you can download them again or view them online immediately.

How do I buy Early Access content? Chevron down icon Chevron up icon

Early Access is a way of us getting our content to you quicker, but the method of buying the Early Access course is still the same. Just find the course you want to buy, go through the check-out steps, and you’ll get a confirmation email from us with information and a link to the relevant Early Access courses.

What is Early Access? Chevron down icon Chevron up icon

Keeping up to date with the latest technology is difficult; new versions, new frameworks, new techniques. This feature gives you a head-start to our content, as it's being created. With Early Access you'll receive each chapter as it's written, and get regular updates throughout the product's development, as well as the final course as soon as it's ready.We created Early Access as a means of giving you the information you need, as soon as it's available. As we go through the process of developing a course, 99% of it can be ready but we can't publish until that last 1% falls in to place. Early Access helps to unlock the potential of our content early, to help you start your learning when you need it most. You not only get access to every chapter as it's delivered, edited, and updated, but you'll also get the finalized, DRM-free product to download in any format you want when it's published. As a member of Packt, you'll also be eligible for our exclusive offers, including a free course every day, and discounts on new and popular titles.