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?

[swuehl]

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.

[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.

[swuehl · Accepted solution]

Yes, you should use the intersection operator to consider user selections, that's a good practice, forgot to add this to my previous draft:

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

I've created a sample QVW showing what I was thinking about.

[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?

[swuehl]

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?

[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?

[swuehl]

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