OracleBIPublishBestPractice甲骨文BIPublish最佳实践培训

1、Oracle BI Publisher Best PracticesRupesh VarmaPrincipal Product Manager The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functional

2、ity, and should not be relied upon in making purchasing decision. The development, release, and timing of any features or functionality described for oracles products remains at the sole discretion of oracle.Program AgendaOracle BI Publisher Enterprise OverviewReport Design Best PracticesData Modeli

3、ng GuidelinesTemplate Design GuidelinesReporting GuidelinesQ & AOracle BI Publisher EnterpriseOne EnvironmentAuthorGenerateDeliverBenefitsEliminate complexitySimplify report development & maintenanceReduce costsEliminate Multiple-point SolutionsReportsChecksLabelsDestinationsInvoicesXML / EFT / EDIL

4、ayout Template TypesOutput FormatsDestinationsEmailPrinterFaxe-CommerceRepositoryData SourcesLayout Design ToolsWeb ServicesOracle, Oracle BI EE,Oracle BI Apps SQL ServerE-Business Suite, PeopleSoft,Siebel, JDE, PrimaveraOLAPOracle BI Publisher EnterpriseFrom Data to Document to Delivery Layout Edit

5、or MS Word MS Excel Adobe Flex XSLXSLXMLEDIEFTPDFHTMLExcelPPTCSVFlashRTFInteractiveArchitectureBI PublisherOracle Business Intelligence PublisherSeparate data logic, layout & translation benefitsgreater flexibility and reuse fewer reports and better report maintenanceoptimize data extraction and doc

6、ument generation processReport ArchitectureReport outputData LogicLayoutTranslationBI PublisherOracle BI Publisher Scheduler 11gBI PublisherWeb serverBI PublisherWeb serverBI Publisher Reports RepositoryClientClientScheduler databaseJMS ResourcesEmailPrinterFaxRepositoryFile systemFAX Q.Printer Q.Em

7、ail Q.FTP Q.WebDav Q.File Q.Job QReport QBIP System TopicWeb server/JMS Provider(Web Logic, Active MQ)Program AgendaOracle BI Publisher Enterprise OverviewReport Design Best PracticesData Modeling GuidelinesTemplate Design GuidelinesQ & AData GenerationData Source TypesFile (XML)SQL QueryMDX QueryLD

8、AP QueryExcel SpreadsheetHTTP (XML Feed)Web ServiceOracle BI AnalysisData SourcesData GenerationJDBC Vs JNDI (SQL Type)Both types of connection use the same JDBC driverWith JNDI you can take advantage of connection poolingBetter performanceConnection already established to the sourceBest practice fo

9、r reports with many parameters with SQL type LOVs attached to themBetter RDBMS resource managementConnection Types21WebLogic ServerBI PublisherData GenerationEnable for large reportsSlower performance, but prevents out-of-memory errorsUse with scheduled reportsScalable ModeData Model DesignOptimize

10、data extractionSimplify layout design for report developersReduce document generation time ObjectivesData Model DesignLeverage back-end system resources (when possible)Join dataFilter & group dataPerform expensive calculations & data transformationsSort dataConsider going against BI Server instead o

11、f extracting data directly from the source systemsUse BI Server Logical SQL instead of Answers/AnalysisData ExtractionSelect t.OFFICE_DSC as Office sum(f.REVENUE) as “Sales Sum From BISAMPLE.SAMP_REVENUE_F“ f BISAMPLE.SAMP_OFFICES_D“ tWhere t.OFFICE_KEY=f.OFFICE_KEY group by t.OFFICE_DSC20 Vs 20,000

12、 recordsData Model DesignParameters & LOVsCache LOVS for better performanceFor large LOVs, pass NULL to the parameter when users select All & modify WHERE clause in Data Set query to use the NVL function. For example:“Where order_id=nvl(:p_ordid, order_id)”Data ExtractionData Model DesignEvent trigg

13、ersBefore Data TriggersGenerate dynamic SQLPopulate TEMP tablesSet user contextAfter Data TriggersInsert rows to a target tableSend notificationsClean or delete TEMP tablesData ExtractionSelect EMPLOYEE_ID, DEPARTMENT_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARYfrom empl

14、oyeeswhere &p_where_clauseData Model DesignStructure data to match how data is displayed in the reportUse short XML tag names they reduce the size of XML data fileUse display names to simplify template designCreate global & group-level aggregate functions for best performance, create them in data mo

15、del instead of report layoutPre-sort dataNote: Grouping data in data model automatically sorts data by the grouping elementBursting It also requires that data in the XML is sorted (or grouped) by the split-by key. Best Practices for Faster Document GenerationProgram AgendaOracle BI Publisher Enterpr

16、ise OverviewSystem Configuration GuidelinesBI Publisher Server Configuration OptionsReport Design Best PracticesData Modeling GuidelinesTemplate Design GuidelinesQ & ARTF TemplatesEasy to create w/ BI Publisher Template Builder Add-in to WordGreatest flexibility and functionality for highly formatte

17、d reportsRTF marked up with XSL code or BI Publisher simplified XSLWide range of output (PDF, HTML, Excel, PPT, RTF, MHTML)BI PublisherTemplate 11gWeb based Layout Editor no client installation requiredBest for Management Report WYSIWIG experienceSupport Interactive OutputWide range of output (PDF,

18、HTML, Excel, PPT, RTF, MHTML)Excel TemplatesExcel w/ data mapped to named cells & Excel and XSL formattingGreat for formatted, true Excel output and burst over sheetsExcel XLS output onlyDocument Formatting and LayoutWhat Template Type should I use? (1 of 2)PDF FormsPDF forms with XML elements mappe

19、d to form fields.Great to use with government formsPDF output only - created in Acrobat Professionale-Text TemplatesText output only for electronic communicationGreat for character delimited or fixed position docs (EFT & EDI)RTF with table of statements to place fields and separatorsFlash TemplatesS

20、WF files with BI Publisher data for sophisticated interactivityCreate in Adobe Flex BuilderXSL Style SheetsAllows for third party tools and legacy solutionsExcel Analyzer TemplatesNot recommended (deprecated)Excel output only. Can refresh data w/ parameters.Document Formatting and LayoutWhat Templat

21、e Type should I use? (2 of 2)BI Publisher Template (11g) Best PracticesUse Layout Grids to position components similar to tables in RTF TemplatesLayout Grids can be nestedLayout Grid Cell can be joinedThe minimum height of rowscan be fixedDo NOT put large tables (Perform.)or pivot tables into Layout

22、 GridsDesign Consideration BI Publisher Template (11g) Best PracticesThe Interactive Viewer is one of BI Publishers most popular new featuresInteractivity works best for flat tables or simple nested master-detail data setsUnrelated queries or groups can not interactDisable interactions for unrelated

23、 queries in the Configure Events DialogDesign Consideration BAD: Country on two branchesData G1 Country Revenue G2 Country CostGOOD: Country in common parentData G1 Country G2 Revenue G3 CostRTF Template Best PracticesUse tables to control precisely where field data will be placed in the reportUse W

24、ord TablesRTF Template Best Practices Keep the template cleanSupported by the Template Builder Field BrowserCan be colored or hidden to help understand the structureCaveat: Word header & footer dont allow form fields Use Form Fields RTF Template Best PracticesAchieve consistency in the template and

25、between templatesIn 11g use Style Templates to control the template stylesUse Word StylesRTF Template Best PracticesKeep it easy to understand, debug and maintainIn general better to have different business documents in different templatesTry to limit the logic in templates to simple if or loop stat

26、ementsUse sub templates to simplify documents if necessaryMany calculations are better performed in the data modelDont overcomplicate your templateDocument Formatting and LayoutWhat is a sub template?A document that contains layout and or code that can be defined once and used multiple timesMultiple

27、 functions (called “templates” in XSL) or text segments can be defined in a single sub template fileSub template typesXSL sub templates for code re-use or separationRTF sub templates for layout re-useCaveat: Use of RTF sub templates makes it harder to understand and visualize the template structureS

28、ub Templates: IntroductionDocument Formatting and LayoutRTF sub templatesRe-use common layouts such as terms and conditionsInternationalized address blockXSL sub templatesTransformations for complex chart requirements Dynamically apply formatting to a portion of data(e.g. sub scripting / super scrip

29、ting / chemical formula)Print formatted XHTML data convert to FO using sub template (as used in Oracle Contracts) ( supports HTML in data)Starting w/ we recommend using Style Templates for Header / Footer instead of RTF Sub TemplateSub Templates: Use CasesRTF Template Best PracticesBI Publisher uses

30、 XPATH to access data elementsDEPARTMENT_NAME is inserted in the template as is translated to the XPATH ./DEPARTMENT_NAME./DEPARTMENT_NAME searches for DEPARTMENT_NAME in the complete sub-tree starting from current contextPerformance Considerations: XPATH Concepts RTF Template Best PracticesUse the

31、full relative path for large datasetsInstead of use Instead of use For large documents that dont fit into memory the search requires disk accessFor small documents the search time is negligiblePerformance Considerations: XPATH Tuning RTF Template Best PracticesXPATH Tuning Start with the outer loops

32、 and outer most data accessReducing full tree searches will provide bigger improvements than optimizing access in a small subset of the dataFixing the XPATH in a single for-each loop may be all the performance tuning you need to doTablesLARGE tables that span hundreds of pages consume considerable s

33、erver resources. If possible group the data and create a table inside each grouping. Do not nest LARGE tables into tables.Performance Considerations: XPATH Tuning & TablesRTF Template Best PracticesBetter to Sort data in the data modelGroup By will force the XSL engine to sort dataChecking “Data alr

34、eady sorted” option in the Table Wizard will not re-sort data (good thing)Performance Considerations: GroupingTemplate Best PracticesDont overcomplicate your templatesUse tables (layout grids) to place data fields preciselyUse form fields and word styles for clarityUse sub templates for re-use and e

35、ncapsulate complex codeOptimize your data structure for interactivity if necessaryPerformance optimizationOptimize the XPATHFocus optimization on outer loopsSort in the data base and use “group-adjacent” in the templateSummaryStay Connected with BI Publisher/technetwork/middleware/bi-publisher/overv

36、iew/index.htmlQ&AAppendixResourcesCertification matrix (11.1.1.x)/technetwork/middleware/bi-enterprise-edition/bi-11gr1certmatrix-166168.xlsHigh availability white paper /technetwork/middleware/bi-publisher/bip-cluster-deployment-366859.pdfSizing Spreadsheet for BI Publisher Enterprise 10gMy Oracle

37、Support Note - 948841.1Sub-templates white paper /technetwork/middleware/bi-publisher/overview/bip-subtemplate-1-132933.pdfQUARTZ Scheduling (clustering) wiki page/display/QRTZ1/ConfigJDBCJobStoreClusteringCPU Ratings /cpu2006/results/cpu2006.html#SPECint_rateServer Configuration OptionsScalable thr

38、eshold (in bytes)Cache expiration (in min)Cache size limit (# of datasets)Maximum cached reportsDatabase Fetch SizeAdministration Server ConfigurationDefault valuesServer Configuration OptionsBursting (10g only)Enable multithreading Default: False*Thread count Default: 2*FO processingUse BI Publishe

39、rs XSLT processor Default: TrueEnable scalable feature of XSLT processor Default: False*Pages cached during processing Default: 50Enable multithreading Default: False*FO Parsing Buffer Size Default: 1000000*Enable XSLT runtime optimization Default: TrueAdministration Runtime Configuration PropertiesReport-level Properties Edit Report Properties Formatting Report-level PropertiesFor long running reportsTake advantage of scheduling and disable Run Report Online. Viewing Reports OnlineReport EditorReport ViewerReport-level PropertiesEnable Data CachingSaves