We have a scenario with 10M+ fact rows of data which needs to be secured at a row level to 18,000+ users. In our scenario we have high cardinality from users to fact records (each user has access to a relatively large percent of the 10M+ records). We have tried numerous ways to accomplish this but none work. It seems like Tableau is ALMOST there but when you want to work with realistic secure data sets you just can't. Is this type of scenario simply not supported/possible/feasible in Tableau?
Below are the methods we have tried and the reasons they have not worked. I have highlighted the specific issues that stopped us from using a particular method.
- Blending - We brought in just the fact table and then used blending to a data source with users.
- This was very fast
- If you add filters from the primary data source they can not be limited based on security. However if a user selects an option they do not have access to no data is displayed.
- You are unable to use filters from the blended data source (we need to allow users to filter based on a hierarchy in the user data source).
- Not really secure when you view underlying data.
- Joining Data - We joined the fact data so each fact record has a username.
- Fast (when you only have a small amount of data)
- Supports all of the features of Tableau including data source filters.
- Slow (when you have medium size data sources we found that you have to move to extract data sources).
- Does not work (when you have large data sources, which is our case). For our data source, in extract mode we end up with Billions of records. If we try to do the data source live, we have issues because everything slows down (indirect relationships between users and facts).
- Groups - We tried to use ISMEMBEROF() to limit the duplication of data in option #2 such that we could join the data. However, we found that ISMEMBEROF() only accepts string literals which made this option impossible.
- Workbook Actions Across Multiple Data Sources - We created workbooks with one data source being the user information and the other being the fact. We use the data source related to users to filter down and secure the data. Then we use actions to apply the security to other sheets.
- Data is Secured
- Performance is decent
- Filters work as expected
- Difficult/impossible to preload data in the viz (users must select filters first)
- Labor intensive to setup
Thanks for any feedback you can provide.