1 of 1 people found this helpful
If you are using and extract, you can optimize the extract, which can do the equivalent of paste special, depending on the calculation (typically not a table calc or an aggregated calculation)
I was just about to suggest the data extract and noticed that Alex beat me to it!
What is your data source? Because another possibility that may work is to do the calculation ahead of time and store it at the data source level.
Hi Joshua & Alex,
Thanks for your inputs. Could you please let me know about any links or help documents for optimizing data extract?
My data source is Excel. I have used a lengthy calculation which is not possible to run ahead in Excel and store at data source level.
As of now I am doing the following as a workaround for this:
1) Export the calcuated values to a new Excel sheet
2) Integrate this with the original data source (Excel File that has raw input data)
3) Then refreshing the data extract in Tableau
Though this works it takes a lot of time as Excel takes a lot of time to process 150000 records.
Please let me know your thoughts on this.
Is your data already extracted? If not, then click on the "Extract Data" from the Data menu as shown below. Then to optimize, you can select "Optimize" as shown. If your data is already exracted, I would not expect that a row-level calculation to impact performance as you describe. Is it possible that the calculation is done at an aggregate level (does it include SUM, COUNT, AVG, or similar)?
Your approach sounds like it works, but I think I'm missing something in my understanding. A couple of questions:
1. What kind of calculation can be done in Tableau that cannot be done in Excel, but can be re-integrated back into the Excel data? Perhaps you can post the calculation?
2. Why does Excel have a hard time processing 150,000 records if it's not doing the calculation?
Also, a couple of notes on calculations and efficiency:
1. Avoid string manipulation if possible. Numeric calculations are always faster. Aliases and formatting can be done to display numeric values as readable text in the view.
2. Row level calculations can be performed once and have results cached or stored in an extract. They aren't always the correct calculation to use -- so make sure you understand whether the calculation should be done at a row level or aggregate level.
3. Aggregate calculations will have to be calculated every time you adjust the view in a way that changes the level of aggregation (though Tableau will cache the results as much as possible).
4. Version 8 (due for release this month) significantly optimizes calculations, especially the way strings are handled. So that might actually be the answer for you (new versions fix everything, right? )
I tried optimizing the extract and now the performance has increased multiple times. Thanks a lot for the help.
Actually I am trying to classify around 150000 records into 10 pre-defined categories based on specific keyword searches on 3 columns (Remarks, Comments & Description) of the database. The columns contains sentences typed in the by the user at the time of record creation.
I am using the function 'Find' and nested if statements (50 in number) to perform this. As I understand, this calculation involves string operations and it is of row level and hence the performance has come down. However after optimizing the extract it works fine.
Also I am eagerly awaiting the release of Tableau 8 to explore the advanced features of it.
Also are there any data mining techniques and statistical representations like probability distributions, regression analysis e.t.c present in Tableau?
There are definitely statistical trend models, confidence intervals, standard deviation, etc... all built in to Tableau. Version 8 will introduce some forecasting models. I would definitely encourage you to search out the knowledge base articles on Tableau's website and also ask any specific questions on the forums!