Automation server for Qlik Sense

Qlik Sense is in many ways a more advanced platform than its predecessor, QlikView. Scalability, rich APIs, enterprise-level administration -- there are many features of a good architecture in it. However, what can be challenging for Qlik Sense customers (besides dealing with rudimentary data visualization) is automation. QlikView had embedded VBA scripting engine, which let designing automation scenarios initiated by users, but Qlik Sense doesn't have it. Disabled in the standard mode EXECUTE statement only aggravates the situation.

In this article, I'm proposing to extend Qlik Sense's capabilities with an automation server, based on EasyMorph Server. Such extension significantly simplifies a whole range of automation scenarios initiated by Qlik Sense user, that are difficult or non-trivial to implement otherwise. For instance:

• Database writeback based on current selection in Qlik Sense.
• One-click export of a subset of data from a Qlik Sense app into an external system or a disk folder.
• Sending personalized emails with attached customized data extracts from Qlik Sense.
• Downloading a particular file from a web-site and dynamically adding its content to a Qlik Sense app.
• Automated data quality checks of incoming source data with rule-based email notifications.

The integration mechanism between Qlik Sense and EasyMorph Server is based on REST API and Websockets (see the diagram below):

Click to zoom

Actions are initiated by a Qlik Sense user by clicking a dynamically generated hyperlink, or an extension button. This triggers an EasyMorph Server task which runs an EasyMorph project with specified parameters (passed through the hyperlink or extension). The project performs required actions with external files and systems. Finally, the task status is reported back into the Qlik Sense app that initiated it. Alternatively, the task initiates a full or partial reload of the app using the REST API. 

A few benefits of such integration scheme:

• In one Qlik Sense application there can be multiple action buttons that initiate different actions.
• It works well with tiered Qlik Sense apps, where one app is for ETL, another for building a data model, and another one for the UI objects.
• Closed loop feedback: task status and result are reported back to the user. If the task fails the errors will be reported to the user as well.
• Task parameters can be assigned dynamically using Qlik Sense variables and expressions.
• The action server can be hosted on a different machine thus reducing exposure of the Qlik Sense sever.

At this point we're half-way to implementing the integration scheme described above. This means that some of its elements are already in place, while other are in active development and will be released soon. As of version 3.7.1 already available:

• EasyMorph Server REST API
• Triggering EM Server tasks via hyperlinks that can contain task parameters
• Executing external applications and Windows shell commands.
• File commands: copying, downloading, unzipping, etc.
• Conditional IF...THEN...ELSE workflows
• PowerShell integration (think VBA replacement)
• Sending emails with attachments
• 90+ ETL transformations and 100+ functions.

The tool set described above is already suitable for adding automation capabilities to Qlik Sense apps. For instance database writebacks, extract generation or email sendouts are already possible by using dynamic hyperlinks in Qlik Sense applications. For better user experience and more advanced automation capabilities, a few more features are being developed and planned for release in version 3.8:

• An interactive Qlik Sense app extension for triggering EM Server tasks and monitoring task status and errors in real-time.
• The Qlik Sense Command transformation for triggering reloading Qlik Sense apps and QMC tasks right from EasyMorph projects.
• Fetching emails and processing attachments.

With the addition of these features, the full integration scenario described in this article becomes possible. Besides that, EasyMorph will be able to work as a visual data transformation tool for Qlik Sense:

Click to zoom

In this case, a user triggers (through a link or extension) an EasyMorph task that generates QVD files (one file per one table in data model) and initiates reloading of the Qlik Sense app that called it. The app loads the generated QVDs.

If you would like to talk about about automation for Qlik Sense, send me an email (you can find my address in the upper right area of this blog or here).

To receive future updates on EasyMorph and its integrations with Qlik and other systems, subscribe to our newsletter on the download page.

IT execs should not tell business users to choose between Tableau and Qlik

IT executives should not force business users to choose between Tableau and Qlik. After all, business users don't tell IT to choose between Windows and Linux.

Such "standardization", while still very popular in many organizations, brings more harm than good. It's originates from a few myths:

Myth #1: All BI tools are basically the same

This is no more true than saying "All airplanes are basically the same". Such altitude is coming from poor understanding of the purpose of Business Intelligence tools and capabilities of products available on the market. If two applications show charts and allow analyzing data it doesn't make them functionally equivalent because there is huge variety in the ways how data can be viewed and analyzed. Tell a good chef that all knives are basically the same therefore s/he should pick and use only one knife. Because, you know, "standardization".

BI tools are not the same. The task of data analysis and visualization is so complex and broad that no vendor can create a universal comprehensive solution, just like neither Boeing nor Airbus can create one universal airplane suitable for all cases -- from long passenger flights to air warfare to rescue operations.

For instance Qlik has amazing associative engine that allows easy discovery of logical relationships in data. Tableau has absolutely wonderful data visualization concept that unveils hidden patterns and provides meaningful perspectives that could be easily overlooked. Spotfire offers comprehensive means for scientific analysis and predictive modelling. The core concepts of these applications don't overlap. Some features surely do, but that doesn't make them interchangeable. Other BI tools also have their strong features. Any analytical application that has deeply thought-out, fundamental concept behind it will be significantly different from others.

Myth #2: Standardization is always a good thing

This myth is logically connected to Myth #1. Standardization, when it's applicable, has obvious benefits most of which boil down to one -- cost reduction. A company can get a deeper discount if it purchases more software licenses. You can save on training if you train employees only for 1 tool, instead of many. More people with similar skills are interchangeable, therefore less risk of losing critical expertise, also reduced staff count. And so on.

However, any cost reduction is only good when it doesn't degrade key functional capabilities. What would happen if you force a chef to "standardize" on knives? His/her functional capability would degrade. What would happen if you tell a military air force to use the same type of plane for cargo transportation and air dogfighting? Its functional capability would degrade. That's why nobody does it.

Myth #3: All business users have similar data analysis needs

There is a stereotypical understanding in the BI world that there are three main types of BI users: regular users (receive specialized reports), advanced users (interactively analyze and research data) and management users (look at dashboards, monitor high-level KPIs). It's an easy, simple and misleading classification. Misleading because BI adoption is never a one-way street. Unlike transactional systems, BI is still optional. It's hard to force people to use some tool if they don't like it or don't understand how to use it. In the case of BI adoption, they can always retreat to good old Excel (and they frequently do).

People are different and they have different preferences. When it comes to data analysis, two persons doing the same task might have different views on how to do it best. A good sign of a person who knows what s/he is doing is whether s/he has strong opinion on tools needed for the job. Therefore, business users themselves should pick what they need. If business users are okay with any analytical application given to them and have no own opinion on it then they don't really need it and purchasing it would be a waste of money.

Myth #4: Business users can do a good evaluation in a 2 month period

Data analysis is a rapidly developing discipline. It's developing in many directions: methods and techniques, visualizations, processing algorithms, metadata governance, unstructured data processing, etc. The times when a BI system was simply a visual SQL query generator with some interactive charting are long gone. BI is complex nowadays, and its complexity will only increase. Even best analytical applications on the market have rather steep learning curve, despite claims about the opposite in PR/marketing campaigns. Modern BI applications can be relatively easy to start with, but as soon as something slightly non-trivial is needed the learning curve skyrockets. Look at online forums like Tableau Community, or Qlik Community -- they are full of people asking tons of how-to questions which sometimes require rather lengthy and detailed answers.

I believe that a good understanding of capabilities of a data analysis application can be developed after at least 1 year of using the application regularly on complex real-life projects. That's in a case when there was no any previous experience with analytical applications. Or at least 6 months, if there was some (which means that you should be already familiar with some concepts). Asking business users without any previous experience with BI applications to provide a feedback on an application based on 2-3 month evaluation of some demo/PoC dashboard (i.e. which are not in production use) -- is a sure (albeit very common) way to get wrong conclusions. Don't do that.

OK, what's the alternative?

Tool as a Service (a.k.a. The Data Kitchen)

At this point you probably started suspecting that modern BI applications are not just more powerful than ever but also more complex than ever, and are more different from each other than ever. Comparing Tableau with Qlik makes as much sense as comparing apples with oranges. They are all round, after all, aren't they?

I believe that the most efficient way to adopt Business Intelligence is the one where adoption grows organically. IT departments should create an environment that fosters such organic growth, instead of limiting and restricting it for the purpose of hypothetical cost reduction. They should embrace the data kitchen concept, where multiple tools are available for the users who are looking for different ways to work with data. We can call it "Tool as a Service" if you will. Don't standardize on one BI system -- it's not going to work well. Ask business users what they like, and help them make it work from a technical perspective. It's the business users who should decide what to use and when. It's them who will accumulate the expertise of using the applications, not the IT people.

Practically, it means that teams as small as 5-10 people, or as big as 100 (or maybe even more) evaluate and test analytical applications themselves. The IT management should be ready that different teams may choose different applications. It's the users who should decide what works best for them. If they need two tools with somewhat overlapping features -- give them access to both. If more -- let them use as many as they need.

It doesn't mean that you will have to purchase every possible software for every user. Start with small packs of licenses for a few applications chosen by business users. Add more licenses when popularity of one of the applications increases. In this case license usage will correlate with growth of expertise. It's more efficient (also from a cost perspective) than spending millions for enterprise agreements then forcing everyone to use only the "standard" BI tool because "we spent so much money on it".

How to use the memstat metrics in QViewer

Starting from version 3.1 QViewer shows two memstat metrics in Table Metadata window:

• Size, bytes -- total size of the column in bytes
• Avg.bytes per symbol -- average size of column values in byte 

click to zoom

These metrics are calculated similarly to memstat data available in QlikView (not available in Qlik Sense so far). Since the structure of QVD files is very close to the internal in-memory data format in Qlik these metrics can be used to optimize (reduce) memory footprint of resident tables which can be desirable for particularly large applications. The most convenient way to inspect resident tables in QViewer is setup a simple generic subroutine as described here. Alternatively, you can insert temporary STORE statements to save resident tables into QVDs and then open them in QViewer manually.

When looking at the memstat metrics in QViewer you would typically want to identify columns that take most of space (hint: click column headers in Table Metadata to sort the grid). A few things that you can do reduce table size:

• Remove unnecessary columns that take a lot of space
• Force Qlik to convert duals to numbers by multiplying them by 1
• Trim text values to remove trailing and leading spaces
• Use integers instead of floats where possible
• Round up floats to fewer decimal digits to have fewer distinct values in the column
• Use autonumbers instead of long composite text keys

Read also "A few tips for dealing with large QlikView applications".

Remarks
Qlik uses special format for columns with incrementally increasing integers (autonumbers) -- they basically don't  take up any space in memory, although QVDs store them as regular columns.

The memstat metrics are calculated correctly even if QVDs are partially loaded in QViewer. Therefore you can use them in the free version of QViewer, or when partial loading was used.

QVDs generated in Qlik Sense v.2 and above are compatible with QVDs generated in QlikView and therefore can be opened in QViewer as well.

The total size is NOT simply the avg. symbol size multiplied by # of rows -- it's calculated using a more complicated logic that accounts data compression.

QViewer v3: Qlik-style filtering and full table phonetic search

New major version of QViewer is out. You can download it here: http://easyqlik.com/download.html

Here is what's new and exciting about it:

Qlik-style filtering

 

click to zoom

The headline feature of the new version is the ability to filter tables in the Qlik fashion using listboxes. Applied filters can be seen in the filter bar that appears above the table (see screenshot above).

When a selection is made, value counters update automatically. The green bar charts behind the counters hint at current selection count vs total count ratio.

Note that nulls are also selectable.

Full table search

It is now possible to search for a value in entire table. Current selection becomes limited to only rows where the searched value is found. Full table search is basically another kind of filter. When it's applied, it can be seen in the filter bar.

Phonetic search mode

The full-table search and the listboxes allow looking up values by matching them phonetically. This is helpful when you don't know for sure the spelling of a word in question. For instance, if you search for "Acord" values "Accord", "Akord", "Akkort" will match. Phonetic search works for whole words only. Currently, only English phonetic matching is supported.

Cell metadata

 

You can view additional metadata of a value in question using Cell Metadata floating window. This is particularly helpful for easy detection of leading and trailing spaces in text values.

New license key format

The new version requires upgrading license keys. All license keys purchased after 1st of August, 2015 will be upgraded for free. License keys purchased prior to that date are upgraded at 50% of the current regular price. To upgrade a license key please send me your old key and indicate the email address it is linked to.

Hints and tips on using QViewer for inspecting resident tables in QlikView

Three years ago I wrote "How to look inside resident tables at any point of loading script". This technique proved to be quite successful and efficient, and was praised by many prominent QlikView developers since then.

This post is a round-up of some best practices of using QViewer for inspecting resident tables in QlikView, collected over the last 3 years:

Here is the most recent variant of the INSPECT subroutine:

SUB Inspect (T)
    // let's add some fault tolerance
    LET NR = NoOfRows('$(T)');
    IF len('$(NR)')>0 THEN
        // Table exists, let's view it
        STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
        EXECUTE "C:\<pathToQViewer>\QViewer.exe" "$(QvWorkPath)\~$(T).qvd";
        EXECUTE cmd.exe /c del /q "$(QvWorkPath)\~$(T).qvd";
    ELSE
        //Table doesn't exist. Let's display a messagebox with a warning
        _MsgBox:
        LOAD MsgBox('Table $(T) doesn' & chr(39) & 't exist. Nothing to inspect.', 'Warning', 'OK', 'ICONEXCLAMATION') as X AutoGenerate 1;
        Drop Table _MsgBox;
    ENDIF
    // Namespace cleanup
    SET NR=;
ENDSUB

Installer of the next version of QViewer will be creating a registry key with path to QViewer, so the subroutine will be able to use the registry key to get location of qviewer.exe instead of hardcoded file path (kudos to Matthew Fryer for the suggestion).

INSPECT is quite helpful in verifying joins for correctness. For this, insert CALL INSPECT twice -- once before a join, and once after it. This will allow you to see whether the resulting table has more rows after the join than before, and check if the join actually appended anything, i.e. if appended columns actually have some data in them.

To find duplicates in a column -- double-click the column header for a listbox with unique values in that column, and then click Count in that list. On first click QViewer will sort values in descending order thus showing duplicate entries (which have counts > 1) at the top of the list. Checking a primary key for duplicates after a join can help detecting wrong joins.

To find duplicate rows in a table -- click "Morph It" to open the table in EasyMorph, and then apply "Keep Duplicates" transformation. You can also filter rows, if you apply "Filter" or "Filter by expression" transformation.

When you deal with wide tables that have many columns, you might need to find specific column. Press F5 to open Table Metadata, and then sort field names in alphabetical order. Another common use case for Table Metadata is checking whether columns have values of expected type. For instance if a column is expected to have only numeric values, its "Text count" should be 0.

To find a value in a column -- double-click the column header to open a list of unique values, then use the search field above the list. To locate the searched value in the main table, simply double-click the value in the list. Press F3 to find next match in the main table.

Currently, the search feature is somewhat obscured (as rightfully pointed by some users). We will be introducing a more convenient full table search in QViewer v2.3 coming out in June. Subscribe to our mailing list on easyqlik.com to get a notification when it happens.

Why I prototype Qlik apps in EasyMorph before creating them

If you want to create a Qlik app just create it, why would anyone build a prototype in another tool first? Isn't it just a waste of time? For simple cases -- probably yes, but for complex apps prototyping them first allows designing apps faster and more reliably. Here is why:

When developing Qlik apps with a complex transformation logic one of the main challenges is to deal with data quality and data structure of poorly documented source systems. Therefore the most time-consuming phase is figuring out how to process data correctly and what can potentially go wrong. There are many questions to answer during this phase, for instance:

• How do we merge data -- what are the link fields, what fields are appended? 
• Does any combination of the link fields have duplicates in one or the other table? 
• Do the link fields have nulls?
• Are nulls actually nulls or empty text strings?
• Are numbers actually numbers, not text?
• Do text values have trailing spaces?
• After we join tables, does the result pass a sanity check?
• How can we detect it if the join goes wrong on another set of data (e.g. for another time period)?
• Are dates and amounts within expected ranges?
• Do dimensions have complete set of values, is anything missing?
• When dealing with data in spreadsheets
• Are text and numbers mixed in the same column? If yes, what is the rule to clean things up?
• Are column names and their positions consistent across spreadsheets? If not, how do we handle the inconsistency?
• Are sheet names consistent across spreadsheets?

These are kinds of questions you would want to have answered before believing that a transformation works correctly and reliably. Unfortunately, scripting in Qlik, like any other scripting, is poorly suitable for interactive data profiling. And that's where you start wasting your time. To answer questions like those above in Qlik you would need to modify and re-run script many times and every time Qlik will reload same data again and again. Even if it's a small test dataset in a QVD, it still takes time, or you risk profiling too little data. Oh, and if you mistakenly omitted a comma your app is dropped and you need to re-open it and re-run again. Then after each reload you would have to create some one-off layout objects and write some one-off expressions, just to get one question answered. Tricks like inspecting resident tables make life a bit easier, but just a bit. Overall, it's an inconvenient and cumbersome process. It effectively discourages data exploration, which sooner or later leads to errors that are discovered on late stages, where the cost of redesigning and fixing errors is much higher than on earlier stages.

This is where EasyMorph comes in handy. First, it loads data once, then keeps it in memory, therefore, it doesn't have to be reloaded every time. And if you load sample data only one time, why not use a bigger data set which is usually better for data profiling? Not only does EasyMorph load data only once, it also keeps in memory results of all successful transformations. So if an error occurs, you continue from where it stopped, not from the beginning -- another time-saving feature.

Second, EasyMorph runs transformations automatically in the background after any change. It's like if you are writing a Qlik script, and while you're writing it after any change or new statement Qlik runs the script proactively, without requiring you to press Reload. Except Qlik doesn't do it. Basically, transformations to EasyMorph is what formulas are to Excel -- you change one and immediately see a new result of calculations, regardless of how many formulas/transformations it took.

Third, designing a transformation process visually is much faster than scripting. Some Qlik developers are exceptionally good at writing scripts, but even they can't beat it when a whole transformation like aggregation is created literally in two clicks. If one knew exactly from the beginning what a script should do then writing it quickly would not be a problem. It's the numerous iterative edits, corrections and reloads that make writing Qlik scripts long. Once I have designed and debugged a transformation logic in EasyMorph, scripting it in Qlik is usually a matter of couple hours, and it typically works reliably and as expected from the 1st run.

Another important advantage of prototyping Qlik apps in EasyMorph is that it allows creating a reference result. When you design a Qlik application off an existing Excel or BI report it usually makes the task easier because numbers in the old report serve as a reference you can compare against. However, if you design a brand new report there might be no reference at all. How can you be sure that your Qlik script, expressions and sets work correctly? There is a whole lot of things that can go wrong. Building a prototype in EasyMorph gives you that reference point and not just for the script, but also for expressions, including set analysis. In airplanes, measuring crucial indicators like altitude and velocity must be done using at least two probes (for each metric) that utilize different principles of physics so that pilots can be sure it's measured correctly. The same principle here -- "get another reference point".

I also found that designing apps in close cooperation with business users is more productive when the users have good understanding of how a transformation logic works. It's better explained by letting them explore a visual process in EasyMorph rather than showing totally cryptic (for them) Qlik scripts.

Resume: EasyMorph is a professional tool which can be used by QlikView / Qlik Sense developers to create robust and reliable applications faster by prototyping them first. I do it myself, and so far it works pretty well.

Transformational data analysis

Single step analytics
The vast majority of BI or Data Discovery tools follow the same approach for data visualization and analysis. I call it 1-step analytics or single pass analytics. In the foundation of this approach there is a single logical data set consisting of one table, or several linked tables. Sometimes it can span across several data sources, but from a user's perspective it's still a single data set or cube. In order to perform analysis a user interacts with different parts of this data set. Visual objects of a report or dashboard are usually built on top of a subset of this data model, or microcube. In another words, under any chart or table in a report or dashboard there is a microcube. In a standard case, every microcube is obtained by filtering or aggregating data from the main cube, usually on the fly. Some BI tools allow users freely change measures and dimensions of microcubes on the fly (query&analysis tools like BusinessObjects or Tableau). Sometimes microcube metadata is fixed during design time and users can only filter data in it (Qlik). But in any case these microcubes are derived from the main cube. So there is only one step (pass) -- derive a microcube from the main cube.

Single-pass analytics

To derive a micro-cube typically 4 operations are used:

• Select columns -- take only certain columns from the main cube
• Filter -- keep only rows that satisfy some rule or condition
• Aggregate -- calculate aggregates like sum or count, grouped by some dimensions
• Calculate new columns -- calculate new measures or dimensions using a formula

 When users perform analysis they actually force BI tools to recalculate microcubes on the fly, using different parameters. For instance, when you drill down from year to month you tell your BI tool to select different columns (add month), apply a different filter (fix year), and aggregate by a different dimension (month instead of year). The power of BI is in the fact that all these operations are performed automatically by a single click. Whatever you, as user, do -- your BI tool automatically does these 4 operations under the hood.

So far, so good. Is anything wrong with this?

Multi-step analytics
For many users and cases, single step analytics is totally sufficient. Remember the times when all users had was just static reports. Probably we could've called that zero step analytics :) So the current state of affairs is a great improvement compared to those times.

But for many cases (and users) it's not enough. In the real life there are many business rules and types of analysis that can not be covered with only single pass. They need two or more passes in which microcubes are derived from another microcubes and not only from the main cube. For instance different kinds of customer behavior analysis require analyzing customer performance over a period of time (one pass), then grouping them based on this performance (another pass), and finally obtaining performance of the groups over the same or different time period (third pass). Example: for a retail chain it's important to see growth of their customer base and not just growth of sales. For this, the organization might analyze how many new customers every month became permanent buyers. But in order to understand if a customer that joined in, say, March is permanent, it's necessary to analyze his/her purchases from April to, say, October. And only then we can come to conclusion that, for example, 3% of new customers joined in March became permanent. And then calculate the same for all months. And then let the user experiment. For instance, modify the rule how we consider a customer permanent -- change it from "1 purchase over 3 months" to "2 purchases over 6 months", and see what happens.

Multi-step analytics

Number of cases when users need ad hoc multi-pass analytics will only grow because as organizations learn more about their customers, they slowly move from basic metrics like number of orders or total revenue to more sophisticated performance indicators that monitor key drivers of business.

You might argue that the type of tasks I described above is not unheard of, and is typically solved by the means of ETL. That's true. The common wisdom across BI developers is try to pre-calculate everything that cannot be calculated on the fly. Move it into ETL. But this doesn't really work because ETL is static. Well, it's not static for a developer, but for a user it's static. The reason it's static for the user is because designing ETL requires technical skills and users don't have them (and shouldn't have). You know how users get around this problem? They do ad hoc ETL in Excel. Yes, it's horribly inconvenient, very error prone, and requires a lot of tedious manual work, but what else can they do? They simply don't have a choice. At least Excel is something they are comfortable with.

ETL today is the same thing as static reporting was before BI started to prosper. I believe it will change with transformational data analysis.

Transformational data analysis
We've used to the fact that ETL and BI are different beasts. It's like a dogma. But what if the borderline between BI and ETL is somewhat artificial and shouldn't be that distinct? I'm not talking about low-level ETL that for instance integrates legacy systems. I'm talking about these parts of business logic that are moved to ETL simply because current BI tools can't handle it.

So, I'm proposing a new term -- "transformational data analysis". It's a way to analyze data through ad hoc data manipulation and multi-step analytics built as series of transformations. It effectively eliminates the difference between BI and ETL (in its business logic part).

EasyMorph (http://easymorph.com), the tool I've been working on since 2013, follows the concept of transformational data analysis. It allows non-technical users to build multi-step analytics using more than 20 various transformations (not just 4), and then re-run it with different parameters and modify it on the fly. At this point EasyMorph doesn't look like a traditional BI tool in that sense that it doesn't have interactive charts or pivot tables (yet!), but we're getting there.

PS.

Can Qlik do multi-pass analytics?
QlikView (and Qlik Sense) is somewhat different from most BI tools (including Tableau) in that sense that it allows going slightly beyond the 1-step limit. The set analysis feature allows building rather complex filtering conditions that might include the element functions ( P() and E() ) which can be considered a 2-step logic. Also, with the help of aggr() function you can add an extra aggregation step. But there are plenty cases where they can't help. And even if they can help, designing expressions with set analysis and aggr() is a huge hassle (I did it for years) and hardly can be considered user friendly or suitable for ad hoc workflow. To be fair we can assume that Qlik allows 1.5-step analytics.

UPDATE 16/3/2016
Starting from version 2.8 EasyMorph offers dynamic drag-and-drop charts for interactive data visualization and more than 50 transformations.

UPDATE 9/8/2015
Interesting comment from Christophe Vogne:

Hi Dmitry,

Reading again your article I understand better what you mean.
From my perspective you are highlighting the fact that BI is moving from multidimensional database to graph database.
Multidimensional database need to be strongly structured and this design break the hidden links between information if they haven't been considered.
With kimball/Inmon BI method, there's always somebody that decide for you what you are supposed to discover: the datawarehouse team and their cascading cubes, datamarts...
QlikView is 1.5 because it's a tabular database (the fields values storage) on top of a graph database (the pointers layer that make the links between values). So some lost associations can be rebuilt by users.

So one aspect of the BI future is graph database because users need to link any information in any context at any time.
But to aggregate values on graph database is a tough challenge (the Set Analysis) and to manage multiple edges between vertex (the synthetic keys) is tougher.

Interesting article indeed

12 similarities between EasyMorph and QlikView loading script

Here are a few analogies that can help QlikView users become more familiar with data transformation concepts in EasyMorph:

In-memory data transformation

Both QlikView and EasyMorph don't require an external database to transform data. Instead, they rely on their own in-memory engines. In QlikView the data transformation part of the engine is single threaded. EasyMorph's engine is multi-threaded but less memory efficient. Both EasyMorph and QlikView use data compression.

Mixed value types in one column

Like QlikView, EasyMorph can mix text values and numbers in the same column, which makes it an excellent visual tool for parsing Excel spreadsheets. Although, type system in EasyMorph is a bit different.

Transformations in EM are similar to preceding loads in QV

In QlikView's preceding loads one load statement is based directly on the result of another load statement, which sometimes is convenient. Transformations in EasyMorph are somewhat similar to preceding loads in QlikView in that sense that a transformation directly uses the output of its previous transformation as input, without any intermediate (resident) tables. While in QlikView you can't do much in preceding loads -- only calculate new columns, and de-duplicate data (using DISTINCT), in EasyMorph you can use any of 25+ transformations (including filters, sorts, and even joins) in any order. Basically, all transformation process in EasyMorph is designed like super-powerful preceding loads.

Little things like the ability to replace existing column with an expression (instead of calculating new column, dropping the old one, and renaming) make life easier. And no annoying script errors because of a missed comma that cause aborting and reloading entire application.

Variables

Variables are ubiquitous in QlikView loading scripts and sometimes used up to the point where entire parts of script are replaced with a variable. The closest (although not exact!) analogue to variables in QlikView are project parameters in EasyMorph. Parameters are global. They don't change from transformation to transformation. Parameters can be used in transformations instead of file names and other properties, and also in expressions as constants.

Loops and iterations

Loading several files or processing a list of dates can be done using loops in QlikView, or iterations in EasyMorph. Although, the loops are imperative (as is the loading script syntax), while the iterations follow the functional programming style.

QVDs

No wonder QlikView can read/write QVD files. But so does EasyMorph. QlikSense QVDs are not supported yet (as of version 1.8) -- we're expecting Qlik to publish a respective API.

Calendars

Almost every QlikView / QlikSense application requires a calendar. If you're lucky to use Rob Wunderlich's QlikView Components then calendars might be easy for you. But for EasyMorph users it's even easier as it has built-in Calendar transformation that creates a calendar in a few clicks. See below an example of Calendar transformation, and its result.

Click to zoom

Mapping tables

Mapping table is a special temporary two-column table in QlikView. In EasyMorph any table can be a mapping table when it's used in Lookup transformation (equivalent to MAP USING... in QlikView). At this point (ver.1.8) in EasyMorph there is no analogy to ApplyMap function in QlikView.

Resident tables

In QlikView you can create new tables based on previously loaded, so called resident tables. In EasyMorph for the same purpose you can use derived tables. A derived table is like a dynamic copy (or view) of another table. It updates automatically if the original table changes. You can derive multiple tables from one table.

Derived tables in EasyMorph. Click to zoom.

Keep / Exists

Sometimes it's necessary to filter one table based on values in another table. In QlikView it can be done using either KEEP statement or WHERE clause with EXISTS() function. In EasyMorph the same can be achieved using Keep Matching or Keep Mismatching transformations.

Crosstable Load

Unpivot transformation in EasyMorph is the equivalent of CROSSTABLE LOAD in QlikView and can be used for transforming matrix tables into straight tables.

Command-line mode

Both QlikView and EasyMorph can be run in command line mode. Both applications allow defining variables (parameters) from command line. Here is EasyMorph's command-line syntax.

As you can see there are quite a few similarities. While in general QlikView loading script is more flexible (as you would expect from a programming language), designing transformations in EasyMorph is simpler, faster, and can be done by people without programming skills at all.

To learn more how EasyMorph works take a look at our illustrated PDF tutorials.

PS. One more similarity will appear in future releases -- the analogue of subroutines.

PPS. Totally forgot about resident/derived tables. Now it's 12 similarities :)

Data transformation in QlikView without the loading script

While I admire QlikView's genius associative in-memory engine I'm not a big fan of QlikView's loading script. If QlikView wasn't meant for departmental use, it would not have been a problem. But since QlikView is heavily oriented on business departments with limited IT supporting staff, the necessity to deal with transformation logic encoded in a proprietary script syntax is an obstacle because of several reasons:

1. It makes it harder to promote QlikView in organizations, since readability of QlikView script is no better than of Visual Basic macros in Excel spreadsheets or SQL.
2. Since business users usually can't write/read the scripts (they are not supposed to have programming skills), it inflates development and maintenance costs because even minor changes to the logic have to be made by developers and developers have to answer all questions about how this or that is calculated.
3. Total lack of metadata lineage. To make it even worse, the dollar-sign expansions make it impossible to parse scripts and rebuild abstract syntax tree in external applications.

With QlikSense the situation is even worse, because QlikSense is targeted as a self-service data discovery platform, but the need in loading script makes it dead on arrival.

I believe that data transformation can be done without programming in the vast majority of cases. That's why I designed EasyMorph that can be integrated with both QlikView and QlikSense.

Today we've published an example of QlikView Integration. It shows how data transformation logic can be moved from a QlikView loading script into an EasyMorph project, where it can be visually designed, modified and viewed. While it doesn't replace the loading script completely (yet) it demonstrates how core transformation logic can be made accessible for non-technical users, who can now explore and modify the logic in a visual way, without dealing with the script syntax.

Here is its transformation logic. Input and output transformations are marked on the screenshot.

The logic loads a text file (one file per state), maps state names from another file, does a few calculations and aggregations, and exports the result into a QVD file. Here is the loading script with EasyMorph project integration (comments removed, download the example for the full version):

States:
LOAD * Inline [
State
California
New York
Texas
];

LET pathEasyMorph = GetRegistryString('HKEY_CURRENT_USER\Software\EasyMorph', 'ApplicationPath');

let vCount = NoOfRows('States');

FOR I = 0 to vCount - 1

    LET State = Peek('State', I, 'States');
   
    //create input file for the EasyMorph project
    EXECUTE cmd /C copy /Y "Inc5000 $(State).csv" input.csv;
   
    EXECUTE $(pathEasyMorph) /c /run "Inc5000 for QlikView.morph";   

    //read the QVD generated by the EasyMorph project
    LOAD
        *,
        '$(State)' as State
    FROM output.qvd (qvd);

NEXT I

Since EasyMorph can't process a list of files yet (this feature will be added in future releases) a simple workaround is used -- a cycle, that iterates through a list of file names, and renaming.

For convenience the EasyMorph project can be opened right from desktop QlikView (in example there is a button for this).

For server execution, you might need to specify the path to EasyMorph executable explicitly, since EasyMorph is installed under a user account and may not be available in the registry under QlikView Server's account.

While it's not shown in the example, you can also add a link to the HTML documentation automatically generated by EasyMorph when it runs a project with /doc parameter from command line (see help on this). In this case users will be able to see the documentation right in the web-browser, next to the application. Or, right in the application, using an extension for web page embedding.

Links:
EasyMorph website
QlikView Integration example

PS. Technical details are copied from EasyMorph Blog.

Major redesign of QViewer

It's been a while since I posted last time and now I've several posts and announcements waiting to be published.

Today's post is about major QViewer redesign and update which has been due for a long time. I wrote first version of QViewer 2 years ago mainly for myself, although it was interesting to see if it can be useful for other QlikView developers. I turned out -- it can, and in the last 2 years it was downloaded more than 10'000 times (7'500 downloads of 64-bit version and 2'500 downloads of 32-bit version) with average number of downloads steadily going up.

At the same time the utility remained to great extent what it was initially -- a custom-made "home-grown" software written during afterhours and in spare time, and it had good potential for optimization.

Today, I'm releasing deeply updated and redesigned QViewer version 2.0 which is finally made comme il faut, i.e. -- as it should be. If you used QViewer previously you will notice new look and feel of the new version, however the majority of changes are under the hood. During the redesign the main focus was on performance and memory consumption that would allow using QViewer with large QVD files (up to 2GBs and more) comfortably. As the result, memory consumption was reduced in 5-7 times, and load speed has reduced in 5-10 times. For instance, load time for a sample 200MB file which I'm using for internal testing, has decreased from 17 seconds to just 1 second. Even more than that -- now load time depends only on hard drive speed, so if you have SSD -- you will enjoy even faster opening.

Two other changes -- more responsive user interface better suited for larger displays, and replacement of Query (simple aggregation feature) with integration with another tool which will be released soon. Also the free version now allows twice more rows -- 100'000.

Your existing license keys will keep working with version 2.0 so no replacement needed.

Don't hesitate to try out new QViewer and let me know what you think about the changes.

FastStart: tiny package to speed up creating new QlikView apps

FastStart is a tiny bundle consisting of an empty QlikView application with a couple of useful subroutines packed with empty configuration .xls file and portable QViewer for inspecting resident tables. I made it for myself for the cases when I need to quickly create a new app from scratch but you're welcome to download and use it. It was made suitable for rapid debugging and dealing with large datasets.

I'm sure there are other similar bundles, here is my approach:

Changes to default Document Settings:

• Compression -- None
• Generate Logfile -- Yes
• Default font -- Calibri, 9pt

Changes to default Load Script:

• Added Subroutines tab that contains two subroutines -- LoadVars (loads variables from external configuration file) and Inspect (calls QViewer for inspecting contents of a resident table);
• Added Partial Load tab -- calls LoadVars so that variables can be updated without full reload (by simply pressing Ctrl + Shift + R), also can hold additional script commands for execution during Partial Reload which is convenient for modifying data model;
• Default tab Main renamed to Variables  -- intended for setting up variables and runtime parameters;
• Added Main tab -- blank tab, here we start manipulating with data.

Two additional folders

• Config -- contains config.xlsx
• Tools -- contains QViewer.exe

I intentionally tried to keep it minimalist and not overload it with pre-built stuff like UI layout framework, calendar scripts, predefined color variables, etc. Although, external configuration file for storing variables and subroutine for inspecting resident tables -- these two features I use in almost every application.

A few tips for dealing with large QlikView applications

Recently, I had to develop an application with significant amount of data -- starting with about 100mln. of records with future increase up to 400mln. It was an interesting experience and below are my tips and findings that might be of use for those who have to deal with large amounts of data in QlikView.

Tip #0 -- go to Document Properties and turn off compression. It will make saving and loading application much faster. Yes, it takes more disk space now but you're not going to send such large application by mail anyway.

Mitigate long reload times
Once you've got tens of millions of records you probably don't want to perform full reload after every small change in the script. Some people use built-in debugger for limited runs, although you can have better control over limited reloads using a variable and FIRST prefix for LOAD statements that operate with large tables. Example:

LET vFirst = 1000;//00000000;

FIRST $(vFirst) LOAD ...

In this case you can do quick short runs with a limited set of data either to check correctness of script syntax after small changes or verify transformation logic. Remove semi-colon and double slash for a full load. Make sure that you have enough zeroes.

You would probably want to reduce even full reload times as much as possible. In some cases, if data model allows logical partitioning of data it's better to switch to partial reload as the main reload mode for updating the application with new information. In this case data model isn't dropped entirely, so you can append new chunks of data to it instead of building everything from scratch. Full reload can be left for emergency cases, when entire data model has to be rebuilt.

This technique is especially useful when you work with monthly (weekly, daily) snapshots and you need to join them (you might want to do this for denormalization as a way of  performance improvement discussed below). In this case instead of joining two full tables you can join monthly snapshots first, and then concatenate results month by month, using partial reloads (see picture below).

To make it more manageable, it's better to store monthly snapshots in individual QVD files -- one QVD per month.

First look at QlikView Direct Discovery

Direct Discovery is one of the recent and long awaited features of QlikView which became available starting from version 11.2. Direct Discovery allows mixing data from external data sources and from QlikView's in-memory data engine using the same QlikView scripting language and expressions. Aim of Direct Discovery is to go beyond RAM limitations and enable real-time querying against large volumes of data outside of QlikView, while presenting the same associative experience as for regular in-memory data. Here are two good documents that describe it in more details: QlikView Direct Discovery Datasheet and QlikView Direct Discovery FAQ.

From development standpoint, Direct Discovery can be employed by two ways: using DIRECT SELECT statement in loading script and SQL( ) function in expressions.

Better Lineage in QViewer and some other updates

Here is a brief overview of features recently added to EasyQlik QViewer -- my favorite QVD viewer :) If you're subscribed to QViewer newsletter then you've heard about some of them except the most recent.

Better Lineage

Since QlikView 11 all QVD files generated by version 11 apps contain SQL statements from loading script. This is quite handy when you need to recall what fields from database were used as source for a QVD.  It doesn't contain full snippets of script so it's not possible to see entire transformation logic, however this is handy when you only need to remind yourself what it was. Also QVDs contain connection strings, so that you can double-check if source database and schema are correct. Previously, Metadata window in QViewer showed only 1 line per Discriminator/Statement and it wasn't convenient for viewing SQL statements. Therefore starting from version 1.4.4 there is new additional multi-line window for viewing Discriminator/Statement records from QVD headers. See screenshot below (clickable).

Using web-services with QlikView

There is a number of ways to establish bi-directional integration between QlikView and external systems. Some of them employ web-services. In this case a web-service is a helper application which sits between QlikView and external application (or is part of that external application) and communicates using web-protocols. Web-services can be written in any popular programming language (PHP, Java, C#, Python, Ruby, Go etc.) and hosted using popular web servers like Apache, IIS or nginx or any other of your choice.

Let's consider several architectures:

Using Dynamic Updates in QlikView 11

Dynamic Updates is another one feature that makes difference between QlikView and other popular BI tools even bigger (see also "Really, is QlikView a BI tool?"). If you haven't heard about it -- it allows adding new or modifying existing data right in the in-memory data model of a QlikView application without re-running loading script. While this feature can be used (to some extent) for real-time updates, in my opinion the main benefit from it lies in a different area -- it enables creating closed-loop workflow with operational applications. It means that not only can you take data from operational applications (e.g. Salesforce or SAP), but return some user-generated output from QlikView into these applications as part of a single workflow. For example, in one of my applications a user can get a list of pending orders from logistic system, create batches from these orders based on some filters and selections in QlikView and then return these batches for delivery into the logistic application.

Dynamic Updates can be performed in two ways -- using VB macros (starting from version 10) and using Actions (starting from version 11). Using actions is a more preferable way (as VB macros are losing more and more favor with QlikTech). Unfortunately, documentation about Dynamic Update Actions is very scarce and there are lots of limitations and side effects, so I tried to collect here some practical hints and tips how it can be used:

Actions utilize SQL-like syntax (however very very limited). E.g.:

INSERT INTO * ('Batch ID', 'Order ID') VALUES (1, 'ABC123');

UPDATE Batches SET WrongBatch = 1 WHERE "Order ID" = null();

DELETE FROM Batches WHERE -1;

These commands look like SQL but here similarities end. Some of limitations:

• You can use equal sign (=) in WHERE clause. You cannot use greater (>) or less (<) signs or not equal (<>)
• You can combine conditions using AND or OR in WHERE clause. You cannot use NOT
• Check for nulls is done differently than in ANSI SQL (see example above)
• Notice different syntax for field names with spaces

I had problems with inserting dynamically several values by one command despite it's possible with hardcoded command. So I ended up dynamically generating several commands separated by semicolon using dollar-sign extensions. E.g.:

$(=concat(DISTINCT 'INSERT INTO * (OrderID) VALUES ($(vNewBatch),'&[Order ID]&')', ';'))

On QlikCommunity there is excellent demo application made by Matthias Dix that shows basic Dynamic Update commands and their equivalent using VB macros (full thread here).

You should also keep in mind that there are some significant side effects from using Dynamic Updates which often lead to unpredictable behavior. This article explains very well these side effects and how to deal with some of them. I struggled with failed variable assignments (Set Variable action) in the same action batch with Dynamic Update, unexpected freezes, and that any Dynamic Update command for some reason triggers all OnSelect actions. Finally I came to this workaround:

• Put all variable assignments (Set Variable action) before Dynamic Update command otherwise new values of these variable won't be visible in expressions.
• Use some dummy command right after Dynamic Update to fight freezes, e.g. Set Variable for dummy (i.e. not used) variable, or even better ...
• ... use Selection -> Back action right after Dynamic Update command to compensate triggering all OnSelect events (won't have effect on VB Macros tied to OnSelect).

UPDATE

Keep in mind that for server-hosted applications all changes made by Dynamic Update apply immediately for all users that are working with the applications. It means that you need to make sure that actions of different users working simultaneously won't interfere. It can be achieved, for instance, by adding user name for each record created by Dynamic Update and then using set analysis expressions to limit records to only those which are required for the user. But in any case, extra care should be taken to manage correctly all possible concurrency cases.

QViewer rebranded, got paid version

A few significant changes happened to QViewer -- viewer for QVD files which I wrote a few months ago and still continue developing.

First of all, QViewer has been re-branded as EasyQlik QViewer and got its own web-site http://easyqlik.com (which however doesn't differ much from the old one).

Second major change is that QViewer has now two versions -- free and full. Free version is limited to 10'000 rows which is still quite enough to do some data profiling. Full version is paid (license key can be purchased for $45) and unlimited. License is personal (i.e. limited to a person, not organization) and has no limitation by number of installations -- you can use QViewer on as much computers as you need. In 8 months the demo version was download almost 1'000 times so it gives me the hope that someone found it as much as useful as I do :) However, further development of QViewer in terms of features, usability and functionality will directly depend on number of licenses purchased as this will show if QViewer is really useful tool or not.

Third change is that finally I got rid of troublesome ClickOnce installer which caused lots of problems with installation and even more with uninstallation. Now QViewer comes with standard neat NSIS-based installer and actually is a portable application. It means that you don't necessarily need to run installer if you want to move QViewer to a different machine. Once it has been installed, you can simply copy qviewer.exe to other machine or add it to your portable apps collection on USB-stick. However, running installer has advantage of automated association with .qvd file extension, which can be problematic otherwise. And as previously, QViewer requires installed .NET v4.0 or above -- keep this in mind when using QViewer in portable mode.

Future development plans include adding sort functionality for listboxes and metadata tables, and design of custom data grid. The latter should significantly decrease load times as the current implementation uses standard DataGridView which is so slow that only initializing it actually takes major part of total load time.

And of course, if you have feature suggestions -- don't hesitate to give me a shout by email or in comments here.

How to look inside resident tables at any point of loading script

What do you usually do when you need to look inside a resident table somewhere in the middle of loading script? I guess you store it as QVD, then load this QVD in a separate temporary QlikView application, then create table box or something else to browse the table.

Here is small trick how you can make your life easier using QViewer:

Insert in the beginning of the loading script this short snippet:

SUB INSPECT (T)
LET vPathToQviewer = 'C:\User\Dmitry\AppData\Local\EasyQlik\QViewer\';
STORE $(T) into [$(vPathToQviewer)temp_qvd.qvd] (qvd);
EXECUTE "$(vPathToQviewer)qviewer.exe" "$(vPathToQviewer)temp_qvd.qvd";
ENDSUB

And then simply insert calls to this function in any place of the loading script where you need to inspect table in its current state. For instance:

CALL Inspect('Orders');

When execution of the loading script will come to this line it will open QViewer showing the table. Script will continue running as soon as you close QViewer.

This is convenient technique that allows inspecting resident tables after every transformation like joins, concatenate loads, etc. You can have as many inspection points as you need during a single run.

A few more tips:

You may receive Security Alert from QlikView because of EXECUTE statement as depicted below. Just press Override Security button. Or you can change security settings in the bottom of the Script Editor window.

You can put this snipped into some file (e.g. debug.qvs) and then include this file when you need to do debugging of some application. Also you might want to save inspected tables under their own names in some temporary folder -- just modify the snippet accordingly or add a new similar function, e.g. InspectSave.

Any more ideas?

UPDATE 1

+Donald Hutchins made a good point -- he offered to store temporary QVD file not in QViewer's folder but in the working folder of the appliction which loading script is being executed. I think it makes a lot of sense because of less problems with access rights and better fit for parallel use. Here is his variant with my minor changes:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\QViewer.exe" "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

UPDATE 2

+Matthew Fryer proposes one more variant of the Inspect sub in his blog QlikView Addict.


UPDATE 3

You can use EasyMorph for exactly the same purpose -- inspecting resident tables. Pros: with EasyMorph you can do more than just viewing a QVD -- you can filter, aggregate, calculate new columns, join other tables, etc. Also the free EasyMorph doesn't have the limit of 100K rows as the free QViewer does. Cons: EasyMorph opens QVD slower than QViewer as it needs to convert them to its internal format. Also it's less memory efficient when it comes to large QVDs.

The only difference in script would be additional /load key:

SUB INSPECT (T)
    STORE $(T) into [$(QvWorkPath)\~$(T).qvd] (qvd);
    EXECUTE "C:\.......\morph.exe" /load "$(QvWorkPath)\~$(T).qvd";
    EXECUTE cmd /c del "$(QvWorkPath)\~$(T).qvd";
ENDSUB

Impressions from Tableau in comparison with QlikView

I had the chance to get to Tableau Customer Conference in early November 2012 and thanks to exceptionally good organization of the event I was able to greatly enhance my picture of Tableau (which I first reviewed 2 years ago) and get answers to some questions.

Below are my impressions from Tableau (which I haven't used in real projects yet) in comparison with QlikView -- tool I've been working with since 2009.

First thing I'd like to tell -- starting from version 8 Tableau can honestly be considered a truly mature product -- a big difference with what I saw two years ago. It's a smartly designed, feature-full and powerful analytic tool which is especially good for ad hoc query and analysis (Q&A). Prior to Tableau I considered BusinessObjects WebIntelligence to be the best Q&A tool on the market. However, in my picture of BI world this honorable title now belongs to Tableau.

Here is what I liked (not in order of importance):

State-of-art data visualization makes Tableau outstanding in the crowd of BI suites. Tableau people talk about "being creative with data" and it's easy to believe in this while looking at clean and elegant Tableau dashboards. By the way, in French tableau has two meanings -- painting and table. Artwork and data. Excellent match of brand and product concept.

Drag-n-drop authoring as cornerstone of analysis and design processes. That's what WebIntelligence was good at, but Tableau makes it even better, simpler and easier. Sadly QlikView has almost nothing to offer here -- fields still have to be picked from a cluttered properties dialogs and dashboards have rather static layout.

In Tableau there are (at least) two special types of dimensions: time and location. I like the idea of special dimensions in general because indeed some dimensions should be treated differently for more efficient analysis and Tableau demonstrates this very well. For instance location dimensions can easily be used for maps and spatial analysis. Time dimensions should be treated differently as well because almost in all cases information is relevant only in specific time context.

Maps is one of the strongest features in Tableau. Usually maps is a real pain for BI developers because support for maps usually is rather poor in BI platforms. Some of them provide mapping functionality via integration with 3rd party GIS platforms like ESRI or MapInfo. But level of integration is never good enough, not to forget additional licensing costs. Some (e.g. QlikView) imitate mapping by offering maps simply as a colorful background for scatter charts, without important capability of highlighting regions or providing additional visual layers. Tableau has done a good work here and offers excellent mapping functionality which includes regularly updated maps and complimentary information (e.g. population or income) licensed from 3rd parties (without any additional costs for customers).

Groups and Sets. Ability to dynamically group dimensions is not something unseen before in BI suites (e.g. in Cognos). However, creating naming sets on the fly, applying set algebra operations (introduced in Tableau 8) to them like addition, intersection, subtraction and calculating aggregates against sets is a very useful and practical feature, for some reason underestimated and neglected by major BI vendors. I can recall only BusinessObjects Set Analysis which was quite clumsy last time when I saw it a few years ago.

QlikView has not much to offer here. While it is possible to save different selections into bookmarks it's not possible to apply set algebra to them. Comparison of aggregates of two ad hoc sets is possible but requires knowing rather complex set analysis expression syntax which is a non-trivial task for even advanced business users. How many of them are capable to quickly write something like this?


sum({Set1<Year=$::Year, Month=$::Month>} [Amount]) - sum({Set2<Year=$::Year, Month=$::Month>} [Amount])


Despite developers of a QlikView application can implement comparison of sets in a dashboard, it's not available out of the box. Dynamic grouping (of dimensions) is not possible in QlikView at all -- grouping requires creating additional data structures and reloading the application.

Now let's talk about some disadvantages of Tableau.

Performance. Working with QlikView it's easy to forget what performance optimization is. Surely, there are some tricks how to improve performance for very large datasets (hundreds of millions of records) but this question rarely appears in daily agenda of a QlikView developer. Having subsecond response time on 20 millions of records even on a laptop is not something unusual. However, I suspect that question of performance optimization will rise much more often for Tableau applications which heavily rely on relational databases. And that's not so trivial task as it may seem.

SQL query optimization is not a trivial task itself and may include special indexing strategy, joins optimization strategy and use of various hints and tricks -- task that requires experienced database professionals and is simply impossible for a business user (who has a lot of other things to do besides query optimization).

However that wouldn't be so dramatic if that was the only thing to do. Tableau enhances source data with its own data (e.g. calculated fields, dynamic groups, sets, latitude and longitude for locations) and also makes in-memory cross-source joins. Therefore query optimization requires good understanding of how Tableau performs these operations under the hood, how it interacts with database and what are implications of different settings. This makes task of performance optimization even less trivial.

I've got impression that performance is not something that Tableau is ready to boast about. Calculation in-memory engine is much weaker and far not so sophisticated as QlikView's one. Not significant but interesting detail -- two demo databases in standard Tableau desktop installation have only 4'248 and 8'399 records respectively.

One more reason for concern is that both Tableau Desktop and Tableau Server exist only in 32-bit version. We were told that 64-bit version is being actively developed however as of now the only version available for customers is 32-bit.

Having in mind these concerns about performance I'm not sure that ability to directly work with very large datasets really becomes an advantage on practice. Yes, theoretically it's better to be able to query 10TB of data than not to be. But would it have any practical use if this could require a few hours of waiting time?

Advanced authoring. Contrary to QlikView, Tableau makes bet not on syntax and scripting but on various settings and actions performed via user interface. While it is good for fast start and early learning curve, as complexity of dashboards increases it eventually leads to necessity of knowing various hacks, tricks and workarounds. For instance making objects (charts, tables, text labels) appear or disappear depending on some parameter (variable) is a straight forward task in QlikView but is actually a hack in Tableau. And that's not good, because in case of QlikView there is albeit complex but logical and well documented syntax but in case of Tableau you will need to learn these hacks and tricks from someone else. Because sometimes it's near to impossible to understand logic behind them without help of more experienced developer. So finally experience of a user becomes largely defined by amount of various collected hints and trick.

Use of screen estate. It's hard to compete with QlikView in efficiency of screen estate use. QlikView offers various gadgets, in-line minicharts, easy management of object visibility which allows making dashboards very information rich. While it's a usual thing for a QlikView dashboard to have 10 listboxes, having 10 quick filters (analogue of listbox) on Tableau dashboard will most probably make it completely cluttered and barely usable. Also such thing as in-line minicharts simply doesn't exist in Tableau.

Need for an ETL. As any other BI tool that heavily relies on database engine Tableau needs cleansed and transformed data. In general this also is true for QlikView. However since loading script in QlikView is capable to perform light ETL and data cleansing therefore in many cases this is sufficient enough.

Lack of collaboration activities --  while I'm not quite excited about the way data annotation is done in QlikView (here is my point of view on data annotation) however it's present at least in this form and instant application sharing is simply awesome feature  Tableau definitely needs something that would allow users discuss and share their findings.

Resume

Tableau is an excellent Q&A tool which is very well designed and suited for non-technical users. It is powerful, easy to use, highly visual and aesthetically pleasant. Good evidence that Tableau is a good fit for business users was audience of TCC2012 -- there were a lot of women and at the same time there were not many Indian developers which is not a typical case for a BI event.

However, promise of Tableau's execs and sales that "IT is not required with Tableau" is much less true than it might seem because involvement of IT personnel could be higher than expected as more complex dashboards become required.

QlikView applications usually require IT developers to create them, however the developers get much more flexible and powerful toolkit that allows them to create very information rich dashboards with fixed layout.

As vendor, Tableau is clearly on rise and the company seems healthy. Their product is developing rapidly, which unfortunately is not the case with QlikView -- I'm getting impression that QlikTech chronically under-invest in R&D and put too much on sales and marketing. There are lots of things in QlikView which wait to be modernized and improved very long time. However, its main killer feature -- ultra-fast in-memory engine -- is still unbeatable.

UPDATE (7/31/2015)
Since the time when this post was published Tableau has got a 64-bit version and greatly improved performance. Also, the data manipulation part now is less critical for Tableau with the help of  EasyMorph (http://easymorph.com) -- an easy-to-use visual data transformation tool for non-technical users created by the author of this blog.

Qlik in turn released QlikSense 2.0 which now is the company's flagship product. It has drag-n-drop dashbord authoring, redesigned security system and even faster in-memory engine. But the most interesting part of it is its highly customizable layout plug-in architecture that makes possible to insert literally any visualization into a dashboard.

QViewer: What's next

Two weeks ago I made initial version of QViewer (my standalone QVD file viewer) available for public download. Since that time it was downloaded more than 300 times. As I myself am a practicing QlikView developer I tried to build a tool which I would use in my daily work. Therefore, a few more features were added. E.g.

• Partial load (for dealing with large files)
• Search
• Pre-calculated statistical data (various counts for each field and each unique value)
• Query tool for calculating simple aggregates (counts, sum, avg)

They make QViewer not simply a file viewer, but actually a data profiling tool which should help find data anomalies (unexpected nulls, text instead of numbers, duplicate rows, etc.) faster. I tried to automate some frequent analysis operations which usually require some time & effort when using regular QlikView. For instance, besides viewing QVD row-level data, with QViewer you can do a little bit faster the following:

• Search a field for nulls
• Filter values by type (text/number)
• Count unique values
• Count occurrences of each unique value
• See immediately type of value (numeric/text/null)

These operations are pretty basic and make no problem for regular QlikView developer, but they take time because of their frequency. If QViewer can save you 15 minutes a day, it saves you 65 hours of working time a year (or more than 8 full working days). Multiply this number by your hourly rate and see the benefit in cash equivalence :)

Digging QVD format was an interesting exercise -- since it's a native QlikView format it gives some understanding how QlikView works under the hood, from what it started and how it has evolved into what we know today. I now understand better technical challenges with which QlikTech's developers faced and I have to admit that they've done excellent job resolving them. Exceptional QlikView's performance, which we know, is result of smart optimization and hard work. Also it looks like there was a portion of luck as some QlikView's key features were not designed as those from the beginning, but were result of evolution.

What's next for QViewer? As of today, I've implemented all essential features which I planned to make it useful for daily work. It will be remaining free until end of this year. Before end of this year a paid version to be introduced along with free limited version.

There still are some things to do, mainly because QViewer still doesn't perform as I want it to on large files due to limitations of standard .NET control (specifically DataGridView). I'm thinking about writing own custom control to replace it but this task may be very time-consuming. It would also be interesting to implement partial load with WHERE condition, as well as make Query a bit more advanced and, for instance, make it support conditions like where field is null.

I would appreciate to hear your ideas -- how to make QViewer better for your daily work. Please, leave your comments here. Thanks.