 |
| Spoiler: Audit logs! |
Over the last 15 years or so, we have occasionally created QlikView* solutions with writeback, always involving macros. Macros are not every BI developer's comfort zone. Building a robust writeback solution can be time consuming, as well: they require significant testing, defining some kind of landing zone for the data, and changing security settings to work at all, which not all customers are comfortable with doing.
Last week I spoke with another consultant who was in a pinch and needed to create a QlikView writeback solution quickly. He was trying to commit writeback through users clicking a button: clicking the button triggers an action that sets a variable value, which would subsequently be written out, then consumed again as a data source, so all users see the updated data.
This is when I had an idea that avoids the complexity and time of implementing writeback the way we have done it before, and I can't tell if it is brilliant or mad. The variable value, user, and timestamp were already being written out, automatically, without any coding. It was just a matter of finding it... in the audit log.
Yes, the good old audit log. Depending on the level of logging detail enabled, the audit log contains user activity like selections, exporting, opening sheets, and setting variable values. It's well-organized and easy to winnow down to exactly the entries that are relevant to you.
Sure enough, we opened a recent audit log file in the customer's environment and were quickly able to find log entries related to that specific application, button (via its object ID), and the action setting the variable value. He was already logging exactly what was needed, without realizing it.
To write something out, simply trigger an action that sets a variable equal to the writeback value, and it will appear in the log.
- If you need to write back multiple "columns" at once, introduce a column delimiter to the variable value, ex., 'Column 1|Column 2'
- To write out multiple "rows" at once, introduce a column delimiter, ex., 'Column 1a|Column 2a~Column 1b|Column 2b'
One optimization I would introduce is to create an incremental process to accumulate the limited audit log data you care about in a QVD.
- Logs may be archived periodically, and you will want to maintain the writeback history if some of the logs go away.
- Additionally, there is no reason to continually reread older audit log files, so logic to only load log files updated since your last refresh will also speed up this process.
* Several of our customers are still using QlikView. I throw no shade -- it's cheap (if you got perpetual licenses and own your servers), mature (not a lot of bugs), and ETL is fast and simple. It lacks modern BI platform features, but if you have a very specific vision of what you want to build, it's flexible and still performs well.