Connectors → Smartsheet

About Smartsheet

Smartsheet is a software as a service offering for collaboration and work management. It is used to assign tasks, track project progress, manage calendars, share documents, and manage other work, using a tabular user interface.

About the Smartsheet Connector

The Smartsheet connector uses the CData JDBC Driver which offers the most natural way to connect to Smartsheet data from Java-based applications and developer technologies. The driver wraps the complexity of accessing Smartsheet data in an easy-to-integrate driver that applications can use to access Smartsheet as a traditional database.

The Smartsheet connector supports the following Incorta specific functionality:

Security configurations for the Smartsheet connector

There are two authentication method options for the Smartsheet connector:

• OAuth
• API Access Token

Use the OAuth Authentication Method

If your application requires users to be able to login with their own account, implement the full OAuth flow. A Security or System Administrator who manages your organization’s Smartsheet account as well as the Incorta Cluster creates the OAuth client credentials. Refer to the Smartsheet Adding Security by Using OAuth documentation for more information.

Use the API Access Token Authentication Method

Use the API Access Token Authentication method if you want to get started quickly, or are developing a standalone application that can run with your credentials. Refer to the Smartsheet Raw Token Requests documentation for more information.

Deploy the JAR file

The Smartsheet connector requires the following JAR file:

• cdata.jdbc.smartsheet.jar

The Smartsheet connector connector requires the deployment of a JAR file to the Incorta Node hosts of the Analytics Service and the Loader Service. A systems administrator with root access to the host can deploy the JAR file. A CMC Administrator can restart the Incorta cluster.

Here the steps to copy the JAR file to standalone Incorta cluster:

• Secure copy the data.jdbc.smartsheet.jar file to the host. Here is an example using scp:

  Copy
  INCORTA_NODE_HOST=100.101.102.103
  cd ~/Downloads
  scp -i ~/.ssh/host_pemkey.pem  cdata.jdbc.smartsheet.jar incorta@${INCORTA_NODE_HOST}:/tmp/
  
  

• Secure shell into the host

  Copy
  ssh -i ~/.ssh/host_pemkey.pem incorta@${INCORTA_NODE_HOST}
  
  

• Copy the cdata.jdbc.smartsheet.jar to the IncortaNode/runtime/lib/ directory in bash shell

  Copy
  sudo su incorta
  INCORTA_INSTALLATION_PATH=/home/incorta/IncortaAnalytics
  cp /tmp/cdata.jdbc.smartsheet.jar $INCORTA_INSTALLATION_PATH/IncortaNode/runtime/lib/cdata.jdbc.smartsheet.jar
  
  

Here are the steps to restart the Analytics and Loader Services in an Incorta Cluster from the Cluster Management Console (CMC).

• As the CMC Administrator, sign in to the CMC.
• In the Navigation bar, select Clusters.
• In the cluster list, select a Cluster name.
• Select the Details tab, if not already selected.
• In the footer, select Restart.

Steps to connect Smartsheet and Incorta

To connect Smartsheet and Incorta, here are the high level steps, tools, and procedures:

• Create an external data source
• Create a schema with the Schema Wizard
• or, Create a schema with the Schema Designer
• Load the schema
• Explore the schema

Create an external data source

Here are the steps to create a external data source with the Smartsheet connector:

• Sign in to the Incorta Direct Data Platform™.
• In the Navigation bar, select Data.
• In the Action bar, select + New → Add Data Source.
• In the Choose a Data Source dialog, in Application, select Smartsheet.
• In the New Data Source dialog, specify the applicable connector properties.
• To test, select Test Connection.
• Select Ok to save your changes.

Smartsheet connector properties

Here are the properties for the Smartsheet connector:

Create a schema with the Schema Wizard

Here are the steps to create a Smartsheet schema with the Schema Wizard:

• Sign in to the Incorta Direct Data Platform™.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Schema Wizard.
• In (1) Choose a Source, specify the following:
  • For Enter a name, enter the schema name.
  • For Select a Datasource, select the Smartsheet external data source.
  • Optionally create a description.
• In the Schema Wizard footer, select Next.
• In (2) Manage Tables, in the Data Panel, first select the name of the Data Source, and then check the Select All checkbox.
• In the Schema Wizard footer, select Next.
• In (3) Finalize, in the Schema Wizard footer, select Create Schema.

Create a schema with the Schema Designer

Here are the steps to create a Smartsheet schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform™.
• In the Navigation bar, select Schema.
• In the Action bar, select + New → Create Schema.
• In Name, specify the schema name, and select Save.
• In Start adding tables to your schema, select Smartsheet.
• In the Data Source dialog, specify the Smartsheet table data source properties.
• Select Add.
• In the Table Editor, in the Table Summary section, enter the table name.
• To save your changes, select Done in the Action bar.

Smartsheet table data source properties

View the schema diagram with the Schema Diagram Viewer

Here are the steps to view the schema diagram using the Schema Diagram Viewer :

• Sign in to the Incorta Direct Data Platform™.
• In the Navigation bar, select Schema.
• In the list of schemas, select the Smartsheet schema.
• In the Schema Designer, in the Action bar, select Diagram.

Load the schema

Here are the steps to perform a Full Load of the Smartsheet schema using the Schema Designer:

• Sign in to the Incorta Direct Data Platform™.
• In the Navigation bar, select Schema.
• In the list of schemas, select the Smartsheet schema.
• In the Schema Designer, in the Action bar, select Load → Load Now → Full.
• To review the load status, in Last Load Status, select the date.

Explore the schema

With the full load of the Smartsheet schema complete, you can use the Analyzer to explore the schema, create your first insight, and save the insight to a new dashboard.

To open the Analyzer from the schema, follow these steps:

• In the Navigation bar, select Schema.
• In the Schema Manager, in the List view, select the Smartsheet schema.
• In the Schema Designer, in the Action bar, select Explore Data.

Additional Considerations

Types of Incremental Loads

You can enable Incremental Load for a Smartsheet data source. There are two types of incremental extracts:

Last Successful Extract Time

Fetch updates since the last time the tables were loaded. It is determined by the difference between the current time and the database timestamp.

Maximum Value of a Column

The column-based strategy depends on an extra column called Incremental Column in each table. The Smartsheet connector supports both timestamp and numeric columns. A timestamp column is of the type date or timestamp. A numeric column is of the type int or long.

Incremental Load Example

In this example, the invoices table must contain a column of the type Date or Timestamp in order to load the table incrementally with a last successful extract time strategy. In this case, the name of the date column is ModifiedDate and the format of the column is Timestamp.

Here are the data source property values for this example:

• Incremental is enabled
• Query contains SELECT * FROM invoices
• Update Query contains SELECT * FROM invoices WHERE ModifiedDate > ?
• Incremental Field Type = Timestamp