Update security rights from an excel template
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Update security rights from an excel template
I created a template from a cube slice in the security dimension for the Adminstrator to easily update TM1 security rights using a DBS function but it comes back with an error meassage [Value]. Does anyone know the correct function that can update the TM1 security cube e.g. Read to Write access through an excel template please?
I have used the DBS function to send data to different dimensions but it seems to be different for the Security dimension. Please help!
Thanks very much.
I have used the DBS function to send data to different dimensions but it seems to be different for the Security dimension. Please help!
Thanks very much.
Last edited by Sewande@aol.com on Sun Dec 05, 2010 9:17 pm, edited 1 time in total.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Update security rights from an excel template
Request for assistance guidelines (PLEASE READ)Sewande@aol.com wrote:I created a template from a cube slice in the security dimension for the Adminstrator to easily update TM1 security rights using a DBS function but it comes back with an error meassage [Value]. Does anyone know the correct function that can update the TM1 security dimension e.g. Read to Write access through an excel template please?
I have used the DBS function to send data to different dimensions but it seems to be different for the Security dimension. Please help!
Same with "I'm using DBS functions but they don't work properly".Try to make the question as specific as possible. ... Similarly if you're getting unexpected results, specifics of what you're running, how, and what results you're getting will yield a more valuable response than "I'm running a T.I. but my code doesn't work properly".
- What cube are you writing to (you don't update dimensions with DBS formulas, you update cubes);
- What value are you writing (trying to write) to the cube;
- What is the EXACT syntax of the DBS function that you're using, including the values of all of the arguments?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Re: Update security rights from an excel template
Please delete the post since you are an adminstrator and I will get the right information when i get to work tomorrow. Mr Kirk
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Re: Update security rights from an excel template
ThanksI am quite new to TM1,
Thanks very much for your help.
Just tried the DBSS as suggested but its now blank instead of error {Value} displayed with the DBS formula.
The new formula below,
=DBSS(C5,$C$1,$B5,U$4)
C5 is the "Write" (string as suggested)
C1 is scm:}ElementSecurity_PPL
B5 is el 1
u4 is el 2
How do I issue the refresh command for it to flow directly into Tm1?
Please see sample attached, was hoping the formula could change the security rights directly in TM1 by just recalculating the worksheet.
I have a static column on the left for admin to edit, and was hoping to just hit a submit button to send the edited rights through the column TM1 Formula using the DBSS formula from a worksheet to TM1.
What am I not doing right please?
Thanks very much for your help.
Just tried the DBSS as suggested but its now blank instead of error {Value} displayed with the DBS formula.
The new formula below,
=DBSS(C5,$C$1,$B5,U$4)
C5 is the "Write" (string as suggested)
C1 is scm:}ElementSecurity_PPL
B5 is el 1
u4 is el 2
How do I issue the refresh command for it to flow directly into Tm1?
Please see sample attached, was hoping the formula could change the security rights directly in TM1 by just recalculating the worksheet.
I have a static column on the left for admin to edit, and was hoping to just hit a submit button to send the edited rights through the column TM1 Formula using the DBSS formula from a worksheet to TM1.
What am I not doing right please?
- Attachments
-
- DBSS.xls
- (28 KiB) Downloaded 202 times
Last edited by Sewande@aol.com on Mon Dec 06, 2010 11:00 am, edited 1 time in total.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Update security rights from an excel template
There's no problem with leaving the post... but it's pretty much impossible to help you if the reader doesn't know exactly what it is that you're doing. A #Value error could be from anything; without seeing what it is that you're trying to write and where you're trying to write it to, any answer would be just a guess.Sewande@aol.com wrote:Please delete the post since you are an adminstrator and I will get the right information when i get to work tomorrow. Mr Kirk
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- MVP
- Posts: 3654
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Update security rights from an excel template
Security cubes in TM1 do not contain values they contain strings.
Therefore you need to use DBSS not DBS.
Therefore you need to use DBSS not DBS.
-
- MVP
- Posts: 2832
- 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: Update security rights from an excel template
Also be aware that when updating a security cube directly, versus going through the TM1 GUI, your security changes will not be active until you issue a SecurityRefresh command or recycle the TM1 service.
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Update security rights from an excel template
That's not been my experience, though I haven't yet tested to confirm that it's still the case in 9.5.tomok wrote:Also be aware that when updating a security cube directly, versus going through the TM1 GUI, your security changes will not be active until you issue a SecurityRefresh command or recycle the TM1 service.
I've found that (up to and including 9.0 at least) a simple assignment of a value to }ElementSecurity_ cube has an immediate effect with two potential "gotchas":
(a) It seems to be required (or at least recommended) if the change is done via TI, although this would normally be done via the ElementSecurityPut command rather than via writing straight to the cube; and
(b) If the security cells are rules-generated, it needs to be done. (Though obviously in such a case you aren't writing to the cube.)
Other than that, I've been running sheets similar to the one that the original poster referred to for years, albeit by using (what is now) TM1 Tools Bulk Paste rather than DBSS functions, and I've always found it to be reliable without needing to either execute Security Refresh or reboot the server.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Re: Update security rights from an excel template
ThanksI am quite new to TM1,
Thanks very much for your help.
Just tried the DBSS as suggested but its now blank instead of error {Value} displayed with the DBS formula.
The new formula below,
=DBSS(C5,$C$1,$B5,U$4)
C5 is the "Write" (string as suggested)
C1 is scm:}ElementSecurity_PPL
B5 is el 1
u4 is el 2
How do I issue the refresh command for it to flow directly into Tm1?
Please see sample attached, was hoping the formula could change the security rights directly in TM1 by just recalculating the worksheet.
I have a static column on the left for admin to edit, and was hoping to just hit a submit button to send the edited rights through the column TM1 Formula using the DBSS formula from a worksheet to TM1.
What am I not doing right please?
Thanks very much for your help.
Just tried the DBSS as suggested but its now blank instead of error {Value} displayed with the DBS formula.
The new formula below,
=DBSS(C5,$C$1,$B5,U$4)
C5 is the "Write" (string as suggested)
C1 is scm:}ElementSecurity_PPL
B5 is el 1
u4 is el 2
How do I issue the refresh command for it to flow directly into Tm1?
Please see sample attached, was hoping the formula could change the security rights directly in TM1 by just recalculating the worksheet.
I have a static column on the left for admin to edit, and was hoping to just hit a submit button to send the edited rights through the column TM1 Formula using the DBSS formula from a worksheet to TM1.
What am I not doing right please?
- Attachments
-
- DBSS.xls
- (28 KiB) Downloaded 192 times
- Alan Kirk
- Site Admin
- Posts: 6606
- Joined: Sun May 11, 2008 2:30 am
- OLAP Product: TM1
- Version: PA2.0.9.18 Classic NO PAW!
- Excel Version: 2013 and Office 365
- Location: Sydney, Australia
- Contact:
Re: Update security rights from an excel template
In the sample that you attached, U4 is actually a blank cell. Are you sure that it shouldn't be G4? It has to be the name of a member of the }Groups dimension.Sewande@aol.com wrote:ThanksI am quite new to TM1,
Thanks very much for your help.
Just tried the DBSS as suggested but its now blank instead of error {Value} displayed with the DBS formula.
The new formula below,
=DBSS(C5,$C$1,$B5,U$4)
C5 is the "Write" (string as suggested)
C1 is scm:}ElementSecurity_PPL
B5 is el 1
u4 is el 2
How do I issue the refresh command for it to flow directly into Tm1?
Please see sample attached, was hoping the formula could change the security rights directly in TM1 by just recalculating the worksheet.
I have a static column on the left for admin to edit, and was hoping to just hit a submit button to send the edited rights through the column TM1 Formula using the DBSS formula from a worksheet to TM1.
What am I not doing right please?
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Re: Update security rights from an excel template
Yes its G4 for this sample, just made a smaller version to demonstrate what I am trying to do. Thanks
-
- Posts: 9
- Joined: Tue Jul 13, 2010 2:07 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2003
Re: Update security rights from an excel template
Hi Please ignore my last post, just tried it again and my formula works, thanks for the DBSS tip.
Thanks again.
Thanks again.