Oracle Business Intelligence Enterprise Edition …

1 Oracle Business Intelligence Enterprise Edition ( obiee )Presented by Sing Chan ERP Technical LeadMontgomery County Department of Technology ServicesAgendaGeneral OverviewWhat is BI/Data Warehouse and Why?What is obiee ( Oracle Business Intelligence Enterprise Edition )?What is STAR schema? BackgroundWhen did the BI project start?What is the early day obstacle on BI analytics?Why the County decided to go with custom BI models?BI Project in Three PhasesPrototype PhaseProduction PhaseEnhancement/KPI PhaseSummaryWhere are we today?What is obiee or the BI? obiee Oracle Business Intelligence Enterprise Edition obiee is a platform that contains different tools for creating reports, performing data analysis and presenting the data obiee can be used for different users, depending on the skills and needsExecutive:Interactive Dashboard, GraphicFunctional Operation: Tabular view of data, ReportAnalyst:Ad-Hoc Query Others Capabilities: Notification and Alerts thru BI agent Pixel Perfect Report using BI Publisher obiee Analyst Report or Dashboard requires data to be organize as STAR BI Publisher Report does not have a STAR requirement and it is relatively slower with the same amount of dataWhy do we use the BI tool?

2 OTraditional System focuses on Data Integrity for Update and Insert of Single RecordoReport needs to link many records and group data in an effective way for fast speed and aggregation of larger amountsof dataoTraditional Table(s) not optimized for ReportingoBI Star schema is optimized for Reports in SQL Database since each relationship is only 1 step from the FACT (Single Join)dimensiondimensiondimensionMore about the Star Schema A fact table usually contains data elements that can be aggregated (counted). Example: Dollars($)A dimension table contains data elements that describe the FACT (Who, What, How, When, Why, ) that can be used to group the reportor filter the data. Take the following example:How much($)Sales Person JohnSELLon MarylandStoreduring month of Januaryfor ProductABC?

3 Green: Fact AttributesRed: Dimension AttributesUnderline: DimensionBOLD: FACT/ProcessBackgroundERP Project Began in 2008 The ERP project introduced the Oracle eBusinessSuite as the county s Enterprise Business solution software Oracle eBusinessSuite is comprised of many modules, which were implemented in a staggered approach Finance(payroll, accounts payable, accounts receivable, general ledger, procurement, iReceivable, iExpense, iSupplier, PnG, fixed assets, Enterprise asset management) Human Capital Management or HCM (position management, workforce performance management,, learning management, labor distribution, advanced benefits, iRecruitment, employee self-service) Order and Warehouse Management (DLC iStore, warehouse management, order management, inventory management, advanced pricing)

4 Property Management and Lease Management (coming soon)Background (cont.) The obiee project was a subset of the ERP project County purchased Oracle Purchasing and HR Analytics along with obiee and Informatica In 2011 the county switched to custom-built BI for several reasons: Oracle BI analytics focused on Key Performance Indicators at high level and does not adequately support operational reporting requirements without extensive customization Oracle BI analytics depends on Informatica which Oracle is slowly replacing with Oracle Data Integrator and County has more ODI skill set resources than Informatica There is no Grants Analytics or Labor Distribution Analytics available from Oracle (those two modules are very critical for County daily operation)The Custom BI Project The County BI project can be broken down into three phases, and each one has different focus and challenge.

5 Prototype Phase Production Phase Enhancement/KPI PhasePrototype Phase Focus Build a correct Star Model Data Integrity Data Testing Quick Delivery of Model Provide training to Key Department Power User Challenge Slow Refresh speed Weekly data gap Lack of Enterprise security Access is Limited to Key Department PlayerProduction Phase Focus Timeliness of data refresh (speed) Streamline development process and standard Implement Enterprise security with data and role protection Develop dashboard for non-power user Train users on dashboard and BI analysis to general users Build summary model that links across multiple subjects and star model System performanceProduction Phase (cont.) Challenge Not everything is incremental updatable Data integrity (source delete situation) User data knowledge and BI analysis skill Oracle EBS data only Missing actionable KPI ModelsAP iExpenseAP Invoice DistributionAP Invoice HoldsAR AgingAR CustomerAR ReceiptsAR TransactionsAudit TrailCash Management Bank StatementsCash Management ReconciliationEAMWO Cost DistributionEAM Work RequestPO ContractFixed AssetsFND Role and ResponsibilityGL -Daily Average BalanceGL -PnG DeltaGL ActualsGL BudgetGL EncumbranceGL PC projectionGL SummaryHR AssignmentHR Benefits (Enrollment)HR Benefits ElgibilityModels Cont dPayroll BalancePayroll PaymentsHR PositionPayment Distribution (AP)

6 Labor Distribution LD SchedulePnG Award FundingPnG BudgetPnG EncumbrancePnG ExpenditurePnG Funding PatternPnG RevenuePO ReceivingPO RequisitionPO DistributionHRMS Change LogPnG SummaryPO ContractPayrollSales OrderiRecruitmentMainframe ModelsMCG FAMIS DocumentMCG FAMIS General LedgerMCG FAMIS Operating Budget MO13 MCG FAMIS Operating SummaryMCG FAMIS PaymentsMCG FAMIS Revenue MO13 MCG FAMIS SubsidaryAccountMCG FAMIS TransactionMCG FAMIS Trial BalanceMCG Legacy ADPICS Change OrderMCG Legacy ADPICS InvoiceMCG Legacy ADPICS Purchase OrderMCG Legacy ADPICS RequisitionMCG Legacy ADPICS VoucherMCG Legacy FAACSMCG Legacy FHADA History AdjustmentsMCG Legacy Job HistoryMCG Legacy OHR HRB Def CompMCG Legacy Payroll Biweekly GrossMCG Legacy Payroll EarningsMCG Legacy Payroll Gross CY 2010 MCG Legacy Payroll Hours CY2010 MCG Legacy Payroll Year to DateMCG Payroll Gross-to-NetExisting Dashboards: Turn on obiee Usage Tracking and Monitor the obiee Performance Set Baseline and Investigate/Resolve Performance SpikeEnhancement and KPI Phase Focus Key Performance Indicators Other obiee Capability BI Agent BI publisher GIS Etc.

7 Expanding Datasets beyond Oracle EBS Complex Analysis and Report Real Time and Semi-Real Time Report Expand User Acceptance and Ease of UseEnhancement and KPI Phase (cont.) Challenge Data Size Actionable KPI Data Complexity Data Archiving Data Ownership On-Going PerformanceSummary BI requires a lot of up-front effort, but the end result is worth it. Complex reports are faster and easier to create than without the BI tool Functional User can create/own their individual report without technical assistance Enables users to become familiar with and better understand their Business data to make more informed decisions Takes significant load off from the source system Summary Current County BI Statistics 77 BI models with 25 modelson the old mainframe dataset 41 Dashboards in Production 472 different dimensions 10 to 100 attributes within the various dimensions 1 terabyte data size Avg.

8 400 unique access per month Avg. 22,000 queries per month Avg. query response time is 9 seconds 3 models with more than100 million records 10+ models with more than10 million records 2 hours to refresh the data 50 Automatic BI Agent/Publisher Alert Report Over 70 Custom BI publisher Report Almost an infinite number of BI Analyst reports can be generatedTraditional report vs BI reportQuestions?Sing ChanDepartment of Technology