SaaS BI Publisher Data Model Validation for Building Performing Custom Reports [PRSH3359].pdf

1、SaaS BI Publisher Data Model ValidationEugene Perkov and Pavel BuynitskySeptember 11,20241Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesAnalytical(BI)SQL QueriesSQL Code Development ProblemsSQL Coding Patterns are there any?Plan Efficiency How to Define?Typical Modes of Operation:Desi

2、gn Time AssistanceProduction Code MaintenanceBIP Data Model ValidationSample Use CasesMerging Two UNION ALL BranchesScalar Subquery With AggregationAgendaOracle CloudWorld Copyright 2024,Oracle and/or its affiliates2Large(sometimes very large)ComplexPossibly with many redundanciesOracle CloudWorld C

3、opyright 2024,Oracle and/or its affiliates3Analytical(BI)SQL QueriesSQL Code Optimization ProblemOracle CloudWorld Copyright 2024,Oracle and/or its affiliates4SQL Code DevelopmentO(n!)100!10157,thats 3 10143 years!Function ofData volumeData shapeOracle CloudWorld Copyright 2024,Oracle and/or its aff

4、iliates5SQL Query PerformanceCertain SQL phraseologyMay result in a less efficient planOracle CloudWorld Copyright 2024,Oracle and/or its affiliates6SQL Coding Patterns Are There Any?Meaningful Measure of Redundancy.Less Redundancy=More Efficiency.Plan Efficiency How to Define?Oracle CloudWorld Copy

5、right 2024,Oracle and/or its affiliates7Efficiency FactorDefinitionLIO Efficiency1=_Objects Redundancy Factor2=_Rows Multiplicity Factor3=_Plan Efficiency=123Query LIOTotal BlocksDistinct ObjectsTotal ObjectsFinal RowsIntermediate Rows60,189531,7821877867442,8620.1130.2340.0020.00005Design of New Co

6、deMaintenanceTwo Modes of OperationOracle CloudWorld Copyright 2024,Oracle and/or its affiliates89Oracle CloudWorld Copyright 2024,Oracle and/or its affiliatesDesign Time AssistanceDetects SQL coding patterns earlyStimulates incremental design approachGuides SQL developmentSQL query complexity incre