Tag Archives: performance

Row Level Security Using Tableau 10.0 Cross Data Source Filters by Jamieson Christian

Jonathan here. Serendipity is a wonderful thing! I was briefly on the Tableau community forums last week and found this thread where Jamieson Christian described a new method for user- row-level security in Tableau that I hadn’t seen before, and it’s so awesomely cool I asked him if he could blog about it, and he accepted my invitation! I’ve already implemented this method at a client and it brought a dashboard from unacceptable refresh times to nearly instantaneous. So read on for Jamieson’s description.

Row level security often presents challenges, if your security data is not in the same data source as your main data. In such cases, solutions tend to leverage one of four techniques:

  1. Data prep outside of Tableau – often involves cross products that can make data sets unacceptably large.
  2. Passing parameter-based filter through Tableau’s Javascript API – requires using a portal that is not always feasible for organizations, also won’t work with Tableau Mobile.
  3. Data blending — just about the only in-Tableau option prior to Tableau 10.0.
  4. Cross data source joins — available in Tableau as of Tableau 10.0.

This post introduces a 5th option using a cross data source filter. see below for how to set it up!

Continue reading

Performance

Instructions for Tableau v8.1 and earlier

Open your workbook and display the view with lots of data so it takes a long time and then immediately open the log.txt file the Logs directory under your Tableau repository with a text editor (i.e. without even closing Tableau), go to the very end of the file and then search backwards for “Updating sheet ‘sheetname’ for View ‘viewname”. Copy everything from that point to the end and save it as another text file and attach that to this thread I can have a quick look and see if I can see where the time is going.

Multiple Tables can be up to 30% faster than Custom SQL:
http://tableaulove.tumblr.com/post/20781994395

Think about how much data Tableau has to pull over the network. E.g. SUM(foo) is one value, while SUM(foo) for every visit is a lot more work.

Users can zoom with dates and other hierarchies – use these to avoid quick filters

Reduce level of detail – LOD makes Tableau work for every row. Instead, use dashboards & actions (Action filters) to give detail and allow Tableau to make a smaller query & speed things up. Can use “Exclude All Values” on the target detail sheet so it doesn’t show any data when nothing is selected (like a tooltip doesn’t show until a mark is hovered)

Move more calculations outside of Tableau

For wide data sets, use context filters to speed things up

More on context filters:

  • Using a single context filter that significantly reduces the size of the data set is much better than applying many context filters. In fact, if a filter does not reduce the size of the data set by one-tenth or more, it is actually worse to add it to the context because of the performance cost of computing the context.
  • If you do have multiple filters to add to the context it is better to create all of the filters first and then create a context that includes them all. To create a context that includes them all, select Analysis > Set Context and then add the multiple filters to the context all at once. Using the standard Add to Context command in the context menus of each filter will force Tableau to compute the context once per filter which can degrade performance.
  • Complete all of your data modeling before creating a context. Changes in the data model such as converting dimensions to measures, require recomputing the context.
  • Set the necessary filters for the context and create the context before adding fields to other shelves. Doing this work first makes the queries that are run when you drop fields on other shelves much faster.
  • If you want to set a context filter on a date you can use a continuous date. However, using date bins like YEAR(date) or context filters on discrete dates are very effective.

Tableau Basic Performance Tips whitepaper

http://community.tableausoftware.com/docs/DOC-1041

The Information Lab post on performance tips:

http://www.theinformationlab.co.uk/2012/11/19/why-is-my-tableau-viz-slow/

Tool to Evaluate what data comes from where

http://community.tableausoftware.com/message/180515#180515

Multipass aggregations (white paper)

 

Using Tableau Server, then tabcmd

http://community.tableausoftware.com/thread/118600

Performance Tips

http://onlinehelp.tableausoftware.com/v7.0/pro/online/en-us/performance_tips.html

Improving Database Query Performance

http://kb.tableausoftware.com/articles/knowledgebase/database-query-performance

Authoring Views for Better Tableau Server Performance

http://www.tableausoftware.com/learn/tutorials/on-demand/tableau-server-authoring-performance

Using Context Filters to Improve Performance

http://kb.tableausoftware.com/articles/knowledgebase/using-context-filters-improve-performance

Dashboard Best Practices

http://kb.tableausoftware.com/articles/knowledgebase/best-practices-designing-vizes-and-dashboards

Optimizing Tableau Server Performance

http://kb.tableausoftware.com/articles/knowledgebase/optimizing-tableau-server-performance

Authoring Efficient Calculations presentation from TCC2012

http://community.tableausoftware.com/docs/DOC-1243

Some notes from that presentation:

Convert Unix timestamps (1 billion values in 45 seconds):

DATE( DATEADD( 'seconds', INT([unix] / 1000), #1970-01-01# ) )

Convert YYYYMMDD dates (1 billion values in 64 seconds):

DATEADD( 'day', [yyyymmdd] % 100 - 1, 
    DATEADD( 'month', INT( ( [yyyymmdd] % 10000 ) / 100 ) - 1, 
       DATEADD( 'year', INT( [yyyymmdd] / 10000 ) - 1900, 
          #1900-01-01# ) ) )

Using Math instead of IF/THEN statements to get week # in quarter:

INT( [DayOfQuarter] /7 ) + 1

Then instead of using aliases or string formatting, use number format “Week #”0

Grouping by numbers only compares numbers, 10-100x faster than strings. Aliases & formatting are applied after the query.

Combined fields (Create Set in v7) are 90x faster than a BYO combined field like [string1] + ” – ” [string2]

From Making the Most of Calculations – Ross Bunker talk at TCC13

  • – reduce the number of rows
  • – reduce cost per row (write more efficient calcs)
  • “viz level calcs” – aggregates inside Tableau can be faster. e.g. MIN(UPPER([Product])) is way slower than UPPER(MIN([Product])) (When Product is in the level of detail in the view)
  • CASE satements are faster than IF, when using IF minimize comparisons (especially string comparisons)
  • Use SIGN([Profit]) instead of IF([Profit] >0)

Other useful functions:

  • CONTAINS, STARTSWITH, UPPER, LOWER, ROUND, ABS, LOG (determines # of digits in a number), MIN/MAX (can take two arguments)
  • INT(LOG(MAX(ABS([Value]),1))) + 1 is 4x faster than LEN(STR([Value]))

table calcs

  • WINDOW_ can cause n*n calcs
  • RUNNING_ can be faster

(don’t forget the PREVIOUS_VALUE and IF FIRST()==0 tricks to speed up WINDOW_ calcs)

Dealing with out of memory issues on Tableau Server:
http://community.tableausoftware.com/message/222443#222443

Related posts: