I am working on writeback table for the User Entries. The requirement is when the User Enters the data, I want to store them in the backend and once I am reloading the application, those data appear in the filter, charts, KPIs and in the Table, also it shows the Updated data. I am not having S3 as connector to connect with Qlik Sense. Available options I am having is PostgreSQL, Amazon RedShift, Rest API (Direct Access gateway), Athena (But this is a Query Editor not a Database storage)
Due to Access Limitation, I am not having a permission to create an API key.
which would be the best option for me to discuss with the client for the Access.
Note: I am thinking of using PostgreSQL and connecting with Qlik sense using Username and Password.
I highly welcome your suggestion on the above-mentioned doubt.
I have a working write-back process that persists data. In my case I want to pull data from the data warehouse, bounce it against the data entered in the write table, and then update the data in the data warehouse with the data entered by the write table.
Here is my environment:
We have a data warehouse inside our company firewall riding on top of SQL Server.
We have a data gateway to that data warehouse from Qlik Cloud.
I have the ability to connect to that data warehouse, and I have a connection that allows me to run stored procedures that do return values. That is a setting in the connection configured to hit Microsoft SQL Server via direct access gateway.
I have the privileges needed to connect to the write table data store.
Here is my approach:
I have a "Helper Qlik App" that has no user interface, but has a load script. The load script connects to the Qlik data store using a rest connection. It is pretty simple to use the rest connection. The core of it is this having the correct URL:
where xxxxxxxx is the write table data store id, which you can get at the bottom of the write table, from the information button that appears next to the write table save button. Note that if you publish the app with the write table into a different space, the write table in that space gets it's own change store, and you will need to change the helper app's write table change store id to reflect the change store of the newly published write table.
Another interesting feature of the write tables is that they do not create fields into the change store until that field has had data entered into it through the write table, so go into each field in the write table and enter something. This will create the field in the change store, and so it will be found by the REST connection. Do this before you create the REST connection.
And if the dashboard with the write table has been published to a new location, you will need to enter values into each field in the new location before creating the REST connection.
So back to the helper app. It starts by connecting to the rest connector and pulling all of the data from the write table into a table in the script. Another detail here is that the non-key fields pulled from the helper table have a numeric value appended to the end of each field name. That numeric value is also specific to each instance of the write table, so if you publish an app with a write table to a new location, you will need to get the new field names from the rest table.
Next my script pulls the data from the data warehouse and loads it into a table in the load script. This table must have the primary key fields from the data warehouse. The natural primary keys in addition to the surrogate keys. It then merges this data with the data in the change store (with a cs_ prefix, so that I know these merged fields come from the change store). Again, the change store primary key must be identical to the natural primary key of the table in the data warehouse.
With the two fields merged into one table (change store data with prefix cs_, and data warehouse table with prefix source_), I then load a new table resident the table with both fields. This new table checks each field. It uses the change store value if it has changed. Otherwise it keeps the value loaded from the data warehouse. Here is a sample line from that part of the script, where it checks the value of field ConstructionOverheads:
If(Len(Trim([Cs_ConstructionOverheads_Edit] & '')) > 0, [Cs_ConstructionOverheads_Edit], [Source_new_constructionoverheads]) AS [new_constructionoverheads],
This new table is then stored into a QVD file on the qlik server using this syntax:
STORE <<new table>> INTO [$(vQvdLibName)/$(vQvdFileName)] (qvd);
Next, the code checks to see if an archive file exists for this data. If it does not exist, it creates the archive file. If it does exist, it loads the data from the archive file into a new table in the load script like this:
IF QvdNoOfRecords('$(vQvdArchiveLibName)/$(vQvdArchiveFileName)') >= 0 THEN TagName_Archive_AllRows: NoConcatenate LOAD * FROM [$(vQvdArchiveLibName)/$(vQvdArchiveFileName)] (qvd);
It then bounces the archive data against the new source data, and identifies new rows:
Timestamp(Now(1), 'YYYY-MM-DD hh:mm:ss[.fff]') AS [ArchiveTimestamp], '$(vArchiveBatchId)' AS [ArchiveBatchId] RESIDENT <<The table with the merged data from the change store and data warehouse>> ;
It then stores the archive data back into a Qlik side qvd file like this:
STORE Tagname_Archive_AllRows INTO [$(vQvdArchiveLibName)/$(vQvdArchiveFileName)] (qvd); DROP TABLE Tagname_Archive_AllRows;
When this is all done, the change store data has been merged with the data from the data warehouse and stored into a local QVD file. Also, the changes from this session have been merged with all of the past changes into an archive qvd file.
Next, I loop thorugh the local qvd file containing just the change store, and store each field of each row into a variable, like this:
Tagname_QvdRows: NoConcatenate LOAD * FROM [$(vQvdLibName)/$(vQvdFileName)] (qvd);
LIB CONNECT TO ':Databasename via Direct Access Gateway';
LET vRowCount = NoOfRows('Tagname_QvdRows');
Trace Ready to loop for $(vRowCount) records;
FOR vRowNo = 0 TO $(vRowCount) - 1 LET vParam_DeleteThisRecord = If(Len(Trim(Peek('DeleteThisRecord', $(vRowNo), 'Tagname_QvdRows') & '')) = 0, 'NULL', Chr(39) & Replace(Peek('DeleteThisRecord', $(vRowNo), 'Tagname_QvdRows'), Chr(39), Chr(39) & Chr(39)) & Chr(39));
I then call a stored procedure and pass each variable into it. That stored procedure updates the data warehouse with the new data using the natural key of the table. I make sure that the stored procedure returns a single record on success, and a separate record on failure.
I end the loop like this:
LET vRowCount = NoOfRows('ExecutionData'); TRACE ExecutionData loaded $(vRowCount) records.;
NEXT vRowNo;
DROP TABLE Tagname_QvdRows;
Once the helper dashboard is working, I can call a reload of this helper dashboard from the dashboard with the write table. The user of the write table must push the save button found in the lower right hand corner of the write table, and then must push a button on the dashboard. The button invokes the reload of the helper file. It then reloads the dashboard with the write table, and the user can see that the changes were made.
A couple of notes:
the write table does not know that you have pulled data and reload it. The changes will continue to reside in write table until they age out 90 days or so later. This can "gum up the works", and give the user the sense that things entered a few days ago - and saved - have not been attended to. There are ways to handle this.
Users do have to save twice (once for the write table and once on the button that reloads the helper dashboard). That needs to be addresse with training.
You might have noticed that one of the field in the change store is DeleteThisRecord. In this instance, if DeleteThisRecord has the right value, the stored procedure marks the data in the data warehouse as delete. I don't let users create new records in the data warehouse. We have not had that use case yet. I do let them update and delete.
This whole process is a bit convoluted. I addressed this by creating an agent that receives a pair of files as input (one describing the overall process and one containing one row per field in the write table). The agent now creates all of the script to implement the write table helper dashboard load script. But I do need to manually create the rest table connection load (because the field names in the rest connection table have that seemingly random number attached).
