Developing a Corporate Business Intelligence Portal Alfred Adams

Developing a Corporate Business Intelligence Portal Alfred Adams Avanco International, Inc. alfreda@avanco. com (703) 749 -7749 ext. 218

Business Intelligence Agenda • • What is Data Warehousing? What is Business Intelligence? What are Enterprise Information Portals? The Oracle 9 i. AS BI Architecture BI Overview A BI Portal Methodology – Project Team – Project Definition – Requirements Definition – Design Prototype – User Interface Q&A

Avanco Corporate Oracle Expertise • ORACLE Customer Advisory Program (BI and Oracle Portal) ORACLE Certified Developers • Oracle 9 i. AS Portal, Discoverer, Developer, JDeveloper Known Oracle authors (Oracle Press) • Steve Vandivier ‘Oracle 9 i. AS Portal Handbook’ • Anissa Stevens ‘Oracle Security Handbook’ Oracle Data Warehousing expertise • Oracle 9 i. AS expertise • • •

IT Doesn’t Matter Harvard Business Review, May 2003 “…Information Technology has become a commodity. Affordable and accessible to everyone, it no longer offers strategic value to anyone. Scarcity – not ubiquity – makes a business resource truly strategic. Companies gain an edge by having or doing something others can’t have or do. ”

Data Rich - Information Poor “The typical Fortune 1000 company has a big problem: it collects a lot of important business data that never gets used. Users who could take data and turn it into competitive advantage can’t get the level of access they need. ” Forrester Research

Ad Hoc Operational Data Analysis • • • Jeopardizes operational processing Data is a moving target Information fluctuates Data accuracy diminishes over time Inefficient database structure for query Result - Users don’t trust the data

BI and Data Warehousing • Data Warehousing is the process of assembling • • enterprise information into a report only format that mirrors your business processes Business Intelligence is the User Interface and set of reporting tools that are assembled and constructed to offer strategic reporting In combination they offer significant Return on Investment (ROI)

Data Warehouse - Defined A Data Warehouse is that portion of an overall Architected Data Environment that serves as the single integrated source of data for Informational Processing, and is: n Subject Oriented n Integrated n Time Variant n Non-Volatile

Why a Data Warehouse? • • • Integrates enterprise business processes Organized and queried by subject areas concerned strictly with historical data updated regularly – Right time not Real Time loaded data rarely changes structured to meet information requirements of the enterprise • Result - Users trust their information

Data Warehousing Concepts Spreadsheets Operational Systems Marketing Sales Accounting Finance Human Resources Sales Finance Data Warehouse External Data Marts

Corporate Performance Management What business managers want to know. . . • What happened? • Why did it happen? • What will happen in the future? • What if we. . . ?

Oracle Business Intelligence Solutions Oracle BI Client Products Oracle Business Intelligence Systems Reports Standard Reports Discoverer Query Express Modeling, Drill Calculations Time Derived Down, What If Series, Data Pivot Financial BI Increasing Complexity

What are Enterprise Portals? “Web Applications that provide a single point of entry for corporations to identify and unlock structured and unstructured information from many sources, both inside and outside the organization, and to tap into the corporate knowledge base in order to make better more informed decisions”

Enterprise Portals What Problems do They Solve? • Serve as a Corporate Dashboard • Integrates Information Access under One Roof • Organizes Information including unstructured content • Provides Role-based Information Access • Cross Referencing of Data Items with Like Items • Collaborative Departmental Level Development • Unifies disparate IT processes

Oracle Portal Provides Unified Access to Structured and Unstructured Information Sales Portal HR Portal Business Intelligence Portal Role Based User Interface Unstructured Data Network and Operating System ERP Data Internet Warehouse Oracle Portal and Oracle 9 i ERP Portal

Oracle 9 i. AS architecture

Oracle 9 i. AS Portal Page Generation mod_plsql Page Response P aral l el P ag e Engine Page Request Oracle 9 i. AS Portal Database Tier Page Metadata mo d _o c 4 j Browser Client O racl e HT T P S erver Oracle 9 i. AS Portal Middle Tier Cached Metadata & Content Portal Cache Portal Repository Portlet Content Web Provider Database Provider

Portal Middle Tier • Oracle 9 i. AS installation acts as the Portal middle tier • Portal directly leverages the following Oracle 9 i. AS services: – Oracle HTTP Server – mod_plsql – mod_oc 4 j – Web Cache

mod_plsql • Translates URL • requests into stored procedure calls Gateway into Portal Database – Access to Portal Repository, Portal UI and Database Providers • Provides connection pooling for efficient session management

mod_oc 4 j • Apache plug-in • Routes Servlet requests to Oracle 9 i. AS Containers for J 2 EE (OC 4 J) Servlet engine • Performance • Supports latest J 2 EE specifications

Oracle 9 i. AS Web Cache • Memory-based cache • Combines caching, • compression, and assembly to accelerate both static and dynamic content Also provides load-balancing, failover, and surge protection to improve availability, reliability

Portal Development Approach • Secure Management Sponsor • Scope the Project • • – Use client and departmental questionnaires to define goals Identify the Project Team Identify Content Contributors Use user identified requirements to define the Portal structure Identify structured and unstructured content • Iteratively refine assumptions through verification and validation with users • Perform Enterprise metadata analysis • Identify content and system standards (Branding) • Organization and Navigation • Ensure ongoing content maintenance • Measure ROI

Suggested BI Portal Project Team Corporate Sponsor • Drives Project goals Em p. Elo mype EX loe EXsd s fe Ab cd e ye plo Em yee lo plo Emp Em e ye lo lo p p Em Em Em p. Elo mype EX loe EXsd s fe Ab cd Xs c. Ed Ab Portal Development Team • Portal Administrator • Application Developers • Database Administrators • Graphics Designers e ye plo Em yee lo plo Emp Em e ye lo lo p p Em Em Xs c. Ed Ab Portal Architect • Manages • Facilitates • Identifies Bottlenecks • Establishes Standards Content Providers • Content Manager(s) • Content Contributors • Graphics Designers • Quality Assurance Specialists

What makes a BI/DW project Successful? • • • Corporate sponsorship chain Management of expectations Data quality Design issues User Interface Scalability issues Performance Administration Process alignment

Scoping the Project • • Define project goals in a Mission Statement Define objectives of the site Identify short and long term goals with questionnaires Define ROI metrics Use draft objectives to hone in on site requirements Use a User and Data-centric approach Manage user expectations for the site

Requirements Definition Process 1. Define Business Processes and roles 2. Perform Enterprise Information Assessment • h 4. Define hardware, software, And security requirements 3. Define Look, Feel and Branding concepts

Requirements Definition Process • • • Define roles and privileges by user type Define user functions and business processes Perform enterprise information assessment (data architecture) Define unstructured document and content standards Define structured data and determine data quality Organize information (categories and perspectives) Define look, feel, and branding concepts Define hardware capacity Define software needs Define security requirements (SSL, firewall, user access)

Content Planning Form and Content Inventory Form

Define Corporate Data Architecture

BI Portal Design • • • Define site look and feel Applications and content Site organization Navigation methods Dimensional database design and sourcing Architecture and security design Labeling Themes User Interface Develop prototypes iteratively

Develop Dimensional Model from Enterprise Model

Discoverer Components Warehouse Builder OEM Discoverer Administrator Discoverer Plus Discoverer Viewer Oracle Net Export HTTP Discoverer Portlets Excel Reports End User Layer

Discoverer End User Layer User Sees End User Layer Ease of Use Database Client address cl_addr 1||', '|| cl_addr 2|', '|| cl_addr 3||', '|| cl_twn||', '|| cl_zipcode Total Revenue Forecast sum(nvl(fcast_qty* unit_price, 0)) Hides the Complexity of the Underlying Database

It’s All in the Metadata • • EUL simplifies user access to database Controls query environment Controls summary table aggregate awareness Provides consistent view of data User access control Object relationships grouped by business area Metadata shared with OWB/Designer repositories

Oracle Discoverer Design Issues • • • Set up and group Business Areas by business function Refine relational structure - joins, calculated items, conditions Edit/format properties using item properties work sheet Create date and Item hierarchies Create Item Classes Create date and item drills Create Summary tables Create complex folders last Develop Discoverer Viewer workbooks Develop multiple report looks/ multiple tabs

Oracle Portal and Discoverer • Worksheet Portlet – Customizable – Data and/or graph – Scheduled and cached – Calls Viewer • List of Workbooks – Customizable – Scheduled

Adding Discoverer Portlets • Ensure Discoverer Services are running • Ensure OC 4 J_BI_Forms are running • Click on Discoverer link within OEM – Enable graphs in Discoverer Viewer / Portlet – Create a public connection using Create Connection • Create workbooks with multiple work sheets – Keep Portlet charts and reports simple • Register Discoverer within the Portal Repository using • • ‘Register a Portlet Provider’ on Portal Build tab Check to ensure Discoverer Portlet is running by checking http: //<hostname. domain: 7779>/discoverer/portletprovider Add Discoverer Portlet to a Portal Page

Integrating Discoverer within Portal • Edit your Portal Page Group • Click on Add Portlets icon on the region you want to work with • Within the Portlet Repository find the Discoverer Portlet Repository and choose Worksheet • Click OK after Worksheet is displayed as shown

Adding a Discoverer Portlet • You will see an error that says “Error This Portlet has not been defined. The publisher must define the portlet by clicking on Edit Defaults…. ” • Under Editing Views at the top of the edited page, Click Layout • Choose Edit Defaults next to the worksheet portlet

Discoverer Portlet Wizard • Choose the Publishers Connection for the workbook you wish to display • Click Next and choose the workbook for display from the workbooks available – If the workbook is not listed invoke Discoverer Plus and give privileges to the publisher • Select a Worksheet for display if there are multiples

Portlet Settings Wizard • In the Portlet Settings wizard, provide a Portlet Title and choose the content type – Both crosstab/table and graph – Crosstab/table only – Graph only • Note: It is normally better to display just the report or the Graph • Set Refresh Options as appropriate for data volatility

Integrated Discoverer Portlet

Design Working Prototype • • • Define ROI for the project Organize Portal for end users Group content according to Business Process Populate dimensional model Organize information within the Portal Develop site navigation controls Brand the Portal site consistently Utilize application standards Plan remainder of the project Measure ROI after rollout

User Interface Considerations • • Use appropriate BI tools Use Portal Publish with Approval privilege Use reports and charts liberally and drill down/across Use HTML forms or Java where possible to avoid Oracle Forms and Jinitiator Use Alerts where appropriate Ensure role based security is enforced Integrate Discoverer and Developer on Portal pages Integrate non-structured information

Other Development Tools – Portal PDK • Omni Portlet – component of Oracle 9 i. AS Portal that helps page designers quickly publish data (Tabular, Chart, Form, List, News) from various different data sources (CSV, xml, Web Services, JCA, SQL and Web Page) • Web Clipping – allows you to extract specific portion or "clipping" (e. g. , HTML table) of a Web page content and publish it within Portal. • Java applications – Various Java apps including Discussion Forums and integration apps

Author Contact Information • Steve Vandivier – Http: //www. Avanco. Com – stevev@avanco. Com – Oracle 9 ias portal handbook – Osborne Mc. Graw-hill, Oracle press: ISBN 0 -07 -222249 -2