Applying security filters to a fact and degenerate dimension

[ashley]

In the foundation/physical layer of my framework model, I have a fact table with various metrics in and a degenerate dimension which contains non-metric attributes related to the facts. This degenerate dimension is just an alias shortcut of the fact dimension as it all comes from the same source DB table.

FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION

There's been a requirement to implement row based security to limit the data in the fact table to only users who are allowed to see it. There's a user security table in place which is set up as a query subject, created a 1.1 join on the fact table, and then applied a filter to the fact query subject in the business layer using the username of the person logged in to.

USER_SECURITY 1.1 <----------> 1.1 FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION

This works fine and as expected, I always want the fact dimension to be filtered when anything is queried from it in Cognos. However, because the degenerate dimension also includes data directly related to the fact dimension, I also need to add the same process of security to that dimension.

USER_SECURITY 1.1 <----------> 1.1 FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION 1.1 <----------> 1.1 USER_SECURITY

This seems to work fine, the data being returned is as expected. I took a look at the generated SQL and it seems that the statement is doing the SELECT from the degenerate dimension first, and then joining the fact dimension onto this. My only guess is that this is because of the degenerate dimension being an alias shortcut of the fact dimension? Is the way that the SQL has been generated an issue? Can anybody see any problems with the implemented solution? Any feedback would be great :). Thanks.

[MFGF]

In the foundation/physical layer of my framework model, I have a fact table with various metrics in and a degenerate dimension which contains non-metric attributes related to the facts. This degenerate dimension is just an alias shortcut of the fact dimension as it all comes from the same source DB table.

FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION

There's been a requirement to implement row based security to limit the data in the fact table to only users who are allowed to see it. There's a user security table in place which is set up as a query subject, created a 1.1 join on the fact table, and then applied a filter to the fact query subject in the business layer using the username of the person logged in to.

USER_SECURITY 1.1 <----------> 1.1 FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION

This works fine and as expected, I always want the fact dimension to be filtered when anything is queried from it in Cognos. However, because the degenerate dimension also includes data directly related to the fact dimension, I also need to add the same process of security to that dimension.

USER_SECURITY 1.1 <----------> 1.1 FACT_DIMENSION 1.n <----------> 1.1 DEGENERATE_DIMENSION 1.1 <----------> 1.1 USER_SECURITY

This seems to work fine, the data being returned is as expected. I took a look at the generated SQL and it seems that the statement is doing the SELECT from the degenerate dimension first, and then joining the fact dimension onto this. My only guess is that this is because of the degenerate dimension being an alias shortcut of the fact dimension? Is the way that the SQL has been generated an issue? Can anybody see any problems with the implemented solution? Any feedback would be great :). Thanks.

Hi Ash,

You need to be careful here with your relationships. Remember the impact cardinality has on fact determination when generating queries - any object at the 'n' end of all relationships used in the query is treated as a fact, and any object at the '1' end of any relationship used in the query is treated as a dimension. With the relationships you're showing here, your fact is at the 1 end of the relationship with user_security, which could give you very weird results in a multi-fact query.

I would probably approach this as follows:

Create a model query subject that encapsulates the fact table and security table (so that the relationship between them is always enforced). Use this new query subject as the fact in your main model (ie replacing the current fact query subject and re-defining all the same relationships to it).
Create a model query subject that encapsulates the degenerate dimension and security table. Use this new query subject as the degenerate dimension in your main model (as above, re-defining all the same relationships to it).

Cheers!

MF.

[bus_pass_man]

The order of the joins might be due to the names of the relationships as they are resolved alphabetically in some cases (ambiguous query paths for example) and it might also be the case here.

Here are two other ways to approach the problem.

i.

If your degenerate dimension is represented as a shortcut to the fact table and your business layer objects use it, you probably would not need to have a join from it to the security table and you would be picking up the security filters from the fact table's join to the security table.  The query would be a bit more efficient too.

ii.

Security filter parameter maps don't require that you have a join to the security table.

Look at the implementation of the language parameter maps and their macros in the sample models.   You could use that as your pattern, where you pick up some session parameter which would be a key in the parameter map.

You can create a parameter map using columns from a table. 

You can nest macros in macros.

Hope that helps.