Transcription

Getting Started with Oracle Business Intelligence PublisherPurposeThis tutorial mainly covers how to get started with Oracle Business Intelligence Publisher, and how to createhighly formatted reports using BI Publisher based not only on conventional data sources (such as OracleDatabase) but also on various other data sources (such as Web Services, RSS feeds, and so on). It also covershow to create reports with parameters and list of values (LOVs).Because BI Publisher is integrated with Oracle Business Intelligence Enterprise Edition, you will learn how tocreate reports based on Oracle BI subject area and Oracle BI Answers request. You will also learn how to createRTF templates in MS Word by connecting to BI Publisher, and publish these templates for the reports to viewdata in BI Publisher. You will learn to configure the BI Publisher scheduler to schedule reports and view thesaved data.Time to CompleteApproximately 1 hourTopicsThis tutorial covers the following topics:OverviewScenarioPrerequisitesGetting StartedLogging In to Oracle BI PublisherSetting PreferencesCreating a BI Publisher Report Based on Oracle DatabaseCreating Report Layouts Using BI Publisher Desktop (Template Builder) in MS WordCreating an RTF Template with a Table and a ChartPublishing the Template for a BI Publisher ReportFormatting Template Using MS Word Native FeaturesCreating and Publishing Multiple Templates for a ReportViewing Report Data in BI Publisher Using TemplatesCreating a Report with Parameters and List of ValuesScheduling BI Publisher ReportsConfiguring the BI Publisher SchedulerScheduling and Viewing Saved OutputCreating BI Publisher Reports Based on Various Data SourcesCreating a Report Based on Oracle BI Answers RequestCreating a Report Based on Oracle BI Server Subject AreaCreating a Report Based on Web ServicesCreating a Report Based on HTTP Data SetCreating a Report Based on File Data SetSummaryRelated InformationViewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: Thisaction loads all screenshots simultaneously, so response time may be slow depending on your Internetconnection.)Note: Alternatively, you can place the cursor over each individual icon in the following steps to load and viewonly the screenshot associated with that step.OverviewOracle BI Publisher (formerly known as XML Publisher) is a revolutionary reporting and document outputmanagement solution from Oracle. Oracle BI Publisher report formats can be designed using Microsoft Word orAdobe Acrobat, the tools that are easy to use, and most users are already familiar with. Oracle BI Publisher alsoallows you to create reports from multiple data sources.Oracle BI Publisher can be used as a stand-alone reporting product or integrated with the Oracle BusinessIntelligence Suite Enterprise Edition. You will start by logging in to BI Publisher and create reports fromvarious data sources, such as Oracle Database, Web services, RSS feeds, and Files. You will also createparameterized report that uses LOVs.Because BI Publisher is integrated with Oracle BI Enterprise Edition, you can also create the BI publisherreports based on Oracle BI Answers request, and also based on Oracle BI Server subject area. However, thesetopics are briefly discussed in this tutorial. For more details, you can refer to the OBE titled "Integration ofOracle BI Publisher with Oracle Business Intelligence Enterprise Edition, 10.1.3.2."Also, you will be guided through the steps to create and publish RTF templates in MS Word by connecting toBI Publisher. You will also schedule the report, and view the saved data.Back to Topic ListScenarioYou will be using various data sets for this OBE. For example, you will create a simple employee salary reportbased on the human resources (HR) sample schema of the Oracle Database. For the File data set, you will usethe demo files and templates that are provided with BI Publisher. For the BI Answers request and BI Serversubject area, SH.rpd is used, which is based on the SH schema of the database, and so on.To continue with the steps listed in the topics, you should have installed the required software and performedthe other setup as mentioned in the following topic, Prerequisites.Back to Topic ListPrerequisitesBefore starting this tutorial, you should:1. Have access to or have installed Oracle Database 10g (preferably version 10.2)

2. Have access to or have installed the sample schemas (HR, OE, and SH specifically)3. Have access to or have installed Oracle BI Publisher and Oracle BI Publisher Desktop 10.1.3.2.You can install BI Publisher Desktop by clicking the Template Builder link in BI Publisher:When you have successfully installed BI Publisher Desktop, the BI Publisher menu and BIPublisher toolbars are displayed in MS Word.Note: Oracle BI Publisher Desktop was formerly known as Template Builder, and is mainlyused for building the RTF templates for BI Publisher reports in MS Word.4. Have created a user for installing the BI Publisher scheduler schema tables by performing thefollowing:Create a BIPSched user with the required privileges in the database by logging in to SQL* Plusas the sys or system user:SQL create user bipsched identified by bipscheddefault tablespace userstemporary tablespace tempquota unlimited on users;

SQL grant connect, resource to bipsched;Note: The name of the user can be any appropriate name.5. Have set up the proxy parameters (to avoid any firewall problems with the Web Services andRSS data set reports) in the xmlpserverstart.bat file or the oc4j.cmd file as per your installation.Modify the following line, which defines JAVA HOME. (Observe the proxy parameters inbold.): "%JAVA HOME%\bin\java" %JVMARGS% -jar -Dhttp.proxyHost myproxy.mycompany.com -Dhttp.proxyPort Port "%OC4J JAR%"%CMDARGS%Restart xmlpserver or oc4j as the case may be.If you want to create reports based on Oracle BI Answers request and Oracle BI Server subject area,then you should also:1. Have access to or have installed Oracle BI EE 10.1.3.2 including Oracle BI Publisher and OracleBI Publisher Desktop 10.1.3.2.You can follow the instructions as listed in the OBE " Installing the Oracle Business IntelligenceSuite Enterprise Edition on Windows."2. Have created an ODBC connection to the Oracle Database, and set up the SH.rpd metadatarepository by following the steps listed in the OBE "Creating Interactive Dashboards and UsingOracle Business Intelligence Answers." Also, it is strongly recommended that you go throughthis tutorial to know the steps involved in creating an Answers request, creating a dashboardpage, and so on.3. Have set up the integration between BI Publisher and Oracle BI EE:If you have included BI Publisher Enterprise, when you installed Oracle BI Enterprise Edition,then Oracle BI installer will set up the integration with the Presentation Services, by setting hostname, port, and URL values. However, you need to set the Administrator password from the BIAdministration tool as follows:1. Select All Programs Oracle Business Intelligence BI Administration.2. Select Open Online. ( To open SH.rpd online, enter Administrator as User name andleave the Password field blank.)3. Select Manage Security Users Administrator, and check out the Administrator user.4. In the properties, set the password for the Administrator user and click OK.

If the Integration with Presentation Services is not set up at the time of Oracle BI EE installation,you can also set up this integration from BI Publisher as follows:1. From the Admin page, under Integration, select Oracle BI Presentation Services.2. Enter the following information about your BI Presentation Services server: Server Protocol – Select http or https Server Version – Select v4 Server – Enter the server host name (for example, server01.mycompany.com) Port for the server – The default port is 9704. Administrator Username and Password URL Suffix – The default value is analytics/saw.dll.

Back to Topic ListGetting StartedFor getting started with BI Publisher, perform the steps listed in each of the following subtopics:Logging In to Oracle BI PublisherSetting PreferencesBack to Topic ListLogging In to Oracle BI Publisher:To log in to BI Publisher, perform the following steps:1. Enter the URL for BI Publisher in a browser window which is of the format:http:// hostname : port /xmlpserver/ (for example, http://localhost:9704/xmlpserver/)The Log In screen for the BI Publisher appears. Enter Administrator as the Username andPassword. Click Sign In.

Note: Ensure that you have the correct password for the Administrator user in BI Publisher, elseyou may face problems in logging in to BI Publisher.Also, on Windows, depending on the type of installation you have done, you can invoke BIPublisher as follows: If you have installed BI Publisher along with Oracle BI Enterprise Edition, then you canselect All Programs Oracle Business Intelligence BI Publisher from the Start menu.

If you have installed BI Publisher in stand-alone mode, then select All Programs Oracle OracleHome BI Publisher Server from the Start menu.OracleHome - refers to Oracle Home name, where you installed Oracle BI Publisher.2. This displays the Welcome screen for BI Publisher.

Note: As you have logged in as the Administrator, the welcome page displays the Reports,Schedules, and Admin tabs. For the users who do not have administrator privileges, the Admin tabis not displayed.Back to TopicSetting PreferencesTo change the preferences, perform the following steps:1. Click Preferences link. (highlighted in the screen below):

The Preferences screen is displayed with the General Preferences and Account tabs.2. Observe the options on the General Preferences tabbed page, and select the appropriate optionsfor UI Language, Reports Locale, Reports Time Zone according to your requirements.Click Apply.

Note: You can also enable the accessibility option.3. To edit the password settings for the Administrator account, click the Account tab.Change the password for Administrator as required by entering the current and new passwords inthe screen.Click Apply to save the preference settings.Note: If you have installed BI Publisher along with Oracle BI Enterprise Edition, for theintegration between these to work, for any given user account , you should set the samepassword in BI Administration Tool and in BI Publisher. (Refer to Prerequisites section forsetting up integration between BI Publisher and Oracle BI EE.)Back to TopicBack to Topic List

Creating a BI Publisher Report Based on Oracle DatabaseIn this topic, you will create an employee salary report based on the HR schema of Oracle Database. To create aBI Publisher report based on a query directed to Oracle Database, perform the following steps:1. Before you create a query based on Oracle Database, you need to define the connection details inthe Admin tab.Click the Admin tab. Click JDBC Connection found under the Data Sources section of thepage.2. In the JDBC Data Sources screen that is displayed, click demo to edit the connection details forthis data source.

Note: You can also add a new data source and define the connection details.3. In the Update Data Source screen, enter the following details:Connection String: This is of the format: jdbc:oracle:thin:@ Host name : Port : SID ,for example, a sample connection string may look like: jdbc:oracle:thin:@myhost:1521:orclUser Name: oePassword: oeDatabase Driver Class: oracle.jdbc.driver.OracleDriver (This is the default.)Do not click Cancel or Apply.

4. After entering the above details, click Test Connection.When the confirmation message is displayed saying that the connection is establishedsuccessfully, click Apply to save the connection details.

Note: OE schema also gives access to HR schema.5. Click Reports to go back to the Reports page.Click My Folders , and click Create a New Folder link (found in the Folder and ReportTasks section).Enter Learn as the folder name and click Create. (You will save the reports you create in thistutorial in this folder.)

6. To create report based on Oracle Database, navigate to My Folders Learn, and click Create aNew Report.Enter Based on OracleDB as the name of the report, and click Create.

7. The report is displayed in the Learn folder in BI Publisher.Click the Edit link under the name of the report to open the report in Edit mode.8. In the General Settings section of the Report Properties, select demo from the Default DataSource drop-down list.Click the Saveicon (found at the top-left corner) to save the changes.

9. Click Data Model and click New to define the data source for this report.10. In the Data Set screen that appears,ensure that SQL Query is selected as Type, anddemo is selected as Data Source.Select the Cache Results check box,and click Query Builder to define the query.

11. The Query Builder screen appears, ensure that HR is selected from the Schema drop-down list.This displays objects from HR schema on the left.Click EMPLOYEES and DEPARTMENTS tables to add them to the Model canvas on theright.

12. Define a join between these two tables as follows:Click the box beside the DEPARTMENT ID column in the DEPARTMENTS table. (See thefollowing screens.)Similarly click the box beside the DEPARTMENT ID column in the EMPLOYEES table.These boxes when marked for joins turn to light gray.Also note that a fine line joining the tables appears in the Model canvas.Hint: Take the help of the screens below:

13. Select the following columns from the tables (by selecting the check boxes beside the columnnames):-Select FIRST NAME, LAST NAME, HIRE DATE, and SALARY columns from theEMPLOYEES table-Select DEPARTMENT NAME from the DEPARTMENTS table. (See the following screen.)

14. Click Conditions, and move the DEPARTMENT NAME, to appear first in the list (as shownin the screen below).You can use these iconsbeside each column to move the columns up or down.Similarly, move SALARY and other columns, so that the final list appears like this:

15. Click Results to see how the query results appear in Query Builder.Click Save to save the query.16. This will take you back to the Data Set screen, with the query you created reflected in the SQLQuery text area.Click Saveto save the BI Publisher report.

17. If required, you can further edit the query manually. For example, instead of displaying theFIRST NAME and LAST NAME columns separately, you may want to concatenate both thesecolumns and display this as a single column EMPLOYEE NAME.So you can modify the query as:select DEPARTMENTS.DEPARTMENT NAME as DEPARTMENT NAME,EMPLOYEES.FIRST NAME ' ' EMPLOYEES.LAST NAME as EMPLOYEE NAME,EMPLOYEES.HIRE DATE as HIRE DATE,EMPLOYEES.SALARY as SALARYfrom HR.DEPARTMENTS DEPARTMENTS,HR.EMPLOYEES EMPLOYEESwhere DEPARTMENTS.DEPARTMENT ID EMPLOYEES.DEPARTMENT ID

Click Saveagain to save the changes to the query.Note: Ensure that a space is included in the query (as in the example) between theFIRST NAME and LAST NAME columns in the concatenation string ofEMPLOYEE NAME. Also, note that once you have edited the query manually, you will not beable to edit the query in Query Builder.18. Click View to view the report. Because you have not defined a template, you can view onlyXML data.Click View again to view the XML data.

Note: You may not be able to interpret the XML data, but you may want to check whether the BIPublisher report is returning some valid data by viewing the XML data before creating atemplate for the report. Creating and publishing templates is covered in the next topic.Back to Topic ListCreating Report Layouts Using BI Publisher Desktop (Template Builder) in MS WordIn this topic, you create RTF template for a BI Publisher report and publish it. You will also work with multipletemplates for a report, and view the report data using these templates in various formats.Note: All the subtopics in this topic are continuous, so follow the instructions, and do not close any applicationsor windows as mentioned in the steps.Creating an RTF Template with a Table and ChartPublishing the Template for a BI Publisher ReportFormatting the Templates Using MS Word Native FeaturesCreating and Publishing Multiple Templates for a ReportViewing Report Data in BI Publisher Using Templates

Creating an RTF Template with a Table and Chart1. Start the MS Word application from the program menu. From the Oracle BI Publisher menu, select LogOn.Note: The Oracle BI Publisher menu is displayed in MS Word only when you have successfully installed theOracle BI Publisher Desktop as mentioned in step 3 of the Prerequisites section. Also, observe the BIPublisher toolbar in MS Word, with the Data, Insert, Preview, Tools, and Help menus. Observe the variousoptions in these menus.2. In the Login screen that appears, enter Administrator as the Username and Password, and click Login.The first time when you connect, you may be asked for the report server URL, enter the URL which is of theformat:http:// Host :9704/xmlpserver

3. This displays the Open Template window. Ensure that BI Publisher is selected from the Workspace dropdown list.Navigate and select the Based on Oracle DB report that you have created from My Folders Learn. ClickOpen Report.Note: When you open the report you will not see any data in the MS Word document, but this will load the

XML data definitions from the query to facilitate the defining of a template.4. In the Word document that loads this report, select Insert Table Wizard on the BI Publisher toolbar todefine a format for the table data in the query.5. This displays the Table Wizard. Select Table and click Next.

6. In the next step, ensure that ROWSET/ROW is selected as the Grouping Field, and click Next.7. In the next step, clickClick Next.to include all the available columns in the table. (See the screen below.)

8. In the next step, select Department Name from the Group By drop-down list, ensure that the Group aboveoption is selected, and accept the defaults for other options. Click Next.9. In the next step, select Salary from the Sort By drop-down list, and select the options Descending andNumber to sort the Salary column in descending order. Click Next.

10 In the next step you can edit the labels of the columns as required. Accept the defaults and click Finish.The template you created looks like this:Important Note: The BI Publisher Desktop Template Builder has just created a table for you. Notice thewords and letters with the gray background. These are called form fields. Form fields are Word objects thatallow you to reference other data (for example, a mail merge letter). BI Publisher uses form fields in twoways: First is to reference data fields from the report definition (like YEAR and MONTH).The second use is to embed instructions that control how the data fields will be laid out (like GGroup By, F- For Each , and E- End).If you are curious to know what these instructions are, double-click the form field and view the Help text. Itis important to treat these form fields carefully and not accidentally delete or move them. Doing so willchange the layout of the table in your report. Also, you can add or modify your own form fields with XSLcommands to do more sophisticated things with the table layout.

Also, you can additionally use MS Word's native formatting features on this template, such as changing thebackground and text colors, adding a title, and so on. This is covered in the next topic.11 You can also add a chart in the template. For example, you can add a chart to display the average employee. salaries by department in this report.To add a chart to the template, select Insert Chart.12 In the Chart window that appears, define the graph characteristics by following the instructions listed below. carefully:1. Drag Salary to the Values field