How to Add Data From Databases
Analytics Canvas Online connects to your database and ingests your queries into Smart Tables. These tables are then versioned, updated incrementally on-demand or on a schedule (where applicable), and available for use within the subscription.
To add data from an available database connector, follow the steps below. If your database is not listed, try the ODBC Driver in Canvas Desktop and uploading your data to a Smart Table for use in Canvas Online.
If you have trouble connecting, review the troubleshooting guide below.
How to connect to your database
1. Add the “Database” block to the canvas.
2. All previously added databases will be available in the list. To add the first one, click “Add New Database.”
3. Select the DB type - SQL Server, MySQL, PostgreSQL, or Oracle.
4. Add the Server URL, port, and Database name and its credentials and click “Test Connection.” If everything is correct, you will see a confirmation message.
5. After adding the DB, you need to select the table using the navigation tree on the left. It generates automatically based on the structure of the DB Server.
6. You can also create a new synchronized table.
7. The next step is to choose one of the tables from the selected schema. You can see all of them under the dropdown menu. Click on a table’s name to see the data on the right.
8. There’s a search bar above the Table/View tab, which you can use to search through the DB tables.
9. You can switch to “Views” and select one available in the list.
10. Alternatively, you can click on the "SQL Query" tab to use a custom SQL query to extract data from the DB. Click the name of the table or a column's name to have their labels brought into the SQL query editor. Click "Run Query" to run the query and get a preview of the data. If you are satisfied with the query, click "Select Table and Continue."
11. After selecting the table or writing the SQL, click on "Select Table and Continue" in the top right corner.
12. Provide a name for the table as it should appear in your Tables menu and a description to help you and your team understand what's inside that table or how it can be used.
13. If you are concerned about the bandwidth of your database, select the option to "Limit the rate of load," then define the number of rows per batch and the delay between batches.
14. Click Submit to create the synchronized table. The data will now load and be available as a data block on the Canvas, and available in the Smart Tables menu, where it can be brought into other Canvas workflows.
Troubleshooting Database Connection Errors
In order for us to connect to your database, you must have:
- a username and password
- valid connection string
- permission for Analytics Canvas to access your database remotely
If any of the above are missing, or if you're using a separate connection method (like NT Login), you will not be able to connect to your database. Please consult your database administrator and request access for Analytics Canvas. If required, contact support for the list of IPs to permit for your account.
⚠️ If you see the triangle with a “!” in it, click on it to see the warning message. If it doesn't help you to resolve the issue, send a screenshot of the error message to support.