Using Tableau with IBM Informix Using Tableau with IBM Informix About IBM Informix (aka IDS) and the Informix Warehouse Accelerator (IWA) For businesses seeking “speed of thought” analysis of warehouse data or operational data, two editions of Informix complement the integrated tooling with additional, patent-pending software technology that exponentially accelerates even the most complex queries. Informix Advanced Enterprise Edition and Informix Advanced Workgroup Edition both leverage this technology known as the Informix Warehouse Accelerator (IWA). IWA loads data completely into system memory in a compressed form, using a special columnar scheme developed by IBM. As queries come in, the Informix database passes them to the Informix Warehouse Accelerator which can scan billions of rows of data in seconds to return immediate results. The Informix Warehouse Accelerator is an in-memory columnar database engine plug-in which integrates with IBM Informix database server, and is designed to be completely transparent to any business applications so no application changes are needed to take advantage of accelerated query performance against an Informix database. Users can utilize front-end analysis and reporting tools such as IBM Cognos, Tableau, Microstrategy, etc. with IBM Informix database server and experience unprecedented response times for analytics of order of magnitude 50, 100, 500 or 1000+ times faster than in other relational data sources. IWA constitutes an in-memory columnar OLAP database plug-in for Informix databases, which tightly integrates to Informix servers using TCP/IP connection, and behaves seamlessly behind the scenes to provide extreme speed for analytic queries, for the Informix database users and applications. Informix offers tremendous costsavings since IWA runs on commodity hardware (Linux on Intel/AMD x86_64) and an integrated Informix database server + IWA system can simultaneously meet and surpass service level agreements for both transactional and analytical workloads, eliminating the need for separate OLTP and OLAP systems. IBM Informix provides exceptional database technology that saves costs and simplifies the deployment and maintenance of the most challenging and variable data requirements and needs in: OLTP (transactions), OLAP (analytics), Internet-of-Things (IoT), Smart Meters, High-Availability and Data Replication (geographicallyspread data for HA, data replication, consolidation, dissemination and query sharding), Big Data (NoSQL engine) and Hadoop integration, and Embedded/OEM autonomic systems. IBM Informix is an hybrid database platform, which is: object-relational OLTP and in-memory columnar OLAP database; an SQL and NoSQL (JSON/BSON) database. For more information about IBM Informix, please, refer to ibm.com/Informix and, for more information specifically on the Informix Warehouse Accelerator in-memory columnar analytic technology, please, consult: http://www-01.ibm.com/software/data/informix/warehouse/ .

Connecting Tableau Desktop with IBM Informix database

1

Using Tableau with IBM Informix

To connect to Informix from Tableau, users must install the 64-bit or the 32-bit IBM INFORMIX ODBC driver on the Tableau (Desktop and/or Server) machine, and connect to the Informix data source using Tableau's "Other ODBC" connector. The steps below show this process and provides the suggested paramaters values in the customizable Tableau's data source (.tds) file. This is a sample setup, customization and test using Tableau Desktop 8.1 with IBM Informix via Tableau’s generic ODBC connection, and using a DSN with the latest IBM Informix ODBC driver in its 32-bit version.

(1) Install and set up the IBM Informix and Tableau products at both server and client machines In this test, we used recent versions of Informix (on the server side: IBM Informix/IDS + Informix Warehouse Accelerator (IWA); and on the client side: IBM Informix Client SDK –for INFORMIX ODBC driver—and Tableau Desktop). On the client machine (Windows 7 64-bit): Product

Version

Where to download trial

Tableau Desktop

8.x (specifically, 8.1)

http://www.tableausoftware.com/ products/trial?os=windows

IBM Informix Client Software Development Kit (aka Informix Client SDK or I-CSDK) It is possible to also install and use the runtime version of the Informix client package: IBM Informix Connect

4.10.xC3+ (specifically, a Fixpack on CSDK/ Connect 4.10.TC3, to include new ODBC scalar function {fn QUARTER()} in the Informix ODBC driver) It is important to make sure you install the INFORMIX ODBC DRIVER

For the Informix Client SDK (development): http://www14.software.ibm.com/ webapp/download/search.jsp? rs=ifxdl For the Informix Connect (runtime): http://www14.software.ibm.com/ webapp/download/search.jsp? rs=ifxic All IBM Informix downloads and trials (90-days): http://www-01.ibm.com/software/ data/informix/downloads.html All IBM Informix and other IBM Software FixPack downloads (requires IBM Support Entitlement):

2

Using Tableau with IBM Informix

http://www-933.ibm.com/support/ fixcentral/

On the server machine (Linux on Intel/AMD 64-bit): Product

Version

Where to download trial

Informix Advanced Enterprise Edition

12.10.xC3 (specifically, 12.10.FC3)

Informix database server is formerly known as Informix Dynamic Server (IDS). If IDS is integrated with IWA, then IDS needs to be 64-bit and run on: Linux on x86_86, AIX on Power, HP-UX on Itanium, or Solaris on SPARC/Intel 64-bit.

Installed both Informix database server (IDS) and the Informix Warehouse Accelerator (IWA).

All IBM Informix downloads and trials (90-days): http://www-01.ibm.com/software/ data/informix/downloads.html

In this case, both the objectrelational (IDS) and the in-memory columnar (IWA) database engines were installed and run on the same Linux x86_64 box.

All IBM Informix and other IBM Software FixPack downloads (requires IBM Support Entitlement): http://www-933.ibm.com/support/ fixcentral/

Informix Warehouse Accelerator (IWA) is an in-memory columnar OLAP database engine plugin for Informix databases. IWA integrates with Informix/IDS using TCP/IP, and needs to run on a Linux x86_64 system with enough memory and cores (commodity HW).

Informix version of Tableau’s sample database: superstore Attached you can find the Informix version (Informix’s dbexport  output) of Tableau’s sample database superstore (which was ported from Tableau's sample Excel/xls file to Informix). You can use Informix’s dbimport command to import this database into your Informix database server. superstore (Tableau sample database) in Informix export format (dbexport) File: superstore.exp.tar

3

Using Tableau with IBM Informix

Before importing the superstore database into Informix, you may want to edit the file superstore.exp/ superstore.sql to replace the “informix” user which currently owns all the database tables and objects in the superstore database, with you own or preferred database user. For example: Copy and uncompress the export of database superstore, into your Informix data server machine: # cp superstore.exp.tar /tmp # cd /tmp # tar -xvf superstore.exp.tar Edit file ./superstore.exp/superstore.sql as needed. For instance, you may want to replace the user “Informix”, which appears as owner of all the database objects (ex: “Informix”.), with a different database user. Verify that your Informix database server is up and running, set the proper value format for date data types, and import the database superstore into the Informix storage space (dbspace) that you prefer: # onstat IBM Informix Dynamic Server Version 12.10.FC3 -- On-Line -- Up 9 days 21:01:00 -- 491680 Kbytes # export DBDATE=mdy4/ # dbimport superstore [-dbspace ] Once the dbimport completes successfully… dbimport completed You can take a look at your Informix database superstore: # dbaccess superstore Database selected. > select count(*) from orders;

(count(*)) 8546

4

Using Tableau with IBM Informix

1 row(s) retrieved. > select first 1 * from orders;

row_id 1 order_id 3 order_date 10/13/2010 order_priority Low order_quantity 6 sales 261.5400 discount 0.04 ship_mode Regular Air profit -213.2500 unit_price 38.94 shipping_cost 35.00 customer_name Muhammed MacIntyre city Highland Park zip_code 60035 state Illinois region Central customer_segment Small Business product_category Office Supplies product_subcatego+ Storage & Organization product_name Eldon Base for stackable storage shelf, platinum product_container Large Box product_base_marg+ 0.80 ship_date 10/20/2010 1 row(s) retrieved. > select count(*) from returns;

(count(*)) 572 1 row(s) retrieved. > select first 3 * from returns;

5

Using Tableau with IBM Informix

order_id status 65 Returned 69 Returned 134 Returned 3 row(s) retrieved. > select count(*) from users;

(count(*)) 4 1 row(s) retrieved. > select * from users;

region manager Central Chris East Erin South Sam West William 4 row(s) retrieved. > ^C # Now, we are ready to use our Informix database superstore from Tableau Desktop.

Informix Warehouse Accelerator data mart definition for database superstore Optionally, if you want to use IWA for the entire database superstore: Attached you can find the IWA datamart definition (xml file) for this superstore Informix database, in case you want to use IWA as well.

6

Using Tableau with IBM Informix

If the user/schema of the superstore’s database tables and objects is not “Informix”, then, you will need to replace the occurrences of the user/schema “informix” with the one you use for your database objects in the data mart’s .xml file. You can use GUI tools IBM Smart Analytics Optimizer Studio (ISAO) or Informix OpenAdmin Tool (OAT); or the Informix IWA SQL Admin routines or IWA Java CLI, to deploy this IWA datamart (that we named superstore_dm) for the entire Informix database superstore. IWA datamart definition for the entire database superstore File: superstore_dm.xml file

In order to see the impressive IWA acceleration for OLAP queries against superstore database, and appreciate IWA benefits, you will probably need to insert more sample data so that you can have a medium or large size superstore database. You can add more data, especially into fact table orders until the table has millions of rows, and into returns until this table has dozens or hundreds of thousand rows. Also, make sure you use Live Connection (instead of Data Extract) from Tableau Desktop to this superstore Informix database (which will be leveraging IWA technology).

(2) Create and Test an Informix ODBC DSN (using Windows' Control Panel) at the Tableau client Create an ODBC Data Source to your Informix database superstore, using Window’s ODBC DSN Administrator application (under Control Panel) and the latest version of the Informix ODBC driver. In this particular example, we used Informix ODBC driver 32-bit and Windows’ ODBC 32 Admin, because up until the very recent Tableau Desktop version, Tableau was only available in 32-bit.  However, the latest versions of Tableau now support 64-bit as well. We named the ODBC DSN superstore_iwa, and connects to a remote database in Informix 12 (IWA-enabled) called superstore, which is based on Tableau's sample database in Excel, as mentioned earlier. Note: In the latest versions of Informix and the Informix CSDK/Connect, there is support for SQL and scalar ODBC function {fn QUARTER()}, as well as new LIMIT syntax which Tableau uses in Preview Data and Top/ Bottom queries. Create a new DSN superstore_iwa from Windows Control Panel application ODBC Data Source Administrator, and make sure you use the latest IBM INFORMIX ODBC DRIVER (version 4.10 or later) to connect to remote Informix DB superstore: ODBC DSN superstore_iwa (ODBC 32-Admin configuration, using Informix ODBC driver 4.10.xC3, xC4 or later)

7

Using Tableau with IBM Informix

...

8

Using Tableau with IBM Informix

(3) Launch Tableau Desktop and Created a new Tableau Data Source there called superstore_ifmx based on Generic ODBC Connection (last option in Tableau connectors) - In DSN: Select already created local ODBC DSN superstore_iwa (the one that uses latest INFORMIX ODBC DRIVER and connects to remote Informix database superstore) - Click Connect button to test connection and be able to do few things online to the server and database - Under Connection Attributes, set/change the following: - odbc-connect-string-extras = DELIMIDENT=y - odbc-native-protocol = yes

9

Using Tableau with IBM Informix

- Select/change the owner/schema of the tables that will be chosen, from the drop/down list. In our case, informix. In your case, your own user/schema for your superstore’s database objects. - Add just one table by default in this connection for now. In our case, we added all the 3 tables: orders, users and returns. - Put a name that you prefer to your generic ODBC connection just created, for instance: superstore_ifmx - Once this basic setup is done. Click OK. With this, a new Tableau Data Source (.tds) file called superstore_ifmx is generated and can be found under: .tds Example, on Windows 7: C:\Users\\Documents\My Tableau Repository\Datasources\superstore_ifmx.tds

(4) Exit Tableau, or Close/Disconnect from the data source before making changes to this file Close Tableau Desktop after having created and saved your superstore_ifmx data source.

(5) Backup the generated superstore_ifmx.tds file (to keep the original copy), before making changes (in Step 6) (6) Edit the superstore_ifmx.tds (Tableau's data source) xml file created and associated to this new Informix odbc data source superstore_iwa, Apply the suggested customizations to make it work better for Informix: Note: Refer to this article in Tableau's KB to customize ODBC connection to any database Customizing and Tuning ODBC Connections http://kb.tableausoftware.com/articles/knowledgebase/customizing-odbc-connections Suggested customization parameters for Informix are colored in blue below. Please, apply them as needed to your Tableau data source:

10

Using Tableau with IBM Informix

Editions made the Tableau's data source file for your generic ODBC connection called superstore_ifmx.tds which was located under \My Tableau Repository\Datasources directory: Note: As general recommendation, back up your original/previous version of superstore_ifmx.tds file before editing, and edit it according to what Informix ODBC driver supports in functionality ($INFORMIXDIR/incl/cli/ sqlext.h ) and Tableau's KB article above. Below, we provide the customization parameters for best work of Tableau with Informix. We at IBM continue reviewing, adjusting and testing results of new and better changes to it. Different changes may be required to be done by the users, depending on their needs, Informix and Tableau environment and version, and Informix server and client versions. At this moment, the best setting for superstore_ifmx.tds file looks like the suggested sample below. The customization section, colored in blue below, is the one you need to focus on and copy “as is” into your own customization section of your .tds file, in order to change behavior of Tableau with Informix data source to work best together. Also, make sure the values for the connection, in magenta below, are also similar to the ones you have for your connection section in your .tds file. File superstore_ifmx.tds customization data source xml file under \Tableau Repository \Datasources superstore_ifmx.tds file ( Tableau data source - customized odbc connection ) used in Tablau (live connection) with ODBC DSN (Informix) : superstore_iwa



11

Using Tableau with IBM Informix



12

Using Tableau with IBM Informix



13

Using Tableau with IBM Informix



14

Using Tableau with IBM Informix



15

Using Tableau with IBM Informix



(7) Save the file superstore_ifmx.tds with all these changes and customizations for your Informix data source (8) Open this file using Tableau Desktop: By dragging and dropping the .tds file that you changed, into Tableau Desktop You can leave Tableau Desktop open (without connecting to any data source) and then just drag file superstore_ifmx.tds file from Windows Explorer and drop it on Tableau Desktop application. This action makes Tableau Desktop to attempt the live connection to this data source with the customization made in your .tds file.

(9) Now, you can add more tables (or multiple tables) to your connection, create worksheets/ dashboards in a Tableau book, create reports and dashboards, etc. Enjoy!

16

Using Tableau with IBM Informix

Sample Results: Demo Video of Tableau’s Getting Started with Informix DB superstore (IWA-enabled) File: Tableau-Informix-IWA-sample-superstore-demo-getting-started.mp4

At IBM, we are continuing to add improvements and innovations into Informix server and client products, and IWA, in order to provide a more seamless and optimized experience for BI applications, such as Tableau. Unfortunately, up until now, Tableau Software has not provided a specific connector for Informix database in their products, to provide a more straight forward, seamless connectivity and fully functional experience for the many common Informix and Tableau users worldwide. Until then, we at IBM hope that this post and suggested customization of Tableau’s .tds file for Informix can help our many Informix users, partners and prospects around the world, who want to use Tableau with Informix. If you are interested in integrating Tableau with Informix, please, reach out to Tableau Software team and let them know, so that they can listen to the public demand and hopefully, provide this much needed Informix connector soon in the future. :-)

17