Drill Rule to Block users from running the process on select accounts

Post Reply
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Drill Rule to Block users from running the process on select accounts

Post by michaelc99 »

I am in the process of implementing drill through functionality; however, I want to prevent end-users from selecting any leaf level or consolidated level accounts under the "Total Compensation and Benefits" hierarchy and executing the drill through process. So, I set-up a Drill Through rule with the code below and it seems to work as intended. However, the user can still select the parent level member and execute the process.

Is there a function, or another way to write the process below, that prevents users from running the process on any leaf level or consolidated level under the "Total Compensation and Benefits" hierarchy?

Code: Select all

[ ' Actual '] = S:
IF ( ELISANC ( 'ACCOUNT MAIN' , 'Total Compensation & Benefits' , !ACCOUNT MAIN) = 1 , STET, 'DrillThroughQuery'); 
Thank you!
Michael
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Drill Rule to Block users from running the process on select accounts

Post by paulsimon »

Hi Michael

I am not really working with TM1 at the moment, but the approach that you are taking is fine. It will disable Drill Thru on the selected Accounts. However, there is probably more to this than first meets the eye. I am assuming that the reason that you don't want users to drill on these Accounts is to avoid users being able to drill thru into detail which will show the salary related details of individual staff. Is that correct?

In practice we usually want some users to be able to drill on these accounts but only a limited set, eg those in certain sub groups of Finance or HR, or the manager of the cost centre, but only on his own cost centre.

The approach that I have taken in the past is that rather than drilling directly to the underlying tables with a SQL query, I instead pass parameters such as Account, Cost Centre, and User Security Group, etc, into a SQL Stored Procedure. If the user drills on Accounts on which they should not be able to access the full detail, then instead of returning the detail level transactions, the Stored Procedure returns only a summary, using Group By and Sum. This can work for cases where the consolidated level Account contains a mix of eg General Expenses and Staff Expenses, so those without special access still get the detail for the General Expense Accounts, but only a summary for the Staff Expense Accounts. A mechanism for this can be to have a table of consolidation to detailed account, so each consolidation regardless of level appears with each detailed level account below it. This still gives a fast lookup in SQL from consolidation to detailed account. It is then easy to add a flag to indicate whether an account is restricted or not, and to only return a summary if it is and the user does not have access.

There are different levels of complexity to which you can take this sort of solution. It may be that what you have done already meets your needs.

In your rule, I would suggest that you return '' instead of STET. You could make it work faster by using attributes populated at dim build time instead of using ELISANC, but whether it is worth the effort depends on how many drill requests you get.

Regards

Paul Simon
michaelc99
Posts: 46
Joined: Mon Jul 26, 2021 12:55 pm
OLAP Product: TM1
Version: 2.0.0
Excel Version: Office 365

Re: Drill Rule to Block users from running the process on select accounts

Post by michaelc99 »

Good Morning Paul,

Yes, the intent of the rule is to prevent all users from being able to access salary-related information. However, if the business decides to change course and only allow a select number of users (I.e. HR and Finance) to be able to drill into those transaction-level details then your recommendation would be the best course of action.

I am going to test out using " instead of STET to see if that produces the desired effect; however, ELISANC only seems to block the rule from running when the user explicitly selects those accounts. In the image example below, if the user selects "Total Comp and Ben" they can trigger the drill-through process, but if they select Total Comp, Total Ben of any of the Accounts 1->5 then they drill rule prevents the user from running the drill through process.
2022-05-04_10-26-03.png
2022-05-04_10-26-03.png (6.36 KiB) Viewed 827 times
If replacing STET with " does not work for me in my situation, then I will add a new attribute and code the drill through rule to block where the Attribute Flag = TRUE.

Thank you,
Michael
User avatar
gtonkin
MVP
Posts: 1192
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: Latest and greatest
Excel Version: Office 365 64-bit
Location: JHB, South Africa
Contact:

Re: Drill Rule to Block users from running the process on select accounts

Post by gtonkin »

ELISANC does not check if the element you comparing is the same as the ancestor. You need to add in an OR statement for this e.g.

Code: Select all

[ ' Actual '] = S:
IF ( ELISANC ( 'ACCOUNT MAIN' , 'Total Compensation & Benefits' , !ACCOUNT MAIN) = 1 % !ACCOUNT MAIN@='Total Compensation & Benefits' ,
 STET,
 'DrillThroughQuery'); 
Post Reply