Emailing reports to non OBI users using IBots

.

Sometimes companies need to distribute their OBI reports via email to their employees. For example the sales department needs to have the weekly sales report on their email inbox every Monday morning so they can start the day analysing the sales without having to download any report in OBI. This can be easily achieved by using IBots, as we can schedule them to send the reports to the users or groups that we want.

But let’s imagine now that as the sales figures are really good and the company decides to email them to organisations outside the company (newspapers, magazines…). In this case as we want to email reports to non OBI users we need to reconfigure the IBots as it is not configured by default to send reports by email to non OBI users.

In this article we are going to see how to send a report by email to users that do not belong to any of the defined OBIEE groups or are users by itself of OBIEE using iBots.

To do so we will need to do the following:

1) Setup the SA System subject area

2) Create a conditional request to filter the sender list

3) Create an IBot to schedule and send the desired report

 1. Setup the SA System subject area

The purpose of the SA System subject area is to be able to manage user profiles that do not belong to any OBIEE group.

First of all we need to create the physical table in our database:

Setup the SA System subject area

As we can see there are many fields that can be filled for each users, for our case we will use the following ones:

-          EMAIL: Stores the primary email address of the user

-          EMAIL_PRIORITY: Priority of the email; it can be ‘H’ for high, ‘N’ for normal or ‘L’. The default one ‘HNL’ means High, Normal and Low.

-          EMAIL_TYPE: Can be HTML or Text depending on the email client.

-          LOGON: The unique user ID of the user that will log on the system.

-          GROUP_ NAME: The name of the group that this user belongs to.

-          DISPLAY_NAME: The full name of the user.

Fill the table with the users that need to receive the email:

Setup the SA System subject area 1

It is very important that the logon is not administrator otherwise it will not work.

Next step is to import and map the table created in the OBI repository and rename the Presentation Layer columns with the exact names displayed below in the left side, otherwise it will not work:

Rename the Presentation Layer columns

Notice that you will need an auxiliary fact table to build the subject area. In the Business model layer duplicate the SA system user table and use it as a fact table by removing all columns except logon- Set the aggregation rule as count distinct. Create a new complex join with the dimension table to finish.

2. Create a conditional request to filter the sender list

To create the conditional request in OBIEE go to answers and in the main page click on the newly created SA System subject area to create a new report:

Create a conditional request to filter the sender list

Drag Email and Logon columns and filter the user that we want to receive the email:

Create a conditional request to filter the sender list 1

Save the report and the conditional request will be ready to use.

3. Create an IBot to schedule and send the desired report

Go to OBIEE and click on more products and select delivers in the top right menu, and click on create new iBot:

Create an IBot to schedule and send the desired report

 Go to conditional request tab:

Conditional request tab 

In this tab we select the request that will trigger the iBot. If the request returns rows the iBot will be executed otherwise the iBot will not run. Click on select condition… and select the conditional request created in the section before.

Go now to the schedule tab:

Schedule tab

Select start immediately to run the iBot automatically after saving it.

Click on recipients tab:

Recipients tab

In the recipients tab we can specify the users that will receive the report. In our case we want to use the conditional request as a source of senders. To do so enable this option on the right side Determine recipients from condition request. As a column containing recipients select Logon. Leave the second column containing recipients in (none).

Click on Delivery content tab and select the report to be sent by the iBot. Also you can add the headline of the email or a substitutive text in case the report returns no rows.

Finally click on Destinations tab:

Destinations tab

Select only Email below Specific Devices.

Save the iBot and it will automatically run as we scheduled it to start immediately. Go to the email account set to receive the email and check that the email has been received.

Conclusion

In this article we have seen an easy way to email OBI reports to non OBI users by setting up the System Subject area and using it later to schedule IBots to email the reports to the users that do not belong to any OBI group or are users by itself.

13 Responses to “Emailing reports to non OBI users using IBots”

  1. Mark says:

    Great article, thanks – I was wondering if OBIEE v11.1.1.6 or general OBIEE has made any further improvements to emailing non-OBI users?
    Any reason why BI Publisher could or couldn’t be used to email reports to non-OBI users?
    Apologies if I’m sounding a bit basic here :)

  2. Prakash says:

    Can we implement the SA system subject area for sending the emails to Non OBIEE users ,for ex. External Vendors.

    I’ve tried implementing the SA System subject area (i’m using OBIEE 10.1.3.4.2) but getting the following error.

    +++ ThreadID: 6 : 2012-09-17 11:42:53.364
    iBotID: /users/z077/_ibots/email test
    No devices for user: 2545

    where 2545 is the vendor Id defined in the LOGON column of the SA_SYSTEM_USER table.

    You have any idea how to achive this??

  3. Axel says:

    Hi Prakash,
    It seems that Ibots are not reading the device information from the SA_System table.
    Can you make sure that you have all the columns in your SA_SYSTEM table and that they have the correct names.
    You can check the column names in the article above. Also make sure that you have data for the Email columns for user 2545.

  4. Prakash says:

    Hi Axel,
    Thanks for the reply,we have reconfigured the SA System subject area.
    Now we are getting other errors in the sawlog0.log files.

    following are the errors.

    Type: Error
    Severity: 40
    Time: Tue Sep 25 11:50:37 2012
    File: project/websubsystems/ssquery.cpp Line: 564
    Properties: ThreadID-513;HttpCommand-UserPreferences;Proxy-Z077;RemoteIP-172.16.26.191;User-Z077;Impersonator-
    Location:
    saw.httpserver.request
    saw.rpc.server.responder
    saw.rpc.server
    saw.rpc.server.handleConnection
    saw.rpc.server.dispatch
    saw.threadPool
    saw.threads

    SQL Error retrieving users from System SA. Odbc driver returned an error (SQLExecDirectW).
    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred.
    [nQSError: 27005] Unresolved column: “Email Type”. (HY000)

    SQL Issued: SELECT Logon, Email, “Email Type”, “Email Priority”, “Cell Phone”, “Cell Phone Priority”, Pager, “Pager Priority”, Handheld, “Handheld Priority” FROM “SA System” WHERE Logon = ‘Administrator’

    —————————————

  5. Ashi says:

    Hi
    we have a clustered shared repository with OBIEE services( presentation,oc4j,server,scheduler) at two servers and RPD at shred location.
    But the Analytics application goes down with screen showin ‘LOGGING IN’ message though all the services are in active state but Repository becomes inaccessible in online Mode.The problem is oly solved by physical restart of server(not even restarting the services works).So please can some one help us out here.Which direction to look for RCA.
    Thanks in Advance !!

  6. Axel says:

    Hi Ashi,
    Did this problem start after implementing the solution given in this article?
    As a general advice I would take a look at the log files and also check if that happens with all users that try to access OBI or only for some of them.

    Axel

  7. Hariharan says:

    We want to create these users on the platform which will need to be inactivated after they have been configured.
    The inactivation is required to avoid any additional licensing costs.

  8. Hariharan says:

    Can anyone please give the possible suggestion for the below issue:

    Currently the users are unable to send iBots to many users they desired.

    After analysis from our end the problem is due to those users (recipients) not having an account and thus no mail address.
    We investigated possible solutions from our end and have the following proposal:

    We want to create these users on the platform which will need to be inactivated after they have been configured. The inactivation is required to avoid any additional licensing costs.

  9. Fernando says:

    Hi, Axel!
    I want to implement this solution with specific filter for each recipient.

    Example:
    Fernando only sees sales of regional 1.
    Lucia only see regional 2 sales.
    Carlos only see regional 3 sales.
    Maria sees only regional 4 sales.

    Wagner sees only regional 152 sales.

    The relationship Recipients x Rule is a table in DW database.

    How to implement this solution?

    Thanks!

  10. Steve says:

    Hi,
    We have added a user to the SA_SYSTEM table but despite refreshing caches, etc, they do not appear in the”Available Recipients” list. Is there another step we need to take to force the new user to appear?

    Thanks,

  11. Avinash says:

    Hi,

    We are using OBIEE 11.1.17.1 version, how to add column in recipients Tab,
    i’m unable to find that option.

    Thx,

  12. shashikant says:

    HI,

    I am doing the simlar thing in 11 g ,but when i run the agent i am getting error message below.

    Eventually succeeded, but encountered and resolved errors…
    Number of skipped deliveries: 0 of 0

    AgentID: /users/skandikonda/Agentsuserdetails
    Invalid subscribers skipped:
    xxx@xxx.com,XXX@XXX.com

  13. admin says:

    Hi shashikant,
    Could you provide the log files information from that error?
    Thanks,

    Axel

privacy policy - Copyright © 2000-2010 ClearPeaks

topnav