1

I am working from a PostgreSQL db that has several tables including Orders and Customers. I do most of my analysis in Tableau and while possible (I think), it would be MUCH easier if I could bring the data into Tableau with a few new fields already calculated.

Specifically would like to

  1. add a field to the Customers table counting how many orders that customer has made as of today(), and
  2. add a field to Orders that tells how many orders the associated customer has made as of the current row order date (essentially an 'Nth order' by customer count).

The end goal is to run analyses on new v. returning customers, and characteristics about orders based on being the customer's 1st order, 5th order, etc..

My idea was to generate the fields and write calculations to populate them before connecting with Tableau. I don't have much experience with SQL, so trying to understand what my options are to achieve this. Any suggestions?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
eb moore
  • 41
  • 1
  • 4
  • Can you post your database schema? – tsnorri Aug 26 '14 at 23:33
  • Answer is yes, but you will have to give more details for a full solution. Are you currently just bringing in the table, or is an existing query you are looking to modify? If existing...we need the existing sql statements. If by table, you table schema is needed – Twelfth Aug 26 '14 at 23:40
  • This question would be a lot more useful with more information: table layout, version of Postgres, example query. – Erwin Brandstetter Aug 27 '14 at 03:27
  • your words: *'it would be MUCH easier if I could bring the data into Tableau with a few new fields already calculated.'* That is true, because this people use [ETL](http://en.wikipedia.org/wiki/Extract,_transform,_load) tools. Another approach is to write SQL Query in tableau instead a table. – dani herrera Aug 27 '14 at 14:03
  • Thanks for the responses -- the solution I found temporarily is to write an additional custom SQL query while connecting the db to Tableau. That got me most of the way there, but only works the first time I connect (opening a workbook with the saved query throws me an ODBC escape convert error). – eb moore Sep 02 '14 at 19:43
  • I ended up asking this question separately with more detail here: – eb moore Sep 02 '14 at 19:44
  • http://stackoverflow.com/questions/25577192/nested-select-using-count-to-tally-each-row-in-postgres Hoping that I can write this into the custom SQL, since it seems like writing separate columns to the postgres tables is not advised. – eb moore Sep 02 '14 at 19:45

2 Answers2

2

Unless performance is your paramount requirement and those calculations are expensive, do not add columns to store redundant information.

Even if it's all about performance, MATERIALIZED VIEW are often the superior approach, so you can separate actual information from redundant, functionally dependent work tables.

In all other cases, you would rather use a VIEW, which computes derived columns on the fly. This is a standard feature of SQL and often faster than newcomers might think.

Or you could emulate "computed fields" with a function. For special purposes ..

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

Tableau offers one other option to consider besides creating a view or possibly materialized view

You can define table calculations which in effect post-process the aggregated query results returned from your database. Table calculations can become very sophisticated, and also a bit complicated, but they offer some flexibility beyond what you can do with a materialized view.

For instance, if you want your calculations to factor in the filters in your Tableau visualization, such as a date range or restricting to a certain class of order, then a table calc can dynamically take those filters into account. Or if you wish to rearrange your visualization frequently, grouping by different dimensions, then a table calc may be more convenient than a view.

For those unfamiliar with Tableau, table calcs are similar to SQL windowing or analytic queries, but performed by the client. Table calcs are useful, but take some time to learn.

Here are a few places to start

http://www.tableausoftware.com/table-calculations http://www.tableausoftware.com/sites/default/files/pages/table_calcs_in_tableau_6.pdf

Alex Blakemore
  • 11,301
  • 2
  • 26
  • 49