8 Replies Latest reply on Apr 12, 2013 11:34 PM by Tamas Foldi

    How to use row level security in filters itself?

    Tamas Foldi

      I have a quite big (6-10 billions of rows) fact table and several lookup tables. I am using row level security, thus, my users can see their own data without any problem (this is a simple USERNAME = [column] filtering). The problem is, that I would use only quick filters where the users cannot see each others data. I see two ways to achieve this:

       

      1) use show relevant values. It works, as the quick filter values will be selected after joining the lookup table with the fact tables. But as we have 5-10 quick filters (and it is an another story that these quick filters cannot fit in tableau's non-scrolleable filter pane) the huge table will be joined several times with the lookups causing a huge and innecessary performance degradations.

       

      2) use "show all database" values. Here I have a big security problem, as even if I define USERNAME filters on the lookup tables, tableau does not enforce it. Performance is OK, but the users see each others values.

       

       

      My questions is, how to create quickfilters which are fast and secure?

        • 1. Re: How to use row level security in filters itself?
          Russell Christopher

          Hello Again!

           

          In order to get the secured cascading/relevant filter behavior you're looking for, I can't see any other way to approach this except use mechanism #1 and accept the accompanying performance hit one takes when using the "Relevant" option.

           

          Here are a couple of ideas...don't know how worthwhile they'll be:

           

          • Have you added your UserName() = [Column] calculated field/filter into Context? We do this automatically with User Filters created via the UI, but not with calculated fields - This should help you a little
          • I wonder if creating an additional User Filter via the UI might help you here?  Filter dimension values using the User Filter while filtering measure values with your calculated field.
          • Re-shape your data so that there are fewer joins to traverse - flatten the data model so the data system's response is faster.

           

          Sorry I can't be more helpful. Hopefully someone else will have other ideas...would love to know what happens with this - very interesting.

          1 of 1 people found this helpful
          • 2. Re: How to use row level security in filters itself?
            Tamas Foldi
            • Re-shape your data so that there are fewer joins to traverse - flatten the data model so the data system's response is faster.

            This is not necessarily true: if you have a huge fact table and several small lookup tables then it is much more faster to get the filter values (distinct values from lookup tables, with practically no cost) than to scan fact table for each quick filtered columns. Also, in our case we use the lookup tables also for internationalization so it is not feasible to store all dimensional values in 10+ languages directly in the fact table.

             

             

            • Have you added your UserName() = [Column] calculated field/filter into Context? We do this automatically with User Filters created via the UI, but not with calculated fields - This should help you a little

            When I add that filter as a context filter the dashboard stops working (something like invalid argument: database does not support calculation). Without context filters the dashboard is ok. So this does not really play for us.

             

             

            • I wonder if creating an additional User Filter via the UI might help you here?  Filter dimension values using the User Filter while filtering measure values with your calculated field.

            Correct me if I am wrong but the User Filter via the UI is the same as hand made filters. The only exception is that in UI case tableau generates a set for the users.

            • 3. Re: How to use row level security in filters itself?
              Russell Christopher

              Tamas Foldi wrote:

               

              This is not necessarily true: if you have a huge fact table and several small lookup tables then it is much more faster to get the filter values (distinct values from lookup tables, with practically no cost) than to scan fact table for each quick filtered columns. Also, in our case we use the lookup tables also for internationalization so it is not feasible to store all dimensional values in 10+ languages directly in the fact table.

               

               

              You are correct if referential integrity constraints are in place and Tableau join culling can therefore address the lookup table directly via a single SELECT F1 From Lookup. If the database doesn't have referential integrity constraints in place, then join culling won't work and we'll have to issue the whole SELECT....Fact JOIN.Lookup1..JOIN..Lookup2..JOIN. Lookup3...statement which is quite expensive just to get some lookup values

               

              Correct me if I am wrong but the User Filter via the UI is the same as hand made filters. The only exception is that in UI case tableau generates a set for the users.

               

              They operate the same way, however my suggestion is to use them differently. Currently, your calculated field is expressed like this in SQL:

               

              ....WHEN NOT ([Table_1].[User] = 'russch')......

               

              What I'm suggesting is to create the User Filter directly ON dimension values. Doing this would result in SQL that looks like this:

               

              ....WHERE (([Table_1].[Region] NOT IN ('east')) AND (1=1))

               

              So, depending on your schema, it could be substantially cheaper to filter on the dimension values directly rather than replying on your JOINs to filter the same values by restricting the username in a different table.

              • 4. Re: How to use row level security in filters itself?
                Russell Christopher

                Another idea occurred to me:

                 

                What if you include the lookup tables in a distinct data source - You'll probably find that filtering the lookup values by themselves if faster than when joined to the fact table. You also might be able to aggregate and store these values in a Tableau Extract so that you don't even need to touch the relational source when filtering down the results displayed in the quick filters. Secure the lookup values using row-level security + relevant values.

                 

                Then, use action filters to apply the "selected values" from the new data source to the views based on the original data source. This last part might be a little clumsy in terms of UI, but it could help you with the first part of the scenario.

                1 of 1 people found this helpful
                • 5. Re: How to use row level security in filters itself?
                  Tamas Foldi

                  You are correct if referential integrity constraints are in place and Tableau join culling can therefore address the lookup table directly via a single SELECT F1 From Lookup. If the database doesn't have referential integrity constraints in place, then join culling won't work and we'll have to issue the whole SELECT....Fact JOIN.Lookup1..JOIN..Lookup2..JOIN. Lookup3...statement which is quite expensive just to get some lookup values

                  Hmm, I am using Greenplum database without any referential integrity stuff (database does not support it anyway). Should I try to experiment with informative foreign keys? Has it any effect on the generated SQLs?

                   

                  They operate the same way, however my suggestion is to use them differently. Currently, your calculated field is expressed like this in SQL:

                  My calculated field filters directly the necessary values in all tables. Due to partition elimination I cannot use user filters on lookup values only, thus, I filter on all values including fact table's references. My user names like '123456 Foo Bar' and I am using filter expression like:

                   

                  USERNAME() = "administrator" OR
                  [fact_dwh_customer_id] = RAWSQL_INT('SUBSTR(%1,1,6)::integer',FULLNAME()  )  AND

                  [lkp1_dwh_customer_id] = RAWSQL_INT('SUBSTR(%1,1,6)::integer',FULLNAME()  )

                  [..]

                   

                  I don't really like this, but this is the only way where I can ID level filtering on multiple tables with proper partition elimination. As the users cannot log in to the system, they do not really see their ugly full names.

                   

                  Another idea occurred to me:

                   

                  What if you include the lookup tables in a distinct data source - You'll probably find that filtering the lookup values by themselves if faster than when joined to the fact table. You also might be able to aggregate and store these values in a Tableau Extract so that you don't even need to touch the relational source when filtering down the results displayed in the quick filters. Secure the lookup values using row-level security + relevant values.

                   

                  On Monday I will try it out and let you know the results. Sounds bit complicated but it could work.

                   

                  Thanks

                  • 6. Re: How to use row level security in filters itself?
                    Russell Christopher

                    Tamas Foldi wrote:

                     

                    I am using Greenplum database without any referential integrity stuff (database does not support it anyway). Should I try to experiment with informative foreign keys? Has it any effect on the generated SQLs?

                     

                    I don't know specifically how we implement (or don't) Join Culling against Greenplum. I'd use the Tableau Logs, Interworks Performance Analyzer, or PostgreSQL's pg_stat_statements to determine what queries are being fired to populate the quick filters. The difference should be pretty obvious. 

                    • 7. Re: How to use row level security in filters itself?
                      Tamas Foldi

                      I have tried the distinct data source, but it had two drawbacks: 1) it was slower than the database joined version 2) the vizualizations changed due to the overlapping values. Since I would use the lookups only for filtering, data blending is not a really good option as it always involves the common dimension in the vizualizations as well (with overlapping).

                       

                      The foreign key stuff not really working as some of the source objects are views and not tables, therefore I cannot add foreign keys on them. But I will try that out just to check if it is a working solutions or not.

                      • 8. Re: How to use row level security in filters itself?
                        Tamas Foldi

                        This was solved in Tableau 8, there the data source level filters applied on "Show all values" LOVs