1 / 59

Carolina Yalamova NDB Ltd.

IBM Business Intelligence. Carolina Yalamova NDB Ltd. Anatomy of Business Intelligence Solutions. Data. Business Actions. Discovery. Transaction Operational Demographic Lifestyle Financial Economic Government Associations. Customer retention programs Where to advertise

aolani
Download Presentation

Carolina Yalamova NDB Ltd.

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. IBM Business Intelligence Carolina Yalamova NDB Ltd.

  2. Anatomy of Business Intelligence Solutions Data Business Actions Discovery • Transaction • Operational • Demographic • Lifestyle • Financial • Economic • Government • Associations • Customer retention programs • Where to advertise • Optimize mailing list • Maximize store layout • Cross-product marketing • Improved risk profiles • Funds allocation • Customer segmentation • Demand & forecasting • Affinity merchandising • Product & customer profitability • Fraud patterns & profiles

  3. Why Should Customers Do BI projects Now? % Average ROI = 431% 30 25 % of Clients Achieving This ROI 20 15 10 5 1-50 51-100 101-500 501-1000 1000+ % ROI Achieved Source: IDC,The Financial Impact of Business Analytics study, Nov. 2002

  4. End-User's productivity Source: Gartner Group

  5. ETL Business Intelligence Architecture Operational Databases Production Clean it up Consolidate Extract, transform, merge, load metadata A single version of the truth Enterprise Data Warehouse Slices of data for departments Line of Business Data Marts End user Delivery & analysis End User Analytic Tools Data Mining Query & Reporting Spatial DTG

  6. MOLAP, HOLAP, ROLAP

  7. IBM Business IntelligenceSoftware Overview

  8. The DB2 Framework for Business Intelligence SQL XML PMML ETL OLAP Data Mining Scalability Administration DB2 Information Integration Build BI function into the database, as part of an integrated BI platform, exposed solely through open, standard interfaces, and collaborating with partners for other layers of the BI architecture

  9. ETL Data Mining Query & Reporting Spatial IBM Products Mapping Operational Databases Production DB2 WH Center DB2 WH Manager DB2 II Extract, transform, merge, load metadata DB2 ESE DB2 Query Patroller DB2 Cube Views DB2 DPF Enterprise Data Warehouse DB2 OLAP Server Line of Business Data Marts Intelligent Miner Spatial Extenders Office Connect QMF Alphablox End User Analytic Tools DTG

  10. IBM Business IntelligenceDB2

  11. Fast Data Access - Multidimensional Clustering • Data is organized on disk based on dimensions • Eg. store data on disk by Region and Year • Queries skip portions of table that we know don’t qualify • i.e. no need to scan 1997 data if query is asking for 2000 • Data automatically clustered • Never need to recluster data (i.e. no REORG) • Customer Quote: • “The new multidimensional data clustering capability has improved performance of our most complex queries by up to 30 times while removing the need for additional reorganization.” Ken Ross, Senior Vice President and CTO, Thomson West

  12. Multi-Dimensional Clustering Advisor • The MDC advisor feature of the DB2 Design Advisor recommends MDC clustering dimensions, including coarsifications on base columns in order to improve workload performance • This includes potentially recommending generated columns that define coarsification of dimensions. • Cost benefit analysis includes impact of MDC on Insert/Update/Delete activity against dimensions (which may cause records to move cells). • Cost benefit analysis also models the potential negative effect of MDC caused by table expansion. • The MDC advisor has a goal to select MDC solutions that result in a moderate table expansion Benefit: Competitive Advantage, Customer Requirement

  13. Fast Data Access - Materialized Query Tables • Caches results for faster data access • Cache automatically maintained as base data is modified • Queries automatically rerouted to use MQTs that qualify • MQTs can cache data from other platforms • Including Oracle, Microsoft and other data sources Oracle Server DB2 Server MQT2 MQT_1 Table 1 Table 2 Table 3 Oracle Table 1

  14. Materialized Query Table Advisor • An advisor to recommend creation (and removal) of Materialized Query Tables (formerly known as AST's in DB2) based on submitted SQL workload • Very difficult to choose the correct MQTs to create without the help of this wizard • Much more complex than deciding which indexes to create • Includes enhancements to the index wizard • allow indexes and MQTs to be recommended together (including recommendations for indexes on MQTs). • "db2advis" command with added flags to allow index and MQT recommendations • This feature procedure will also be accessible using a GUI interface. Benefit: Competitive Advantage, Customer Requirement

  15. IBM Business IntelligenceOLAP Server

  16. DB2 High concurrency option Partitioning option source data Multi-dimensional Cubes DB2 OLAP Server Analyzer, Brio, or Windows Client DB2 OIS DB2 OLAP Server SQL interface Essbase API MS Excel macros Currency conversions

  17. DB2 OLAP Server v8 Summary • Hybrid Analysis -- cubes with performance of DB2 OLAP with transparent link to highly scalable relational detail data • Integration Server • Parallelism • Load, Calc, Export • OLAP Miner -- end user tool to discover unknown trends and opportunities • Enterprise Services for high availability • load balancing, • connection pooling, • clustering • failover capabilities • Administration Services -central management of OLAP Servers from anywhere in the enterprise

  18. DB2 OLAP Server- Benefits Benefit DTG

  19. IBM Business IntelligenceDB2 Warehouse Center /DB2 Warehouse Manager

  20. ETL - Data Warehouse Center / DB2 Warehouse Manager

  21. DB2 Data Warehouse Manager and Center • DB2 Data Warehouse Center • DBA administration console • Access to most RDBMS's & flat files • Schedules and monitors database tasks • 150+ prebuilt data transformations • Parallel extracts & transforms • DB2 Warehouse Manager adds: • Extracts & transformations on remote servers via agents • Information Catalog end user metadata WPS v1.2 - The Cutting Edge View File Edit Tools Help x Home Back Forward Stop Refresh Search History Mail Print Address: http://the_call_center/customer/lookup 07/02/2001 19:29:20 home 1 cust-nbr 2 acct-code 3 first name 4 last name 5 street 6 city 7 zip 8 country 1 cust-number 2 acct_type 3 F-name 4 L-name 5 street 6 city 7 postal_code 8 country Internet DB2 Warehouse Center extract, transform, load, schedule, administrate DB2 3m DB2 Warehouse Manager IBM Metadata Information Catalog ETL agents

  22. DB2 Warehouse Manager - Benefits Benefit SQL/Java extracts & transformations • Leverage existing programming skills -- nothing proprietary • Standards based transforms are portable to applications • Reduced training costs now & in future Parallel extracts & transforms • High performance gets you through the processing "night" • Scalability to match your workload ETL Server agents • Processing at the source spreads the workload, avoids bottlenecks, and saves hardware costs • No licenses costs for agents on servers saves money 150+ transforms • Pre built transforms cover majority of tasks so you don't have to write or maintain as much software - less code, less cost • Accelerates programmer productivity DTG

  23. IBM Business IntelligenceQuery Patroller

  24. Query Patroller • Intercept query from the client • Governs & Throttles Queries • Ask DB2 the cost in resources • Queue SQL based on user's priority • Release SQL to run • Graphical tools to Administer and Report • Charge Back & Trend Statistics • Flexible result set options • Queue handling of reports returned • Interoperates with end user tools Query Patroller answer sets queued queues SQL 14 SQL 15 SQL 21 answers explains SQL 23 DB2 Optimizer Data Warehouse

  25. Query Patroller Benefits Benefit Manages SQL traffic into DB2 • Ensure smooth use of RDBMS resources, maximizing throughput, and making best use of server; prevents unnecessary server upgrades SQL query result set "caching" • let's users submit queries and pick up results later without locking up the workstation • answer sets can be shared between users reducing need to run the query once per user Historical analysis reports • analyze trends to forecast when upgrades may be needed • analyze patterns to detect problems and minimiz Prioritize user queries • ensure time critical business tasks get done • minimize performance impact of long running queries, ensuring uses with short, quick reports get through

  26. IBM Business IntelligenceOffice Connect

  27. Application Databases Client Browser Servlet Engine HTTP Server Tomcat WebSphere JDBC/ HTTP Tomcat Apache HTTP Office Connect Servlet JDBC HTML sources repository Web Edition Design time ODBC/ HTTP ODBC Microsoft Excel - Sales forecast.txt demo pg6 File Edit View Insert Format Tools Data Window Essbase Help A Z I B 100% Arial 10 ABC A B C D E F G H I J K L M N 1 Profit Actual Cola 2 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Tot 3 New York 262 245 259 276 295 357 370 368 312 247 236 271 3498 4 Florida 367 340 366 397 434 547 564 563 465 348 324 390 5105 5 Virginia 68 63 67 73 78 99 104 104 86 64 63 73 942 Office Connect

  28. Office Connect - Benefits Benefit DTG

  29. IBM Business IntelligenceDB2 Cube Views

  30. DB2 Cube Views = an OLAP Accelerator OLAP servers ROLAP Servers DOLAP Servers BI Tools Applications Query & Reporting Tools Business Functions & Operators Applications Business Functions & Operators Business Functions & Operators Net present value, allocations, write back, etc. MOLAP Engines DB2 Cube Views DB2 DTG

  31. MQT What Are DB2 Cube Views? • Wizard to design & generate cube views • Wizard for cube definition, metadata modeling, and optimization • Multi-dimensional metadata inside DB2 • Import/export utilities for metadata exchange a objects, relations • described in XML for ease of use • Used to generate the Materialized Query Table(s) • Application Programming interface • SQL & XML based interfaces from DB2 to tools & applications • CLI, ODBC, JDBC, stored procedures • OLAP Web services

  32. Cube Models & Cubes

  33. Bridges • Simple interface • Stored Procedure and XML document • Bridge into and out of DB2 • Enables end to end flow of dimensional metadata XML XML 1 1 Hyperion DB2 Cube Views BUSINESS OBJECTS DB2 Stored Procedure

  34. Aggregates are required for OLAP Data loading • MQT's for • Faster cube load • Faster drill through reports • Faster hybrid analysis • Faster Ad-hoc analysis • Faster reporting DB2 OLAP Server Hybrid Analysis Cube Build rows & columns Drill through Query Results materialized query tables

  35. Performance Advisor Administrator Aurora Model Catalog Tables Base Tables OLAP Metadata . Statistics Query history Model Information Time & Space constraints Optimization hints Data Samples MQT's

  36. IBM Business IntelligenceData Mining

  37. What is Data Mining? Data Mining summarises a set of methods to analyse very large data sets,which discover, from the innumerable amounts of detail data, new and unknown structures and patterns then present them such that they efficiently support the decision process to provide business advantage. or... finding out useful things in your data that you didn't know! "The secret to business isknowing something that someone else doesn't." Aristotle Onassis

  38. IBM Data Mining Products • DB2 Intelligent Miner Modeling • DB2 Intelligent Miner Scoring • DB2 Intelligent Miner Visualization • DB2 OLAP Miner

  39. Data Mining Techniques Discovery • Clustering (Demographic and Neural) • What logical groups exist? • Associations • What are the linkages among events? • Classification • Can I determine which category an event will fit? • What are the major factors that influence the occurence of the event? • Regression • How much? Prediction

  40. Data Mining Functions in DWE Analyst defines model and runs the analysis … Model applied real time in applications XML XML Model can be viewed standalone or over the web … using data from the data warehouse DB2 OLTP, DW or ODS IM Visualization IM Scoring IM Modeling

  41. Data Mining with IM DB2 Functions • Preprocess your data • With any SQL statement, Warehouse transformers, create VIEWs • Use IM Modeling SQL types and methods • Define mining parameters • Call IM Modeling stored procedure • Do the mining run • Call IM Visualization • Visualize and analyse the resulting mining model • Use IM Scoring function in SQL or Java bean • Apply the model to new data

  42. IBM Business IntelligenceDB2 Data Warehouse Edition

  43. What is DB2 Data Warehouse Edition? • A strategy for ensuring more BI function is easily available to more customers • Simplify: Make it easy to use, easy to buy, easy to understand • A collection of engines and tools to build data warehouses and analytic applications: • DB2 UDB Data Warehouse Enterprise Edition • DB2 UDB Data Warehouse StandardEdition • DB2 UDB Data Warehouse BaseEdition • A pricing strategy to remove cost as a barrier to BI success • The foundation for Information On Demand • via the Real Time data warehouse

  44. Data Warehouse Standard EditionWhat Is in the Box?

  45. DB2 Data Warehouse Standard Edition Pricing DTG

  46. Data WarehouseEnterprise EditionWhat Is in the Box?

  47. DB2 Data Warehouse Enterprise Edition Pricing DTG

  48. DB2 Data Warehouse Enterprise Edition Competitive List Prices * = Included n/a = not applicable ** = per server or site DTG

  49. DB2 Data Warehouse Standard Edition Competitive List Prices * = Included n/a = not applicable ** = per server or site *** = 4 CPU maximum Note: Oracle 10G Std One Edition has 2 CPU limit for $5K/CPU DTG

More Related