[Solved] Security Filters Causing Cross-Joins

[c6lapsteel]

I have a model which contains ~8 conformed dimensions joining 3 fact tables. My FrameWork Manager model uses 3 layers - database, model and presentation layer (only the latter published in the package). All joins have been created in the model layer. (Thought this was best practiceÃ,  :-\)

The presentation layer contains 3 star-schema groupings based on each of the fact tables. No performance issues with reports, no cross-joins, SQL looks just as I would expect (includes joins).

Now I want to build in Security Filters. I want to restrict users to seeing only facts associated with a particular site. I have a conformed Site dimenision in the model - so want to make use of this. Ideally I want to define one set of filters (eg. Site.Name = 'Houston'), assign to a set of users and re-use by applying to each of my 3 fact tables.

However, this stops all reports from running unless I allow cross-joins. I can indeed see from the generated SQL that all joins have disappeared.

Why? What is the best way to create re-usable security filters? Is this a consequence of having joins in a model layer rather than the database layer? I have tried recreating the filters in the database layer but does not make any difference.

Thanks

[bdybldr]

Have you tried the burst option?  If you have access to Cognos Support, there is a tutorial at

http://richmedia.cognos.com/integration/cognos/contentDisplay.jsp?contentId=34207&uId=INIXdGrVRoM=&authenticationId=86327&stageId=1

[JGirl]

It is definitely possible to apply security in a framework manager model based on a conformed dimension.

I'll need a little bit more information from you....

Are you trying to secure the dimension in the model layer?

Is your presentation layer made up of a singluar query subject per star schema grouping, or a number of shortcuts and relationship shortcuts?

Are you publishing the package with:
(a) only the presentation layer included and all else excluded,
OR
(b) with the presentation layer included, and all else included but hidden?

If you are publishing the package with scenario (a), i'd suggest using scenario (b) and applying the security filters in the model layer dimension.

J

[c6lapsteel]

J, Thanks

I am trying to secure the dimension in the model layer (though I did try the database layer to and saw the same problem).

The presentation layer is made up of ~8 shortcuts/relationship shortcuts per star schema grouping - these are created automatically - I've not modified or added anything else.

I published the package using option (a). However FM prompts that items included in other layers are required and that it is going to include and hide those in any case.

[JGirl]

Hmmmmm......This has me a little stumped as to why it isnt working.

Do you get any errors / warnings when you validate the entire project?

In most cases where I've applied the security, i've done it at the layer before the joins are created, but I can't see why you are having trouble since you've tried applying them at both the DB and import layer.

Just as a test, try creating a new query subject in the presentation layer, and try to include columns from the fact shortcut and one of the dimension shortcuts in the presentation layer, and click OK to validate it.  If this works, it will confirm that your relationships are working correctly.  If not, I'd be testing the relationships between the facts and dimensions first.

J

[c6lapsteel]

J,

Tested as you suggested - no problems. I think the joins are working fine.

What I have noticed though is the following:

1. Open a query subject (intermediate layer).
2. Create a filter based on a field within the subject (ie. no joins to any other object in the model to cause confusion).
3. Test -> no problem
4. Promote the filter to a stand-alone filter (so that it can be re-used). As soon as you do that, the nomenclature of the filter changes and does not match the nomenclature of the other items in the query subject. Test and, of course, complains that cross-joins are required.

Is this intentional - in which case would seem that it has significant implications on how models are designed (ie. must not have an intermediate layer with joins at all if you want to use security filters).

[c6lapsteel]

J, Thanks for your help. Actually, this is due to a bug!!