There are a few things that need to be in place before you can export your answerset as a database table.
1. Do you see your database as a datasource? 2. Does the table already exist in the database or are you creating a new table? 3. Are there any firewalls that would prevent Paxata from accessing your database? 4. Do the fields in the answerset match the parent table structure and do they adhere to the requirements of your underlying database?
There is a one-time setup to enable this feature. Depending on your deployment, you may need assistance from the following members of your team:
Database Administrator (DBA): They will make sure that whatever account is going to be used to create a new table within the database is setup with the correct privileges. The DBA will also provide the schema where your table will reside and the name you should use when creating the table.
Network or Security Administrator: The Paxata Server will be making a direct connection to your database there could be protections in place by this group that would prevent you from writing to the database.
Paxata Administrator: Any database setup is a post installation configuration. Your Paxata Administrator will setup a connector so that Paxata can make a database connection to your database and a data source configuration that will supply the database credentials, the location, and any additional information required to connect. The Paxata Administrator can work with the Network/Security team to make any changes that will allow to adhere to the security requirements in order to allow Paxata to directly access your database.
During the setup, you will need to make some decisions prior to performing the export, and the Paxata Administrator will need to work with your DBA & Network/Security Administrator to ensure that your actions can be performed on the database.
If the table already exists within the database, you'll need to decide if you are going to simply add your records to the table or if want to drop and recreate the table. If you wish to preserve the original contents of the table, you should work with your Paxata Administrator & DBA to determine the best approach to preserve the work.
If the table does not exist, then your DBA will need to provide the rights to allow for the new table to be written.
Once you've worked out how the data needs to land in the database, then your DBA can either setup a special account, or modify an existing account to enable the capabilities. Your Paxata Administrator can setup the datasource definition with that account and with those permissions.
Once the datasource is enabled, and you have validated that the field names & datatypes match the database requirements, then all you need to do is as follows:
Go to the Paxata library (click on the drop down menu in the upper left corner, and choose Library)
Find your answerset within the library (if its not at the top of the list, use the search options to locate your answerset) and hover your mouse above the name
The Export button will appear to the right of the answerset name. Click on the button
You will be taken to the Exporting Dataset Account screen. Click on the green button Select Data Source
Scroll down and select the name of the datasource your Paxata Administrator setup for you.
You can click and navigate to the schema that your Database Administrator provided, and once you are there, click on the select button to the right of the panel (the button color will become active once selected)
In the right panel, the Export Settings will become active. Make sure the Format is set for JDBC, and if necessary, change the Name field from the name of the Answerset to the Name of the table you wish to write
Click the Export button in the upper right
You can go to the Library -> Export Logs to check the status of your export.
If there are any issues with the Export, you can work with your Paxata Administrator or open a support ticket to receive assistance from Paxata's Expert Services. Things to look out for:
Are there any duplicate field names?
Do the field names match the field names in the original table?
Are there any spaces or special characters in the field names? (your database may have an issue with those)
Replace any spaces with underscores ( _ )
Do the datatypes match the expected datatypes within the database
Your Paxata Administrator can also check the logs to see the exact root cause of any issue. Otherwise, you can point your BI Tool of choice to the database and begin your analysis.
Thanks @jmayhew for the very detailed explanation. Let me make sure I have this right. If I want to export a dataset to a database... a) Make sure there is a datasource defined in Paxata pointing to that database, b) then I hover over dataset in the Library and click "Export". c) then, on the Export page, I select the datasource, fill in the required info, and click finish.