I could connect to the iSeries, but each connection could only be to a single table. The multiple tables option never worked and the custom SQL option would throw an error after I clicked [OK]. I attempted to get around it by creating multiple Single Table connections, but then Tableau complained about doing relationships (joins) on the same data source. Plus, it was incredibly slow.
I haven't experienced any errors by using a linked server and OpenQuery. After making my query a little more efficient I have been able to get my dashboard load times down from 15 seconds (which I considered acceptable for the amount of data) to under two seconds.
The ease of ODBC would be preferable, but using SQL Server to access the goldmine in the iSeries appears to be my best option, for now.
Thanks for the info regarding linked server and OpenQuery. I'm trying to now to see how this performs against connecting via ODBC.
Could you give me a hand with the OPENQUERY sql statement?
FROM OPENQUERY(LinkedServerName, 'SELECT * FROM LinkedServerName.Database.Schema.TableName')
Happy to help.
I'll start from the beginning or close to it. I'm going to assume that you have created the Linked Server on your SQL Server. Now in Tableau select the Microsoft SQL Server database connection. In the drop down select the SQL Server and enter your credentials, then Connect. I select any of the databases on that server. It doesn't seem to matter. Then Custom SQL.
A basic statement is: SELECT * FROM OpenQuery([LinkedServerName], 'SELECT * FROM Schema.TableName')
Something that I've learned, if you're going to do any data manipulation or filtering (JOIN, WHERE, SUM, GROUP BY, etc), do them in the OpenQuery. It's much quicker. Otherwise you're pulling all of the data into the SQL Server before anything gets filtered and that can take a while.
Here's one that I wrote today:
SELECT * FROM
'SELECT SUM(YRMO01) AS TRMO01, SUM(YRMO02) AS TRMO02, SUM(YRMO03) AS TRMO03, SUM(YRMO04) AS TRMO04, SUM(YRMO05) AS TRMO05, SUM(YRMO06) AS TRMO06, SUM(YRMO07) AS TRMO07, SUM(YRMO08) AS TRMO08, SUM(YRMO09) AS TRMO09, SUM(YRMO10) AS TRMO10, SUM(YRMO11) AS TRMO11, SUM(YRMO12) AS TRMO12, YRTER1, YRYEAR, AVCODE, SPSN
FROM XXXXXX.SAM501P AS T01
JOIN XXXXXX.AVCATS AS T03 ON T01.YRCSCL = T03.AVCCCD
JOIN XXXXXX.TERRCRN AS T02 ON T01.YRTER1 = T02.TERR AND T03.AVCODE = T02.ACCODE
WHERE T01.YRYEAR IN (''2012'', ''2013'') AND T03.AVCODE IN (''A'', ''C'')
GROUP BY T01.YRTER1, T02.SPSN, T03.AVCODE, T01.YRYEAR
I came across the following tableau discussion thread and wanted to share this with you. Using the info in the discussion thread allowed me to connect multiple tables and custom SQL using ODBC.
Your Microsoft solution works great too, just wanted to share that ODBC is also an option now.