The add-in models Tableau CRM datasets and lenses as database views, or read-only tables, using the Tableau CRM REST API. Datasets additionally support bidirectional access.

The following sections provide information on the functionality available through the views and stored procedures.

Views

You can use the add-in to work with all the datasets and lenses in your account. View schemas are discovered dynamically at run time.

Dataset tables start with "Dataset_", so if you want to get the data of a dataset called "Test", the table name would be "Dataset_Test". Lens tables start with "Lens_", so if you want to get the data of a lens called "Test", then the table name would be "Lens_Test".

See System Tables to obtain schema information and other add-in metadata at run time. The Views section shows an example database modeled on datasets and lenses available in the Tableau CRM development environment.

Dynamic Schemas

When you connect, the add-in gets the list of tables and the metadata for the tables by calling the appropriate Web services. Any changes you make to your datasets or lenses, such as adding new columns or changing the data type of a column, will immediately be reflected when you connect using the add-in.

Query Processing

The add-in offloads as much of the SELECT statement processing as possible to Tableau CRM and then processes the rest of the query within the add-in.

Executing SQL to Datasets and Lenses

Datasets support INSERT statements, while lenses support only SELECT; you can also call the UploadCSVToDataSet stored procedure to insert to an existing dataset or to create a new dataset.

See the following sections for examples of executing SQL to datasets and lenses:

• Selecting Datasets
• Inserting Datasets
• Uploading Datasets

Customizing Schemas

You can customize table schemas to do the following:

• Change the columns and data types reported.
• Change the operations that can be executed against a dataset or lense.

To customize a schema, call the CreateSchema stored procedure. The stored procedure saves the dynamic schema to a static configuration file. Schema files have a simple format that makes them easy to customize.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to access other API functionality not surfaced through the views.