I hope this will be useful to someone and that someone also can take what I have achieved to another level. I have managed to reach to the end of the rainbow, what is the pot containing not gold but Sage data.
This is not a complete solution but a reference for someone to use to get an idea, expand, test and plan with if they want to do analytic s with Sage 50 Accounts (UK), driver v20. If you are not comfortable in creating custom connections then don't try it.
There are many solutions and methods out there to be able to query sage data via Tableau. I have tried
1. SSIS packages directly via ODBC but failed as it does not support SQLSetConnectAttr
2. Costly ETL software which some managed to connect and some did not. They spit out a CSV or write directly to MSSQL. These were time consuming and required alot of code or setting up.
3. Using Powershell to refresh data connections within Excel and a SSIS package to pick up.
So I went back to basics. One thing I do know is that the Sage driver is only verified and tested to work in Excel. If you have some software packages which can connect to it, then you are lucky (ish) as I'm sure you noticed issues.
Tableau users in the forum have always recommended having a go at building your own custom connections here Customizing and Tuning ODBC Connections | Tableau Software..
I have had a go and through trial and error managed to get something that works enough for me. What I have learned is trying to connect to Sage via Tableau's generic ODBC is that it crashes. Why does it crash? It is because the driver does not support metadata. In fact it does not support alot of things Tableau wants to do using sql.
I achieved making a connection within Tableau BUT I cannot interrogate the data. What I do is straight away is create a Tableau Data Extract. Once this is done I can start doing the fluffly-colourful stuff and refresh the extract when needed.
The key was to create a custom TDS file with a couple of key customisations which enabled me to connect. You will need to adjust some of the script for your Sage product. The best way to do this, is to follow what the knowledge base article says. Try to make a fresh and direct connection and when it fails, check the logs to get specific details, such as the driver name etc.
So here it is.
1. Install the Sage ODBC driver on your desktop if the application is elsewhere. Makes sure you map the file path to the relevant folder where the data tables are held. Do the same if you are using Tableau server on the server.
2. Create a custom TDS file using notepad with the following script. Change what is necessary for such as driver details, data directory, username/password, table you want to connect to. The key customisation name must be in the script!
<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='genericodbc.Sage' inline='true' version='8.2' xmlns:user='http://www.tableausoftware.com/xml/user' >
<!-- build 8100.13.0808.2000 -->
<connection class='genericodbc' odbc-connect-string-extras=';DIR=\\YOURSERVER\Company.000\ACCDATA' odbc-dbms-name='Sterling' odbc-driver='Sage Line 50 v20' odbc-dsn='' odbc-native-protocol='yes' odbc-use-connection-pooling='' password='' port='' server='' this='08da4e80' username='Manager' >
<relation name='AUDIT_HEADER' table='[AUDIT_HEADER]' type='table' />
<connection-customization class='genericodbc' enabled='true' version='8.2'>
<vendor name='Sage (UK) Limited'/>
<driver name='Sage Line 50 v20' />
<customization name='CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY' value='yes' />
<customization name='CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY' value='yes' />
<customization name='CAP_SKIP_CONNECT_VALIDATION' value='yes' />
<customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='yes' />
3. Save the connection file in your datasources folder within the tableau repository.
4. Launch Tableau and click on the saved data source to launch. If it all goes well you should see the message from Tableau data the data source has limitations! If it crashes tableau then you need to fix your script.
5. Press okay and voila all your measure and dimensions on the side. Now! Hold on - don't try to make something as it will not work. You will see below in red - that the driver's limitations won't enable to Tableau to query it when you drag pills on to the stage.
6. Right click on the data source and create a data extract and save it. Tableau should be able to query all the rows and insert them into the extract.
7. Voila you can now go and build Sage 50 reports using the extract. Refresh the extract when necessary.
That is it. Please post if you manage to make any improvements!