Skip navigation

Using Tableau with IBM Informix

score 20
You have not voted. Active

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 cost-savings 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 (geographically-spread 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


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):

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

 

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.

 

Informix Warehouse Accelerator (IWA) is an in-memory columnar OLAP database engine plug-in 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).

12.10.xC3

(specifically, 12.10.FC3)

 

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

 

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

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):

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

 

 

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

 

 

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”.<database_object>), 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 <dbspace_name>]

 

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

 

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;

 

 

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.

 

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)



...

 

 

(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

 

- 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:

 

<Windows User's Documents folder>\My Tableau Repository\Datasources\<Saved Tableau's generic-odbc for your Informix DSN>.tds

 

Example, on Windows 7:

 

C:\Users\<Your_User_ID>\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:

 

Editions made the Tableau's data source file for your generic ODBC connection called superstore_ifmx.tds which was located under <My Documents>\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 <My Documents>\Tableau Repository\Datasources


superstore_ifmx.tds file

( Tableau data source - customized odbc connection ) used in Tablau (live connection) with ODBC DSN (Informix) : superstore_iwa

 

 

<?xml version='1.0' encoding='utf-8' ?>

 

<datasource formatted-name='genericodbc.41649.761909340275' inline='true' version='8.2' xmlns:user='http://www.tableausoftware.com/xml/user'>

  <!-- build 8100.13.1231.1350                -->

  <connection class='genericodbc' dbname='' odbc-connect-string-extras='DELIMIDENT=y' odbc-dbms-name='Informix' odbc-driver='IBM INFORMIX ODBC DRIVER (64-bit)' odbc-dsn='superstore_iwa' odbc-native-protocol='yes' odbc-use-connection-pooling='' port='' server='' username=''>

    <relation join='inner' type='join'>

      <clause type='join'>

        <expression op='='>

          <expression op='[orders].[region]'>

</expression>

          <expression op='[users].[region]'>

          </expression>

        </expression>

      </clause>

      <relation join='inner' type='join'>

        <clause type='join'>

          <expression op='='>

            <expression op='[orders].[order_id]'>

</expression>

            <expression op='[returns].[order_id]'>

</expression>

</expression>

        </clause>

        <relation name='orders' table='[informix].[orders]' type='table' />

        <relation name='returns' table='[informix].[returns]' type='table' />

      </relation>

      <relation name='users' table='[informix].[users]' type='table' />

    </relation>

    <cols>

      <map key='[city]' value='[orders].[city]' />

      <map key='[customer_name]' value='[orders].[customer_name]' />

      <map key='[customer_segment]' value='[orders].[customer_segment]' />

      <map key='[discount]' value='[orders].[discount]' />

      <map key='[manager]' value='[users].[manager]' />

      <map key='[order_date]' value='[orders].[order_date]' />

      <map key='[order_id]' value='[orders].[order_id]' />

      <map key='[order_priority]' value='[orders].[order_priority]' />

      <map key='[order_quantity]' value='[orders].[order_quantity]' />

      <map key='[product_base_margin]' value='[orders].[product_base_margin]' />

      <map key='[product_category]' value='[orders].[product_category]' />

      <map key='[product_container]' value='[orders].[product_container]' />

      <map key='[product_name]' value='[orders].[product_name]' />

      <map key='[product_subcategory]' value='[orders].[product_subcategory]' />

      <map key='[profit]' value='[orders].[profit]' />

      <map key='[region]' value='[orders].[region]' />

      <map key='[returns_order_id]' value='[returns].[order_id]' />

      <map key='[row_id]' value='[orders].[row_id]' />

      <map key='[sales]' value='[orders].[sales]' />

      <map key='[ship_date]' value='[orders].[ship_date]' />

      <map key='[ship_mode]' value='[orders].[ship_mode]' />

      <map key='[shipping_cost]' value='[orders].[shipping_cost]' />

      <map key='[state]' value='[orders].[state]' />

      <map key='[status]' value='[returns].[status]' />

      <map key='[unit_price]' value='[orders].[unit_price]' />

      <map key='[users_region]' value='[users].[region]' />

      <map key='[zip_code]' value='[orders].[zip_code]' />

    </cols>

<connection-customization class='genericodbc' enabled='true' version='8.2'>

      <vendor name='Informix' />

      <driver name='IBM INFORMIX ODBC DRIVER (64-bit)' />

      <customizations>

<customization name='CAP_CREATE_TEMP_TABLES' value='no' />

<customization name='CAP_ISOLATION_LEVEL_READ_COMMITTED' value ='no' />

<customization name='CAP_ISOLATION_LEVEL_READ_UNCOMMITTED' value='no' />

<customization name='CAP_ISOLATION_LEVEL_REPEATABLE_READS' value='no' />

<customization name='CAP_ISOLATION_LEVEL_SERIALIZABLE' value='no' />

<customization name='CAP_ODBC_BIND_DETECT_ALIAS_CASE_FOLDING' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_DATE_AS_CHAR' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_MEDIUM_STRING_BUFFERS' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_SIGNED' value='no' />

<customization name='CAP_ODBC_BIND_FORCE_SMALL_STRING_BUFFERS' value='no' />

<customization name='CAP_ODBC_BIND_SUPPRESS_COERCE_TO_STRING' value='no' />

<customization name='CAP_ODBC_BIND_SUPPRESS_INT64' value='no' />

<customization name='CAP_ODBC_BIND_SUPPRESS_PREFERRED_CHAR' value='no' />

<customization name='CAP_ODBC_BIND_SUPPRESS_PREFERRED_TYPES' value='no' />

<customization name='CAP_ODBC_BIND_SUPPRESS_WIDE_CHAR' value ='no' />

<customization name='CAP_ODBC_CURSOR_DYNAMIC' value='no' />

<customization name='CAP_ODBC_CURSOR_FORWARD_ONLY' value='no' />

<customization name='CAP_ODBC_CURSOR_KEYSET_DRIVEN' value='no' />

<customization name='CAP_ODBC_CURSOR_STATIC' value='no' />

<customization name='CAP_ODBC_ERROR_IGNORE_FALSE_ALARM' value='no' />

<customization name='CAP_ODBC_FETCH_BUFFERS_RESIZABLE' value='no' />

<customization name='CAP_ODBC_FETCH_BUFFERS_SIZE_MASSIVE' value='no' />

<customization name='CAP_ODBC_FETCH_CONTINUE_ON_ERROR' value ='no' />

<customization name='CAP_ODBC_FORCE_SINGLE_ROW_BINDING' value='no' />

<customization name='CAP_ODBC_METADATA_STRING_LENGTH_UNKNOWN' value='no' />

<customization name='CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY' value='no' />

<customization name='CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY' value='no' />

<customization name='CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR' value='no' />

<customization name='CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_API' value='no' />

<customization name='CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API' value='no' />

<customization name='CAP_ODBC_REBIND_SKIP_UNBIND' value='no' />

<customization name='CAP_ODBC_TRIM_VARCHAR_PADDING' value='no' />

<customization name='CAP_ODBC_UNBIND_AUTO' value='no' />

<customization name='CAP_ODBC_UNBIND_BATCH' value='no' />

<customization name='CAP_ODBC_UNBIND_EACH' value='no' />

<customization name='CAP_ODBC_USE_NATIVE_PROTOCOL' value='yes' />

<customization name='CAP_QUERY_BOOLEXPR_TO_INTEXPR' value='no' />

<customization name='CAP_QUERY_GROUP_ALLOW_DUPLICATES' value='yes' />

<customization name='CAP_QUERY_GROUP_BY_ALIAS' value='yes' />

<customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />

<customization name='CAP_QUERY_HAVING_REQUIRES_GROUP_BY' value='yes' />

<customization name='CAP_QUERY_HAVING_UNSUPPORTED' value='no' />

<customization name='CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT' value='yes' />

<customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='yes' />

<customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />

<customization name='CAP_QUERY_NULL_REQUIRES_CAST' value='no' />

<customization name='CAP_QUERY_SELECT_ALIASES_SORTED' value='no' />

<customization name='CAP_QUERY_SORT_BY_DEGREE' value='yes' />

<customization name='CAP_QUERY_SUBQUERIES' value='no' />

<customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='no' />

<customization name='CAP_QUERY_SUBQUERY_QUERY_CONTEXT' value='no' />

<customization name='CAP_QUERY_TOPSTYLE_LIMIT' value='no' />

<customization name='CAP_QUERY_TOPSTYLE_ROWNUM' value='no' />

<customization name='CAP_QUERY_TOPSTYLE_TOP' value='no' />

<customization name='CAP_QUERY_TOP_0_METADATA' value='no' />

<customization name='CAP_QUERY_TOP_N' value='no' />

<customization name='CAP_QUERY_WHERE_FALSE_METADATA' value='no' />

<customization name='CAP_SELECT_INTO' value ='yes' />

<customization name='CAP_SELECT_TOP_INTO' value='no' />

<customization name='CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API' value='no' />

<customization name='CAP_SET_ISOLATION_LEVEL_VIA_SQL' value ='no' />

<customization name='CAP_SKIP_CONNECT_VALIDATION' value='no' />

<customization name='CAP_SUPPRESS_CONNECTION_POOLING' value='no' />

<customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='yes' />

<customization name='CAP_SUPPRESS_DISPLAY_LIMITATIONS' value='yes' />

<customization name='SQL_AGGREGATE_FUNCTIONS' value='127' />

<customization name='SQL_CATALOG_NAME_SEPARATOR' value=':' />

<customization name='SQL_CATALOG_TERM' value ='Database' />

<customization name='SQL_CATALOG_USAGE' value='31' />

<customization name='SQL_COLUMN_ALIAS' value ='yes' />

<customization name='SQL_CONVERT_BIGINT' value='0' />

<customization name='SQL_CONVERT_BINARY' value='0' />

<customization name='SQL_CONVERT_BIT' value ='0' />

<customization name='SQL_CONVERT_CHAR' value ='0' />

<customization name='SQL_CONVERT_DATE' value ='0' />

<customization name='SQL_CONVERT_DECIMAL' value='0' />

<customization name='SQL_CONVERT_DOUBLE' value='0' />

<customization name='SQL_CONVERT_FLOAT' value='0' />

<customization name='SQL_CONVERT_FUNCTIONS' value='0' />

<customization name='SQL_CONVERT_INTEGER' value='0' />

<customization name='SQL_CONVERT_INTERVAL_DAY_TIME' value='0' />

<customization name='SQL_CONVERT_LONGVARBINARY' value='0' />

<customization name='SQL_CONVERT_NUMERIC' value='0' />

<customization name='SQL_CONVERT_REAL' value ='0' />

<customization name='SQL_CONVERT_SMALLINT' value='0' />

<customization name='SQL_CONVERT_TIME' value ='0' />

<customization name='SQL_CONVERT_TIMESTAMP' value='0' />

<customization name='SQL_CONVERT_TINYINT' value='0' />

<customization name='SQL_CONVERT_VARBINARY' value='0' />

<customization name='SQL_CONVERT_VARCHAR' value='0' />

<customization name='SQL_DATETIME_LITERALS' value='0' />

<customization name='SQL_DBMS_NAME' value='Informix' />

<customization name='SQL_DBMS_VER' value='12.10.0000 FC3' />

<customization name='SQL_DRIVER_ODBC_VER' value='03.51' />

<customization name='SQL_DRIVER_VER' value='  4.10.TC3 ' />

<customization name='SQL_GROUP_BY' value='10' />

<customization name='SQL_IDENTIFIER_QUOTE_CHAR' value='&quot;' />

<customization name='SQL_INSERT_STATEMENT' value='7' />

<customization name='SQL_MAX_IDENTIFIER_LEN' value='128' />

<customization name='SQL_NUMERIC_FUNCTIONS' value='0' />

   <customization name='SQL_ODBC_API_CONFORMANCE' value='1' />

<customization name='SQL_ODBC_INTERFACE_CONFORMANCE' value='1' />

<customization name='SQL_ODBC_SAG_CLI_CONFORMANCE' value='1' />

<customization name='SQL_ODBC_SQL_CONFORMANCE' value='1' />

<customization name='SQL_ODBC_VER' value='03.52.0000' />

<customization name='SQL_OJ_CAPABILITIES' value='107' />

<customization name='SQL_QUOTED_IDENTIFIER_CASE' value='2' />

<customization name='SQL_SCHEMA_TERM' value ='Owner' />

<customization name='SQL_SCHEMA_USAGE' value ='31' />

<customization name='SQL_SPECIAL_CHARACTERS' value='' />

<customization name='SQL_SQL92_DATETIME_FUNCTIONS' value='7' />

<customization name='SQL_SQL92_NUMERIC_VALUE_FUNCTIONS' value ='0' />

<customization name='SQL_SQL92_PREDICATES' value='16383' />

<customization name='SQL_SQL92_RELATIONAL_JOIN_OPERATORS' value='1023' />

<customization name='SQL_SQL92_STRING_FUNCTIONS' value='14' />

<customization name='SQL_SQL92_VALUE_EXPRESSIONS' value='1' />

<customization name='SQL_SQL_CONFORMANCE' value='1' />

<customization name='SQL_STANDARD_CLI_CONFORMANCE' value='3' />

<customization name='SQL_STRING_FUNCTIONS' value='7348061' />

<customization name='SQL_SYSTEM_FUNCTIONS' value='7' />

<customization name='SQL_TABLE_TERM' value='Table' />

<customization name='SQL_TIMEDATE_ADD_INTERVALS' value='0' />

<customization name='SQL_TIMEDATE_DIFF_INTERVALS' value='0' />

<customization name='SQL_TIMEDATE_FUNCTIONS' value='1016687' />

          <customization name='__46' value='0' />

       </customizations>     

</connection-customization>

  </connection>

  <column datatype='integer' name='[Number of Records]' role='measure' type='quantitative' user:auto-column='numrec'>

    <calculation class='tableau' formula='1' />

  </column>

  <column datatype='integer' name='[order_id]' role='dimension' type='ordinal'>

  </column>

  <column datatype='integer' name='[returns_order_id]' role='dimension' type='ordinal'>

  </column>

  <column datatype='integer' name='[row_id]' role='dimension' type='ordinal'>

  </column>

  <column datatype='string' name='[zip_code]' role='dimension' semantic-role='[ZipCode].[Name]' type='nominal'>

  </column>

  <layout dim-ordering='alphabetic' dim-percentage='0.5' measure-ordering='alphabetic' measure-percentage='0.4' show-structure='true' />

  <semantic-values>

    <semantic-value key='[Country].[Name]' value='&quot;United States&quot;' />

  </semantic-values>

</datasource>

 

 

(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!

 

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. :-)




Comments

Vote history