7 Replies Latest reply: Mar 15, 2016 1:31 PM by Stefan Wühl RSS

    How to apply Section Access to reduce part of the data model

    Austin James

      Is it possible to apply section access to reduce the data in a data island and then take the reduced data island and bring it into the overall data model so that the overall data model isn't reduced?

       

       

      For example, lets use a simple data model.

       

      //This table holds countries

      Countries:

      LOAD * INLINE [

      COUNTRY_ID, COUNTRY_NAME

      USA, United States

      CAN, Canada

      MX, Mexico

      ];

       

      //This table lists states/provinces per country

      States:

      LOAD * INLINE [

      COUNTRY_ID, STATE_ID, STATE_NAME

      USA, CA, California

      USA, OR, Oregon

      USA, WA, Washington

      CAN, BC, British Columbia

      CAN, YT, Yukon Territories

      MX, SI, Sinaloa

      MX, BN, Baja

      MX, BS, Baja Sur

      ];

       

      //This table holds population data for each state/province

      Population:

      LOAD * INLINE [

      STATE_ID, POPULATION

      CA, 38800000

      OR, 3970000

      WA, 7062000

      BC, 4631000

      YT, 33897

      SI, 2768000

      BN, 3155000

      BS, 637026

      ];

       

      //This table defines which states a user has access to

      UserStates:

      LOAD * INLINE [

      USER_STATE_ID, USERNAME

      CA, SAM

      OR, SAM

      WA, SAM

      BC, CARL

      YT, CARL

      SI, MIKE

      BN, MIKE

      BS, MIKE

      ]

       

      Section access commands and tables have been intentionally left out of this example.  I'm not concerned with the syntax of how. Rather, I'm interested in the concept of how I accomplish this.

       

      Now, I would like to apply Section Access to the UserStates table so that this table is reduced based on username.  Once this table is reduced, I would like to bring this table into the data model.  Once complete, I can build a display that shows the population of each country.  I can also build a display that shows the population of each state, but only for the states the user has access to.

       

      From a conceptual standpoint, all users should have access to all data at the aggregate level (ie any user can see the population for every country in the data model).  However, each user only has access to view detailed data for a subset of more granular data (ie a user may only view the population for the states they have access to).

       

      Please avoid the following: 

      • Building a population summary table for each country. 
      • Pointing out that users will still have access to the States table.  This is obvious.  However, if we never use a field from the States table as a dimension in a chart/display, the end user (non-developer) will never know that they do, in fact, have access.

       

      How do I accomplish this task?  Can I do it in one application?

        • Re: How to apply Section Access to reduce part of the data model
          Stefan Wühl

          You could create a set expression for any aggregation in a chart with dimensions from table states (you certainly want to show these dimensions, in contrary what you described, because this table holds the states names), like this (assuming you created a section access that limited the UserStates table accordingly):

           

          Dimension

          STATE_NAME

           

          Expression

          =Sum({<STATE_ID = p(USER_STATE_ID) >} POPULATION)

           

          But, as you said, the data is still in your model, so it's not ensured that the user won't find a way around (compared to real data reduction) or that a developer misses to implement a filter.

            • Re: How to apply Section Access to reduce part of the data model
              Austin James

              This is a possible solution, however, if you use the STATE_ID as a dimension, with the set analysis as you define, the user is not able to further select the data they wish to see by Qliking on the display.  Since the UserStates table is still an island, and the set analysis is tied to possible values in the UserStates table, you are limiting the functionality of the display, unless you provide the user access to the UserStates island.

               

              I'm not saying this isn't an option worth considering.  This option just has limitations.  Maybe you can use triggers and scripting to keep the STATE_ID and USER_STATE_ID selections synchronized...

               

              On the other hand using a set operator in conjunction with your suggestion like:

               

              =Sum({$*<STATE_ID = p(USER_STATE_ID) >} POPULATION)

               

              Might be a good route to go.

            • Re: How to apply Section Access to reduce part of the data model
              Austin James

              It sounds like there is a possible solution using Set Analysis.

               

              Is there a way to do this in the Data Model?  Can I reduce a data island using Section Access and then bring the data island into the overall data model without reducing data in the overall data model?

                • Re: How to apply Section Access to reduce part of the data model
                  Stefan Wühl

                  Not sure what you envision with


                  Can I reduce a data island using Section Access and then bring the data island into the overall data model without reducing data in the overall data model?

                   

                  Could you detail this?

                    • Re: How to apply Section Access to reduce part of the data model
                      Austin James

                      In this article Data Reduction – Yes, but How? it is mentioned that, regarding aggregation-level access,

                       

                      It is difficult to achieve aggregation-level access within one single application, so it is better to solve this problem using two applications: One with detailed data that you reduce using a reducing field, and a second unreduced with aggregated data for all countries.

                      Obviously, the recommended solution is two applications.  However, it appears that it may be possible in one.  We have already identified one way to do it with Set Analysis, but that requires configuring the sets on every display.  If you have a need to use Set Analysis for another task (eg comparing current year to last year), writing the expression becomes much more complex. So, I'm curious to know if there is a way to achieve this using the data model and section access instead of relying on Set Analysis.

                       

                      Conceptually, it seems that you might need to duplicate a (significant?) part of the data model.  One part has the entirety of the data, the other part is only the dimension fields the user has access to via section access.  Finally, you join the two parts together to provide one cohesive data model.  For displays that need limited by security, you pull dimensions from the part of the data model that had Section Access applied.  For aggregate displays and displays that do not require security, you pull from the non-Section Access applied dimensions.  Since both are in the same data model (that is, no data islands) building the displays is an exercise is choosing the correct dimension.  Any Set Analysis is much more straightforward.

                       

                      Can it be done?  If so, how?

                        • Re: How to apply Section Access to reduce part of the data model
                          Stefan Wühl

                          If I understood correctly what you are saying, you could create an aggregated fact table in the script, joining Population table to States table, then aggregate SUM(POPULATION) AS POPULATION_COUNTRY grouped by COUNTRY_ID.

                           

                          This new table would be linked to Countries table by COUNTRY_ID.

                           

                          You would need to add dummy records to States table to create links to every country:

                           

                          States:

                          LOAD * INLINE [

                          COUNTRY_ID, STATE_ID, STATE_NAME

                          USA, CA, California

                          USA, OR, Oregon

                          USA, WA, Washington

                          CAN, BC, British Columbia

                          CAN, YT, Yukon Territories

                          MX, SI, Sinaloa

                          MX, BN, Baja

                          MX, BS, Baja Sur

                          USA, DUMMY,

                          CAN, DUMMY,

                          MX, DUMMY,

                          ];

                           

                          Now your table used for section access will look like (note: not a complete section access table shown here)

                           

                          UserStates:

                          LOAD * INLINE [

                          STATE_ID, USERNAME

                          CA, SAM

                          OR, SAM

                          WA, SAM

                          BC, CARL

                          YT, CARL

                          SI, MIKE

                          BN, MIKE

                          BS, MIKE

                          DUMMY, MIKE

                          DUMMY, SAM

                          DUMMY, CARL

                          ];

                           

                          This should allow all users to see the aggregated POPULATION_COUNTRY facts for all countries, but only their specific detailed state population.

                           

                          Of course, this gets much more complicated with more complex fact and dimensional tables.

                           

                          Hope this helps,

                          Stefan