Using Business Objects 4.x to Create Tableau Reports

Tableau Workshops - miso -While Tableau has a great visualization capabilities, getting data into it from other databases is a chore unless the schema is simple.  Joining tables is easily through the GUI, but if the request requires complex filters it soon requires custom SQL. 

When this happens, Tableau is no help making it easy to code the SQL.  There are no hints or prefills like modern code programs have.  Therefore, I leverage the work of my Business Objects Universe authors.  They took the time to create the correct database joins needed to build visualization fast.  While Business Objects doesn’t produce the cleanest SQL, it will nearly always work in Tableau.

image

1. In BOXI (Business Object web version,) create the query

2. On the Query Panel control bar, click View Script

 

image

3. In the Query Script viewer, click Use custom query script

4. Highlight the SQL

5. Right-click > Copy (CTRL + C)

 

image

6. In Tableau, connect to a New Data Source

I have tested this and it works for DB2, SQL Server, and Oracle.  While it can work for Access so long as their is no spaces in table and field names, though there are ways to get around it by switching the [] brackets with double quotes.

 

image

7. Enter your database server information and ID/password.

For this to work, you will need at least Read Only access to the database in question.

 

image

8. If necessary, choose the correct Schema.

Most complex databases will have this, but products such as Access will not.

 image

9. At the bottom of the Table section, double-click New Custom SQL

 

image

10. In the Edit Custom SQL popup, right-click > Paste (CTRL + V)

 

image

The SQL should be displayed.  Now the SQL should be cleaned a bit before clicking OK. 

  • Remove anything after the WHERE or GROUP BY clauses such as FOR READ ONLY.  Most databases don’t like it.
  • If there is custom field formatting in the SELECT clause such as Date formatting remove it.  That is what Tableau is for.  If the SQL will be used in lots of workbooks, I like to give them a standard name.  This makes it easier to keep naming structures constant.
  • CASE as well as Min/Max statements work fine. 
  • If you want, clean the SQL to make it a bit more readable.  Things such as adding friendly field name helps when error checking is necessary.

11. Click OK

Hopefully there are no errors.

 

image

If there is an error click Show Details in the error box to see the problem (this examples show a missing comma after one of the variables.)  Unfortunately, Tableau only show the first error it encounters.