Oracle Business Intelligence

Oracle Business IntelligencePublisher Overview & BestPracticesPradeep SharmaSenior Principal Product ManagerBusiness IntelligenceKevin McDermottSenior Principal Technical Support EngineerOracleOct 02, 2014Copyright 2014, Oracle and/or its affiliates. All rights reserved.

Safe Harbor StatementThe following is intended to outline our general product direction. It is intended forinformation purposes only, and may not be incorporated into any contract. It is not acommitment to deliver any material, code, or functionality, and should not be relied uponin making purchasing decisions. The development, release, and timing of any features orfunctionality described for Oracle’s products remains at the sole discretion of Oracle.Copyright 2014, Oracle and/or its affiliates. All rights reserved.

Program Agenda1Oracle BI Publisher Overview2Easier, Better Managed & Faster3BI Publisher Best Practices4Data Model Design Best Practices5Layout Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Program Agenda1Oracle BI Publisher Overview2Easier, Better Managed & Faster3BI Publisher Best Practices4Data Model Design Best Practices5Layout Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Oracle BI Publisher OverviewCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Oracle BI Publisher EnterpriseSingle Solution Environment One Environment– Author– Generate– Deliver Benefits– Eliminate complexity– Simplify reportdevelopment & maintenance– Reduce costsInvoicesChecksDeliveryReportsLabelsCopyright 2014, Oracle and/or its affiliates. All rights reserved. XML/EFT/EDI

BI Publisher For All Your ReportsInvoicesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondenceCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsEFT & EDICopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsEFT & EDIChecksCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsEFT & EDIChecksOperational ReportsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsEFT & EDIChecksOperational ReportsFinancial StatementsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher For All Your ReportsInvoicesCorrespondencePurchase OrdersShipping LabelsGovt. FormsEFT & EDIChecksOperational ReportsFinancial StatementsInteractive ReportsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Oracle BI Publisher EnterpriseInteractiveOracle,Oracle BI EE,Oracle BI AppsSQL ServerPDFHTMLExcelE-Business Suite,PeopleSoft,Siebel, JDE,PrimaveraOLAPWeb ServicesData SourcesEmailPPTXSLFlashLayout Template Types Layout Editor MS Word MS ExcelPrinterFaxRTFCSV Adobe Flex XSLXMLRepositoryEDILayout Design ToolsEFTOutput FormatsCopyright 2014, Oracle and/or its affiliates. All rights reserved. e-CommerceDestinations

Oracle BI Publisher EnterpriseBI PublisherCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Program Agenda1Oracle BI Publisher Overview2Easier, Better Managed & Faster3BI Publisher Best Practices4Data Model Design Best Practices5Layout Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Easier, Better Managed & FasterCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher: Easier to ImplementEasierChallengeSolution Purchase Orders for 50Operating Units acrossthe globe Vendor Country Terms &Conditions 90 days and 3 people(incl. 3rd party vendor) toimplement POs BI Publisher’ ease ofdevelopment 3 days and 1 person toimplement POsBenefits 12x faster implementation w/1/3 fewer people Over 90% reduction in turnaround time End-to-End process using outof the box functionalityCopyright 2014, Oracle and/or its affiliates. All rights reserved. Oracle Confidential – Internal22

BI Publisher: Better ManagedBetter ManagedChallengeSolution Over 200 Crystal Reports Cumbersome reportrepository frustratedusers and IT staff Duplicate reports withminor layout changesand to support multilanguage BI Publisher’s separation of 3Layers (Data, Layout &Translation) 3 days and 1 person toimplement POsBenefits Now only 30 Publisherreports (85% reduction) 3 months w/ one f/t reportdeveloper End Users and IT staff veryhappyCopyright 2014, Oracle and/or its affiliates. All rights reserved. Oracle Confidential – Internal23

BI Publisher: FasterFasterChallengeSolution Generates 100,000Enrollment Forms in 48hours .NET server taking 24 hrsto generate 4,000 Forms 24 .NET servers & 15people to monitor andresubmit failures High performance of BIPublisher reports Scalable Enterprise ServerArchitectureBenefits Now generates 100,000 Formsin 3 hours 1 BI Publisher Server, 2 .NET 8 X Faster 1 / 8 Fewer no. of ServersCopyright 2014, Oracle and/or its affiliates. All rights reserved. Oracle Confidential – Internal24

Program Agenda1Oracle BI Publisher Overview2Easier, Better Managed & Faster3BI Publisher Best Practices4Data Model Design Best Practices5Layout Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesSeparate data logic, layout & translation benefits Benefits– Greater flexibility and reuse– Fewer reports and better report maintenance– Optimize data extraction and document generation processData LogicLayoutTranslationBI PublisherCopyright 2014, Oracle and/or its affiliates. All rights reserved. Report output

BI Publisher Best PracticesBI Publisher SizingSizing Questionnaire Portal Log on to Sizing Questionnaire Portal using URL:https://apex.oracle.com/pls/apex/f?p ORACLESIZINGTOOL WW:UPD Follow the steps:– STEP 1:Request one or more sizing questionnaires using theform.– STEP 2:When you receive our email(s) with your questionnaire,follow the included instructions to complete the sizing exercise.– STEP 3:An Oracle representative will contact you to discuss theresults.Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesBI Publisher SizingSizing Email with Attachment STEP 1:Save the attachment to your hard drive andrename the extension from ".zippy" to ".zip". STEP 2:Unzip the file. It will create a new directory withthe questionnaire. STEP 3:Run the flash client executable (.exe for Windowsor .app for Mac) to open up the sizing questionnaireclient. STEP 4:Fill out this questionnaire as completely aspossible.Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesBI Publisher SizingSend Email STEP 5:Fill in your Oracle Salesrepresentative (with correctemail address) and click theEmail button. A completedemail will be created in yourdefault email client, simply sendthe email as created. STEP 6:Your Oraclerepresentative will contact youto discuss the results.Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesBI Publisher SizingConcurrent Usage1DescriptionBursting Usage1 Users viewing reports online Scheduled Jobs running at the same time2Factors to Consider Number of users viewing Small, Medium, Large,XLarge reports Number of CPUs /serverDescription Bursting reports2Factors to Consider Total Number of reportsTime window% of Small, Medium, Large reportsNumber of CPUs/ServerCopyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesBI Publisher Sizing My OracleSupport – Note948841.1Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesHigh Availability Architecture When BI Publisher Enterprise isDeployed as Standalone– Oracle BI Publisher supports an active-activehigh availability configuration.– Each node acts as an independent server thatshares a common repository and thescheduler database with the other Oracle BIPublisher nodes.Refer to Oracle Fusion Middleware HighAvailability Guide 11g Release 1 (11.1.1)Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesHigh Availability Architecture When BI Publisher is deployed alongwith OBIEE and other products in BISuite.Refer to Oracle Fusion Middleware HighAvailability Guide 11g Release 1 (11.1.1)Copyright 2014, Oracle and/or its affiliates. All rights reserved.

BI Publisher Best PracticesScheduler ArchitectureClientClientWeb server/JMS Provider(Web Logic, Active MQ)FAX Q.FaxPrinter Q.Web serverWeb serverBI PublisherBI PublisherPrinterJob QEmail Q.Report QFTP Q.BIP System TopicWebDav Q.JMS ResourcesFile Q.EmailRepositoryFile systemBI Publisher Reports RepositoryScheduler databaseCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Program Agenda1Oracle BI Publisher Overview2Easier, Better Managed & Faster3BI Publisher Best Practices4Data Model Design Best Practices5Layout Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesData Source Types–––––––––––SQL QueryMDX QueryOracle BI AnalysisView ObjectWeb ServiceLDAP QueryXML FileMicrosoft Excel FileCSV FileHTTP (XML Feed)Oracle Endeca QueryCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesJDBC vs JNDI to Connect to Database Both types of connection usethe same JDBC driver1 With JNDI you can take advantageof connection pooling– Better performance Connection already established to the source Best practice for reports with many parameters with SQLtype LOVs attached to them– Better RDBMS resource management2Copyright 2014, Oracle and/or its affiliates. All rights reserved. WebLogicServer

Data Model Design Best PracticesEnable Scalable Mode Enable for large reports– Slower performance, but prevents out-ofmemory errors– Use with scheduledreportsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesLeverage Database Capabilities Leverage back-end system resources(whenever possible)– Join data– Filter & group data– Perform expensive calculations & datatransformations– Sort dataSelectt."OFFICE DSC" as "Office"sum(f."REVENUE") as “Sales Sum"From"BISAMPLE"."SAMP REVENUE F“ f"BISAMPLE"."SAMP OFFICES D“ tWheret."OFFICE KEY" f."OFFICE KEY"group byt."OFFICE DSC"20 Vs 20,000 recordsCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesReport Data & LOV Caching Report Data Cache LOV Parameter CacheCopyright 2014, Oracle and/or its affiliates. All rights reserved.

Data Model Design Best PracticesPass Null Value when User selects “All” in LOV– For large LOVs, pass NULL to the parameterwhen users select All & modify WHERE clause inData Set query to use the NVL function.For example:“Where order id nvl(:p ordid,order id)”Copyright 2014, Oracle and/or its affiliates. All rights reserved.