… and points beyond

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.
• Support the updating of Input Fields from AJAX.

Happy Qliking!