![External User - Tim Beard (Customer)](https://community.tableau.com/img/userprofile/default_profile_45_v2.png)
Tim Beard (Member) asked a question.
I wanted to describe a solution to a problem that seems to work. It feels pretty kludgey to me, so if anyone can suggest alternatives that might work better, I'd be very pleased to hear about them!
Background and problem:
- I'm connecting to an Oracle SQL database used for a PLM system
- The report I'm trying to create is based on joining the two largest tables in the database, namely Items and Documents
- The resulting joined output table is very wide (lots of fields) and very deep (around 20M rows and growing)
- This breaks the dashboard and even if it didn't, it would be very slow. Also an extract would be pretty huge and a duplication of much of the main db
- The purpose of the dashboard is to generate a report of a subset of Item-Document relationships based on a range of filters selected by the user
General Strategy:
- Create a prefilter dashboard that greatly reduces the number of rows returned and use this list as the basis for the main filter dashboard (within the same workbook or another) for further tuning by the user and ultimate generation of the report
- Separate data sources
- One for a prefilter with minimal fields but all rows. Extracted for performance
- Fields in the SQL query were minimised ba hiding all unused fields
- One live data source with all fields, filtered based on input from the input from the prefilter dashboard
- User not permitted to navigate to the main filter page until the count of hits is below a certain threshold
- One for a prefilter with minimal fields but all rows. Extracted for performance
Failed tactics
- Extracting the data and connecting to that. Too big. Too slow
- Filter across blended data sources filtering across data sources
- Doesn't work as this means that there is a one to many relationship for multiple fields, meaning that just a * is displayed
- Generate lists of (e.g. items) as a string in a parameter and pass that to a second workbook via a URL
- Doesn't work (a) the URL could easily be longer than the maximum URL length allowed (b) potential issues differentiating, e.g. between "123" and "123A". Need to be able to control the delimiter
Solution
- See also diagram below (sorry, can't make it any bigger in this post)
- Pass lists from prefilter dashboard to main filter dashboard as string parameters via dashboard actions, then use the CONTAINS() function to filter for the second data source
- Generating the concatenated lists:
- Based on @Sean Miller (Member) 's Workout Wednesday exercise from 2020: https://workout-wednesday.com/2020w08/
- He's the king of table calculations!
- The calculated field is slightly extended to avoid some items being listed multiple times (calculated table across):
- Based on @Sean Miller (Member) 's Workout Wednesday exercise from 2020: https://workout-wednesday.com/2020w08/
- The dashboard with the table using the above also has a second field with a similar calculation. The chart type is set to square and all headings are removed so that the remaining square + label can be used as a button
- This button is then placed on the dashboard and actions are added to it such that clicking on this button updates both parameters with the concatenated lists and navigates the user to the main filter page.
- On this page there is a simple filter of the form
- CONTAINS([p.List of Item IDs], [PART_ID]+[p.Delimiter])
Please let me know if you can think of any better solutions. Also if any of the description above is unclear.
Thank you,
Tim
So how you accomplished it. Can you please elaborate it with an example. Or do you have better solution now i know this post is an old post.