OBIEE – Configuring the publisher/scheduler for MySql

Publisher can be used to share and distribute reports. Scheduler is a quartz based scheduler for reporting jobs. In this post we will look at sending a report to a user via mail using the scheduler and publisher.
1. Login to BI publisher. If you face any problems during login, use the following troubleshooting options
2. Note that the scheduler tab will be inactive.
3. To configure the scheduler. Open the Administration tool. Connect to the repository. Click on Manage->Jobs to launch the Job Manager.
4. In the job manager, Click on File -> configuration options.
5. Click on the Scheduler tab on the tab.
6. Click on the Database tab on the second row. Fill in the relevant information as shown in the screenshot. The DSN can be created as shown in earlier posts.

7. Click on the general tab on the second row and fill in the username and password. Check the scheduler script path and default script path (the default values should be ok).

8. Click on OK to close the job manager dialog.
9. Open BI publisher. Click on the Admin tab (If admin tab is not visible look at the links posted in step 1).
10. In the datasources section of the admin tab click on the link that says ‘JDBC connection’.
11. Click on the tab that says JDBC.
12. The default jdbc connections will be visible. We need to add the mysql connection to this list. This link explains how to do that
13. Once that is done, go to the jdbc tab and create a new datasource for mysql.
. The radio button that says ‘user proxy authentication’ should not be checked.
14. Next, click on the Admin tab in BI publisher and select that link that says ‘Scheduler configuration’ in System maintenance section. Click on the tab that says scheduler configuration. Fill in the mysql jdbc connection settings as shown.

15. click on ‘test connection’. Once the connection is successful. click on install schema. This will install the scheduler related schema in the foodmart database of mysql.
16. We need to create some more tables so that the scheduler service works. go to /server/Schema location. There will be a file called SAJOBS.Oracle.sql Modify this file to suit Mysql and use this file to create tables in the foodmart schema of mysql.
17. we will now attempt to start the scheduler service. stop the BI server . start the scheduler service followed by the BI server (make sure mysql is running). It is important to start the scheduler service before BI server otherwise the schedule tab in BI publisher will not be enabled.
18. We will now create the report. Click on the Reports tab in BI publisher.
19. Click on create a new report. Once the report is created, open it in edit mode.
20. Add a data model to the report.
21. In the new data model page, select SQL query as the Type. User oracle BI EE as the datasource. click on query builder to build the report query.

22. click on layout and create a new layout based on default data model.
23. save the report. click on view. the report should be visible.
24. To schedule the report, click on schedule when the report is in view or edit mode.
25. Fill in the relavant details. Use E-mail as notification channel. enter the destination e-mail address in e-mail delivery.
26. Before scheduling the report, however, the e-mail options need to be set in BI publisher. To do so, click on the admin tab and click the link that says Email in the delivery section. Click on Add server and add the connection info for the smtp server that will be used for sending the mail.
27. once scheduling is complete, look at the schedules tab for a list of jobs that have been scheduled.

Leave a Comment