Get documents about "Oracle BI (XML) Publisher Installation
Document Sample
Oracle BI (XML) Publisher Installation
John Chemers
AOL LLC
TimeWarner Inc.
NCOAUG/MWRUG/ORACLE
CONFERENCE
Monday, March 12, 2007
Types of Paths in this Presentation
2
Your Presenters
John Chemers
Technology Architect/DBA
johnchemers06@aol.com
(847) 858-8109 cell
Marybeth Hinkel
PeopleSoft Support
mhink44@aol.com
(630) 629-5613
3
Overview
Oracle BI (XML) Publisher
Installation
4
Agenda
•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
5
Database Architecture
Database Architecture
•Oracle’s Database Architecture
•My Database Architecture
7
Oracle’s Database Architecture
8
My Database Architecture
9
Database Configuration
Database 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
11
Oracle 9i Configuration
•Activate Oracle JServer/JVM option
• Increase INIT.ORA param values:
− java_pool_size (>20M)
− shared_pool_size (>50M)
• Run
$ORACLE_HOME/javavm/install/initjvm.sql
as SYS AS SYSDBA to install JServer Option
• Grant JAVAUSERPRIV to users that use Java:
− SQL> GRANT JAVAUSERPRIV TO SCOTT;
• Use rmjvm.sql to deinstall JServer option
12
Oracle 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)
13
Create 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;
14
Create Sample Schemas-Oracle 9i
% sqlplus /nolog
SQL> @?/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.
15
Create Sample Schemas-Oracle 10g
Use DCA – Database Configuration
Assistant
The entire process takes about 30 to 45
minutes to complete.
16
Database Configuration
•Configure tnsnames.ora
•Configure listener.ora
•Reload listner
•TNSPING
17
Configure tnsnames.ora
REPT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = <YOUR HOST>)
(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = REPT)
)
)
18
Configure 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_DBNAME=rept)
(ORACLE_HOME = C:\oracle\product\10.2.0\db_1)
(SID_NAME = rept)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = <YOUR HOST>)
(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
19
Reload Listener and TNSPING
•lsnrctl reload
•tnsping rept
20
Sample User
Configuration
Enable Sample Report User
C:\> sqlplus sys/password@REPT
as SYSDBA
SQL> alter user OE account unlock;
SQL> ALTER USER OE IDENTIFIED
BY password;
22
Custom User Creation
Identify location of Data Files
C:\>sqlplus sys/password@REPT as
sysdba
SQL> select file#,
substr(name,1,60) from v$datafile
order by file#;
24
Create Tablespace
SQL> CONNECT
SYS/PASSWORD@REPT AS SYSDBA
SQL> CREATE TABLESPACE SYSADM
DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORA
DATA\REPT\sysadm01.dbf'
SIZE 20M EXTENT MANAGEMENT
LOCAL AUTOALLOCATE;
25
Create Custom Report User
SQL> CONNECT
SYS/PASSWORD@REPT AS SYSDBA
SQL> CREATE USER sysadm
IDENTIFIED BY sysadm
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE sysadm
QUOTA UNLIMITED ON sysadm;
26
Grant Connect Access
SQL> CONNECT
SYS/PASSWORD@REPT AS SYSDBA
SQL> GRANT connect TO sysadm;
27
Grant Create/Select Privileges
SQL> CONNECT SYS/PASSWORD@REPT
AS SYSDBA
SQL> GRANT SELECT ANY TABLE TO
SYSADM;
SQL> GRANT CREATE TABLE TO SYSADM;
SQL> GRANT CREATE VIEW TO SYSADM;
SQL> GRANT CREATE SEQUENCE TO
SYSADM;
28
Grant Java Privileges
SQL> CONNECT
SYS/PASSWORD@REPT AS SYSDBA
SQL> GRANT JAVAUSERPRIV TO
SYSADM;
29
Database Link Creation
Database 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
31
Grant Privileges
Grant Privileges
SQL> CONNECT
SYS/PASSWORD@REPT AS SYSDBA
SQL> GRANT CREATE DATABASE
LINK TO SYSADM;
SQL> GRANT CREATE SYNONYM TO
SYSADM;
SQL> GRANT EXECUTE ANY
PROCEDURE TO SYSADM;
33
Database Link Creation-
Oracle DB
My Database Architecture
35
Create Database Link-Oracle DB
SQL> CONNECT
SYSADM/SYSADM@REPT
SQL> CREATE DATABASE LINK
SAPDB
CONNECT TO SAPADM
IDENTIFIED BY password USING
'SAPDB'
36
Create Views-Oracle DB
create force view SAP_TABLE_NAME
as select * from
SAP_TABLE_NAME@sapdb;
create force view SAP_VIEW_NAME
as select * from
SAP_VIEW_NAME@sapdb;
37
My Database Architecture
38
Database Link Creation-
Microsoft SQL Server
DB
My Database Architecture
40
Oracle Heterogeneous Services
ODBC agent-Microsoft DB
Make a connection from Oracle to
SQL Server using Oracle
Heterogeneous Services ODBC agent
41
Define 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
42
Create 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
43
Configure 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
44
Configure listener.ora – Microsoft DB
- Part 2
LISTENERMYAWDSN =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=15
22))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
SID_LIST_LISTENERMYAWDSN=
(SID_LIST=
(SID_DESC=
(SID_NAME=MYAWDSN)
(ORACLE_HOME = c:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
)
45
Configure 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
46
Configure tnsnames.ora – Microsoft DB
- Part 2
MYAWDSN =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(
PORT=1522))
(CONNECT_DATA=(SID=MYAWDSN))
(HS=OK)
)
47
Reload listener – Microsoft DB
Lsnrctl reload
48
Create a Database Link – Microsoft DB
SQL> CONNECT
SYSADM/SYSADM@REPT
SQL> create database link myawdsn
connect to sa identified by
password using 'MYAWDSN';
49
Create Views-Microsoft DB
create force view MS_TABLE_NAME
as select * from
MS_TABLE_NAME@myawdsn;
create force view MS_VIEW_NAME
as select * from
MS_VIEW_NAME@myawdsn;
50
My Database Architecture
51
XML Publisher
Enterprise Installation
XML 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\s
etupinfo.txt
•XMLP URL
• http://<YOUR HOST>:15101/xmlpserver
•Start/Stop XMLP
• C:\oracle\product\XMLP\5.6.2\xmlpserver\x
mlpserverstart.bat
• C:\oracle\product\XMLP\5.6.2\xmlpserver\x
mlpserverstop.bat
53
Modify xml-server-config.xml File
Verify File Path
•The original entry is:
<file path="%s_Reports_Path%"/>.
•The value for the entry should be:
<file path="C:\oracle\product\XMLP\5.6.2\xmlpserver\"/>
54
Modify xml-server-config.xml File
Modify Scheduler Section
<scheduler>
<connection>
<connectionType>jdbc</connectionType>
<url>jdbc:oracle:thin:@<YOUR
HOST>:1521:rept</url>
<username>oe</username>
<password>password</password>
<driver>oracle.jdbc.driver.OracleDriver</dri
ver>
</connection>
</scheduler>
55
Run oracle_tables.sql Script for
Quartz Scheduler
From the SQL script directory:
\manual\SQL_Scripts, run the
oracle_tables.sql script with sqlplus:
C:\> sqlplus SYSADM/SYSADM@REPT
SQL> @tables_oracle.sql
C:\> sqlplus oe/password@REPT
SQL> @tables_oracle.sql
56
Setup Quartz Scheduler for storing
job information
•cd
C:\oracle\product\XMLP\5626AD~1
.2\oc4j\j2ee\home\applib
•notepad quartz.properties
•Update hostname <YOUR-HOST>
for org.quartz.dataSource.myDS.URL
in the quartz.properties file.
57
See Quartz Website for
Configuration Detail
•http://www.opensymphony.com/q
uartz/wikidocs/Configuration.html
58
Start XML Publisher
Enterprise
Start XML Publisher Enterprise
•Start
• C:\oracle\product\XMLP\5.6.2\xmlpserver\x
mlpserverstart.bat
•Login
• http://<YOUR HOST>:15101/xmlpserver
60
Set 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 jdbc:oracle:thin:@<YOUR HOST>:1521:rept
− Username oe
− Password password
− Database Driver Class oracle.jdbc.driver.OracleDriver
61
Client Configuration
Client Configuration
•Install Internet Explorer
•In Control Panel, disable Internet
Explorer Enhanced Security
Configuration
•In IE, enable Java and JavaScript
•Install Adobe Acrobat Reader
63
Install XML Publisher
Desktop
Install XML Publisher Desktop
•Run Oracle Universal Installer to
install Oracle XML Publisher Desktop
for Microsoft Word.
…\XMLP_DESKTOP\setup.exe
65
Connecting to SQL Server
with XMLP Enterprise
Oracle’s Database Architecture
67
Connecting 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 <<INSTALL
DIR>>/lib directory
• mssqlserver.jar
• msbase.jar
• msutil.jar
68
Connecting 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/xmlpserve
r/xmlpserver/WEB-INF/lib and bounce.
69
Connecting to SQL Server with XMLP
Enterprise-Part 3
•Login as Administrator in the Enterprise server, go to the Admin -
> 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
jdbc:sqlserver://server:port;databaseName=dbname e.g.
jdbc:sqlserver://111.11.1.111:1133;databaseName=AdventureWorks
• Username - simple stuff
• Password - even simpler
• Database Driver Class - com.microsoft.jdbc.sqlserver.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.
70
Creating XML Publisher
Applications
Creating 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!
72
Oracle OC4J Deployment
Notes
Oracle 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:
<?xml version = '1.0' encoding = 'windows-1252'?>
<orion-web-app>
<web-app-class-loader search-local-classes-
first="true" />
</orion-web-app>
74
Getting Started with XML
Publisher
Getting 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
76
XML Publisher
Presentations
XML Publisher Presentations
Implementing Oracle XML Publisher for PeopleSoft Enterprise
Shawn Zerby, Oracle Corporation
8:45-9:25 AM, Room 48
Oracle BI (XML) Publisher Installation
John Chemers, AOL LLC, TimeWarner Inc.
9:35-10:15 AM, RM #57
Fax and Email from Oracle E-Business Suite using XML Publisher
Mike Piland, STR Software
9:35-10:15 AM, RM #43
XML Publisher Reports - E-Business Suite
Kiran Kumar Akkiraju, eAlliance Corporation
12:45-1:25 PM, RM #52
XML Publisher in PeopleSoft; Easier Than You Think
John Jaent, Emerging Solutions
1:35-2:15 PM, Room 48
78
Questions
Questions
John Chemers
Technology Architect/DBA
johnchemers06@aol.com
(847) 858-8109 cell
Marybeth Hinkel
PeopleSoft Support
mhink44@aol.com
(630) 629-5613
80
