Tableau Integration

Overview

Organizations often require quick insight into data to understand the business impact, and don't want to waste valuable time consulting their corporate IT team. With the InsightEdge JDBC driver , this can be done using the data stored in the data grid. Tableau can be connected to the data grid via an ODBC-JDBC gateway, in order to retrieve and present the required data in a visual format.

This topic describes how to set up an integration of Tableau with GigaSpaces in a Microsoft Windows environment, so that the GigaSpaces JDBC driver can be used to retrieve data and display it in a graphic visual representation.

Architecture

Tableau can connect to the data grid using the GigaSpaces JDBC driver. However, Tableau can only use ODBC as a general connection option, so a third-party ODBC-JDBC connection is needed to convert ODBC requests from Tableau into JDBC requests for the GigaSpaces JDBC driver. The Easysoft ODBC-JDBC gateway has been evaluated and certified for use with GigaSpaces, and is used in the integration described here.

Sample Data Set

The example in this topic uses a familiar Simple - Superstore Tableau users data set. The original schema is too simple to show the join capabilities, so the model was updated with location information that is referenced by the orders entity. The updated schema is as follows:

Integrating Tableau with GigaSpaces

In order to integrate Tableau with GigaSpaces, you need a data grid that is up and running, and you need access to it. After setting up and starting GigaSpaces, do the following to create the necessary environment to integrate Tableau and view data:

  1. Add sample data to the Space.

  2. After GigaSpaces has been configured, download and install the ODBC-JDBC gateway, activate it with a trial license, and set it up in Microsoft Windows.

  3. Download and configure Tableau to work with GigaSpaces via the ODBC-JDBC gateway.

  4. Perform a query on the data grid via Tableau, and view the results.

Configuring GigaSpaces Locally

Before you deploy the data grid for the purpose of this demo, you should install the GigaSpaces dependencies.

Deploying the Data Grid

The first step is deploying a data grid and creating a Space.

To deploy the data grid on a local machine:

  1. From the $GS_HOME\tools\maven directory, run the installmavenrep.bat script.
  2. From the $GS_HOME\insightedge\tools\maven directory, run the insightedge-maven.cmd script.

  3. Navigate to the $GS_HOME\bin directory and launch a command window.

  4. Type insightedge host run-agent --auto --gsc=2 to create a data grid.

  5. Launch a new command window.

  6. Type insightedge space deploy --partitions=2 tableauSpace to create a Space with the name tableauSpace.

Populating the Space

After the Space is created, it needs to be populated with sample data, so that you can later run queries on this data via Tableau.

To populate the Space with data:

  1. Download the InsightEdge SQL demo and unpack it.

  2. Under the extracted insightedge-sql-demo folder, build the sample with mvn clean install.

  3. Execute the following command:

    java -jar target\insightedge-sql-demo.jar --space-url "jini://*/*/tableauSpace?locators=127.0.0.1" --lookup-group <DATA_GRID_LOOKUP_GROUP>

  4. Substitute the value <DATA_GRID_LOOKUP_GROUP> with the appropriate value for your deployment. The lookup-group argument is optional.

  5. Open the GigaSpaces Management Center in order to verify that the data was populated in the Space as expected.

Adding the GigaSpaces JDBC Client JAR to the Classpath

When connecting to the data grid, the ODBC-JDBC gateway runs the GigaSpaces JDBC driver. In order to make the driver visible to the ODBC-JDBC gateway, you have to create a special JAR file for use when configuring the gateway in Microsoft Windows.

To generate the GigaSpaces JDBC Client JAR:

  1. Navigate to $GS_HOME\insightedge\tools\jdbc.

  2. Run the build-jdbc-client.cmd script to create the file insightedge-jdbc-client.jar.

Setting Up the ODBC-JDBC Connection

Follow these instructions to download, install, and set up the Easysoft ODBC-JDBC gateway.

If you don't want to use the Easysoft ODBC-JDBC Gateway, you can use your preferred bridge implementation.

Installing the Easysoft ODBC-JDBC Gateway

To download and install the Easysoft ODBC-JDBC gateway:

  1. Log in to the Easysoft web site: link. (If this is your first time working with Easysoft, first follow the instructions to register and create your Easysoft user account.)

  2. Download the gateway from the Easysoft website.

  3. Install the gateway on your local machine.

Requesting an EasySoft License

After you install the Easysoft gateway on your machine, you must request a trial license in order to run the application.

To obtain a trial Easysoft license for the ODBC-JDBC gateway:

  1. Navigate to <EASYSOFT-HOME>\Easysoft\License Manager and open the Easysoft Data Access License Manager.

  2. Complete the following:

    • Provide your Easysoft account information: Name, E-Mail Address, Company, Telephone

    • Click Request License.

    • Select the license type (Trial).

    • Click Next.

  3. From the dropdown list, select Easysoft ODBC-JDBC Gateway (Desktop)(2.5), then click Next.

  4. Review your contact information to verify that it is correct, and click On-line Request.

Look in your email spam section for the license. At this point, your user account is updated and you have access to the ODBC-JDBC gateway software for the duration of the trial license period.

Configuring the ODBC-JDBC Gateway

After you have installed the gateway software and obtained a trial license, you have to configure the gateway to point to the required data source.

To configure the ODBC-JDBC gateway:

  1. Navigate to the ODBC Data Source Administrator in Microsoft Windows.

    For instructions on how to access the ODBC Data Source Administrator, see this Microsoft help topic.

  2. On the right-hand side of the ODBC Data Source Administrator window, go the System Data Sources tab, click Add.

  3. In the Create New Data Source dialog that opens, click Easysoft ODBC-JDBC Gateway.

  4. Click Finish.

  5. IMPORTANT - edit the CLASSPATH environment variable and append to it the path to your insightedge-jdbc-client.jar file. Create the CLASSPATH environment variable if it doesn't exist.

  6. In the Easysoft ODBC-JDBC Gateway DSN Setup window that is displayed, configure the gateway access to the data source by filling in the fields with the following values:

    • DSN: easysoft-insightedge-odbc-jdbc

    • Driver Class: com.gigaspaces.jdbc.Driver

    • Class Path: Leave field empty

    • URL: jdbc:insightedge:url=jini://*/*/tableauSpace?locators=127.0.0.1

  7. Check the Strip Quote check box.

  8. Click Test to verify the new connection.

  9. Click OK in the confirmation message, and click OK in the Easysoft ODBC-JDBC Gateway DSN Setup window to save your changes.

  10. Click OK in the ODBC Data Source Administrator window.

Setting Up Tableau

Download and install the Tableau desktop.

After the data grid has been populated with the sample data, and the ODBC-JDBC Gateway Tableau has been configured to connect to GigaSpaces, you can configure Tableau to read the data and display it in a graphic visual format. You can then query the data grid and see the activity that occurs under the hood when Tableau accesses it as a SQL database and reads the requested data.

Configuring the Data Source

Tableau has to be configured to use the ODBC-JDBC gateway as the data source.

To configure the ODBC-JDBC gateway:

  1. Start Tableau.

  2. Click Connect in the Tableau desktop, and select Other Database (ODBC) as the data source.

  3. In the Other Databases (ODBC) window, configure the options as follows:

    a. In the Connect Using area, defin the DSN:

    • Select easysoft-insightedge-odbc-jdbc.

    • Click Connect.

    b. In the *Connection Attributes** area, provide the name of the database:

    • In the Database field, type space.

  4. Click Sign In.

  5. Under Connections, click the search icon in the Schema area and select space.

    The sample data contains a Space called space. If you have additional Spaces in your GigaSpaces environment, they may appear in this list.

  6. Verify that Tableau can see the sample data by peforming a table search. You should see Locations and Orders in the list of tables.

Querying the Data Grid

Before performing a query on the data grid, you can use the GigaSpaces Management Center to look at the Space that contains the sample data. At this point, there have been no read operations.

Everything that needs to be configured has been set up, and you can execute queries against the data in the grid.

To query the data in GigaSpaces:

  1. From the Data tab, drag and drop the Orders and Locations tables to the data pane.

  2. Join the tables as Inner.

  3. Below the data pane, select the Sheet1 tab.

  4. Select Country and State from the Locations node using standard Windows functionality (press the Ctrl key while selecting).

  5. Right-click and select Create Hierarchy from the menu that is displayed.

  6. Double-click State.

  7. From the Measures area, drag and drop Sales to the Size option in the Marks card, and Profit to the Color option.

  8. View the results in the data pane; you can easily see the sales-profit insight that was derived from the data retrieved from the data grid.

  9. In the GigaSpaces Management Center, view the Space operations statistics. You can see the read operations that were performed when Tableau accessed the data grid to construct the view:

Additional Resources

Predictive Stock Pricing Demo