Document Segmentation Best Practices
QlikView Enterprise Best Practices
Document Segmentation
Segmenting QlikView Documents for Performance and Scalability
Version: 1.0
Date: 2009-03-10
Author(s) BPN
[3]
Document Segmentation Best Practices
Table of Contents
Overview………………………………………………………………3
Sample Architectures……………………………………………….. 4-6
Application Linking……………………………………………………7-8
Summary………………………………………………………………8
Overview
Segmentation is a process of dividing up QlikView applications (documents) to accommodate needs in the areas of performance, security, scalability, simplicity and maintenance. The key to segmentation strategies is to utilize a consistent data model so that there is not increased maintenance needed to manage the segmented applications. This document covers several examples of segmentation and descriptions of how and when these techniques would be best used. Please note: it is not the intention of this document to suggest that you need to segment your QlikView applications as a best practice. The reader should take note of the situations where this is advantageous and apply these techniques thoughtfully where appropriate. See the QlikView Data Architectures.pdf document for examples of data architecture for large deployments.
Why Would I Segment My Apps?
There are several reasons to segment QlikView applications. The most common reasons are:
· Monolithic apps are too large for mass distribution
· “Simplification” through subject-matter views that roll up and across
· Increased security through segmentation
· Line of sight between dashboards and analytics apps
The Data Model
Re-use of a data model is essential to segmentation, so let’s take a look at the data models. There are two approaches to re-using a data model across QlikView applications (assuming we don’t count copy/paste of source queries across applications as a viable technique). These two approaches are binary reloads and QVD reloads.
The binary reloads fully load a QVW’s data model into another application. You can then retain all of the data or limit the data through filters or selectively dropping tables and/or columns.
The QVD reloads method is most often used within a two-tiered architecture. See the Two-Tier Application Architectures.pdf document in the Enterprise Best Practices framework for an example of this architecture.
Sample Architectures
1) Single monolithic application is too large to distribute to my whole audience
Scenario: I have a retail application with 500+ million rows of data from 12 tables across 48 columns. With all data loaded the QVW would be 5.4 GB and results in an 22 GB footprint in RAM on the server. The analytics (and more importantly the insights) in the application are extremely valuable so we would like to expose them to our retail analysts across our departments as well as our executives and a few power analysts that do the bulk of the detailed analytics. Segmentation will allow us to “break up” this application to serve those different groups without each of them incurring the full cost of RAM and CPU of the full version of the application. We can do this with very little maintenance overhead to manage the segmented versions.
*The depicted linking between applications is discussed in a later section in this document.
How to implement this solution:
This solution is predicated on pre-aggregation of data. This is much easier in a QVD-based data architecture, but can be accomplished without it. The Detailed Analysis application is the starting point, as the scenario above describes. Designing the summarized QVDs is the first step.
Let’s assume we have an Orders QVD with 5+ years of data pre-loaded. This QVD is appended to each day with new transactions and is so large (4.8 GB) that we cannot justify using it on the Executive dashboard (above) and other applications with large audiences. The data is far too granular to be useful in high level executive metrics so we need an aggregated version of this QVD for portability and scalability to mass audiences. Moreover, we would like to re-use the aggregated amounts across several applications without each of them having to aggregate in their scripts.
To build the aggregate QVD we add the following code to an existing QVD Reload application (perhaps the one that loads the Orders.QVD or a new application that handles aggregates).
LOAD
YearName([Order Date]) as [Order Year],
MonthName([Order Date]) as [Order Month],
[Order Type],
[Region ID],
[Division ID],
[Payment Type],
SUM(Price) as Price,
SUM(Tax) as Tax,
SUM(Discount) as Discount,
SUM([Items Sold]) as [Items Sold],
Avg(Margin) as AvgMargin
FROM [\\Prod\QVD\Orders.qvd] (qvd)
GROUP BY [Order Date], [Order Type], [Region ID],
[Division ID], [Payment Type];
Note how much smaller the new aggregated version of the QVD is now.
Following this simplified example using the Orders data, let’s look at how the three applications in this scenario might be loaded from QVDs:
2) Customer base requires subject-matter views that roll up to Executive Dashboard
Scenario: I have a CRM application which is comprehensive of several customer subject areas. Each of the subject areas corresponds to a department within our company. We are in need of not only dashboard and comprehensive detailed views of the application, but also subject-area views that are specific to each department. The managers and analysts of these departments will primarily use the departmental views of the application, while executives will use the dashboard and power users will use the detailed analytics application.
How to implement this solution:
The Customer 360 application in this diagram represents a “fully loaded” application with all base QVDs used for source data. This would be a very large application and in this scenario described above we would only grant access (through Publisher) to a very small group of power users.
The 4 applications represented as vertical slices above represent “loop and reduce” versions of the Customer 360 application. Publisher controls the loop and reduce logic through the “Create Reduction” feature in Task Management, an example of which is shown below.
The Executive CRM Dashboard depicted in the diagram can be created from aggregations on the base QVDs (which the other apps use) or against aggregated script from a binary load of the 360 application.
While it’s important to note that security can be accommodated through data level authorization, this solution also helps add a file security layer for the departmental users (assuming distribution settings or section access prohibit them from viewing the other apps outside their department).
Document Chaining
Document chaining refers to the linking (or jumping) from one application to another and maintaining some sense of “state” or selections that the user had made prior to linking. These techniques can be useful in the following scenarios:
- Line of sight linking from a dashboard to analysis application
- Departmental drill-down from a dashboard to a departmental application
- Subject jumping from one subject-matter application to another
Linking Techniques
- Using a macro to fire off second application with selection values as parameters
Note: dimensions are hard-coded into macros individually. Works with QV8 or QV9.
- Using a macro and bookmark to pass and open in linked document
Note: all selections applied. Bookmarks are saved – requires system access. Works with QV8 or QV9.
Version 9 Document Chaining Feature
- Launching a QlikView document through the new QlikView 9 actions, passing state values from the first application to the second.
Note: All selections and state applied. Requires QV9.
Please see the DocSeg Demo – Dashboard and DocSeg Demo – Analysis QlikView demo applications for examples of how to implement these linking techniques. These are located in the Enterprise Best Practices folder under the Development/Application Architecture topic.
Summary
The benefits or Document Segmentation in QlikView are:
- Decreased CPU and RAM consumption
- Decreased training needed for some apps
- Decreased maintenance (less unnecessary dashboard changes)
- Increased performance
- Increased adoption rates
- More flexible solutions
- More scalable architecture
Document segmentation can be a great addition to your QlikView architecture when faced with challenges with application size, complexity and scope. Segmentation is made feasible when re-use of a data model (or at least portions of it) is pre-architected. Taking the time to plan out the usage of applications and project their growth may lead you to segmentation as a solution. If so, take good stock of your QlikView Data Architectures (Best Practices Framework) and plan out a thoughtful approach to segmenting your applications.
[3]