Session ID:
Prepared by:
BI Publisher SIG Collaborate 2017 Meeting
10028
Brent Lowe, STR Software
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BI Publisher SIG
Front of Room/Podium
EBS JDE
PeopleSoft Other Enterprise, OBIEE, etc…
Seating Chart! Since this is a Special Interest Group, we all have BI Publisher in common. Sit with others that are using BIP with the same application to give yourself the opportunity to network. Introduce yourself to your neighbor, ask them how they are using BIP before we get started.
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Agenda
• BI Publisher SIG Intro/Update
• BI Publisher for Static Reports – Chao-Yee Watson
• BI Publisher and JD Edwards – Andrew Ostdiek
• BI Publisher and PeopleSoft – Marcus Bode
BI Publisher SIG Intro/Update Brent Lowe, STR Software
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Who’s This Guy?
• Brent Lowe
BI Publisher Chair
Head of Product Architecture – STR Software
Worked with BI Publisher since 2004ish
Note to self… pass around clipboard…
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BI Publisher SIG Intro/Update
• Charter:
The OAUG BI Publisher SIG will inform and educate members on topics relevant to the usage of BI Publisher in Oracle Applications such as Oracle EBS, Oracle JD Edwards and Oracle PeopleSoft while offering a forum for peers to share their experience and knowledge with one another.
• Most importantly: Getting folks together to help each other out with BI Publisher.
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Business - Vision
• Quarterly educational webinars.
• In person meetings at OOW and Collaborate.
• Robust website with pointers to existing information.
• Oracle involvement.
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Business – Help Wanted
• Leadership Needs (all Volunteer): • Partner in Crime (Co-Chair)
• Program Director – meeting planning, coordinating speakers
• Communication Director – website upkeep, member communication
• Non Leadership Needs • Presenters, Material, Experiences, etc…
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Business – Become a Member
• Create an OAUG Profile ---- FREE
• Join a GEO/SIG -> BI Publisher SIG
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Where to Go When You Don’t Know
Central Hub – SIG Website
http://bipubsig.communities.oaug.org/
BI Publisher for Static Reports Chao-Yee Watson, Xilinx
Session ID:
Prepared by:
OAUG BI Publisher SIG Case Study: BI Publisher for Static Reports Conversion of Legacy Discoverer Reports and Moving Users to BI Publisher
10028
Chao-Yee Watson, Xilinx
@ChaoYee_Watson
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Agenda
• Introduction
• Speaker Biography
• State of Reporting at Xilinx
• Approach to Determining Requirements
• Evaluation of Replacements
• Plan to Replace
• Current Status of the Replacement Project
13
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Introduction
• In 2016, we started the journey of converting Customer Operations reports from Discoverer to BI Publisher.
• Talk about current state of reporting in my organization
• Discuss how Discoverer compares to BI Publisher
• Discuss how BI Publisher compares to OBIEE Answers
• Share our lessons learned from our Discoverer retirement process and the implementation of BI Publisher
14
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Speaker Biography
• Over 20 years managing business process and system change in High Technology
• Extensive experience driving international change management and enterprise-wide roll-outs with a focus on process design, key performance metrics design and resource optimization
• Thought leader in design and re-engineering of processes in the areas of business intelligence, program management, data governance, customer service, supply chain management, demand management, product lifecycle management
• Featured speaker and panelist at multiple OAUG COLLABORATE conferences, OAUG ConnectionPoint and PMI Silicon Valley Symposium
PMP® certified
MBA from Santa Clara University
BS in Mechanical Engineering from UC Berkeley
Chao-Yee Watson
15
April 2-6, 2017 in Las Vegas, NV USA #C17LV
State of Reporting Tools
• Discoverer remains the general reporting tools standard
• Other legacy custom tools developed using PL/SQL, APEX, PHP
• OBIEE Answers, BI Publisher, BIC’s in production for various departments
• Original plan was to retire all Discoverer reports in Nov 2016 and replace with BI Publisher 11g
• This plan has been delayed based on user requirements from different departments
• New plan is to roll out BIP for groups that require scheduled static formatted reports
16
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Discoverer – the “Swiss Army Knife”
• Original intent when Discoverer was implemented over a decade ago was to replace Oracle Reports and other text file reports
• The management model for Discoverer was loose • IT created the folders in Business Areas
• The users became rather creative in how they utilized Discoverer
17
April 2-6, 2017 in Las Vegas, NV USA #C17LV
How Discoverer was Used • Static Reports
• Shared Customized Reports
• Volume Data Extracts
• Ad hoc Data Extracts
18
Options for Discoverer Replacement • BI Publisher
• OBIEE Answers
• Oracle BI Cloud
• Other legacy customizations
• Other options not yet evaluated
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Evaluating BIP vs OBIEE Answers
19
BI Publisher OBIEE Answers Highly formatted or specific layout reports Ad-hoc analysis Operational or transactional data reporting. Any aggregations or pivots must be done by end-user
Analytical reporting. Aggregations, pivots, and drills defined in RPD. Drag and drop for end-user
Power users or Business Analysts (technical users) reporting on non-hierarchical data. Data for the report is directly retrieved via SQL.
Known data elements to answer the question (e.g.: Opportunity) Dimensions and Facts are pre-built
Requires SQL expertise for creating Data Models. Can be a risk when used by novices
End-user reporting. No SQL expertise required
Multiple data sources including OBIEE Subject Areas
Row-level security (Must use Answers)
Quickly and easily develop or change reports (source data)
Requires a longer development cycle to incorporate all required data elements into the RPD
Easily create multiple report layouts from a single data model
Reporting only predefined subject areas
Ad-hoc reporting Hierarchical data (Must use Answers) Summary: BI Publisher is primarily meant as a tool for creating highly formatted, published reports (Invoices, Quotes, etc) by technical users. It is NOT suited for analytical reporting OR for hierarchical data OR where row-level security is required.
Summary: OBIEE Answers is the primary, best of class, analytical reporting tool. Best used for reporting of structured and pre-summarized data.
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Evaluating Discoverer vs. BIP
Task / Requirements Discoverer BIP
User creates ad-hoc report Yes No
Data Model needs to be defined prior to report creation No Yes
Create / modify report parameters at runtime Yes No
Multiple levels of conditions at report level Yes No
Defining calculation functions at report runtime Yes No
End user creation of data model NA No
Group by function Yes Yes
Multiple column selection at runtime Yes No
Drill down reports Yes Yes
Unrestricted Excel export Yes No
20
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Customer Operations Use Case • Xilinx Customer Master Team
• Reports compile data from Oracle ERP, CDH and other proprietary applications
• Team utilizes static reports in their daily tasks maintaining customer master data
• Total of 38 reports required replacement
• BI Publisher chosen to replace static reports • Small group of homogeneous users
• Users only needed formatted reports for daily use
• Team needed push reports that could be scheduled
• IT created the replacement reports
• Business Users tested the reports
21
April 2-6, 2017 in Las Vegas, NV USA #C17LV
List of Customer Master Reports
22
S/N Report Names Date in UAT Test Status Feedback Date 1 XCM - DNB Incomplete Data Report 8/22/2016 Migrated to PROD 8/26/2016 2 XCM - Source System Data Quality Metrics Report 8/24/2016 Migrated to PROD 8/26/2016 3 XCM - Source System ID Mapping Trace Report 8/24/2016 Migrated to PROD 8/26/2016 4 XCM - DNB Daily or Quarterly Extract Details Report 8/24/2016 Migrated to PROD 8/26/2016 5 XCM - Site Level Relationship Changes 8/25/2016 Migrated to PROD 8/26/2016 6 XCM - AUTO XU 8/25/2016 Migrated to PROD 8/26/2016 7 XCM - Customer Data Hierarchy Comparison (Manual vs D & B) Report 8/25/2016 Migrated to PROD 8/26/2016 8 XCM - Customer Listing Summary Report 8/25/2016 Migrated to PROD 8/26/2016 9 XCM - DNB Unmatched & Rejected Report 8/26/2016 Migrated to PROD 8/26/2016
10 XCM - Customer Relationship Listing Report 8/26/2016 Migrated to PROD 8/26/2016 11 XCM - Xilinx DNB GUs Link to XU List (AS-IS) Report 9/2/2016 Migrated to PROD 9/2/2016 12 XCM - Xilinx DNB GUs not in Finance Hierarchy (AS-IS) Report 9/2/2016 Migrated to PROD 9/2/2016 13 XCM - Xilinx Finance Hierarchy (AS-IS) Report 9/2/2016 Migrated to PROD 9/2/2016 14 XXCM certified records with null primary addresses. 9/7/2016 Migrated to PROD 9/8/2016 15 XCM - DNB Match Rules Report 9/7/2016 Migrated to PROD 9/8/2016 16 XCM - Bulk Import Error Report 9/7/2016 Migrated to PROD 9/8/2016 17 XCM - Xilinx DNB Matched records with No GU and Xilinx Custom Hierarchy (AS-IS) 9/7/2016 Migrated to PROD 9/8/2016 18 XCM - Party Merge Trace Report 9/19/2016 Migrated to PROD 9/22/2016 19 XCM - XU Status Trace Report 9/19/2016 Migrated to PROD 9/22/2016 20 XCM - DNB WorldBase Trace Report 9/19/2016 Migrated to PROD 9/22/2016 21 XCM - Customer Classification Report 9/19/2016 Migrated to PROD 9/21/2016 22 XCM - Customer Certification Report 9/28/2016 Migrated to PROD 10/4/2016 23 XCM Data Specialist Quality Check Report 9/28/2016 Migrated to PROD 9/29/2016 24 XCM - Split Review Report 9/28/2016 Migrated to PROD 10/4/2016 25 XCM - Flagged Record Status Trace Report 9/28/2016 Migrated to PROD 10/4/2016 26 XCM - CDH Input Data Report 10/6/2016 Migrated to PROD 10/6/2016 27 XCM - Subscription Error Report 10/6/2016 Migrated to PROD 10/6/2016 28 XCM XU-GU-SITE-CHECKING.DIS Report 8/22/2016 Migrated to PROD 8/26/2016 29 XCM - Party Merge 10/6/2016 Migrated to PROD 10/6/2016 30 XCM - Record Status Report 9/19/2016 Migrated to PROD 9/21/2016 31 XCM - Standard City State Country Report 9/28/2016 Migrated to PROD 9/29/2016 32 XCM - DNB Review Report 10/28/2016 Migrated to PROD 10/31/2016 33 XCM - Hierarchy Relationship Trace 9/28/2016 Pend IT 9/29/2016 34 XCM - Review Request Trace Report 10/12/2016 Pend IT 10/18/2016 35 XCM - XU Hierarchy with Source System Details Report 10/18/2016 Pend IT 11/1/2016 36 XCM - Oracle 11i Status Report 11/1/2016 Pend IT 11/7/2016 37 XCM - GD IDs referenced in last 3 month transactions that are not yet certified in XCM On Hold 38 XCM ASP - GD IDs to be Cleansed On Hold
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Results from Customer Master Reports Conversion
• Conversion started June 2016
23
Status (as of February 2017) # %
Reports in PROD 32 71.1%
Reports – Tested & Pending IT Rework • Unable to extract huge report • Unable to extract 3 months data • Issues on parameters & data extracted
4 2 1 1
13.2%
Reports - Not Ready in UAT • Pending Finance/GD - GD BI project is currently on hold
2 2
13.2%
Total reports to migrate 38 100.0%
• Majority of the reports required for daily use are now in production
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Plan to Replace Remaining Customer Master Reports
• For reports where are large volume of data is required to be extracted, we are still evaluating potential options for Discoverer replacement
• For reports which are dependent on the Finance department to make a decision on tools, those reports are on hold indefinitely
24
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Key Lessons Learned
• Don’t assume that one tool or platform will satisfy all business use cases
• BI Publisher fits the use case where reports are: • Highly formatted static output
• Parameters are set and not expected to change
• Users do not plan to change the report format
• Users do not require a high volume of columns and rows of data
25
BI Publisher and JD Edwards Andrew Ostdiek, Hamilton Telecommunications
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Speaker Biography
• 15+ years of JDE Experience • End User to Evangelist! • World to E910
• JDE Generalist • Development • Project Manager • CNC ‘lite’ • Functional Analyst • Business Analyst • Technical Analyst • Etc…. I JDE
Andrew Ostdiek
27
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Hamilton Telecommunications
• JDE – 9.1 apps, 9.1.2 Tools
• 9.2 Upgrade over next 12 months.
• Transitioning from Create!form to BI Publisher for Forms Management
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition – The Why
• Moving to BIP b/c: • Cost savings
• Lack of in-house knowledge on Create!form • Reduce 3rd Party Development Reliance
• Duplication of owned functionality
• Ease of use
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition: Roadmap
• Transition and consolidate all Create!form projects to BIP.
• Use BIP bursting/emailing to drive distribution internally and externally.
• Educate and enable internal staff to develop BIP templates.
• Look into BIP Packs from TeamCain to speed up development and reduce internal costs.
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition: Roadmap – Early Stages
• One customized form for Contract Billing Invoice
• Used Oracle provided forms for W2, 1099, 1095-C
• Testing bursting/emailing for invoices
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition: Primary Challenges
• Time and resource availability
• Lack of in-house BIP knowledge
• Engaged a 3rd party consultant to help. • Got us started with a new form in short timeframe
• Helped us bridge our knowledge gap
• Continue to engage as we can free staff for other priorities
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition: Lessons Learned
• When engaging with a 3rd party consultant • Ask basic forms management questions to ensure that existing
logic will translate and work in your BIP utilization
• Look for prebuilt forms or solutions to help provide an understanding of standard BIP use cases
• Maintain a vision of future considerations in form solutions
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition – Lessons Learned • Understand conditional logic (example address blocks
where Address lines 2-4 are blank)
• Review XML files via Chrome – easier to see and understand data
• Use Word Plugin to test templates
• Name objects to align with UBE utilizing them • UBE - R48507 • XML Publisher Object - TP5548507 • Report Definition Object - R48507_1
• Use E-Mail Address option as internal ‘Check’ for delivery confirmation for Data Driven Delivery utilization
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition – Lessons Learned • Patience – Larger UBEs with BIP templates take longer to
render. • Job Queue may say ‘D’one, but UBE hasn’t finished.
• Adjust jde.ini file to get output to find rendered output on your Enterprise Server.
[UBE] BipOutputDirectory=E:\JDEdwards\E910\BIPOutput BipSaveOutputOnFs=1
• F95600 (BI Publisher Object Repository) • Reference this table for quick reference on templates (date
information)
April 2-6, 2017 in Las Vegas, NV USA #C17LV
BIP Transition – What’s Next?
• Continue with our Phase 1 project tasks
• Identify our Forms Management footprint
• Put a plan in place for formal Forms Management transition
• Education of internal resources to develop and maintain BIP Forms going forward
BI Publisher and PeopleSoft Marcus Bode
Your Presenters
Marcus Bode Managing Director
[email protected] (415) 509-1151
Application Focus: Oracle Cloud ERP, PeopleSoft FSCM/HCM
Vertical Focus: Financial Services, FinTech, Healthcare, Utilities, Technology
PeopleSoft Financials implementations, upgrades, enhancements & integration
About SpearMC
Founded in 2004, SpearMC is a technology and professional services firm specializing in
PeopleSoft FSCM, HCM, ELM
PeopleTools Development and System Administration
PeopleSoft Test Framework
Oracle Cloud ERP and HCM
Oracle Business Intelligence
PeopleSoft Training
Project Management
Oracle Public Cloud
Quality Insurance & Test Automation
Host Analytics EPM
Our History
PeopleSoft Reporting Overview
April 2-6, 2017 in Las Vegas, NV USA #C17LV
SQR
Query
nVision
Crystal Reports
BI Publisher
The State of PeopleSoft Reporting Tools
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Very powerful–full database access within a "report"
Modular
• re-use logic in building blocks
• override delivered logic
SQR
Part of the Oracle family
Built-in support for debugging
Programmer-centric
– quick to build and run
– requires a technical resource
Lots of knowledge in the PeopleSoft community
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Empowers business users
• Solve their own problems
• Explore data
Built-in features
• Query Access Server (instant web service)
• Translate tables
• Embedded URLs
• Output to Excel
Outsource processing criteria/logic to Query and business users
Query
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Microsoft Excel based
• That’s why accountants love it
• MS Excel v 2007 or higher – ‘unlimited’ rows
Very powerful with full database access within a spreadsheet
• Security assigned at the report / layout level
• Row-level security applies to running reports and drill-down
Optimized for Financial Reporting
• Uses Ledger table
• Enables drill-down into sub-ledger and transaction tables
Represents Hierarchical Relationships
• Excellent use of rollup trees for display
• Provides ‘nPlosion’ (Excel Grouping)
Layouts Designed and Stored within Database
• Users can schedule reports or run ad-hoc without additional run-control pages
nVision
Introducing PeopleTools BI Publisher
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Reporting tool introduced as “XML Publisher” in PeopleTools 8.48
Renamed to “BI Publisher” in PeopleTools 8.52
• “BI” as in “Business Intelligence”, aka “Reporting and Analytics”
Positioned as a reporting tool that:
• Replaces Crystal (completely by Tools 8.55)
• Alternative to SQR
• Better integrated with PeopleSoft
• Architected for better maintenance and extensibility
BI Publisher
April 2-6, 2017 in Las Vegas, NV USA #C17LV
In broad strokes, a lot like Crystal • A PS Query runs on the application server
• Prompts filled in at run time
• BI Publisher runtime applies Report Template to the data to build a PDF
• "Data Source" column in Query Report Viewer is the name of the Query
Report Template • RTF file containing formatting, data placeholders, and special syntax
• Created in Microsoft Word with the help of an Oracle-created Add-In
Report Definition • Allows running reports via Scheduled Query or Ad-Hoc
• If Ad-Hoc, must build custom run-control page
• Data Source
• This is how it knows about the Query (but XML will ultimately be the data source, so this is the ‘source of the source’)
• Category
• Defines the report’s security (see Reporting Tools > BI Publisher > Setup> Report Category)
How does it work?
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Data Sources
• Functional users can build data source
• Delivered page runs query-based BIP reports
• End-to-end report development without a single line of code Query
• All the advantages of Query but requires Power users to build data source
• Reports can incorporate parent-child relationships
Connected Query
• Ultimately flexibility but requires code
• Good for external sources of data
• Recommended for large volumes XML File
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Microsoft Word is the BI Publisher design-time environment
• Only MS Word version of BI Publisher is delivered with the PeopleSoft application
• For BI Publisher Templates for Excel or PowerPoint, must purchase Enterprise Edition of BI Publisher
• Instead of having Crystal installed on your machine, you need
• Microsoft Word (2007 or higher)
• Java Runtime
• Oracle’s BI Publisher Template Builder Add-In for Microsoft Word
o Aka “Design Helper”
• Windows only (no Mac support)
Installing Template Builder
• Download directly from within PeopleSoft
• Must install Java seperately
Introducing Template Builder
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Import XML file containing sample data
• Template Builder needs to know the structure of the data source
• E.g. What fields will be available to put on report?
• Sample data useful for quick & dirty testing during report development
• Crystal handles this through special "PeopleSoft PeopleTools" ODBC driver that exposes metadata of PS Query definitions
• BI Publisher handles this by parsing a sample XML file imported via the Template Builder’s Sample XML tool
• Most BI Publisher ribbon tools are grayed out until sample data imported
• Once imported, fields can be added to report with Template Builder tools
• Once imported, developers do not need to be connected to PeopleSoft – they just continue to use the sample XML until another sample is needed or the report is complete!
Working in Template Builder
Introducing Template Builder
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Test Template using Preview tool
• Template Builder Add-In includes the BI Publisher runtime engine!
• Click the desired output format and report is generated and displayed
• Combines XML and RTF into output file
• Runs on locally installed Java runtime
• Allows for iterative, trial-and-error report development (and again, no need to stay connected)
Working in Template Builder
Introducing Template Builder
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Database
App Server Web Server
Architecture Checkpoint
App
Tables
Query
XML
Data
Source
BI Output!
Report
Definition
& Template
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Advanced Topics
Q: How do you write a Query with results that include parent-child relationships? A: In PeopleSoft, use the Connected Query
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Connected Query gives you a way to “join” PS Queries that have a logical parent-child relationship
Allows you to create XML with hierarchical data
Can be used as a BI Publisher Data Source
Introduced in PeopleTools 8.50
Reporting Tools > Connected Query > Connected Query Manager
Connected Query and BI Publisher
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Creating
1. Create PS Queries that will be part of Connected Query
2. Reporting Tools > Connected Query > Connected Query Manager > Add a New Value
3. Name it
4. Add Top-Level “Parent Query” to new Connected Query definition
5. Add descendent Queries
• Click name of a Query to display tool icons
• Use Child and Sibling icons
6. Use Select Related Fields tool to map fields on each child Query to parent
7. Set Status to “Active”
8. Use Preview to test
Connected Query and BI Publisher
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Query Prompts
• Supported for all Queries in a Connected Query
• User prompted at run/schedule time
• Recommendation: When possible, only use with top-level Query
• Queries with In-Tree prompt cannot be used in Connected Query
Advantages
• Leverage existing Queries (Query reuse)
• Easier to develop and maintain
• Multiple bite size Queries instead of one, big, complex one
• Alternative to outer joins
• Very different data sets can be included in same report
Disadvantages
• Performance not good with large amounts of data
Connected Query and BI Publisher
Please Complete Your Session Evaluation
Evaluate this session in your COLLABORATE app. Pull up this session and tap "Session Evaluation" to complete the survey.
Session ID: 10028
April 2-6, 2017 in Las Vegas, NV USA #C17LV
Feedback
• Want
• To serve on board?
• To present at future meeting/webinar?
• These slides?
• To tell me how this SIG can better serve YOU?
• Something else?
I’m all ears: now, during conference, or [email protected]
Q&A