Business Intelligence and the Oracle 9iAS Stack

2. Session id: 36650 Business Intelligence and the Oracle 9iAS Stack Matt RhoadesManager – Data Warehouse Shared Services Henkel Corporation

3. Agenda • Henkel overview • Business intelligence requirements • Deploying Business Intelligence to the enterprise • Building the Business Intelligence system • Oracle Discoverer • Tips / tricks • Summary

4. Henkel Corporation • A Duesseldorf, Germany-based multinational conglomerate with €9.9 billion in revenues last year • 300 subsidiaries worldwide • Hundreds of well-known brands • Well known in the industrial chemicals and surfaces technologies space.

5. Henkel N.A. Data Warehouse Shared Services Developed a shared service organization to act as an internal consulting team for Business Intelligence, servicing all North American companies. North American Locations Centralized Data Center

6. Henkel N.A. Data Warehouse Shared Services • The team • 3 Oracle DBA’s • 6 Developers • * 1 Business Analyst / Designer

7. A business analyst? Of course! • Best practice • Understands the business, its users and the processes. • Typically a power user from the business • Train this person in data warehouse design and the OLAP tools • The “middle-man” between the users and the technical team developing the data warehouse • Requirements: • Generally has some tenure with the company so he/she understands the business well. • Works well with different types of users (from admin assistants to executives) • Picks up technology fairly easily • Multi-faceted – can gather requirements, design, build reports and tools, validate information and train users

8. The Business Intelligence requirements • Ease of use • Powerful analysis • Integration with other applications • Many different ERP systems (J.D.Edwards, SAP, BPCS, Prism, etc.) • Timely access to accurate information • Centralized data center • Access anywhere • Personal information • Customized look and feel

9. Why is Oracle9iAS the right fit? Web Services EnterpriseIntegration J2EE and Web Applications Business Intelligence Portals Wireless Discoverer

10. Deploying Oracle 9iAS to the enterprise Integration with the database and OLAP Tools Crosstabs Graphs List of Workbooks

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

12. Deploying Business Intelligence to the enterprise • Central repository for all enterprise information • Easier to manage • Users will have a much higher confidence level in the data • Designate certain users to be accountable for verifying numbers

13. Deploying Business Intelligence to the enterprise • Executives need to reinforce that the data warehouse is the only place to get company information. • Executives control the budget, and you’ll need money because it WILL GROW.

14. Setting up the infrastructure • Best to have database and 9iAS on separate servers • Large database queries can drag the portal response time down • Best to add a lot of memory to the 9iAS server to handle large number of Discoverer queries. LAN / WAN

15. No budget for separate servers?! • Common for smaller shops or when just getting started with BI. • Keep database files and software on different disks • Spread data and index data files on different disks as well • Try to add more memory to help compensate, lowers disk-thrashing • Optimize by using Materialized Views • Rebuild in the “off hours” • Pre-built datasets will drastically lesson CPU time and disk time “per-query” • DRM – Database Resource Manager

16. Setting up the infrastructure • Which Platform? Cost vs Performance • General rule: Fast, Cheap, Good (pick any two) • Windows 2003 and Linux are good platforms to start on for small to mid-sized data warehouse systems ( < 500 GB ) • UNIX threads much better and is generally faster and more stable • > 1 terabyte, UNIX is preferable Windows 2003 ? UNIX ? Linux

17. How big to size the server(s) • Gather as many statistics as you can • How many total users will you have? • Simple Discoverer Users • Complex Discoverer Users • Best guess on total concurrent queries that will be running • Average size of the query? • Can be controlled through optimization! • Don’t throw hardware at a poorly optimized system • What does Oracle suggest? • Discoverer Capacity Planning Guide and Excel Calculator • Log into http://metalink.oracle.com • Enter “236124.1” in the search field • Contains link to download the PDF and XLS files

18. What about the client requirements? • Web browser – Java enabled • Yup, that’s pretty much it!

19. Building the Business Intelligence System

20. Building the Business Intelligence System • Gather requirements • Interview the users • What information do they need to see? • What information can they use to make actionable business decisions? • Do they need ad-hoc analysis capabilities or static reports? • Can they give you any examples of what they currently use? • Where does this information come from? • Requirements for the timeliness of the information (daily, monthly)

21. Building the Business Intelligence System • Design the Data Warehouse / Data mart • Star schema – Fact or Dimension? • First separate data items into facts and dimensions Time Sales Cost Margin Customer Id Customer name State Product ID Product name Product color Country code Date Warehouse id Customers Facts Products Warehouses

22. Building the Business Intelligence System Customers Time # Customer_Id Name Address City State Zip_code Sales_rep # Date_code Month Year Day Quarter Euro_ER Can_ER Fact Table # # # # # Customer_Id Product_Id Warehouse_Id Company_code Date_code Sales Cost Margin Products Warehouses # Product_Id Name Bus_Unit Bus_Cat Prod_line Color # Whse_Id Name City State Carrier_code Carrier_name Company # Company_code Company_name

23. Building the Business Intelligence System Populating the system Customers Time ERP Facts Text XLS Products Warehouses Warehouse Builder

24. Strategy Actions Building the Business Intelligence System • Covering all levels of analysis • For everybody everywhere

25. ERP Server ODS/DW Server ERP Server EAI Enterprise Application Integration Portal Infrastrure Scope security Normalization Multi-exec Profiles Web servers XML DTD Building the Business Intelligence System J.D. Edwards BPCS • Multitier architecture provides independency between Business Logic and System

26. Building the Business Intelligence System • Joining structured and unstructured information. • Merging information from different external sources.

27. Desktop version • Web version • Viewer version Oracle9iAS Discoverer Oracle Discoverer is a standard tool to give end-users access to all information stored in the Database by only using Wizard dialogs and menus and without having to understand difficult database concepts.

28. Power user Build, modify, format queries and graphs Analysis Export to popular applications Dynamic drill, pivot, graph Building the Business Intelligence System Oracle9iAS Discoverer Plus Pure Java, Firewall support

29. Building the Business Intelligence System Scheduling Reports • Portlet queries : • pre-cached • Individually scheduled • Discoverer Plus • Workbook scheduling • Results managed • Discoverer Viewer • Access to scheduled workbooks

30. Building the Business Intelligence System • Discoverer Administrator • Spend time on your business area • Keep it simple • Develop good descriptions

31. Building the Business Intelligence System You can also use Oracle Reports for static reports

32. Q & Q U E S T I O N S A N S W E R S A