When you say "The source is generated by a sql query from the source system" do you mean that the connection is via custom SQL?
If so, is there a compelling reason for this, or is it because that's the habit, it's the way it's done in other tools, etc?
Up front it's worth mentioning that Tableau was designed to faithfully represent the data it's being asked to access, not as a tool to change existing data.
That said, the simple approach would be to have a set of adjustment data that can be used to offset the problematic source data.
In this case, a mirror transaction that adjusts the original transaction in all aspects except that the value to be adjusted is made negative by the necessary amount is added.
The adjustment data is unioned to the original source data, with the result that any time the transaction is included in an analysis it picks up both the original and adjustment amounts, resulting in the 'correct' amount showing up in the final output.
It would be helpful for the adjustment data set to include an additional field containing a flag value identifying those rows as adjustment rows. This would make it straightforward to identify the adjustments made to the source data, which is going to be of interest.
To answer your question, we are using custom sql to query the source data base (POS system). This is refreshed daily with yesterdays transactions (incremental refresh).
Thanks for the recommendation.
Al, the point of Chris' question is more "why are you doing that"? Custom SQL is useful but has limitations and can cause performance issues.
Is there a reason you are doing that vs. a Tableau extract plus scheduled incremental refresh (for example)?
I may not be tracking, but here are the steps we are taking today:
- Use custom sql in tableau desktop to create extract
- Upload exctract with incremental refresh to tableau server
- Configure incremental refresh schedule on tableau server
Is there a more efficient method that I'm not considering?
Getting there! What is the reason for using Custom SQL? Are you doing complex joins, or unions? Are you only returning a subset of columns from the source data? Are you aggregating/summarizing or otherwise manipulating the data before you bring it into Tableau?
Custom SQL can cause performance issues, and can be inefficient in the way it causes Tableau to query the source database. We're always wary of using it in production vs. creating a view (for instance) in the source data you can directly attach to.
This may work just fine for your purposes - there's nothing wrong with it conceptually. You just have to be aware of the potential drawbacks.
Thanks for your help on this one Michael. We are using custom sql as a temporary solution while we build out data warehouse / BI stack.
Ah, that makes perfect sense! In fact, it's my favorite use for custom SQL.
I'll shut up now...
To try and add a little explanation and perhaps some clarity...
My experience is that people tend to use custom SQL because that's how they've connected to data with other tools.
My position is that custom SQL should only be used rather than establishing a direct Tableau connection when custom SQL offers some real benefits, i.e. accomplishes some functionality that Tableau doesn't provide.
I think that custom SQL is vastly over-used, to no good purpose, and some detriment.
One drawback of the reflexive use of custom SQL is that it impedes the experience that leads to effective and appropriate use of Tableau.
Please don't take this as a criticism of your use of custom SQL, Al. I don't know enough about your situation to have an opinion.