I know lame BI when I see it. BI is lame when it show KPIs without insight into why they’re failing or succeeding. BI is lame when it shows sales figures without enough context to judge performance. BI is lame when it shows the same Top 10 Customers every day when it should show the Top 10 Customer Calls You Need To Make ASAP.
But I’ve never stopped and considered what separates lame BI from quality BI. Curt Monash, in his excellent blog, DBMS2, identifies 2 axes of Business Intelligence: operational and root cause. Into the quadrants he fit the descriptive names that belong there. That gives a matrix like so:
What this also shows is that lame BI has little to do with the product used. Real-time means “in time to make a difference”–Curt Monash uses the phrase “human real-time”–but most businesses can measure that in minutes or hours: long enough for BI to play a pivotal role. Some products do give an advantage. QlikView is excellent for investigative BI and its rapid development model makes it easy to prototype, test for value and deploy a working BI tool.
Lame BI is a product of something else. Politics leads to lame BI. Expose a KPI in a dashboard and if it goes the wrong direction someone gets blamed. Building the real-time or tactical BI to address the issue is someone else’s problem. Somehow this passes for “managing on results.”
Wishful thinking or inexperience is another cause of lame BI. People believe that having a dashboard at their fingertips, whenever, wherever, will improve management. This promise is what continues to sell lame BI. Hopefully they don’t run out of money or lose faith before getting the BI they really need.
Data quality is a major cause of lame BI. KPI’s are looking where the light is. To look at root cause in a tactical app requires that you have captured the cause. Real-time alerting requires that some actionable fact be available on a shorter timeframe than the KPI is measured.
BI is a primary interface to business data. It is often the only interface to data spanning business departments. Therefore, BI is the last stop and the most important place to add context, gain insight and influence business decisions for better or worse. Lame BI is wasting money, time and opportunity. Don’t let another lame BI tool get built in your company.
The scalability material for QlikView states it will scale linearly and take advantage of all cores. I think this is misleading because as I tell my customers, “QlikView scales linearly but hardware does not.” At the most recent Qonnections there was a realistic discussion of this problem in two cases.
The first is Non-Uniform Memory Access, or NUMA. This technology divides the memory among the processors, creating “local” and “remote” banks. QlikView would rather treat memory as a uniform segment. So the advice from QlikTech is to disable NUMA. It’s not always possible so there is also a QlikView configuration option to better spread the data throughout memory. What these options do is enforce average-case memory access times and avoid worst-case situations for one object versus another. If you had an app that could fit entirely into a single memory area, you’d do better to leave NUMA enabled.
I expect that future releases of QlikView will take advantage of NUMA. One of the advantages of QlikView’s batch processing is that memory storage requirements are known ahead of time and therefore optimizable. QlikView’s current data structures are compact, but still tabular. I expect to see QlikView intelligently divide its workload across processors using a hybrid of row-based and column-based storage.
The second issue is inter-processor communication. At Qonnections there was a specific concern about using an 8-way (8 processor socket) design instead of 4-way because the communication starts to take a significant percentage of peak performance. Although the cores are calculating on a subset of rows, the row details are stored across all memory areas, which means constant communciation among processors. QlikView’s current bit-stuffed indexes could be split into row or column chunks to push aggregation to be local to the processors.
TE02 QlikView iOS SDK (Software Development Kit)
This session will demonstrate the new QlikView iOS SDK and explain the differences
between this product and other QlikView client integration tools. Partners can build native
iPhone or iPad applications with this tool using XCode and the Objective-C programming
Druid is an in-memory high-speed analytic database that is distributed in a cloud platform. MetaMarkets says they plan to release the code in the coming year. Data is fed in a semi-aggregated form from a Hadoop backend that stores the raw data. Their cluster of 40 modest machines churns through 1 billion rows of arbitrary dimensionality in 1 second. In another post they mention that they can use 6TB of memory spread among multiple machines before they incur degradation in speed due to cloud communication overhead.
QlikView’s “section access” security can be easily ignored with a hacked copy of QV.EXE. Hacking the file is a simple process that takes only a few minutes. Do not rely on “section access” to protect your data in a local QVW file.
The effectiveness of “section access” on server-hosted files is not a part of this warning.
The ability to push data from QlikView back out to a database is beneficial for what-if analysis, financial reporting, CRM dashboards and more. Unfortunately our ability to achieve this, even using custom code, is limited. On the frontend, there are only a few objects that can support user input and very little control over how these objects display. On the backend, connecting QlikView back to a database is very difficult to do well.
Part of the problem is that QlikView server is not always in control of this communication. If you’re using Plugin, it’s done by the client. If you’re using AJAX or Mobile, it’s done by the server. If, however, you code your solution as an Extension, things shift back to the client side again.
The common way to implement writeback is through VBScript macros. Examples of code to achieve this are plentiful on QlikCommunity. Although a simple version can be mocked up in a few lines of code, deploying this solution to many users in a modestly secure environment has serious disadvantages.
Each client machine needs to communicate to the database. Therefore, database drivers need to be installed on each client. Credentials need to be included in the code of each QlikView document, leaving them exposed to users. Special ports need to be open in firewalls for driver communication. These are poor security practices and should be reason enough for any enterprise to abandon this approach.
Client-side code is difficult to monitor. Error handling is poor. A separate system would need to be in place to capture errors for analysis and resolution.
Managing conflicts in a distributed environment requires careful design and development.
Communication between the VBS execution environment and the database can be slowed for any number of reasons. This leaves the client in an uncertain state, without feedback on progress or problems. Meanwhile, the application state in QlikView can continue to change. This easily can cause inconsistencies.
Database driver communication uses proprietary protocols that are difficult to monitor for debugging and by network security software.
But there is a much better way to implement writeback to a database from QlikView: build a lightweight web service. What this means is to have QlikView send a structured request to a web server that can interpret the request, make the appropriate database changes, and send a useful response back to QlikView. Overall, this approach is far more flexible, reliable, compatible, configurable and maintainable.
The response to a web service command (HTTP POST) can itself be an extensive report on the success or failure of any updates. This data can be made visible to the user as a clear confirmation that changes were successful.
Server-side code is more reliable. It’s far easier to manage many users updating data at the same time. It’s easier to record and react to errors. Implementing your web service in PHP gives you a community with examples of good design.
Server-side code can handle any level of complexity such as triggering other systems. Client crashes need not leave complex processes in unresolved states.
This approach only requires a web port to be open in the firewall and therefore is more likely to work regardless of where the user is located.
With this approach, it is easier to handle database rollbacks, atomic transactions and other features that support the completion of a transaction.
Changes do not need to be sent to a database one value at a time. Instead, changes can be aggregated into a single update on the QlikView side. Aggregating changes is done faster than database communication. There is less chance of stalling the user session or allowing QlikView’s state to change in the middle of an update process.
Multiple tables can be updated. For example, not only can a value be updated, but a separate audit log can be updated with who made the change, when, and to what value.
Communication using XML over HTTP is readily captured by network security software.
Web services can leverage existing network infrastructure. For example, IIS & Active Directory will authenticate the user making the web request. The web service code can be passed this information reliably.
The database is read and written by a single set of credentials, written once in the web services code, and running on a secured server, without any access from other machines on the network. This is far more secure and a much easier sell to the IT department.
I’ve had plenty of success with this approach, combining IIS, ActiveDirectory, PHP and QlikView VBScript macro code. I don’t think we need writeback as a QlikView feature. I would, however, like to see a few changes to QlikView to better support features like this.
Support the editing of Input Fields in more objects, such as when used as dimensions in a Pivot Table, or in a bar chart.
Support multi-line text in Input Fields.
Add functions to VBScript and Extensions to identify the rows of a table with Input Fields that have been changed since reload. For Extensions, something like a “next” iterator that moves to the next changed value.
Make it possible to share Input Fields across users–without using collaboration objects as a kludge.
Add Extension/AJAX functions for managing the data behind Extension objects with millions of rows.
It is possible to copy and paste color settings among all layout objects! Even two-color gradients can be easily copied and pasted. If the destination object doesn’t support gradients, the first color is kept and the second color is ignored.
Simply right-click on the color box to copy or paste.
If there was just one question I could ask at this year’s Qonnections 2011, it would be this…
When are we going to see improvements to the most basic QlikView task: displaying data?
Look at the following examples from competitors…
Above is a Spotfire chart that cleanly displays a 2-level hierarchy of dimension values on the x-axis. Increase to 3 levels and the labels stay organized and readable.
Below is a chart from Tableau.
The axis labels are only shown at the left and the bottom of the entire trellis. QlikView shows axis labels on each square, adding unnecessary clutter that is not easy to remove. Two dimension values are coded in the size of the dots and their color. Tableau also uses color gradients easily and effectively.
Tableau and Spotfire put a lot of energy into making displays clean and readable. Tableau makes excellent guesses at how to display your data.
QlikView’s charts have felt clunky for years. The Chart building dialog is huge, confusing and too often doesn’t work as expected. Charts don’t adapt well to being small. Axis labels cram into each other, don’t split lines, and don’t respect chart settings. Legends use excessive real estate, have limited positioning with no intelligence and don’t split text. Expression cycles are confusing for end-users. Fonts and colors are buried 3 levels deep. “Themes” exclude certain chart elements, requiring the developer to dive deep into menus to make targeted changes. Scatter plots quickly become a messy jumble of points and labels. Removing scatter plot data point labels makes identifying a data point a painful task of color matching.
There doesn’t seem to be any point in discussing geospatial data, for which QlikView has no native abilities. QlikTech has been frustratingly quiet on this. Want to include Google Maps? You’re welcome to search for code in the community, or pay more for third-party tools. Meanwhile, the competitors’ native support is easy and attractive.
QlikView is still the best tool out there for “getting things done”. Graphical display is one of a few areas where QlikView is lagging. But QlikView is too far behind at this point. Charts have not been overhauled since at most version 7. It’s time for a major leap forward.