Hey TK, that's not the question asked. The question is about where calculations are stored (i.e. saved), not sorted.
Those Calculation are stored at Tableau Level. We cant Create any New
Calculations in the DB using Tableau.
When we run the Report with New Calculations, those will run over the DB &
will get the data to Presentation Layer. (Tableau Desktop)
On Wed, Mar 4, 2015 at 11:17 PM, Toby Erkson <
Thanks Kumar, by Tableau level do you mean Workbook level? What about if the datasource is an extract?
Calculated fields are always saved in the workbook and you can see them in the .twb files. Hope this helps
thanks, so even with extracts the calc fields are stored only in the workbooks.
Yes. With the extract, your workbook will point to the data extract and everything should remain the same.
Just curious, is there anything you have noticed with extracts?
No just wanting to double check. thankyou
Hi ali twaij,
Reporting Tools Will interact with Metadata of the Database, Calculated Columns which are created at front end ( in Reporting Tool) will save at front end level only.
And We neither insert new Data nor update data using Reporting Tools.
I'm going to ping Jonathan Drummey, as I think he may be able to clear up any confusion here. I believe .tds files store calculated fields, etc. but I'm not sure what impact this has -- or more importantly, why ali twaij is asking the question.
Jonathan's blog post on Extracts/Data Connections may be useful here as well:
If you do calculation after the extract then its on workbook, if you have calculations and then doing extract, calculation are performed at database level.
5 of 5 people found this helpful
From my own point of view having looked at what's in sources, twb files, tds files, etc. along with a lot of queries all of these answers have grains of truth, but not a more complete picture. Here's my summary:
- Tableau doesn't write data back to the original data source table(s), one of Tableau's selling points is that it's read-only when it comes to our data. The caveat to this is that when permissions exist Tableau will sometimes generate temp tables in the original data source.
- The calculations themselves are stored as metadata in your workbook, and can be stored separately from the workbook as a .tds or .tdsx file. Tableau Server Published data sources add another wrinkle in that they can have calculated fields as part of the data source, then the workbooks using the published data source can have additional calculated fields.
- Sometimes people new to Tableau will want to pull down the Tableau SQL so they can evaluate it, that's a mental model based on how traditional tabular reporting and some BI tools work where the SQL is essentially fixed by the developer other than some runtime parameters like start & end dates. Tableau dynamically generates the SQL on the fly for each view (based on the pills on the shelves and other settings) so what gets stored in each calculated field is the field formulas.
- Most record-level and regular aggregate (SUM, MIN, MAX, MEDIAN, etc.) calculations are computed in the data source. Where this gets complicated is with cross data source joins and data blends, in those cases Tableau will do as much computation as possible in each connection or source, respectively, and then as necessary complete the computation internally. For example in a Tableau data blend when there are linking dimension(s) that are not in the view Tableau will query the primary data source at a finer grain defined by the linking dimensions plus the dimensions in the viz Level of Detail and then re-compute the aggregations to the viz Level of Detail.
- Forecasting, trend lines, most table calculations, most reference lines, and some/most grand totals & subtotals are computed in Tableau. The TOTAL() table calculation function, Reference Lines using Total, and some grand totals and subtotals will sometimes be pushed to the data source (as an additional query, even) based on the type of aggregation being performed and the level of detail of the calculation vs. the viz Level of Detail. The most common case where this happens is with the non-additive aggregations like MEDIAN, COUNTD, and PERCENTILE where to compute an accurate result we need a separate aggregation at the correct level of detail.
- When Tableau creates or optimizes a Tableau data extract, record-level calculated fields that don't depend on parameters or runtime functions like DATE(), NOW(), USERNAME(), or ISMEMBEROF() will be materialized (i.e. create new columns in) the extract. So in this case Tableau will write out calculated fields into the data source (extract).
- My own mental model for Tableau data extracts is that I have two data sources: one is the live source and one is the extract derived from the live source as well as some metadata (e.g. record-level calculated fields that can be materialized and extract filters), and I can swap between them with a couple of mouse clicks.
- One thing to pay attention to given that there are effectively two different data sources (live and extract), when using an extract we can run into a situation where calculated field returns one value when computed on the live source (including when creating/optimizing/refreshing/appending to the extract) and a different value when computed on the extract. The difference that I run into the most often is that there are different epoch dates for Excel files and Tableau data extracts. Also not all functions are available in all data sources, generally Tableau data extracts have the most functions available. This makes it possible to build worksheets that only work on a data extract and will break (have red pills and fail to update) when using a live connection.
- When using Tableau Server Published data sources we can also have two or three "sources" (the original source, a Tableau data extract if that's been used, and the instance of the published data source in our workbook). This creates some edge cases where certain features might not be available and/or opportunities for bugs in Tableau where a calculation works as desired when used within a direct connection or local extract but doesn't when used in a published data source. Tableau has done a lot over time to reduce the number of edge cases, the main one that I know of as of May 2017 is that we can't use published data sources in cross database joins.
And there's lots more detail in my post that Matt linked to.
[Edited 20170517 to update for cross data source joins and add more clarity around calculations and published data sources]
thanks guys. Excellent