OracleBIPublishBestPractice-甲骨文BIPublish最佳實踐-培訓

1、OracleBIPublisher BestPracticesRupeshVarmaPrincipalProductManagerThefollowing is intended to outlineour generalproductdirection. It is intended forinformationpurposesonly,andmay notbeincorporatedintoanycontract. It is nota commitmenttodeliveranymaterial, code, or functionality, andshouldnot be relie

2、dupon in makingpurchasing decision.Thedevelopment, release, andtimingofany features or functionalitydescribed fororaclesproductsremains at thesole discretionoforacle.Program AgendaOracleBIPublisher EnterpriseOverviewReportDesign BestPracticesData Modeling GuidelinesTemplateDesignGuidelinesReportingG

3、uidelinesQ &AOracleBIPublisher EnterpriseOneEnvironmentAuthorGenerateDeliverBenefitsEliminatecomplexitySimplifyreportdevelopment& maintenanceReducecostsEliminateMultiple-pointSolutionsReportsChecksLabelsDestinationsInvoicesXML / EFT / EDILayoutTemplateTypesOutputFormatsDestinationsEmailPrinterFaxe-C

4、ommerceRepositoryData SourcesLayoutDesignToolsWeb ServicesOracle, Oracle BI EE,Oracle BI Apps SQL ServerE-Business Suite, PeopleSoft,Siebel, JDE, PrimaveraOLAPOracleBIPublisher EnterpriseFrom DatatoDocumenttoDeliveryLayoutEditorMSWordMSExcelAdobeFlexXSLXSLXMLEDIEFTPDFHTMLExcelPPTCSVFlashRTFInteracti

5、veArchitectureBI PublisherOracleBusinessIntelligence PublisherSeparatedata logic,layout&translationbenefitsgreater flexibility andreusefewerreports andbetterreport maintenanceoptimizedata extractionand document generationprocessReportArchitectureReport outputData LogicLayoutTranslationBI PublisherOr

6、acleBIPublisher Scheduler11gBIPublisherWebserverBIPublisherWebserverBIPublisherReportsRepositoryClientClientScheduler databaseJMSResourcesEmailPrinterFaxRepositoryFile systemFAX Q.Printer Q.Email Q.FTP Q.WebDav Q.File Q.JobQReportQBIPSystem TopicWebserver/JMSProvider(Web Logic,ActiveMQ)Program Agend

7、aOracleBIPublisher EnterpriseOverviewReportDesign BestPracticesData Modeling GuidelinesTemplateDesignGuidelinesQ &AData GenerationData SourceTypesFile (XML)SQLQueryMDXQueryLDAP QueryExcelSpreadsheetHTTP (XMLFeed)WebServiceOracleBIAnalysisData SourcesData GenerationJDBC Vs JNDI(SQLType)Both types of

8、connectionusethesameJDBC driverWith JNDIyou cantake advantageofconnectionpoolingBetterperformanceConnection alreadyestablishedtothe sourceBest practiceforreportswith manyparameterswith SQLtype LOVsattachedtothemBetterRDBMSresourcemanagementConnection Types21WebLogicServerBIPublisherData GenerationEn

9、ablefor large reportsSlowerperformance, butpreventsout-of-memory errorsUsewithscheduledreportsScalableModeData Model DesignOptimizedata extractionSimplifylayoutdesign forreportdevelopersReducedocumentgenerationtimeObjectivesData Model DesignLeverageback-endsystemresources (when possible)Join dataFil

10、ter&groupdataPerform expensivecalculations&data transformationsSort dataConsidergoingagainst BI Serverinstead of extractingdatadirectlyfrom thesourcesystemsUseBIServer LogicalSQL insteadofAnswers/AnalysisData ExtractionSelectt.OFFICE_DSCasOfficesum(f.REVENUE) as “SalesSumFromBISAMPLE.SAMP_REVENUE_F“

11、fBISAMPLE.SAMP_OFFICES_D“tWheret.OFFICE_KEY=f.OFFICE_KEYgroupbyt.OFFICE_DSC20Vs20,000recordsData Model DesignParameters &LOVsCacheLOVS forbetterperformanceForlargeLOVs,passNULLtotheparameter whenusersselectAll&modifyWHEREclause in DataSet query to usetheNVL function.Forexample:“Whereorder_id=nvl(:p_

12、ordid,order_id)”Data ExtractionData Model DesignEventtriggersBeforeDataTriggersGeneratedynamic SQLPopulateTEMP tablesSetusercontextAfterData TriggersInsertrowstoa targettableSend notificationsCleanordeleteTEMPtablesData ExtractionSelectEMPLOYEE_ID,DEPARTMENT_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBE

13、R,HIRE_DATE,JOB_ID,SALARYfrom employeeswhere&p_where_clauseData Model DesignStructuredata to match howdata isdisplayedinthe reportUseshortXML tagnames theyreducethesizeofXMLdatafileUsedisplaynamestosimplifytemplatedesignCreateglobal &group-levelaggregate functions forbest performance,createthemindat

14、a model insteadofreport layoutPre-sortdataNote:Groupingdata in datamodelautomaticallysortsdata by thegroupingelementBurstingItalso requires thatdataintheXML is sorted(orgrouped)bythe split-by key.Best PracticesforFaster Document GenerationProgram AgendaOracleBIPublisher EnterpriseOverviewSystemConfi

15、gurationGuidelinesBIPublisherServer ConfigurationOptionsReportDesign BestPracticesData Modeling GuidelinesTemplateDesignGuidelinesQ &ARTF TemplatesEasy to create w/ BI Publisher Template Builder Add-in to WordGreatest flexibility and functionality for highly formatted reportsRTF marked up with XSL c

16、ode 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, HTML, Excel, PPT, RTF, MHTML)Exce

17、l TemplatesExcel w/ data mapped to named cells & Excel and XSL formattingGreat for formatted, true Excel output and burst over sheetsExcel XLS output onlyDocumentFormatting andLayoutWhat Template Typeshould Iuse?(1of2)PDF FormsPDF forms with XML elements mapped to form fields.Great to use with gover

18、nment 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 TemplatesSWF files with BI Publisher data for soph

19、isticated 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.DocumentFormatting andLayoutWhat Template Typeshould Iuse?(2of2)BIPublisherTemplat

20、e(11g)BestPracticesUseLayout Grids to position componentssimilar to tablesinRTFTemplatesLayoutGridscan be nestedLayoutGridCell canbejoinedTheminimumheightofrowscanbefixedDoNOTputlargetables(Perform.)orpivottablesinto LayoutGridsDesignConsiderationBIPublisherTemplate(11g)BestPracticesTheInteractiveVi

21、ewer is oneofBIPublishersmost popularnew featuresInteractivity works bestfor flattables or simplenestedmaster-detaildata setsUnrelatedqueriesorgroupscan notinteractDisable interactionsforunrelated queriesintheConfigureEventsDialogDesignConsiderationBAD:Country on twobranchesDataG1CountryRevenueG2Cou

22、ntryCostGOOD:Country in commonparentDataG1CountryG2RevenueG3CostRTFTemplateBestPracticesUsetables to controlprecisely where field datawillbeplacedinthe reportUseWordTablesRTFTemplateBestPracticesKeep thetemplatecleanSupportedbythe Template BuilderFieldBrowserCanbecoloredorhiddentohelp understandthe

23、structureCaveat: Wordheader &footer dont allow formfieldsUseFormFieldsRTFTemplateBestPracticesAchieve consistency in thetemplateandbetweentemplatesIn11guse Style Templatestocontrol thetemplatestylesUseWordStylesRTFTemplateBestPracticesKeep it easytounderstand,debugand maintainIngeneral bettertohave

24、differentbusinessdocumentsindifferent templatesTrytolimitthe logic in templatestosimpleiforloopstatementsUsesub templatestosimplifydocumentsifnecessaryMany calculationsarebetter performedinthedatamodelDontovercomplicateyour templateDocumentFormatting andLayoutWhat is asub template?A document thatcon

25、tainslayout andorcode thatcan be definedonceandusedmultipletimesMultiplefunctions(called“templates”inXSL) or textsegmentscan be definedinasinglesub template fileSubtemplatetypesXSLsub templatesforcodere-useorseparationRTFsub templatesforlayout re-useCaveat: UseofRTFsub templatesmakesitharderto under

26、standand visualizethetemplatestructureSubTemplates:IntroductionDocumentFormatting andLayoutRTFsub templatesRe-usecommon layoutssuchastermsandconditionsInternationalizedaddress blockXSLsub templatesTransformationsfor complexchartrequirementsDynamicallyapplyformatting to aportionofdata(e.g.subscriptin

27、g /superscripting /chemicalformula)PrintformattedXHTMLdata converttoFOusingsub template (asused in OracleContracts) (supportsHTML in data)Startingw/werecommendusingStyleTemplates forHeader/FooterinsteadofRTFSub TemplateSubTemplates:UseCasesRTFTemplateBestPracticesBIPublisherusesXPATH

28、toaccessdataelementsDEPARTMENT_NAMEisinsertedinthe template asistranslated to theXPATH./DEPARTMENT_NAME./DEPARTMENT_NAMEsearchesforDEPARTMENT_NAMEinthecompletesub-treestartingfromcurrent contextPerformanceConsiderations:XPATHConceptsRTFTemplateBestPracticesUsethe fullrelativepathforlargedatasetsInst

29、ead ofuseInstead ofuseForlargedocuments thatdontfit intomemory thesearchrequiresdiskaccessForsmalldocuments thesearchtimeisnegligiblePerformanceConsiderations:XPATHTuningRTFTemplateBestPracticesXPATHTuningStartwith theouterloopsandoutermostdata accessReducingfull treesearcheswillprovide biggerimprov

30、ementsthanoptimizing accessina small subsetofthedataFixingthe XPATH in asingle for-each loopmay be alltheperformancetuning youneed to doTablesLARGEtablesthatspan hundreds of pages consumeconsiderable serverresources. If possible group thedata andcreateatableinsideeachgrouping.DonotnestLARGEtablesint

31、otables.PerformanceConsiderations:XPATHTuning &TablesRTFTemplateBestPracticesBettertoSortdata in thedata modelGroupBywill force theXSLengine to sortdataChecking“Dataalready sorted”option in theTableWizardwillnotre-sortdata (good thing)PerformanceConsiderations:GroupingTemplateBest PracticesDontoverc

32、omplicateyour templatesUsetables (layoutgrids) to place datafields preciselyUseformfieldsand wordstyles forclarityUsesub templatesforre-use andencapsulatecomplex codeOptimizeyour datastructure forinteractivity if necessaryPerformanceoptimizationOptimizetheXPATHFocusoptimizationonouterloopsSort in th

33、edata baseand use“group-adjacent” in thetemplateSummaryStay Connectedwith BI PublisherQ&AAppendixResourcesServerConfigurationOptionsScalablethreshold(in bytes)Cacheexpiration (inmin)Cachesize limit (# of datasets)Maximum cachedreportsDatabaseFetchSizeAdministration ServerConfigurationDefault valuesS

34、erverConfigurationOptionsBursting(10g only)EnablemultithreadingDefault:False*ThreadcountDefault:2*FOprocessingUseBIPublishersXSLTprocessorDefault:TrueEnablescalablefeatureofXSLT processorDefault:False*Pagescachedduring processingDefault:50EnablemultithreadingDefault:False*FOParsing BufferSizeDefault

35、:1000000*EnableXSLTruntime optimizationDefault:TrueAdministration RuntimeConfiguration PropertiesReport-levelPropertiesEdit Report PropertiesFormattingReport-levelPropertiesForlongrunning reportsTake advantageofscheduling anddisableRunReport Online.Viewing ReportsOnlineReportEditorReportViewerReport-levelPropertiesEnableDataCachingSavesXMLdataforreuseuptot