1 2 Previous Next 17 Replies Latest reply on Sep 14, 2018 9:40 AM by Robert Angel

    Oracle Analytics Cloud - iBot issue


      Hi Guys


      I have a basic req. but somewhere i am doing wrong and trying to know where i missed out. I m using Oracle Business Intelligence Product Version

      This is regarding iBots where i have to send the same report to users listed in that.

      Nameemail IdEmployee IDRevenue


      Let's say the above is the custom report i got and requirement is Amar has to see in excel only his record, Akbar has to see in excel only his record,Anthony has to see in excel only his record. They should not see other records.

      Created an agent --> running as recipient -->  In Condition tab, created a condition --> In Deliver content tab, selected content = Condition Analysis, format = Excel 2007+ -->

      In the recipient tab, selected the check box "Get recipients from Analysis used in agent condition" and in settings, selected "Email Id" column for Column containing Receipients

      also selected the check box "Only Return Rows relevant to the user running the agent". Saved the Agent and ran the agent but getting all rows to everybody.


      Can someone advise where i was wrong

        • 1. Re: OBIEE - iBot issue
          Robert Angel

          Do you have row level data security applied that means that when the afore mentioned users run the report manually that they currently only see their own data?

          • 2. Re: OBIEE - iBot issue

            Hi Robert

            Yes we have row level data security and when user of one country logs in he sees the data  of that region / country data only.

            • 3. Re: OBIEE - iBot issue

              Hi All

              Can anybody shed some light on the above issue please


              Thank you in advance

              • 4. Re: Oracle Analytics Cloud - iBot issue
                Christian Berg

                Have you looked at the query that's being run?


                All your setup doesn't matter if the analysis you are sending out isn't filtering for something like "someattribute=USER" or "someattribute=EMAIL". And I don't see any of that in the list of things you did.

                1 person found this helpful
                • 5. Re: Oracle Analytics Cloud - iBot issue
                  Robert Angel

                  Hi Christian,



                  I have not used this functionality for some time in OBIEE - generally for bursting it tends to be BIP - but I had understood that the solution works as described by Gianni Ceresa - in this detailed explanation from this post -> Re: Condition Based Agent


                  Quote: -


                  "Hi Ebin,

                  In the tables (S_NQ_JOB & S_NQ_JOB_PARAM) no real change, it's an attribute in the XML of the agent to tell the system it must be executed "as recipient" and not with a single fixed account.


                  The "run as recipient" is the only possible way to send different content to multiple users with 1 single agent. Let's say for example that you want to send to your country managers the amount of sales at the end of the month. Each manager must see only the data of his country.


                  If you make an agent executed with a single account (not the "as recipient") you will not be able to adapt the content of the report sent by the agent based on the recipient, so everybody will receive the same data, all the rows of the report.


                  To be able to send only the relevant data to each country manager you need to have a way to filter the "rows" of your report, to say which rows to keep and which rows to not retrieve because not related to the correct country.

                  For that kind of filter the most common solution is row-level security in the RPD (saying that for user A only Country A is visible etc.) or some filtering in the report itself based on session variables (related to the user connected) or using the "login" as condition with the value stored in a column (the case of the OP here with column D).

                  So now you have a report that based on the user connected to OBIEE display different information.


                  How to send this report by email keeping the filters? The agent must be executed as every single different user, it's where "run as recipient" is required.


                  In the OBIEE logs you will see the queries of the agent will be executed by the users defined as recipient, the agent will work in a same way as an "act as" connecting to every single recipient to run the query (so you will see many times the same query instead of just one time).


                  You can have more things stored in S_NQ_INSTANCE & S_NQ_ERR_MSG as running as recipient does many times the query, so the logging is impacted."


                  - End Quote (bold emphasis added by me to highlight the question I am about to ask)



                  I know the answer is now three years long in the tooth, but is this no longer the situation, do you have to explicitly impose additional logic to filter by to recreate what row level security would do?

                  • 6. Re: Oracle Analytics Cloud - iBot issue
                    Christian Berg

                    "or some filtering in the report itself based on session variables (related to the user connected) or using the "login" as condition with the value stored in a column"


                    ^--- like "Table"."Resposible Manager Username" = valueof(NQ_SESSION.USER)


                    Or what's your question there?

                    • 7. Re: Oracle Analytics Cloud - iBot issue
                      Robert Angel

                      Thanks Christian, no criticism intended or implied, just trying to understand if the position has changed as it was back in 11.x versions I did this last...



                      My question is that the OP seems to be saying; -


                      a. That they have row level security in their subject area

                      b. They are using 'run as recipient'


                      In these circumstances I thought that was sufficient to get data specific to the end user delivered?  - and cf above - unless I am misreading it - Gianni seems to confirm my understanding?

                      • 8. Re: Oracle Analytics Cloud - iBot issue
                        Christian Berg

                        No offense taken I was just really confused.


                        Their "row-level  security" seems to be country related - or at least that's what he laims / states in a very abbreviated form. So that would do zero for personalized data.


                        "run as recipient" as a functionality "only" does the implict act-as login that Gianni mentioned. "only" because...that gives you all the access to user-related functions and variables. That's the whole point of it. BUT it doesn't automagically apply anything that's not either modeled (row-level security / data filters in the RPD) or as filters in the analysis.




                        I'm trying to travel back since SUnday and keep getting stuck so my attention span is somewhat reduced and I may have misread your question.

                        • 9. Re: Oracle Analytics Cloud - iBot issue
                          Robert Angel

                          Thanks - and yes you confirm how I had understood it, and I think it is me who is being slow in fully understanding what the OP has posted.


                          I have just re-read it and I see that the OP seems to be expecting 'run as recipient' to 'know' that it should add a filter on the e-mail address, which is not what happens - unless there is row level security which exists before the agent to apply this kind of filter.


                          I think I need to go on an 'understand the OP' course....



                          thank you for confirming what I thought I understood - and it turns out I do.

                          • 10. Re: Oracle Analytics Cloud - iBot issue
                            Robert Angel




                            don't know if you are following what Christian has advised / my dialogue with Christian?


                            If not then; -


                            1. Run as recipient emulates what would happen if that user logged in and run the report as himself - he would see all rows - yes?

                            2. You need to build some extra logic into the report to filter by user


                            So, you would need (say) a hidden row with the username in it and a filter to use the function as described by Christian as described previously; -


                            "YourTable"."YourUsername" = valueof(NQ_SESSION.USER)



                            Then it would do what you are hoping for.


                            Hope this clarifies?

                            1 person found this helpful
                            • 11. Re: Oracle Analytics Cloud - iBot issue
                              Christian Berg

                              Robert Angel wrote:


                              I think I need to go on an 'understand the OP' course....


                              Brave new world. Normally one would expect a proper explanation to begin with :-P

                              • 12. Re: Oracle Analytics Cloud - iBot issue

                                Hi Rob/Chris

                                Thank you for all your discussion about this. Yes we have Row-Level security implemented and currently what i have done to  make users happy is "Run as Recipient" at General section and in Recipients tab, added manually "Amar, Akbar, Anthony" full name.  This gives only their records to view for now (i.e. Amar will see 1 record, Akbar will see 1 record, Antony will see 1 record) . For time being, this is okie 


                                But i know this wont help if i got 100's of users exists and trying to understand what should be my next step to ensure OBI sends the alert/mail to individual users with only their records


                                Thank you

                                • 13. Re: Oracle Analytics Cloud - iBot issue
                                  Christian Berg

                                  You seem to have missed my whole point.


                                  "Run as" == an "Act As" == has user variables initialized == analysis can be filtered on xyz=nqsession.user!

                                  • 14. Re: Oracle Analytics Cloud - iBot issue
                                    Robert Angel

                                    What you describe - added manually - is how it works.


                                    There is no standard means to automatically send to all users who exist using 'Run As Recipient' - other than encouraging them to self serve - the majority of the power of OBIEE is in interaction; drill down, personalisation, resort, repivot, etc etc; - flat externalised reports in whatever format lose that advantage and if we are talking of excel then also run the risk of users correcting corrupting the figures...

                                    1 2 Previous Next