Title: Oracle BI XML Publisher Installation
1Oracle BI (XML) Publisher Installation
- John Chemers
- AOL LLC
- TimeWarner Inc.
- NCOAUG/MWRUG/ORACLE CONFERENCE
- Monday, March 12, 2007
2Types of Paths in this Presentation
3Your Presenters
- John Chemers
- Technology Architect/DBA
- johnchemers06_at_aol.com
- (847) 858-8109 cell
- Marybeth Hinkel
- PeopleSoft Support
- mhink44_at_aol.com
- (630) 629-5613
4Overview
- Oracle BI (XML) Publisher Installation
5Agenda
- Database Configuration
- XML Publisher User, Grants, Enable
- Database Links
- XML Publisher Server Configuration
- Quartz Scheduler Configuration
- Database Configuration
- XML Publisher Client Configuration
- Internet Explorer Configurations
- Enable Java, JavaScript
- XML Publisher Presentations
6 7Database Architecture
- Oracles Database Architecture
- My Database Architecture
8Oracles Database Architecture
9My Database Architecture
10 11Database Configuration
- Oracle 9i Configuration
- Oracle 9i 10g Sample Schemas
- Database Configuration
- XML Publisher User
- Enable Report User and Grant Privileges
- Enable Sample Report User
- Database Links
- Database Link to Oracle DB
- Database Link to Microsoft DB
12Oracle 9i Configuration
- Activate Oracle JServer/JVM option
- Increase INIT.ORA param values
- java_pool_size (gt20M)
- shared_pool_size (gt50M)
- Run ORACLE_HOME/javavm/install/initjvm.sql as
SYS AS SYSDBA to install JServer Option - Grant JAVAUSERPRIV to users that use Java
- SQLgt GRANT JAVAUSERPRIV TO SCOTT
- Use rmjvm.sql to deinstall JServer option
13Oracle 9i 10g Sample Schemas
- Sample Schemas
- Human Resources (HR)
- Order Entry (OE)
- Online Catalog (OC) subschema for OE
- Multimedia datatypes (Product Media, PM)
- Queued Shipping (QS) and subschemas
- Sales History (SH)
14Create Tablespace for Sample Schemas
- CREATE TABLESPACE "EXAMPLE"
- LOGGING DATAFILE 'C\ORACLE\PRODUCT\10.2.0\ORADATA
\REPT\example01.dbf' - SIZE 50M REUSE
- AUTOEXTEND ON NEXT 50M MAXSIZE 1000M
- EXTENT MANAGEMENT LOCAL
- SEGMENT SPACE MANAGEMENT AUTO
15Create Sample Schemas-Oracle 9i
- sqlplus /nolog
- SQLgt _at_?/demo/schema/mksample manager
change_on_install hr oe pm ix sh bi example temp
ORACLE_HOME/demo/schema/log/ - The entire process takes about 30 to 45 minutes
to complete.
16Create Sample Schemas-Oracle 10g
- Use DCA Database Configuration Assistant
- The entire process takes about 30 to 45 minutes
to complete.
17Database Configuration
- Configure tnsnames.ora
- Configure listener.ora
- Reload listner
- TNSPING
18Configure tnsnames.ora
- REPT
- (DESCRIPTION
- (ADDRESS_LIST
- (ADDRESS (PROTOCOL TCP)
- (HOST ltYOUR HOSTgt)
- (PORT 1521))
- )
- (CONNECT_DATA
- (SERVER DEDICATED)
- (SERVICE_NAME REPT)
- )
- )
19Configure listener.ora
- SID_LIST_LISTENER
- (SID_LIST
- (SID_DESC
- (SID_NAME PLSExtProc)
- (ORACLE_HOME C\oracle\product\10.2.0\db_1
) - (PROGRAM extproc)
- )
- (SID_DESC
- (GLOBAL_DBNAMErept)
- (ORACLE_HOME C\oracle\product\10.2.0\
db_1) - (SID_NAME rept)
- )
- )
- LISTENER
- (DESCRIPTION_LIST
- (DESCRIPTION
- (ADDRESS (PROTOCOL TCP)
- (HOST ltYOUR HOSTgt)
20Reload Listener and TNSPING
- lsnrctl reload
- tnsping rept
21- Sample User Configuration
22Enable Sample Report User
- C\gt sqlplus sys/password_at_REPT as SYSDBA
- SQLgt alter user OE account unlock
- SQLgt ALTER USER OE IDENTIFIED BY password
23 24Identify location of Data Files
- C\gtsqlplus sys/password_at_REPT as sysdba
- SQLgt select file, substr(name,1,60) from
vdatafile order by file
25Create Tablespace
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt CREATE TABLESPACE SYSADM
- DATAFILE 'C\ORACLE\PRODUCT\10.2.0\ORADATA\REPT\sy
sadm01.dbf' - SIZE 20M EXTENT MANAGEMENT LOCAL AUTOALLOCATE
26Create Custom Report User
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt CREATE USER sysadm IDENTIFIED BY sysadm
- TEMPORARY TABLESPACE temp
- DEFAULT TABLESPACE sysadm
- QUOTA UNLIMITED ON sysadm
27Grant Connect Access
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt GRANT connect TO sysadm
28Grant Create/Select Privileges
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt GRANT SELECT ANY TABLE TO SYSADM
- SQLgt GRANT CREATE TABLE TO SYSADM
- SQLgt GRANT CREATE VIEW TO SYSADM
- SQLgt GRANT CREATE SEQUENCE TO SYSADM
29Grant Java Privileges
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt GRANT JAVAUSERPRIV TO SYSADM
30 31Database Link Creation
- Grant Privileges
- Oracle DB to Oracle DB Configuration
- Create Database Link
- Create Views to Oracle-DB Tables/Views
- Oracle DB to Microsoft DB Configuration
- Oracle Heterogeneous Services ODBC agent
- Create Database Link
- Create Views to Microsoft DB Tables/Views
32 33Grant Privileges
- SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
- SQLgt GRANT CREATE DATABASE LINK TO SYSADM
- SQLgt GRANT CREATE SYNONYM TO SYSADM
- SQLgt GRANT EXECUTE ANY PROCEDURE TO SYSADM
34- Database Link Creation-Oracle DB
35My Database Architecture
36Create Database Link-Oracle DB
- SQLgt CONNECT SYSADM/SYSADM_at_REPT
- SQLgt CREATE DATABASE LINK SAPDB
- CONNECT TO SAPADM IDENTIFIED BY password
USING 'SAPDB'
37Create Views-Oracle DB
- create force view SAP_TABLE_NAME
- as select from SAP_TABLE_NAME_at_sapdb
- create force view SAP_VIEW_NAME
- as select from SAP_VIEW_NAME_at_sapdb
38My Database Architecture
39- Database Link Creation-Microsoft SQL Server DB
40My Database Architecture
41Oracle Heterogeneous Services ODBC
agent-Microsoft DB
- Make a connection from Oracle to SQL Server using
Oracle Heterogeneous Services ODBC agent
42Define a Data Source Name (DSN) for SQL Server
- Open Data Sources (ODBC) in Control Panel
- Add a new DSN Data Source and use the SQL Server
driver - Choose a name for the Data Source i.e MYAWDSN
- Accept (local) for SQL server
- Change default database from master to database
like AdventureWorks
43Create a Oracle Heterogeneous Services
Initialization File-Microsoft DB
- Copy sample HS init file
- Copy ORACLE_HOME/hs/admin/inithsodbc.ora sample
file to initMYAWDSN.ora - Modify parameters
- HS_FDS_CONNECT_INFO MYAWDSN
- HS_FDS_TRACE_LEVEL OFF
44Configure listener.ora - Microsoft DB - Part 1
- Set Listener name to LISTENERMYAWDSN
- Set Port number to 1522
- Set SID_NAME to MYAWDSN
- Set ORACLE_HOME location
- Set PROGRAM hsodbc
45Configure listener.ora Microsoft DB - Part 2
- LISTENERMYAWDSN
- (ADDRESS_LIST
- (ADDRESS(PROTOCOLtcp)(HOSTlocalhost)(PORT
1522)) - (ADDRESS(PROTOCOLipc)(KEYPNPKEY)))
- SID_LIST_LISTENERMYAWDSN
- (SID_LIST
- (SID_DESC
- (SID_NAMEMYAWDSN)
- (ORACLE_HOME c\oracle\product\10.2.0\d
b_1) - (PROGRAMhsodbc)
- )
- )
46Configure tnsnames.ora Microsoft DB - Part 1
- Create TNS entry named MYAWDSN
- Set Port number to 1522
- Set SID to MYAWDSN
- Set HS parameter to OK
47Configure tnsnames.ora Microsoft DB - Part 2
- MYAWDSN
- (DESCRIPTION (ADDRESS(PROTOCOLtcp)(HOSTlo
calhost)(PORT1522)) (CONNECT_DATA(SIDMYAWDSN
)) - (HSOK)
- )
48Reload listener Microsoft DB
49Create a Database Link Microsoft DB
- SQLgt CONNECT SYSADM/SYSADM_at_REPT
- SQLgt create database link myawdsn
- connect to sa identified by password using
'MYAWDSN'
50Create Views-Microsoft DB
- create force view MS_TABLE_NAME
- as select from MS_TABLE_NAME_at_myawdsn
- create force view MS_VIEW_NAME
- as select from MS_VIEW_NAME_at_myawdsn
51My Database Architecture
52- XML Publisher Enterprise Installation
53XML Publisher Enterprise Installation
- Specify Home Details
- Name XMLP_HOME_1
- Path C\Oracle\product\XMLP\5.6.2
- XMLP Setup Information
- C\oracle\product\XMLP\5.6.2\xmlpserver\setupinfo.
txt - XMLP URL
- http//ltYOUR HOSTgt15101/xmlpserver
- Start/Stop XMLP
- C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
start.bat - C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
stop.bat
54Modify xml-server-config.xml FileVerify File Path
- The original entry is
- ltfile path"s_Reports_Path"/gt.
- The value for the entry should be
- ltfile path"C\oracle\product\XMLP\5.6.2\xmlpserve
r\"/gt
55Modify xml-server-config.xml FileModify
Scheduler Section
- ltschedulergt
- ltconnectiongt
- ltconnectionTypegtjdbclt/connectionTypegt
- lturlgtjdbcoraclethin_at_ltYOUR
HOSTgt1521reptlt/urlgt - ltusernamegtoelt/usernamegt
- ltpasswordgtpasswordlt/passwordgt
- ltdrivergtoracle.jdbc.driver.OracleDriverlt/dri
vergt - lt/connectiongt
- lt/schedulergt
56Run oracle_tables.sql Script for Quartz Scheduler
- From the SQL script directory
\manual\SQL_Scripts, run the oracle_tables.sql
script with sqlplus - C\gt sqlplus SYSADM/SYSADM_at_REPT
- SQLgt _at_tables_oracle.sql
- C\gt sqlplus oe/password_at_REPT
- SQLgt _at_tables_oracle.sql
57Setup Quartz Scheduler for storing job information
- cd C\oracle\product\XMLP\5626AD1.2\oc4j\j2ee\hom
e\applib - notepad quartz.properties
- Update hostname ltYOUR-HOSTgt for
org.quartz.dataSource.myDS.URL in the
quartz.properties file.
58See Quartz Website for Configuration Detail
- http//www.opensymphony.com/quartz/wikidocs/Config
uration.html
59- Start XML Publisher Enterprise
60Start XML Publisher Enterprise
- Start
- C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
start.bat - Login
- http//ltYOUR HOSTgt15101/xmlpserver
61Set Connection Values
- Set Connection Values
- Select the Admin tab
- In the Data Sources list, choose JDBC Database
Connection. - Choose xmlpdemo connection.
- Connection Values
- Data Source Name xmlpdemo
- URL jdbcoraclethin_at_ltYOUR HOSTgt1521rept
- Username oe
- Password password
- Database Driver Class oracle.jdbc.driver.OracleDr
iver
62 63Client Configuration
- Install Internet Explorer
- In Control Panel, disable Internet Explorer
Enhanced Security Configuration - In IE, enable Java and JavaScript
- Install Adobe Acrobat Reader
64- Install XML Publisher Desktop
65Install XML Publisher Desktop
- Run Oracle Universal Installer to install Oracle
XML Publisher Desktop for Microsoft Word. - \XMLP_DESKTOP\setup.exe
66- Connecting to SQL Server with XMLP Enterprise
67Oracles Database Architecture
68Connecting to SQL Server with XMLP
Enterprise-Part 1
- Download the SQL Server 2000 Driver for JDBC
Service Pack 3 - some issues with the 2005 jdbc driver Oracle or
MS? - you can access the SQL Server 2005 edition with
the 2000 driver - There will be 3 jars in the ltltINSTALL DIRgtgt/lib
directory - mssqlserver.jar
- msbase.jar
- msutil.jar
69Connecting to SQL Server with XMLP
Enterprise-Part 2
- Copy them into your OC4J_HOME/j2ee/home/applib
if you have installed XMLP under OC4J otherwise
put them in the common apps libraries directory
of your J2EE container. - Bounce your server, the jdbc libraries will be
loaded automatically for all applications on the
instance. If you only want them loaded for the
xmlpserver application then copy the jars to
OC4J_HOME/j2ee/home/applications/xmlpserver/xmlpse
rver/WEB-INF/lib and bounce.
70Connecting to SQL Server with XMLP
Enterprise-Part 3
- Login as Administrator in the Enterprise server,
go to the Admin -gt JDBC page and enter the
details required for the data connection - Data Source Name - give the connection a name
- URL the url to connect to the db, standard MSSQL
stuff, basically jdbcsqlserver//serverportda
tabaseNamedbname e.g. jdbcsqlserver//111.11.1.1
111133databaseNameAdventureWorks - Username - simple stuff
- Password - even simpler
- Database Driver Class - com.microsoft.jdbc.sqlserv
er.SQLServerDriver - Check the connection.
- If it fails check your connection string is valid
and the class definition above. - The data source will now be available in the
report builder to builder queries against.
71- Creating XML Publisher Applications
72Creating XML Publisher Applications
- Copy the XML Publisher Java libraries
(\manual\lib) on the Server CD to the library
directory of your J2EE project or Java Runtime
Environment. - Copy the fonts in the fonts directory
(\manual\fonts) on the Server CD to the fonts
directory of your Java Runtime Environment. - Consult "Setting Runtime Properties" in the
Oracle XML Publisher Enterprise User's Guide
(found under \doc\XMLPublisher562.pdf) regarding
the configuration file. Please define the
temporary directory!
73- Oracle OC4J Deployment Notes
74Oracle OC4J Deployment Notes
- The version of the XML Parser shipped with Oracle
OC4J is incompatible with XML Publisher. When you
create an XML Publisher application for OC4J you
need to configure the system to use the XML
Publisher version of the XML Parser. This can be
accomplished by setting an orion-web.xml
configuration file with the following content - lt?xml version '1.0' encoding 'windows-1252'?gt
- ltorion-web-appgt
- ltweb-app-class-loader search-local-classes-firs
t"true" /gt - lt/orion-web-appgt
75- Getting Started with XML Publisher
76Getting Started with XML Publisher
- Getting Started
- Viewing and Scheduling Reports
- Creating a New Report
- Translating Reports
- Administration
- Creating an RTF Template
- XSL, SQL, and XSL-FO Support
- Creating a PDF Template
- Building a Data Template
- Setting Runtime Properties
- Using the XML Publisher APIs
- Supported XSL-FO Elements
77- XML Publisher Presentations
78XML Publisher Presentations
- Implementing Oracle XML Publisher for PeopleSoft
Enterprise - Shawn Zerby, Oracle Corporation
- 845-925 AM, Room 48
- Oracle BI (XML) Publisher Installation
- John Chemers, AOL LLC, TimeWarner Inc.
- 935-1015 AM, RM 57
- Fax and Email from Oracle E-Business Suite using
XML Publisher - Mike Piland, STR Software
- 935-1015 AM, RM 43
- XML Publisher Reports - E-Business Suite
- Kiran Kumar Akkiraju, eAlliance Corporation
- 1245-125 PM, RM 52
- XML Publisher in PeopleSoft Easier Than You
Think - John Jaent, Emerging Solutions
- 135-215 PM, Room 48
79 80Questions
- John Chemers
- Technology Architect/DBA
- johnchemers06_at_aol.com
- (847) 858-8109 cell
- Marybeth Hinkel
- PeopleSoft Support
- mhink44_at_aol.com
- (630) 629-5613