1 / 38

Desktop Tricks

Desktop Tricks. TCC 2013 . Agenda. Double-counting measures? Table calcs misbehaving? Order of operations strange? Can’t find your extract file? Sorting giving you a head-ache? Updating fields mixing up your report? Confused about blending? Filtering on measures?

inez
Download Presentation

Desktop Tricks

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Desktop Tricks TCC 2013

  2. Agenda Double-counting measures? Table calcs misbehaving? Order of operations strange? Can’t find your extract file? Sorting giving you a head-ache? Updating fields mixing up your report? Confused about blending? Filtering on measures? Understanding row-level security? Permissions got you down? Returning too many results? Are your dashboards ugly? Trouble replicating Excel-type formatting?

  3. Double-counting measures? Problem: If the base query requires GROUP BY on ONE table but not the other (different grains), you may have double counting. Solution: model joins in custom SQL or a database view. Put your own – essential – GROUP BY clauses in view. Why: Tableau assumes that the base query requires no GROUP BY to return the right results

  4. Conforming grain What blending does… Facts (grain 1) Facts (grain 2) X Better Facts (grain 1)

  5. Table calcs misbehaving? Remember that table calcs are WINDOW functions that allow you to tweak the size and position of each window based on partition (boundary) and direction. Practice/model table-calcs in a table view before converting to a chart Partition/Reset every window Across/Addressing 2 2 3 2 2 7 2 3 1 2 3 2 8 7 2 3 2 2 3 2 8 2 2 3

  6. The addressing/partition fields determine the scope of our computation • The addressing fields define what part of the table you are computing along. (Compute using) • The partitioning fields define how to group the calculation. (Available fields) • You can specify the addressing in the Table Calculation dialog box. • The addressing can be relative to the table structure or a specific field. Addressing and Partition Field

  7. Multilevel Calculations Interactivity Driven Calculations Dynamic Hide YoY Growths Ranking Functions Compounding Calculations • Characteristics: • Post Query • Same Stage as Blending • Pre-Rendering Table Calculation Architecture % of Totals based on Filters % of Total of Top 3 Customers

  8. Can’t find your extract file? • After saving workbook as TWBX, extracts “disappear” • Look in: • <save location>\<workbook name> Files\<datasource name>.tde

  9. Can’t find your extract file? • To control where TDE is saved: • Remove extract including file • Save workbook as TWB • Extract again, specifying location of TDE file • Save as TWBX

  10. Sorting giving you a head-ache? • There is no multi-column sort dialog in v8 (this IS a targeted enhancement for future releases). But you can work-around: • Create a “combined” field (or a calculated field that concatenates). • Place combined field on row/column shelf, sort and then hide. This is a priority development focus (finally). Stay tuned.

  11. Sorting: combined fields Before… After…

  12. Updating fields mixing up your report? • Dimensions could be discrete or continuous. • Discrete fields will always be organized before continuous – potentially causing havoc when you drag-drop a new element to a report. • Consider converting continuous to discrete (and vice versa)

  13. Confused about blending

  14. Blending: things to remember • Blends are not joins • Prepare for secondary (blended) data to return fully before correlating/joining • The secondary datasource should be at the same or higher grain than primary, to prevent “*” • Blending performance (and system stability) can be improved by: • Adding filters to the secondary datasource

  15. Understanding row-level security? • Use datasource filters • Like common filters, but applied to the datasource object directly. • Useful session security expressions can bind rules to user identities. • Saved with the datasource • Can be published with the datasource to the data server

  16. Group/Role vs. User Filtering Superstore Tables 1:0 or many Restrict to regions user manages: [Users].[Manager]=Username() Tableau wants to see a STRING literal here (not a dynamic variable) THIS WILL NOT WORK: IsMemberOf ([Users].[Region])

  17. Dynamic group filtering does NOT work Does not work! Works!

  18. User based permissions <NAMEFIELD>=USERNAME() for dynamic security

  19. View-level filter test

  20. Session security expressions • USERNAME() • Username of logged in user, otherwise Windows username. Authors may impersonate other server users. • ISMEMBEROF(<string literal>) • FULLNAME() • USERDOMAIN() • Tableau server domain or windows domain • ISFULLNAME(< string literal>) • ISUSERNAME(< string literal>)

  21. Use data server to “bake” security into datasources

  22. Filtering on measures? You can’t drag a measure into the filter shelf. But you can create a calculated field based on a numeric test, that returns TRUE/FALSE Then, drag the calculated field into the filter shelf or use it in a data source filter.

  23. Permissions got you down? Permissions are copied from project folders as defaults when you publish to a folder Project permissions changes do not automatically cascade from projects to reports: must assign to contents Permissions giving users different permissions for different views (more fine-grained than workbook permissions) cannot be set during publishing; they must be set after from the web-portal

  24. Assign project permissions to contents

  25. Order of operations strange? • Use context filters to force pre-filtering • Compare results with a date filter before and after placing the filter “In Context” • Temp files can be slow. Edit datasource XML to disable temp file creation*unsupported • Check the Tableau log for sure… <customization name='CAP_CREATE_TEMP_TABLES' value='no' /> <customization name='CAP_SELECT_INTO' value='no' />

  26. Order of operations 5. Table Calculations 4. Filter Shelf (no context) Top N on dim 3. Context Filters 2. Datasource Filters Top N with sets Pushed to database 1. Custom SQL, DB view, SPs Top N or LIMIT in SQL

  27. Too many rows? Limits and Top N can skew results do to order of operation issues.

  28. When/where is Top N being called? # = order of evaluation. Are we filtering on a pre-limited subset??? 5. Where you would want a LIMIT function (if other filters) Where you can add one easily 4. Filter Shelf (no context) Top N on dim 3. Context Filters 2. Datasource Filters Top N with sets 1. Custom SQL, DB view, SPs Top N or LIMIT in SQL

  29. Top N as a governor: pick 2

  30. Too many rows? • Tableau does NOT have a global/datasource configuration parameters that adds LIMIT expressions to OUTER query sql. • Initial SQL support is increasing (added Aster with 8.1) • So… explore database governance options and encourage smart use of summarization and domain filters. • Use indicators if you do truncate.

  31. Governance indicator Create calculated field that triggers when limit is reached

  32. Governance View

  33. Indicator with record Count < or > 1,000

  34. Are your dashboards ugly? Document a style sheet and make templates! Specify logo files, fonts (titles/subtitles), layout conventions, shading. Fix page size. Use tile (not float) layout. Be consistent.

  35. Trouble replicating Excel-type formatting? Use conditional formatting technique. Start with 2 calculated fields with 1 or 0 inside. Add to column shelf, mark dual axis. Assign text/shape as necessary, remove tick marks etc..

  36. R integration (8.1) (sign up for beta next week!) • Multiple columns in • R-calculated output variable correlated Example below shows k-means clustering with R. Calculation1 passes the two columns to be used in the analysis to R and number of clusters (3) and assigns the output to a variable named result, then retrieves cluster assignments. It is a vector with same length as input rows as a result; output values are automatically shown in the new column named Calculation1. User can drag Calculation1 to color shelf and visualize the clusters in the data just like using a native Tableau calculation. Calculation2 references the variable created in Calculation1 as a result is able to get the total sum of squares value without having to rerun the calculation. Since this parameter is a single value Tableau automatically repeats it for all the rows. Since Calculation2 references Calculation1, Tableau always executes Calculation1 before Calculation2. Since there are 3 clusters, $cluster returns 3 values which are the centroids of the cluster. Since it is neither a single value nor it matches the number of rows in data Tableau sent to R this can’t be automatically consumed by Tableau. However flexibility of being able to put arbitrary R code RAWSCRIPT function allows user to transform the data (by doing a left outer join with the cluster assignments) into a shape Tableau can understand.

  37. v8.1 v8.1 ETL & Tableau Statistics Coverage v8.1 With 8.1

  38. Questions

More Related