BI Publisher with PeopleSoft Features and Best Practices

1. BI Publisher with PeopleSoft Features and Best Practices 23rd Sep 2013 Oracle Open World, SFO

2. Section Name Confidential Information This document contains information that is confidential and proprietary to Infosys Limited and Oracle. No part of it may be used, circulated, quoted, or reproduced for distribution outside Infosys Limited and Oracle Corporation. Any disclosure of confidential information to, or use of it by a third party will be damaging to Infosys and Oracle. The ownership of all confidential information no matter what  media it resides in, remains with Infosys and Oracle. Views and points expressed in this document are based on Infosys experience working with Oracle BI Publisher and PeopleSoft.

3. Speaker Lead Consultant Chicago, USA Mitul_Kumar@infosys.com +1 281-619-0544 Technology Architect London, UK Abhiram_patankar@infosys.com +44 793-280-8904 Mitul Kumar Abhiram Patankar

4. Feedback Share your thoughts / feedback on this session via Twitter @InfosysOracle Hashtag:#InfosysAtOOW

5. Objectives  Provide overview of standout features of Oracle BI Publisher  Present a comparative study of BI Publisher and Crystal Reports  Provide high-level estimation guidelines for Crystal Reports to BIP migration  Suggest best practices for BI Publisher implementation and configuration

6. Introduction

7. Architecture

8. BI Publisher Features

9. BI Publisher Features BI Publisher Features Formatting features in template creation Versatile data sourcing features Support of several output formats Ability to schedule and distribute reports Ability to easily migrate report definition in PeopleSoft

10. Features – Templates • Template Creation • Supports an easy to use MS word tool (BI publisher desktop) for creating template • Users can totally rely on their MS word knowledge for template building as formatting, look and feel and pagination features inserted in the word template are retained in the report output. • Special Templates • Supports ETEXT templates which are vey useful in creating feed files for interfaces such as EDI and EFT. • Supports XSL templates which provides ability to create multi-sheet excel sheets. • PDF template allows creating of form type of reports such as W4 form.

11. Features – Data sources • Sources As a Standalone • In standalone mode, BI Publisher can support following data sources. • Databases (Oracle, SQL server, DB2 etc) • Web service • View objects • HTTP Feeds • View Objects • Sources With PeopleSoft Integration • Supports using PS query, Connected query and XMLs as sources. • Query reuse by utilizing same query across multiple reports • Performance optimization achieved by pushing the resource intensive operations to database query

12. Features – Output formats • HTML Web page • PDF • RTF (Word Doc) • Excel File • Text File (Fixed Width) • CSV (Delimited text) • XML • Excel File • PDF • RTF

13. Features - Migration

14. Advanced Features - Standalone

15. BI Publisher vs Crystal Reports

16. BI Publisher vs Crystal Reports

17. BI Publisher vs Crystal Reports

18. BI Publisher vs Crystal Reports

19. Effort Guidelines

20. Build Effort Map

21. Best Practices

22. Best Practices - Standalone Always create at least one parameter with a valid default value Always use ‘null value passed’ option when using prompt with ‘All’ Option Always prefer using data template for data retrieval Keep fields in child nodes to the minimum Always try to use the bind variables in data templates to link data sets Data Retrieval Use sub-templates where ever possible Ensure that development is done with same document attributes such as page sizes, fonts and margins as required in final output Use MS word ‘Insert’ option for inserting static images (Logo) to template Ensure that there are no extra spaces before or after if statements For creating tabular layouts having more than 30 columns consider using XSL template Template Design

23. Best Practices - Standalone Other Create xliff files required for translation at the end of development Ensure that font/s used for are setup within BI publisher server properly If the report output displays check boxes ensure that correct font is setup for true and false status of checkboxes While performing translation for right to left languages (e.g. Arabic), get the rendered output validated from language experts as some of the text might get inverted

24. Best Practices – PeopleSoft Prefer using/modifying existing PS query before creating a new one Ensure that all the functions used in crystal reports are converted Try to convert data connection query into a data template While converting groups, take note of properties such as ‘Keep Together’ in tables Crystal Reports Migrations Ensure that data ordering and aggregation is done in the PS query Ensure that hierarchy joins defined in the PS query are suitable for reports layout For multilingual reports ensure that XLAT properties are set Wherever possible try using PS query instead of connected query For bursting ensure that bursting values are unique in the result query PeopleSoft Integration

25. Questions? If you have any questions, please contact • Mitul Kumar

26. Contact Infosys http://www.infosys.com/oracle-openworld http://www.twitter.com/InfosysOracle Hashtag - #InfosysAtOOW http://www.youtube.com/InfosysTechnologies http://www.facebook.com/Infosys http://linkedin.com/company/infosys http://www.slideshare.net/Infosys

27. Thank You

28. Appendix 1 – BI Publisher Pain Points • Although BI publisher desktop is excellent for report format creating it helps in creating relatively standard type of data representations such as pivot tables, charts, graphs etc. For complicated scenarios (e.g. Dynamic columns, non standard graphs etc.) user needs to be skilled in BI Publisher template codes. • BI Publisher desktop is only useful in case of RTF templates. Cannot support development of PDF and XSL templates • ETEXT template though very powerful is not very intuitive due to unusual structure of template. Also debugging errors in the template is very challenging. • PDF template is very restrictive requires lot of patience to develop. • Newer versions of BI publisher desktop tends to add bits of code to the template which is not supported by some BI publisher severs (e.g. xref: bits added to the form fields) • No straightforward oracle developed migration tool to migrate crystal reports to BI Publisher as is the case with actuate or oracle reports

29. Appendix 2 – Steps to Create BIP Report • Determine if creating new PS /Connected query is required to be created • Create and register source definition using the identified PS/Connected query • Using the sample XML from the PS query definition create the template by using BI publisher desktop. • Create report definition using the data source definition created earlier. Also add template and parameters to the report definition • Run the report by using query report viewer which allows viewing and saving of the reports. • Reports can be scheduled through process scheduler or can also be distributed by using PeopleCode Source Design Template design Run the report

30. Appendix 3 – Steps to Create Crystal Report • Determine if creating new PS /Connected query is required to be created • Create a new PS query with Query tool. • Using the query generated through tool, prepare a layout for report. • Use external function or conditional logic for layout modification. • Place Report template in windows server. • Report can be scheduled through PS process scheduler. • Reports can be scheduled through process scheduler or can also be distributed by using People Code Source Design Template design Run the report