Back-end Procedures on Write-Back Server
Summary
It is possible to manually add data to a dataset externally to Write-Back, for that you can either do a manual insert directly on the database, but bear in mind that the actions won't be stored on the audit table (in the end of the recommended procedure we give some best practices for adding data manually).
Adding Data Externally
The recommended way of doing this is by calling the appendData on the extension API. Here is an example (for this it is recommended the use of Postman or a similar tool):
- First, simulate a login:
- This is required because in order to perform a call to the API an authorization token needs to be provided. To get a token a login needs to be performed.
- The login can be made like so:
- API endpoint: https://extension-url:port/twbe/api/user/login
- This endpoint receives a form-data with username and password
- And add the following headers. You need to specify to which site your authentication to
- After that, send the request and a token should be received. Save this token.
- Then do the request:
- API endpoint: https://extension-url:port/twbe/api/dataset/appendData
- This endpoint receives a JSON object
- json object appendData
{ "widgetId" : "1234a567-8b91-23c4-5d6e-7f89a0123b45", "records" : [ [ [ {"field": "Sales", "value": "123456789"}, {"field": "Country", "value": "Portugal"}, {"field": "Region", "value": "Europe"}, {"field": "field 1", "value": "123"} ], [ {"field": "Sales", "value": "123456788"}, {"field": "Country", "value": "Portugal"}, {"field": "Region", "value": "Europe"}, {"field": "field 1", "value": "123"} ], (...) ] ], "system" : "direct_call" }- system - usually says "server" or "desktop" but as a good practice, here it is recommended to set "direct_call" to know that it was a direct call to the API.
- records - set of records to be inserted.
- This will vary according to each use case. In this example, we have the first three fields that represent the keys or pills in the add data screen and the last one (field 1) is the user input field. For the appendData to work it is mandatory that the configured keys are sent in the request. A good way of making sure it goes correctly is to go to the extension, add an entry, and see the result of the request, then copy the JSON that was sent. Example (press F12 on the pop up of the add data before adding data to see the request; only on the browser, i.e, using Tableau Server):
- Example in postman:
- This will vary according to each use case. In this example, we have the first three fields that represent the keys or pills in the add data screen and the last one (field 1) is the user input field. For the appendData to work it is mandatory that the configured keys are sent in the request. A good way of making sure it goes correctly is to go to the extension, add an entry, and see the result of the request, then copy the JSON that was sent. Example (press F12 on the pop up of the add data before adding data to see the request; only on the browser, i.e, using Tableau Server):
- Besides the JSON object, it is also necessary to provide the Authorization header and the Content-Type and ExntensionSite headers
- The Authorization header (that was obtained from the login) is set in the Authorization tab:
- The headers tab. You need to specify the extension site you desire to append data to and the content-type:
- The Authorization header (that was obtained from the login) is set in the Authorization tab:
- After this, just send the request and the application will do everything else. It is possible to see the results in the audit table and in the dataset table.
- If any error happens when sending the request, here are a few common issues:
- Make sure that the keys fields are all being correctly sent.
- Make sure that at least one of the user input fields is being sent.
- Make sure the authorization header and the content-type are in the request.
- Make sure that the body is in the proper format, i. e., JSON.
- If there are other issues, check the log files (usually logs/logger.log) to see if there is any issue with the query or temp/spring.log to check if the back end is throwing any error.
Bear in mind that since this is being used manually it is more susceptible to failure because it does not have the control and error handling present when using the extension's front end.
The recommended method is calling the API because this way the logic is handled by the application and it goes through some checks as well as generates the IDs for the records and registers the actions in the historical_audit table.
Adding data to a tabular configuration
When adding data to a tabular configuration, the endpoint is exactly the same and the JSON structure is also the same, but we feel that it is necessary to show how the JSON is built as the tabular submit screen is quite different from the form.
Here's an example of a tabular add data:
This input will result in the following JSON:
As you can see, very similar, although it creates a bit more data.
This is also because in this scenario we are adding to two marks at the same time.
The records structure is as follows:
Below, you can find the API documentation with the endpoints necessary to use the extension through REST requests.
API Documentation
You can find a detailed list of the available API endpoints in the following link: https://documenter.getpostman.com/view/6626655/2s935pq3eY
If you wish to make these changes manually it is also possible. We strongly recommend using the previous method, calling the API, but we will also provide a possible solution to insert directly in the database.
- Firstly the insert is made on the dataset table.
- Make sure that the key fields are correctly inserted with the proper values otherwise when reading the data in the extension it won't know how to display them and may cause issues.
Example query:
INSERT INTO "dataset_table" ("surrogateKey", "key1", "key2", "key3", "field_1", "field_2", isActive, timestamp_local, timestamp_utc) VALUES ('manual-insert-2019-05-23-13-15-00-i', '12345', 'abcdef', '12345', '12345', 'abcdef', TRUE, '2019-05-23 13:15:00', '2019-05-23 13:15:00');A few notes to consider:
manual-insert-2019-05-23-13-15-00-i
This is the format we recommend for this type of insert to ensure a unique key and to be easy to spot these inserts. The format is manual-insert-TIMESTAMP-i where TIMESTAMP is the current time and date and the "i" is an incremental number. This "i" is particularly useful for batch inserts for example.
"key1", "key2", "key3", "field_1", "field_2" - these fields depend on the table columns and the keys configured
It is also necessary to manually insert the action in the audit table to keep a record of what was made
INSERT INTO historical_audit (ID, ACTIONDATE_LOCAL, ACTIONDATE_UTC, ACTIONNAME, DATASET, DATASETKEY, IDWIDGETCONFIGURATION, "SYSTEM", USERNAME) VALUES ('manual-audit-2019-05-23-13-15-00-i', '2019-05-23 14:15:00', '2019-05-23 13:15:00', 'INSERT_DATA', 'dataset_table', 'manual-insert-2019-05-23-13:15:00-i++', 'dataset_id', 'manual_insert', 'username');A few notes to consider
- The ID format is similar to the insert on the dataset table just changing from "insert" to "audit".
DATASET is the name of the table
DATASETKEY is the surrogateKey of the record
Querying the Write-Back Tables
If you are directly querying the database either when reading with a database management tool or Tableau itself with a custom SQL connection, you might have to pay attention to the way the columns are called as some of them usually require to be used with double-quotes.
Example with historical audit:
SELECT "ID", "ACTIONDATE_LOCAL", "ACTIONDATE_UTC", "ACTIONNAME", "ACTIONPARAMS", "DATASET", "DATASETKEY", "IDWIDGETCONFIGURATION", "SYSTEM", "USERNAME" FROM historical_audit;
Example with a dataset:
SELECT "surrogateKey", categorization, future_action, analysis_date, country, order_date_month__year, "ISACTIVE", "TIMESTAMP_LOCAL", "TIMESTAMP_UTC" FROM outlier_analysis;
As you can see, the technical fields from Write-Back have uppercase letters meaning that they are required to be called with double-quotes.