In 4D Webinar – How to Connect Tableau with 4D, William Taylor (Technical Account Manager, 4D US) gave an overview of the Tableau tool and how it could be used to visualize your 4D data. In this blog post, we’ll dig into the technical details to give you a deeper understanding of how it works. This will allow you to build, for example, a visual showing the highest invoice total with nothing more than 4D REST and Tableau!

TIP: Invoice with 4D and Tableau 

 

What is Tableau?

Tableau is a data visualization tool used for business intelligence. It helps simplify raw data into an easily understandable format. The main job of Tableau is connecting and extracting data stored in various places. It can pull data from any platform and can extract data from any database … including a 4D database. The extracted data can then be connected to Tableau Desktop. This is where a data analyst works with it and develops visualizations.

In order to import your data from an existing API directly into Tableau and create dashboards very easily, you’ll need to use a Web Data Connector.

All of this will be covered in this blog post but first, let’s get started with the prerequisites!

 

Prerequisites

In this blog post, we’re going to:

1. Configure 4D REST and set up the API
2. Get and set the Web Data Connector SDK
3. Create a Web Data Connector
4. Connect to Tableau
5. Build a dashboard

Configure 4D REST and set up the API

Application Example: In this blog post, we’ll use the Invoice application that’s already available on GitHub. Feel free to download or clone the repository.

In order to access data from the 4D database through REST, we need to configure our 4D database. You remember how to do it, don’t you? 

Does checking the “Expose as REST server” option in the “Web/REST resource” page (in our case, the Invoice application) and making sure the data source tables (i.e., where you want to pull data from) are exposed in REST sound familiar? If this doesn’t ring a bell, be sure to check out this blog post for all the details.

To make sure your API is set up correctly and your data can be read within 4D using REST requests, open a web browser, and insert “/rest“ after ADDRESS: PORT.  All 4D REST URL requests begin with /rest.

For example, let’s verify that we get all entities of the [INVOICES] dataclass:

Don’t forget to enable CORS!

To avoid CORS restrictions (resources from different origins which are restricted from interacting with each other) make sure to allow CORS in 4D. It can be done from the UI thanks to a new setting in the Settings > Web > Options (II) tab. You can add allowed domain names (127.0.0.1:8888 in our case – you’ll see that later), and available methods. When the server is next started, the domains and methods will be used automatically. This blog post is available to further explain the details and show you how to do it.

Now that our data is ready and CORS is enabled, let’s move on to the Tableau part!

Web Data Connector (wdc)

   What is a WDC?

Tableau doesn’t have a native 4D interface, but we need to access the data somehow. So how can we connect it to 4D?

That’s where Tableau’s Web Data Connector (WDC) comes into play. WDC is an application running in a node web server. You can think of it as a middleware layer that sits between Tableau and 4D. Tableau calls the WDC and the JavaScript code in it requests data from data sources like 4D.

   Set up the WDC

So basically, we need to set up an HTML file that calls JavaScript code that:

• connects to web data via the REST API,
• converts the received 4D data into JSON format,
• passes the data to Tableau.

 

The good news is that Tableau provides a free software bundle (with examples) that we can use to get started! The connector is also very well documented, so take a look at this page for more information about the different steps below. 

• Make sure you have the following dependencies installed git, node, and npm.
• Get the WDC SDK by either downloading it or cloning it: git clone https://github.com/tableau/webdataconnector.git
• In the command prompt, change to the directory where you downloaded the repository: cd webdataconnector
• Install the dependencies with npm: npm install –production
• Start the test web server: npm start
• Open a browser and navigate to http://127.0.0.1:8888/Simulator/index.html
• The WDC simulator appears:

Congratulations! Your environment is properly set up. Let’s now create our connector to connect to 4D and load the Invoice application data.

Note: Tableau is very well documented. All of the following steps to create your own WDC were taken from this page. Check it out for more technical details.

   Create a connector to connect Tableau and 4D

As we said earlier, in order to let our REST API be consumed by Tableau, we need to create a WDC. This means creating two things:

1. An HTML page that links to our JavaScript code and to the WDC library. The page also contains a simple button element that illustrates how users can interact with the connector before getting data.
2. A JavaScript file where we define the schema and get the 4D data to be passed to Tableau.

 

    Create the HTML page (the user interface)

In the webdataconnector folder that we cloned from GitHub, create another folder named Invoices. In the Invoices folder, create an invoices.html page and copy-paste the following code:

<html>
  <head> 
     <meta http-equiv="Cache-Control" content="no-store" /> 
     <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" rel="stylesheet" crossorigin="anonymous"> 
     <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js" type="text/javascript"></script> 
     <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js" crossorigin="anonymous"></script> 
     <script src="https://connectors.tableau.com/libs/tableauwdc-2.3.latest.js" type="text/javascript"></script> 
     <script src="invoices.js" type="text/javascript"></script> 
 </head> 
 <body> 
  <div class="container container-table"> 
   <div class="row vertical-center-row"> 
    <div class="text-center col-md-4 col-md-offset-4"> 
     <button type="button" id="submitButton" class="btn btn-success" style="margin: 10px;">Get Data from 4D!</button> 
    </div> 
  </div> 
 </div> 
</body> 
</html>

The HTML page is fairly simple to understand. It calls:

1. Bootstrap to beautify and format the page,
2. jQuery to get the JSON data,
3. the main library (tableauwdc-2.3.latest.js) of the WDC API,
4. the JavaScript file that we will create next where our logic will happen (invoices.js).
5. And finally, a simple button “Get Data from 4D!” that will allow users to interact with the connector.

 

     Create the JS file (the logic)

Copy the file structure into the invoices.js. Here, we’re interested in two functions:

• getSchema – where you define how to map the data to one or more or tables.

myConnector.getSchema = function (schemaCallback) { 
 var cols = [ { 
  id: "invoiceNum", 
  alias: "The Invoice Number", 
  dataType: tableau.dataTypeEnum.string 
 }, { 
  id: "total", 
  alias: "The Invoice Amount", 
  dataType: tableau.dataTypeEnum.int 
 }]; 
var tableSchema = { id: "invoices", alias: "List of Invoices", columns: cols }; 
schemaCallback([tableSchema]); };

• getData – where you get your 4D data and pass it to Tableau. The jQuery $.getJSON function gets the data from our 4D database and uses a success handler to store the returned data in a response parameter (resp). Then we simply iterate over the elements in the JSON object and store the data in the tableData array:

myConnector.getData = function(table, doneCallback) { 
$.getJSON("http://192.168.223.26:80/rest/INVOICES", function(resp) {
 var result = resp.__ENTITIES, tableData = []; 
  for (var i = 0, len = result.length; i < len; i++) { 
   tableData.push({ 
    "invoiceNum": result[i].Invoice_Number, 
    "total": result[i].Total
     }); 
   } 
table.appendRows(tableData); 
doneCallback(); 
 });
};

• Finally, add the event listener that responds to the click on the button we created in the HTML file. 

$(document).ready(function () { 
  $("#submitButton").click(function () { 
   tableau.connectionName = "Invoices"; 
   tableau.submit(); 
 }); 
});

Now that our code is ready, let’s test it!

Test the connector

Open the simulator (open a browser and navigate to http://127.0.0.1:8888/Simulator/index.html), and introduce the path to your HTML file in the connector URL:

 

Load your connector, and click the Get Data from 4D! button.

You should see your 4D data up and running in a Tableau table when clicking the Fetch Table Data button:

 

Bravo, you made it! Now it’s time to test the connector in Tableau Public.

 

Note: If you messed up on a step or two, no worries! I put together all of the necessary files for you. So go ahead and download the HDI, put the invoices folder at the same level as the README.md file in the webdataconnector folder and carry on from there!

 

Use WDC in Tableau Public

Tableau Public is the free version of Tableau Desktop which lets you see and understand data in minutes. The free version doesn’t have all of the native connectors, but all we need is the Web Data Connector and that IS available! 

Download the tool, fire it up, and:

1. Click on Web Data Connector
2. Load the Web Data Connector we just created by using the URL: http://127.0.0.1:8888/invoices/invoices.html
3. Click the Get Data from 4D button:

 

Next, click the Update Now button. This action will trigger the getData function that we added earlier.

Now click on Sheet 1 (shown in the image below). This is where you’ll create your graphs.

For example, let’s say I want a visual representation of the highest invoice.

Drag the Invoice Number (from the Dimensions section on the left) into the Columns section on the top. Then drag the Invoice Total (from the Measures section on the left) into the Rows section on the top. Now watch the magic happen:

Voilà! The highest invoice is the one with the ID INV00042 and a total amount of 2.284.

The Tableau tool is incredibly rich and powerful, and their documentation is well written. Check it out and give it a try if you want to go even further. In an upcoming blog post, we’ll see how to host our Tableau Web Data Connector on GitHub.