copying element security data from one element to another

Post Reply
cerend
Posts: 8
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

copying element security data from one element to another

Post by cerend » Tue Feb 23, 2021 5:11 pm

Hello all,

I have a distributors dimension and have created elements like "distributortype1-old", "distributortype5-old" under it. What I'm trying to do is copy the element security of DistributorType1 to DistributorType1-Old. I have thought of using ElementSecurityGet and ElementSecurityPut in metadata (or maybe it should be data?) tab while also passing a csv data source for parameters. But I cannot imagine how ElementSecurityGet value could be stored for each row in the data source? I have written down what I thought I could do but it obviously isn't going to work.

Then I thought maybe I could define variables for each -old suffixed element in prolog but then I'll have to write one function per -old suffixed distributor * number of user groups (20+). So, I'd need to write many lines of code , which is inefficient.

var_security = ElementSecurityGet(Distributors, DistributorType (as parameter from data source), 'Group');
ElementSecurityPut(var_security, DistributorType-Old (as parameter from data source), 'Group');

Would appreciate any and every guidance on this issue.
Best Regards,

Ceren

tomok
MVP
Posts: 2792
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: copying element security data from one element to another

Post by tomok » Tue Feb 23, 2021 8:32 pm

I would use a view on the }ElementSecurity_YourDimName as the source. Just create a subset on the Distributors dimension that excludes the "new" distributors so you are only retrieving values for the "old" ones. If the "new" distributor name cannot be deduced from the "old" name then put an attribute on the Distributor dimension and put it there. You can retrieve that value in the TI via an ATTRS function.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

declanr
MVP
Posts: 1697
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: copying element security data from one element to another

Post by declanr » Tue Feb 23, 2021 10:23 pm

Personally, if this is a one off job I would be inclined to just do it manually in PAX/perspectives with simple lookups to decide where you want to paste the values into.
Declan Rodger

cerend
Posts: 8
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

Re: copying element security data from one element to another

Post by cerend » Tue Feb 23, 2021 11:48 pm

Thank you both for your suggestions!

And yeah, after unsuccessful attempts at trying to automatize the process, and messing up the dimension structure, I decided to upload the security level via csv with ElementSecurityPut. But that function also bailed on me for some reason and didn't change the security levels despite running the process with no errors. In the end I entered the values manually. :|

I'm guessing putting something like below into metadata or data tab isn't possible because it would execute numofrecords^2 times instead of numofrecords times? Or is it possible to get the value from the source and put it into target cell directly before the function executes for the next record ?

ElementSecurityPut(ElementSecurityGet(current_distributor), old_distributor)
Best Regards,

Ceren

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: copying element security data from one element to another

Post by Wim Gielis » Wed Feb 24, 2021 8:34 am

cerend wrote:
Tue Feb 23, 2021 11:48 pm
I'm guessing putting something like below into metadata or data tab isn't possible because it would execute numofrecords^2 times instead of numofrecords times? Or is it possible to get the value from the source and put it into target cell directly before the function executes for the next record ?

ElementSecurityPut(ElementSecurityGet(current_distributor), old_distributor)
You have to think rationally and clearly. What is the data source ? A file ? A view ? Every “item” in the data source will come by once in the Metadata tab and again once in the Data tab. In the Data tab write statements such as CellPutS. In Architect/Perspectives, right click the process name and check the Security access. If needed, do a zero out in the Prolog tab of the relevant cube cells.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

cerend
Posts: 8
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

Re: copying element security data from one element to another

Post by cerend » Wed Feb 24, 2021 9:34 am

Hi Wim,

My problem is that I was stupid and thought that metadata and data tabs were executing first line of code for every record in the data source and then moving to the second line and executing that for every record in the data source and so on. I just realized that it is the other way around and all lines of code in metadata or data tabs are executed against the first record until the tab ends, then all lines executed against second record and so on. That is probably one of the reasons why I was not making any sense.

I provided all the areas necessary for ElementSecurityPut to work with a csv data source like below after failing at automatizing. But it is not clear to me whether ElementSecurityPut function is updating metadata or data so that is why I tried it in both tabs.

I tried and failed so many times doing different things that I don't even remember what else I did. Maybe I did something that prevents me from updating element security and that's why what I've written below didn't work.

Anyway, thank you all for help, I guess I need to do some trial & error to understand what is going on.

tried to run below lines in metadata and data, didn't update anything both times
ElementSecurityPut(UG01, 'Distributors', Elname, 'UG01');
ElementSecurityPut(UG17, 'Distributors', Elname, 'UG17');


data source structure
ElName UG01 UG17
C00684 READ NONE
C00685 READ WRITE
C00686 READ WRITE
Best Regards,

Ceren

Wim Gielis
MVP
Posts: 2646
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.8
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: copying element security data from one element to another

Post by Wim Gielis » Wed Feb 24, 2021 6:18 pm

cerend wrote:
Wed Feb 24, 2021 9:34 am
tried to run below lines in metadata and data, didn't update anything both times
ElementSecurityPut(UG01, 'Distributors', Elname, 'UG01');
ElementSecurityPut(UG17, 'Distributors', Elname, 'UG17');
If you look at the Bedrock process }bedrock.cube.data.copy.intercube.pro, you will see an example in the Data tab.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 117 TM1 articles and a lot of custom code
Newest blog article: Avoid circular references https://github.com/wimgielis

Adam
Posts: 41
Joined: Wed Apr 03, 2019 12:10 am
OLAP Product: IBM PA
Version: 2.0.9.4
Excel Version: Office 365 x32

Re: copying element security data from one element to another

Post by Adam » Wed Feb 24, 2021 8:05 pm

Wim Gielis wrote:
Wed Feb 24, 2021 8:34 am
In Architect/Perspectives, right click the process name and check the Security access.
Hello Ceren. Would you please confirm you did this? Unless you're getting a process error I'm inclined to think this is a problem.
Take care.
Adam

cerend
Posts: 8
Joined: Fri Feb 05, 2021 7:58 am
OLAP Product: Planning Analytics
Version: 2.0.x
Excel Version: Excel 2016

Re: copying element security data from one element to another

Post by cerend » Thu Feb 25, 2021 7:15 am

Hey Adam,

I wrote the process on PAW and there in the process editor settings, there is an option to enable or disable security access for the process, and for my last attempts at making the process work, I did enable security access but the result was the same.

In prod environment, I used CellPutS like below and it worked.

Code: Select all

IF( UG01 @<> 'NONE');
CellPutS( UG01, '}ElementSecurity_Distributors', Elname, 'UG01');
ENDIF;
My other mistakes which may have contributed to this issue:
  • Creating said elements with DimensionElementComponentAdd (with a different process) / not using DimensionElementInsert at all (which I learned I should use later and applied in prod environment)
  • Having non-existent elements under Elname column of csv file (fixed later)
  • Having blank cells where it should have been NONE under UGxx columns of csv file (fixed later)
Also in case it is relevant, the cells that needed security update were C level elements, because I carried some existing elements under them before trying to update their security
Best Regards,

Ceren

Post Reply