Hello. Faced the non-trivial task of getting a list of user groups in the cube, then use these groups for analysis in access in other cubes (maybe some access groups intersect).
In my attempts, I implemented two options, and both of them failed miserably:
First option:
I created a group and placed it the very first (well, or almost) and, using a recursive rule, I extended the accumulating rule along the flow variable from the last element to the first. Where for each element was taken its next element "|" the name of the group if the user is a member of it. so on the first element I got a list of all the groups. And this worked on a test bench unfortunately in the real model I got ~ 2000 groups and got a critical references evaluation stack error.
The second option: I created a cube of 3 dimensions clients, a sheet (dimension with numbers from 1 to N) and measures (Group number and name) and in the rules described the logic if if the previous cell (! Clients,! List -1, Name) is filled then look at the group closest to it by new lines in the rules like the last found group + 1
new line last found group + 2 and so on 2000.
This option returns a beautiful view of the groups, but it takes so long that it is not viable.
The question is, maybe someone already faced with similar tasks
accumulation of text values over large lists.
Any ideas?
p. s.
Yes, I can do this with TI processes. Yes, I can do it partly through processes, store the groups according to user attributes, or implement the whole logic of the processes. Let's just say this is the second less beautiful option.
get a list of all groups the user is a member of. On the rules and without feeding.
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: get a list of all groups the user is a member of. On the rules and without feeding.
Why not an MDX statement on the }ClientGroups cube ?
Do you want to do this for 1 client or for all clients ?
Do you want to do this for 1 client or for all clients ?
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- 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: get a list of all groups the user is a member of. On the rules and without feeding.
Hi Drg
I had a similar issue. A client could be a member of multiple groups. Each group potentially gave them access to a broad range of Cost Centres. When running a process I needed a quick way to check whether the user had access to a particular Cost Centre. A loop within a loop to find all Groups of which the Client was a member and to then check for each Group, whether the Group had access to that Cost Centre was going to be too slow.
I therefore did the big loop overnight to build a dimension with the user as a consolidation of the cost centres to which they have access. Then, in the prolog of the processes that needed to check the access all I needed was TM1USER to get the user name and then an ELISPAR on the dimension to see if the Cost Centre was a child of that user's consolidation.
I am not sure if the reason for looking at Groups is to get to something else, like Cost Centres, as we were, but if you did just want groups, the same approach would work. Just create a dimension with Groups as children of the User in a dimension. Then in your rule, you can use a similar approach
Building a dimension won't be viable if users frequently swap groups but in most cases, group membership is fairly static. If changes are needed during the day then the Dimension... Direct functions could be used.
Regards
Paul Simon
I had a similar issue. A client could be a member of multiple groups. Each group potentially gave them access to a broad range of Cost Centres. When running a process I needed a quick way to check whether the user had access to a particular Cost Centre. A loop within a loop to find all Groups of which the Client was a member and to then check for each Group, whether the Group had access to that Cost Centre was going to be too slow.
I therefore did the big loop overnight to build a dimension with the user as a consolidation of the cost centres to which they have access. Then, in the prolog of the processes that needed to check the access all I needed was TM1USER to get the user name and then an ELISPAR on the dimension to see if the Cost Centre was a child of that user's consolidation.
I am not sure if the reason for looking at Groups is to get to something else, like Cost Centres, as we were, but if you did just want groups, the same approach would work. Just create a dimension with Groups as children of the User in a dimension. Then in your rule, you can use a similar approach
Building a dimension won't be viable if users frequently swap groups but in most cases, group membership is fairly static. If changes are needed during the day then the Dimension... Direct functions could be used.
Regards
Paul Simon
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: get a list of all groups the user is a member of. On the rules and without feeding.
An MDX for the groups a particular client is part of:
Code: Select all
Filter( [}Groups].Members, [}ClientGroups].( [}Clients].[ THE_CLIENT ] ) <> "" )
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
- 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: get a list of all groups the user is a member of. On the rules and without feeding.
Hi Wim
MDX is good for processes but Drg was asking about rules, and, unless I have missed some major IBM improvement, which is quite possible, as far as I know you can't use MDX in rules.
For my particular case, I needed to get not just the group membership, but the cost centres to which each group had access, so the dimension approach worked for me. However, there are always pros and cons to any approach.
Regards
Paul Simon
MDX is good for processes but Drg was asking about rules, and, unless I have missed some major IBM improvement, which is quite possible, as far as I know you can't use MDX in rules.
For my particular case, I needed to get not just the group membership, but the cost centres to which each group had access, so the dimension approach worked for me. However, there are always pros and cons to any approach.
Regards
Paul Simon
-
- MVP
- Posts: 3118
- Joined: Mon Dec 29, 2008 6:26 pm
- OLAP Product: TM1, Jedox
- Version: PAL 2.0.9.18
- Excel Version: Microsoft 365
- Location: Brussels, Belgium
- Contact:
Re: get a list of all groups the user is a member of. On the rules and without feeding.
Hi Paul,
You are right, if it's with rules then MDX won't fit. A TI process is definitely the way to go.
You are right, if it's with rules then MDX won't fit. A TI process is definitely the way to go.
Best regards,
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Wim Gielis
IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Re: get a list of all groups the user is a member of. On the rules and without feeding.
Setting the 'just do the simple thing with TI' aside:Drg wrote: ↑Wed Apr 08, 2020 8:28 pm I created a group and placed it the very first (well, or almost) and, using a recursive rule, I extended the accumulating rule along the flow variable from the last element to the first. Where for each element was taken its next element "|" the name of the group if the user is a member of it. so on the first element I got a list of all the groups. And this worked on a test bench unfortunately in the real model I got ~ 2000 groups and got a critical references evaluation stack error.
Try breaking your recursive rule into 100 line chunks and it'll work around stack overflow (stack is per rule line afaik), so you can do something like this:
[{1,...,99}, ] = S: do the recursive thingie
[{100}, ] S: = DB (99);
[{101,...,199}, ] = S: do the recursive thingie
[{200}, ] S: = DB (199);
etc
This is all a performance killer though as you're working with strings and they are not cached => so do the simple thing with TI
Cheers,
Y
-
- Regular Participant
- Posts: 159
- Joined: Fri Aug 12, 2016 10:02 am
- OLAP Product: tm1
- Version: 10.2.0 - 10.3.0
- Excel Version: 2010
Re: get a list of all groups the user is a member of. On the rules and without feeding.
Thanks you all.
Yuri that this my second variant but calculatintg time kill all idea.
Yep mayme this task resolving only TI.
Yuri that this my second variant but calculatintg time kill all idea.
Yep mayme this task resolving only TI.