Element Security and BI reporting issue

[sachin]

I will try to explain the issue we are facing and then would like to know how you handled it.

We are using TM1 as datasource in Cognos BI reports. We have an Operating Expense cube. One dimension in it is Cost Centers - which needs to be secured. Dimension is named as eList (Planning folks know what this means). Our hierarchy is huge, upto 14 levels deep, it is unbalanced, about 1500 CCs.

So if person A has access to CCs 1, 2, 3 and he/she runs the report, we want him/her to see only CC 1, 2 and 3 data. To achieve this we rt clicked on the dimension and defined Element Security assignments. This created a a cube }ElementSecurity_eList cube.

Security rights is defined in a sql server table. So I built a process to read from this table and load the forementioned cube. In architect, when I login as different users, I can verify that the data I see in the Opex cube, is restricted to what the user id is entitled to. So in BI world, when I run the report as user it shows the correct data. So far good.

The problem we are stuck with is that, users want a tree prompt to pick and choose the CCs before runnning the data. The reason they want tree prompt is because, it gives nice hierarchical view of the dimension. So I added a tree prompt. When I run the report as multiple users, I see blank in the tree prompt. Now for any of the user, if I give READ access to all the parent elements leading upto the root and then run the report ... I can see the tree prompt.

For a particular user, what is a good way to assign READ access to all parent nodes in the dimension, if user has leaf level has READ access? For ex see the screenshot, I'd like the user to have read access to RU2147, RU3382 and RU2126

The only way I could think of it is doing through a TI.
* Create a subset with all consolidations
* Loop through each user in the }Groups dimension (Loop #1)
* Loop through each consildation in the subset (Loop #2)
* Find the number of children for the consolidation and again start a loop (Loop #3)
* if any child has READ access, then put READ, quit loop #3. Start with next element in loop #2

1) Is there a better way to do this? I can write this in TI, but looks awfully tedious 3 loops ... somehow doesn't seem right
2) Can this be done through a rule? I am thinkig no, but I'd like to hear your thoughts

Giving READ access to the parent elements means that user can now see numbers that rollup to the parent. If this is the only way to make tree prompt appear in the report, I think users may be ok with this.

The bigger question I'd like to know answer for is ... If you implemented dimension security in TM1 cube, did you consume it as a source in BI? If so, how did you handle the security? What did you use as prompt in the report for selecting the items?

Thank you!

[tomok]

As long as you don't have multiple trees in the Cost Center dimension here is what I would do. Create a new TI, with the data source being a zero-suppressed view of the }ElementSecurity_CostCenter cube, based on a leaf only subset of the CostCenter dimension. In the data tab of the process have a WHILE loop to look for the parent of the cost center, if found, make the rights to the parent READ for the group. When there are no more parents, exit the loop. Something like this:

WHILE (Parent@<>'');
Parent = ELPAR('CostCcenter',!CostCenter,1);
IF(Parent@<>'');
ElementSecurityPut('READ', 'CostCenter', !CostCenter, !}Groups);
ENDIF;
END;

[sachin]

When I posted this, back in Mar 2012 we were on TM1 9.5 with BI 10.1 ... with this we had to go down to element security level. Somewhere between upgrade of TM1 from 9.5 to 10.1 and BI update from 10.1 to 10.1.1, this issue has been fixed by IBM.

We are currently on TM1 10.1 with BI 10.1.1. Based on IBM Cognos support's recommendation, we got rid of element security and secured only the dimension in question. Tree prompt automatically showed the hierarchy they had access to. We no longer have to grant read access to parent nodes (all the way leading to root) for tree prompt to work.

- Sachin

[Sebastian.Klein]

Hello Sachin,

I am facing the same problem and found this thread with a google search. I have also a dimension which needs to be secured by element level and the users will also be granted access to elements under the top node without access to a top node. The system versions are similar to the ones in your first post (TM1: 9.5.2, BI: 10.1.1). So I've tested also with TM1 10.1 and BI 10.1.1 in another environment as suggested in the last post, but without success. Can you remember what you have done benath upgrading BI and TM1? I wonder why I have still the problem and you have solved it.

Can you tell me what you have configured in security (CAM, IntegratedSecurityMode) and if you are using the TM1 package in compatible or dynamic mode.

Thank you for your help and greetings from Germany,

Sebastian

[sachin]

I did not create any extra process, other than to secure the dimension. This issue is cured by IBM in their upgrade, so we did not do anything except upgrade our environment.

I will summarize what I did.

• 1. Rt click on the dimension > Security > element security assignment. When it comes up, just click ok. This will create a control cube }ElementSecurity_Dimension
  2. Create a replica of the above cube and name it same without the '}' ... we did this 'cos of reporting. We'd like to report to see which user has access to which nodes (or vice-versa). It is diifcult to do the reporting with the control object - or atleast, we did not want it to be put out for reporting. That's why created the replica
  3. Access to element(s) per user are stored in a sql DB. Built a process that loads our replica cube (mentioned in #2) ... we zero out before loading
  4. One more process that copies from replica cube to the }ElementSecurity_Dimension cube
  5. Run RefreshSecurity on the service, so that the updated security is propogated

Remember to do a refresh security, otherwise you will not see the changes.

IntegratedSecurityMode=5 ... is what is defined for us.

It does not matter whether you use CQM (Classic QM) or DQM, the results should be the same - we saw no difference as far as security. I'd strongly recommend using DQM, as I have seen an extreme improvement in report run times. I am impressed with DQM.

[Sebastian.Klein]

Thank you for the detailed description of what you have done. This is similar to my security settings (except of importing the settings into a cube and copy them into the element security cube). I also run RefreshSecurity at the end of setting the security.

What I have found is that the tree prompt is filled with the function "rootMembers". When I remove this function, all secured elements will be visible in the tree prompt. So I think it is generally a problem with a function to fill the tree prompt. I can also add a defined named level to the prompt and it will display the correct elements. But the users have access to different levels, so I don't want to show all of them in the prompt, just the highest has to be selected.

Beneath that, the problem to see the hierarchy in Business Insight Advanced or Report Studio when I expand the dimension itself still exists for those users. But this would be fine because the analysts and report creators have full hierarchy access.

I have more questions: What environment do you have, e.g. Windows Server 2008 64 bit or similar? Are BI and TM1running on the same server or on different ones? Could you try to expand the dimension in Business Insight Advanced with a user who has limited element security?

Thank you again for your help! It is a strange behaviour, also the IBM Support has no answer at the moment.

[sachin]

We are on 64 bit machines with separate BI and TM1 servers. Our end users in the environment we are in, do not have license for Business Insight Advanced, so I cannot test it

In the cognos report that we have built, the query that is generated because of a tree prompt has only one data item in it, with expression - rootMembers (hierarchy_name)

We do not have named levels in the prompt. We have heard that it will degrade the performance, so we were advised against having one if none of the reports were using the named levels.

[Sebastian.Klein]

I finally found out why I have got the problem: The named level are problematic. I removed them for the dimension which will appear in the tree prompt and then it will show the correct result for the restricted users. It is also not an TM1 version problem, I have tested it with 10.1.1 and 9.5.2 versions.

Nevertheless I have already implemented a solution with a seperate top node, which will have all the secured elements under it. It isn't nice, but the end user is happy with that .

[BigG]

Hi, just adding to this, I have the same issue (security on an organisational structure and users not having access to the top node)

My hierarchy has 4 levels (incl top level node -root) that the users may have access too.

I seem to have resolved by, creating 3 tree prompts. Each prompt references a different level set in the prompt query (but uses the same prompt parameter).
Create a String 'variable' in condition explorer to display or not display each prompt - my conditions were 'if the top Level is not Null then 'Top level' else if level 002 is not null then 'Next level' etc etc'

On the PROMPT page - Use these string results in the variable to conditionally 'Render Variable' for each of the 3 prompts. Make sure you use Render variable NOT Style variable.

The main query references the single prompt parameter, and walaah