Oracle BI XML Publisher Installation - PowerPoint PPT Presentation

1 / 80
About This Presentation
Title:

Oracle BI XML Publisher Installation

Description:

Make a connection from Oracle to SQL Server using Oracle Heterogeneous Services ODBC agent ... Add a new DSN Data Source and use the SQL Server driver ... – PowerPoint PPT presentation

Number of Views:143
Avg rating:3.0/5.0
Slides: 81
Provided by: rick296
Category:

less

Transcript and Presenter's Notes

Title: Oracle BI XML Publisher Installation


1
Oracle BI (XML) Publisher Installation
  • John Chemers
  • AOL LLC
  • TimeWarner Inc.
  • NCOAUG/MWRUG/ORACLE CONFERENCE
  • Monday, March 12, 2007

2
Types of Paths in this Presentation
3
Your 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

4
Overview
  • Oracle BI (XML) Publisher Installation

5
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

6
  • Database Architecture

7
Database Architecture
  • Oracles Database Architecture
  • My Database Architecture

8
Oracles Database Architecture
9
My Database Architecture
10
  • Database Configuration

11
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

12
Oracle 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

13
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)

14
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

15
Create 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.

16
Create Sample Schemas-Oracle 10g
  • Use DCA Database Configuration Assistant
  • The entire process takes about 30 to 45 minutes
    to complete.

17
Database Configuration
  • Configure tnsnames.ora
  • Configure listener.ora
  • Reload listner
  • TNSPING

18
Configure tnsnames.ora
  • REPT
  • (DESCRIPTION
  • (ADDRESS_LIST
  • (ADDRESS (PROTOCOL TCP)
  • (HOST ltYOUR HOSTgt)
  • (PORT 1521))
  • )
  • (CONNECT_DATA
  • (SERVER DEDICATED)
  • (SERVICE_NAME REPT)
  • )
  • )

19
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_DBNAMErept)
  • (ORACLE_HOME C\oracle\product\10.2.0\
    db_1)
  • (SID_NAME rept)
  • )
  • )
  • LISTENER
  • (DESCRIPTION_LIST
  • (DESCRIPTION
  • (ADDRESS (PROTOCOL TCP)
  • (HOST ltYOUR HOSTgt)

20
Reload Listener and TNSPING
  • lsnrctl reload
  • tnsping rept

21
  • Sample User Configuration

22
Enable 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
  • Custom User Creation

24
Identify location of Data Files
  • C\gtsqlplus sys/password_at_REPT as sysdba
  • SQLgt select file, substr(name,1,60) from
    vdatafile order by file

25
Create 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

26
Create 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

27
Grant Connect Access
  • SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
  • SQLgt GRANT connect TO sysadm

28
Grant 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

29
Grant Java Privileges
  • SQLgt CONNECT SYS/PASSWORD_at_REPT AS SYSDBA
  • SQLgt GRANT JAVAUSERPRIV TO SYSADM

30
  • Database Link Creation

31
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

32
  • Grant Privileges

33
Grant 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

35
My Database Architecture
36
Create Database Link-Oracle DB
  • SQLgt CONNECT SYSADM/SYSADM_at_REPT
  • SQLgt CREATE DATABASE LINK SAPDB
  • CONNECT TO SAPADM IDENTIFIED BY password
    USING 'SAPDB'

37
Create 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

38
My Database Architecture
39
  • Database Link Creation-Microsoft SQL Server DB

40
My Database Architecture
41
Oracle Heterogeneous Services ODBC
agent-Microsoft DB
  • Make a connection from Oracle to SQL Server using
    Oracle Heterogeneous Services ODBC agent

42
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

43
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

44
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

45
Configure 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)
  • )
  • )

46
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

47
Configure tnsnames.ora Microsoft DB - Part 2
  • MYAWDSN
  • (DESCRIPTION (ADDRESS(PROTOCOLtcp)(HOSTlo
    calhost)(PORT1522)) (CONNECT_DATA(SIDMYAWDSN
    ))
  • (HSOK)
  • )

48
Reload listener Microsoft DB
  • Lsnrctl reload

49
Create a Database Link Microsoft DB
  • SQLgt CONNECT SYSADM/SYSADM_at_REPT
  • SQLgt create database link myawdsn
  • connect to sa identified by password using
    'MYAWDSN'

50
Create 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

51
My Database Architecture
52
  • XML Publisher Enterprise Installation

53
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\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

54
Modify 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

55
Modify 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

56
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\gt sqlplus SYSADM/SYSADM_at_REPT
  • SQLgt _at_tables_oracle.sql
  • C\gt sqlplus oe/password_at_REPT
  • SQLgt _at_tables_oracle.sql

57
Setup 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.

58
See Quartz Website for Configuration Detail
  • http//www.opensymphony.com/quartz/wikidocs/Config
    uration.html

59
  • Start XML Publisher Enterprise

60
Start XML Publisher Enterprise
  • Start
  • C\oracle\product\XMLP\5.6.2\xmlpserver\xmlpserver
    start.bat
  • Login
  • http//ltYOUR HOSTgt15101/xmlpserver

61
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 jdbcoraclethin_at_ltYOUR HOSTgt1521rept
  • Username oe
  • Password password
  • Database Driver Class oracle.jdbc.driver.OracleDr
    iver

62
  • Client Configuration

63
Client 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

65
Install 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

67
Oracles Database Architecture
68
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 ltltINSTALL DIRgtgt/lib
    directory
  • mssqlserver.jar
  • msbase.jar
  • msutil.jar

69
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/xmlpserver/xmlpse
    rver/WEB-INF/lib and bounce.

70
Connecting 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

72
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!

73
  • Oracle OC4J Deployment Notes

74
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
  • 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

76
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

77
  • XML Publisher Presentations

78
XML 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
  • Questions

80
Questions
  • John Chemers
  • Technology Architect/DBA
  • johnchemers06_at_aol.com
  • (847) 858-8109 cell
  • Marybeth Hinkel
  • PeopleSoft Support
  • mhink44_at_aol.com
  • (630) 629-5613
Write a Comment
User Comments (0)
About PowerShow.com