Connecting Tableau with NonStop Database SQL/MX

The NonStop system is the industry’s most Open and Advanced fault-tolerant systems out in the industry — even 40 over years after it was introduced, financial institutions and retailers are still putting their trust in the system designed to keep critical business applications running 365 days a year, 24 hours a day, 7 days a week.

Not surprisingly, the system extends beyond the OS and hardware architecture — the team in HPE has developed:

• a native NonStop database
• that inherits the fault-tolerant design and coherent integration between software and hardware
• and adopts industry-standard features and tools.

What does this mean for the NonStop database, SQL/MX?

This means that software and tools programmed to use industry-standard Open Database Connectivity drivers(ODBC) and Java Database Connectivity (JDBC) drivers will be able to connect to the NonStop database.

There are many data visualization and analytics tools that provide such connectors and in this tutorial, we will be connecting the SQL/MX database to Tableau.

What is Tableau?

Tableau is a data analytics and visualization tool widely used in the industry for business intelligence. 9 years a leader in Gartner Magic Quadrant for Analytics and Business Intelligence space — it helps helps users see and understand their data through its user-friendly interface. Tableau remains one of the top business intelligence platforms for fresh grads and top companies, such as LinkedIn and RedHat.

What is this article for?

This article serves as complimentary material to the existing Tableau Tutorial to their desktop client — the tutorial is already pretty well done so do check it out to continue exploring Tableau’s features while being connected to your SQL/MX database.

This document will provide specific guidance for connecting the Tableau software to the SQL/MX database.

Connection and ODBC prerequisites:

This tutorial assumes that NonStop ODBC 3.x Unicode driver has already been installed. Check out the NonStop ODBC/MX Client Drivers User Guide for more information on the driver.

You can refer to this tutorial to get help in configuring your ODBC — Getting Prerequisites > Configuring your ODBC.

This tutorial also assumes that on your host, NonStop SQL/MX has been installed, MXCS is running, and a MXCS data source has been added and started. Check with your administrator for the IP address, port number etc. (If you’re the administrator you can check this manual)

Data Model and Database Structure:

The data for this tutorial was taken from Tableau’s tutorial and desktop client for “Superstore”. The raw data can be downloaded from Tableau’s desktop client, or through this link – Superstore.xls from Tableau’s Tutorial (Click to Download).

Each excel sheet will be visualized as one table in the database. To import the data into SQL/MX, you would need to:

1. Save each excel sheet as a .csv file
2. Create tables in the database through MXCI.
3. Import from OSS using the command: /usr/tandem/sqlmx/bin/import [catalog].[schema].[table name] -I [table name].csv
   for example: /usr/tandem/sqlmx/bin/import catalog.schema.orders -I orders.csv

Let’s get to it!

1. Connecting to the NonStop Server

Create a new workbook in Tableau, and select the “Other Databases (ODBC)” option when choosing the type of connection to a server.

The connection details

Details you will need:

• Database Username
• Database Password
• Catalog Name
• Schema Name

Another window will be prompted — select the DSN (data source name) that you have registered in your ODBC configuration.

Server Details and Format of Server Connection String

Server Attributes needed:

• IP Address
• Port Number
• Catalog Name

Format: TCP:[IP Address]/[Port Number]

Database Name: [Catalog Name]

2. Setting up tables for Tableau’s Superstore Tutorial

Congrats! If you’ve made it to this step, it means that your SQL/MX database has successfully connected to the Tableau software.

Let’s configure the relationship between the tables in this database.

Selecting tables for Superstore

Select the database and schema where you have created and populated the database. Click and drag the tables into the orange space indicated.

Creating relationships between tables

• Start with the “Orders” table, and then the “People” table
• A line between the tables, and a box showing the relation between the tables will appear
• Check the fields used to link the 2 tables together
• Repeat with the “Returned” table and “Orders” table

And you’re all set! You can continue onto the Tableau tutorial Step 2: Drag and Drop to take a first look.

Other data visualization tools that can work with SQL/MX

Of course, Tableau is not the only data visualization tool that works with NonStop SQL/MX’s ODBC driver. Other applications such as Power BI and even Excel can also connect in a similar manner to the NonStop database.

Not only is NonStop SQL/MX ANSI compliant, but it also adopts ODBC and JDBC standards, allowing effortless database connection with a state-of-the-art fault tolerance.

Hope this was useful, and until the next article with NonStop SQL/MX!