Skip to main content

​
Intro

This article describes how to write a DataSet from Domo into an on-prem database using Workbench writeback. Following this process allows you to securely move your data from Domo into your on-prem system with all the same security features and benefits that come with standard Workbench.

​
Write DataSet to On-Prem Database

This process is broken into a few separate smaller tasks. Complete all tasks in order.

​
Install the ODBC Writeback Plug-in

Important: If you don’t already have Workbench installed, install the latest version of Workbench as an admin. You must install Workbench on a Windows server or laptop that has access to the database server; you do not necessarily need to install it on the database server itself. For more information, see the Workbench installation guide.
  1. After you have Workbench 5 or above installed, download the ODBC Writeback plug-in by selecting this link: https://app.domo.com/workbench/plugins/beta5/OdbcWritebackDataProvider.dwp
  2. After downloading the plug-in, add it to Workbench. See how to do this in Manage Plugins in Workbench 5.
  3. Restart Workbench.

​
Get Client ID and Secret

  1. Go to the developer portal and log in to your Domo instance.
  2. Select Create a client.
    create a client.jpg
    The Create new client page displays.
    create new client page.jpg
  3. Enter a name (Ex. Workbench) and description for your application in the appropriate fields.
  4. Select the appropriate options for the Application Scope. Only check the ones you need. Note: Scope is protection for you by allowing you to specify the level of access your program needs. If you don’t require this level of security, you can check all of the scope boxes.
  5. Select Create. The Manage Clients page displays your client ID and secret.
    manage clients.jpg
  6. Copy the Client ID and Secret and save them somewhere for later in this process.

​
Get DataSet ID

  1. In your Domo instance, navigate to the Data Center and the Details view of the DataSet you want to write back to your on-prem database.
  2. In the address bar of the webpage, locate and copy the DataSet ID and save it for later. It is the string between the datasources/ and the /details.

​
Set Up DSN

To use the ODBC to connect to the server, you must have a system data source name (DSN). Follow the steps below to set up the DSN.
  1. On the server where Workbench is installed, select Start and enter 
    odbc
    You will likely need ODBC data sources (64 bit).
  2. Navigate to System DSN and select Add.
  3. Select SQL Server Native Client. If you do not see that option, download it by selecting this link: https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16 The Microsoft SQL Server DSN Configuration wizard displays.
    select sql server native client.png
  4. Enter a name for this DSN in the Name: field.
  5. Enter the name of the SQL Server in the Server: field.
    dsn config wizard.png
  6. Select Finish.
The DSN displays in the System tab of the ODBC Data Source Administrator (64-bit).
system dsn tab.png

​
Add Account in Workbench

  1. On this same server, open Workbench. Note: If you had Workbench open during DSN setup, we recommend that you restart Workbench.
  2. Select Start and enter: 
    workbench
    or 
    domo
    in the search bar.
  3. In the sidebar, select Accounts.
  4. Select Add.
    add account.jpg
  5. In the Overview tab, enter the name of your Domo instance in the Domo domain field.
  6. Select Authorize.
  7. Follow the authorization link in your browser and when prompted for a code, paste in the code from the Authorization code field in Workbench.
  8. Close the browser window when prompted.
  9. In Workbench, select Save to add your new account.

​
Create Job in Workbench

  1. In the sidebar, select Jobs.
  2. Select Add.
    add job.jpg
  3. In the Job Details section, select the Domo domain, enter a job name, select a transport type, and select a reader type.
  4. In the Domo Details section, enter a name in the DataSet Name field. Note: The DataSet Name that you enter is NOT the DataSet that is being written back. It refers to the name of the log table that Domo creates.
    wb log.jpg
  5. Under Source, select Edit.
  6. Enter the Client ID and secret that you copied earlier in this process in the Domo Client Id and Domo Client Secret fields.
  7. Enter the DataSet ID that you copied earlier in the Domo Dataset Id field. We do not recommend using Browse.
    generate query.png
  8. Select Generate Dataset Query. A query is generated that you can edit to only include the columns that you want to write back. Note: Make sure that the user that is logged in to Workbench has access to this DataSet. If they have an Admin security role, the DataSet does not need to be individually shared with them. Learn about sharing DataSets in Share a DataSet.
  9. Select an ODBC Connection Type.
    odbc connection type.png
  10. Select DataBase Type.
  11. Select Generate Writeback Query.
    generate writeback query.png
    Note: By default, Domo generates a query for all columns. If you adjusted the query above, make sure to update the Writeback Query String.
    adjusted writeback.png
    Note: Domo does not create a table on the database. You need to create it first. Here is an example of a table created in SQL server: 
    create table database1.dbo.domo_widgets
(id numeric(18,0)
, widget_id numeric (18,0)
, qty numeric(18,2)
, amount numeric (18,2)
)
    This is the updated query after creating the table:
    updated wb query.png
  12. Processing (Edit) Section — There is nothing you need to do in this section. Workbench initiates the data connection. It connects to the Domo DataSet and pulls it back as a CSV first and uses this CSV file to load into the SQL server.
  13. Update Method — This is not the update method to the target database. This tells Domo whether to append or replace the log table that is created within the Domo instance.
  14. Set up impersonation. This allows Workbench to run as the active directory user.
  15. Execute the job.

​
Notes

  • In the Writeback Query section, you can only run one statement at a time.
  • There is a default value of 25. Domo writes back all the records from the DataSet, not only the 25. Leave that as is.
  • There is a limit of one million (1M) rows in Workbench Writeback due to the endpoint that is used.
  • Workbench writeback is designed to only run one query at a time. You cannot add more than one Data Manipulation Language (CML) command: Insert, Delete, Update, Merge. Even if you add more than one, Domo only processes one at a time. It does allow alter statements. Per product, even if you include two statements, Domo does not guarantee the integrity of the second statement.
  • Workbench creates a log table with the same name as the DataSet name mentioned in the Overview tab.
  • With normal writeback, you have the option to trigger a writeback when the input DataSet is refreshed. With Workbench writeback, however, you are only able to schedule it. This is a security feature and the connection is initiated from Workbench.