MY OBJECTIVE/DASHBOARD SUMMARY: reduce the rendering time of this map dashboard that accepts two user-supplied values. The dashboard allows a user to specify a 'site' and see how many 'providers' fall within a distance radius specified by the user.
Each 'site' and 'provider' have a lat/long. A calculated field calculates the point to point distance between the 'site' and 'provider'. The user-specified distance value is compared to this value for the filtering.
DATA STRUCTURE: Ostensibly, the dataset is a Cross Join/Cartesian product of my 'provider' and 'site' lists. In other words, there is a record for every possible combination of 'site' and 'provider. The resulting dataset is about 8MB/150k records and results in a slow rendering time. This dataset is a little smaller than the real one. 'Sites' can be distinguished from 'providers' because they have a 'program ID' = 0.
There ends up being a lot of uncessary data in here because a user is only looking at one 'site' at a time, and most likely there will only be <30 'programs' that fall within their specified distance. I was able to achieve a fast rendering in our local environment by connecting the dashboard to a database, instead of an Excel file, and using a paramterized Cross Join query to reduce the dashboard's data set to only the records for the specified 'site'. But I cannot post to Tableau Public/create a packaged workbook with the Database connection. That pseudo query looks like:
SELECT s.SITE, p.*
FROM SITE as S CROSS JOIN PROVIDERS as P
WHERE s.SITE = [p_siteID]
My hope for this problem is that there is a way to connect the Dashboard to two separate Excel files - one containing my site list and one containing my provider list - and that I could achieve a result similar to my set-up with the direct database connection (i.e., a paramterized cross join between the two Excel sheets)
Thoughts on how I may achieve this? Is there a totally different solution I should be pursuing for improving this dashboard?
EDIT: Added Tableau Public viz
v4.1_packaged.twbx 6.6 MB