Element level security issue in Cognos BI (TM1 as datasource

[sreesuku]

Hi All,

Brief Problem description :
When TM1 element level security is defined for more than one element in a dimension, data is getting filtered only for the first element in Cognos BI report.

Detailed description :
I am trying to implement element level security in TM1 (V 10.1) . I have done it on 3 dimensions - Country(GDCs), City and Sectors.
We are using Cognos groups for this. I have created 3 groups and added a user to these 3 groups and defined access (Write/None) at element level.
When I checked in cube viewer I am getting only those elements for which I have defined access and data is also getting filtered.

TM1 Security assignements

TM1 Security Assignment.jpg (45.95 KiB) Viewed 3539 times

When I use the above TM1 cube as data source in Cognos (V 10.1.1) and If you apply TM1 security on 2 elements from a single dimension, although in the report it shows 2 elements, the report data gets filtered only based on the first element in the dimension !!! . If access restriction is there for only single element then both data and prompts are filtering out correctly.. !!!

I have tried to put this in a simple list report. I have a dimension 'Year' for which there are no access restrictions. When I use Year and any measure in a list, data is coming only for the first element from a dimension if it has element level security for more than one elements.

Please refer the below screen shot

Sample.jpg (47.74 KiB) Viewed 3539 times

Can anybody suggest a solution to solve this problem. Not really sure whether its from TM1 side or Cognos side

Regards
Sree

[sreesuku]

Just wanted to add some more findings on this

Scenario : User has access for 2 countries (India, China) and full access on Year dimension, in Cognos report (Graph or List) when we show only Year and Headcount (Measure), we are getting data for only "First" element in the Country dimention (India). But when we bring the Country item along with Year and Headcount, we are getting correct data for all countries (in this case 2 countries). As we do not want to have country item in our Cognos report, we are getting wrong data. Please let me know if any suggestions

I tried using the cell security cube as well. In the above example, when I put the below rules in cell security cube data is getting restricted for India and China, but in Cognos report when I see the measure value for Year Dimension data is shown for the First occurring element after India and China.

['India','CAMID("GDMIS:g:cn=gd mis city,ou=memberlist,ou=ibmgroups")'] = S:'NONE';
['China','CAMID("GDMIS:g:cn=gd mis city,ou=memberlist,ou=ibmgroups")'] = S:'NONE';

Is it something to do with TM1 and BI integration. We are getting the correct data in TM1

[Michel Zijlema]

Is it something to do with TM1 and BI integration. We are getting the correct data in TM1

Yes it is. I think the reason behind is that Cognos BI is traversing tree structures and will start from the first top level node it can get access to. I assume that when you add a total element summing India and China and give the user access to this tree (so all three elements) the report will work as expected.

Michel

[PlanningDev]

Is it something to do with TM1 and BI integration. We are getting the correct data in TM1

Yes it is. I think the reason behind is that Cognos BI is traversing tree structures and will start from the first top level node it can get access to. I assume that when you add a total element summing India and China and give the user access to this tree (so all three elements) the report will work as expected.

Michel

I think Michel is right here. BI doesn't know to sum your items based on security either so having access to two elements and not using both of them in a report would likely cause the first element to be used. This is similar to the idea that if you don't select a dimension in a BI report then the TOP node is assumed (if there is one). It doesn't appear you have a top node for India and China as Michel stated so BI has to assume one.

The interesting scenario here is that you may not have a way to create a rollup of all possible combinations of countries in TM1 to assign security to. If you can then I would do that, as it will be the easiest for BI to understand.

Other wise I would make the report only show a single country at a time. If you have to use a prompt macro for the country as an item in your list or crosstab.