Data security from package to cube

[dhandler]

I have been getting conflicting information from what my Cognos developer has told me, versus what I have read from IBM, and what my gut tells me.  It is a rather straight forward data security issue.  Some people, when they run a report based off a cube can pick from some regions of the country (for example) and when others log in, they can see other regions. 

Based on what I've read, I am under the impression that I can set up a security filter on my Region dimension in the package in my framework model tool and tie various groups to the regions they can see.  Then, when the cube that sits atop of this package is published, that security information is still enforced in any report or dashboard that sits on the cube.

My Cognos developer tells me, that IBM told him, that this will only apply to any reports that hit the package directly, and that we need to specify this in the Transformer model that lays out and builds the cube.  Essentially, that the package security doesn't bubble up into the cube like that.

Which is correct?

Ultimately, I want to have a table with a list of users and the regions they can see.  When somebody looks at a dashboard or report, they can only pick from a list of regions that their name is tagged on.  This sounds like the perfect job for the security filter in FM, but not if the cube is going to ignore it.

Thanks, in advance, for your help and this great resource!

[cognostechie]

Your developer is absolutely correct ! The security filter applied to Framework package will not apply to the cube.
Reason - When the cube is built, the data physically gets captured inside the cube. When you make a report or dashboard
that is getting the data from the cube, it goes only to the cube and FM package is no longer in the picture. FM package
provided data to the cube but once the cube is built then FM package gets disconnected from the cube (at the time of reports
reading the data from the cube).

The security has to be applied to the cube itself in terms of cube views which will work with the user groups.

FM is virtual whereas the cube is physical in terms of data.

The security of FM will apply to the cube if the cube is virtual which is called a DMR is Framework Manager

[dhandler]

The security has to be applied to the cube itself in terms of cube views which will work with the user groups.

Thanks for that description - it was very helpful!  So, does the Cube View in Transformer get setup like the security filters do inside of FM?  I want to drive my security off of a table of data that will have a list of users in one column, and a list of regions (for example) in the other.  I'll link this to the main Regions dimension, and then filter that based on login name.  Something like "AND Region_name in (select Region from UserAccess where username = #($account.personalInfo.userName)#)

Thanks again!

[cognostechie]

The cube views work very differently than the security in FM though it does the same job. However, the security in the cube views will not work with any data in any table in the database. The cube views would need the users from a security provider like Active Directory or Access manager which was used in earlier versions of Cognos. A cube does not deal with joins but rather associations because the data is structured in OLAP style, not the relational style. So let's say your company sells in 3 regions. West, East and Midwest These are called categories in the cube. It will build 3 categories and then accumulate the revenue for each of them. If John Doe needs to see only sales of West resion then the views will be created once the
cube has read the data and created the categories. In the East and Midwest view, John Doe will be denied access.
Next time the cube is refreshed, the security remains the same, no need to revise it again because next time the cube will only look at the revenue and accumulate the revenue to already existing categories in the cube. Since the cube captures the data, there is no way of going back to the database tables when the reports are running from the cube. The way you do it is to create a Region dimension in the cube and assign the users to that dimension. So you can definately have the security in the cube but cannot use your database tables for this. Setting this security can also be a lot of manual work when it is done first time depending on how many users and how many dimensions you have in the cube.

There is another way of creating a cube in FM itself but it will be a virtual cube. If your data is not much and your reports are not slow then this can be another option. This will let you use your security tables because this will inherit it from the SQL you will have in FM. I had the same table structure as you have for security for a company and I also had the same type of SQL to apply the security and it worked perfectly.  Framework has the ability to create a virtual called which reads the data from the tables but restructures it in OLAP style when the reports run.

 

[cognostechie]

The last sentence should read as:

Framework has the ability to create a virtual cube called DMR which reads the data from the tables but restructures it in OLAP style when the reports run.

[dhandler]

This is great information!  If I turned my User <-> Region table into an actual dimension in the cube, couldn't I create a view that simply limits that dimension by the current logged in user?   This would essentially accomplish the dynamic security I am looking for.

Thanks again for the help.  I am now overseeing a Cognos implementation and came from an MS BI background where I had done this in the past.  I am very, very impressed with the Cognos platform so far, but I am struggling just a bit adapting to the new "language" of Cognos.

The cube views work very differently than the security in FM though it does the same job. However, the security in the cube views will not work with any data in any table in the database. The cube views would need the users from a security provider like Active Directory or Access manager which was used in earlier versions of Cognos. A cube does not deal with joins but rather associations because the data is structured in OLAP style, not the relational style. Setting this security can also be a lot of manual work when it is done first time depending on how many users and how many dimensions you have in the cube.

There is another way of creating a cube in FM itself but it will be a virtual cube. If your data is not much and your reports are not slow then this can be another option. This will let you use your security tables because this will inherit it from the SQL you will have in FM. I had the same table structure as you have for security for a company and I also had the same type of SQL to apply the security and it worked perfectly.  Framework has the ability to create a virtual called which reads the data from the tables but restructures it in OLAP style when the reports run. 

[cognostechie]

If I turned my User <-> Region table into an actual dimension in the cube, couldn't I create a view that simply limits that dimension by the current logged in user?

Yes, you can but using the security method I described, not using your database tables. You are again thinking in terms of relational data. The 'current logged in user' logs to the cube, not to the database so neither the tables nor the SQL are accessible at that time.

[bdbits]

To add to what has already been said, the custom views to implement security filtering are also 'frozen' when the cube file is created. However, the membership lookup from the provider user/group occurs in real time. So for instance, assume you use an Active Directory group called "cognos_region_west". In your cube, you create a custom view to apex the view on the "region" dimension to the west region, then assign the "cognos_region_west" group to have access to that custom view. At run time, when an inquiry against "region" is made, Cognos will look at the custom views and see the group membership(s) required. Cognos will then check with Active Directory to see if the current user is a member of that group, and if so apply the custom view thus giving access to that data.

You can assign individual users to custom views, but then you have no way to modify the security without rebuilding the cube as the assignments are captured at cube build time. Using groups as outlined, you can simply change Active Directory group memberships and access is immediately granted or denied.

[dhandler]

That's fine - I understand that I don't have relational SQL access at that point.   However, if I have a dimension made up of users and regions, can I create a cube view that will filter/cloak/supress your access to select members of the dimension based on your login?  In reality, I have a large chart of accounts (hundreds and hundreds) - some people can access some and others can access others.  I do not want to add 500 groups to my AD and then try and tie the groups to each account in this account dimension.  I want to programatically generate the table of user<->accounts that is used as the source for the dimension and then we can reprocess the cube periodically.

Then when a user opens up the cube, they can then only pick from the accounts that they are connected to.

Please don't get frustrated with me!  :-)  I appreciate the help and advice!

Yes, you can but using the security method I described, not using your database tables. You are again thinking in terms of relational data. The 'current logged in user' logs to the cube, not to the database so neither the tables nor the SQL are accessible at that time.