ERP Data Warehousing Architecture Tools and trends An

Скачать презентацию ERP Data Warehousing Architecture Tools and trends An

2712953274e68ca0cbdab9a8512fa552.ppt

Количество слайдов: 46

ERP Data Warehousing Architecture, Tools and trends An overview presentation University of North Carolina at Charlotte Dr. Bjarne Berg February 16 th, 2006

What We will cover • Enterprise Data Warehouse and BI Data Architecture • ERP BI Example – SAP Business Warehouse • ERP Data Warehouse Content and Methodologies • Data Warehouse Presentation tools • Post-implementation Reviews 2

What Do Users Really Want? Source: SAP 3

Logical Enterprise DW Architecture Metadata Source Data Extract General Ledger Other Internal Systems External Data Sources Transform Functional Area Purchasing Invoicing Systems Purchasing Systems Operation al Data Store Data Extraction Integration and Cleansing Processes Data Warehous e Custom Developed Applications Marketing and Sales Corporate Information Data Mining Translate Attribute Summation Product Line Derive Segmented Data Subsets Calculate Location Application s Summarized Data Synchronize Statistical Programs Query Access Tools Data Resource Management and Quality Assurance Source: Bjarne Berg, “Introduction to Data Warehousing”, Price Waterhouse Global System solution Center, 1997

William H. Inmon’s Corporate Information Factory (CIF) 5

Functional Systems: Characteristics Operational Data Store • acts as source to populate DW and marts • Often used for operational reporting • detailed, atomic data • huge data volumes • integrated, clean data • cross functional and cross departmental • supports data mining • may use third normal form modeling (NOT dimensional) Data Warehouse Data Mart • provides mgmt reporting • specific application or workgroup focus • summarized data • narrow scope • tuned to optimize query performance • customized or stand alone analysis • multiple departments or processes • interactive query • staging area for data marts • uses dimensional data modeling • highly summarized • single subject and department oriented • uses dimensional data modeling

Evolution of ERP Data Warehousing Level of Embedded Analytics Complex (score cards, budgeting, planning, KPI) Horizontal approach (2 nd generation) Integrated analytical (3 rd generation) Emerging (1 st generation) Vertical approach (2 nd generation) Interactive Mgmt. reporting (OLAP, MQE) Toolsets & accelerators Level of Pre-delivered Content Source: Mike Schroeck, David Zinn and Bjarne Berg, “ Integrated Analytics – Getting Increased Value from Enterprise Resource Planning Systems”, Data Management Review, May, 2002; Adapted: Bjarne Berg “How to Manage a BW Project”, BW & Portals Conference, 2004, Orlando Analytical applications for specific industries 7

All major ERP vendors have packaged DSS solutions SAP, Baan, People. Soft, Oracle, JD Edwards and others have all packaged Data warehouse solutions. This is commonly known as Business Intelligence (BI) products. There are many types of BI products: 1. Data Warehouses (DW) 2. Strategic Enterprise management (SEM) 1. 2. 3. 4. Corporate Performance mgmt (CPM) Business planning & simulation (BPS) Business Consolidation services (BCS) Stakeholder relationship mgmt (SRM) 3. Data Mining 4. Advanced Planning and Optimization (APO) 5. Supply Chain Event Manager (SCEM) 6. Customer Relationship Manager (CRM) The DW is the source of the data for all these integrated analytical applications (i. Analytics) 9

ERP Data warehousing – Example: SAP Business Warehouse architecture Source: SAP AG, 2005 10

A Detailed view of management tools within BW Source: SAP AG 2004 11

Leveraging Standard Content As a guiding principle we map requirements to standard content before we start customizing. However, we may also have external data sources that require custom ODSs and Info. Cubes. An example from a large manufacturing company Source: SAP, Sdn BW 2005 12

Vendor Methodologies Each vendor has their own methodology built on the System Development Life. Cycle (SDLC) approaches 14

Framework for picking your "poison" Source: Bjarne Berg, “How to pick JAD, RAD, XP or a SDLC Methodology for your project“, Nov. 2005, 15

Methodology to build a data warehouse Source: Bjarne Berg, “Managing a BW project “, Project Management Conference - 2004, 16

The development phase: Model & leverage standard models 1. Create a model based on pre-delivered content 2. Map your data requirements to the delivered content and identify gaps. 3. Identify where the data gaps are going to be sourced from. Storage Requirements + Standard content Map functional requirements to the standard content before you make enhancements Storag e Object s Source: Bjarne Berg, “ 25 tips & tricks for managing BW projects“, BW & Portals Conference 2005, 17

The ETL Processing of a data warehouse On a typical DW project, 40 -60% of project effort will be spent on data integration, transformation and loads (used to be higher when tools were more immature). SAP Net. Weaver has a GUI and formula editors to write complex transfer rules, update rules and data mapping. Source: Bjarne Berg, “An A to Z Guide in Managing SAP Net. Weaver Projects ” SAP Net. Weaver and Portals Conference, March. 2006, Las Vegas 18

Performance differences of architectures Source: Alexander Peter, SAP AG, 2006 19

Deciding Which Front-end To Use For A Global BW System A major decision for the global BW system is the selection of which delivery mechanism to support and who get access to which tool. Most companies start with BW OLAP for web and add other types of interfaces 21

The BW Toolset BW is an enterprisewide comprehensive decision support (DSS) system for analytical needs of an enterprise. BW can also support some degree of operational reporting This part focuses on the presentation tools Therefore it has many presentation tools and options for companies to leverage. 22

BEx what is it? BEx is the 'umbrella name' for many of the presentation tools in BW. Therefore you will notice the tool names are often pre-fixed with names such as BEx Broadcaster, BEx Query Designer and BEx workbooks etc. Source: Bjarne Berg, “Access the easy, quality and performance of your SAP BW based reporting activities ” SAP Professional Journal, Las Vegas, Chicago, Philadelphia, Orlando Oct/Nov/Dec 2005 23

The BEx Query Designer The BEx query designer is a simple tool that you use to develop queries for users. While the Query Designer defined the query, you have many options on how you deploy the queries You can deploy the queries through web templates, broadcasted emails, portals, Excel and more. 24

The BEx Ad-Hoc Query Designer A simplified version of the BEx query designer is the "ad-hoc query designer". This is a web tool that can be used by power users to define their own queries. It can be made available through a BW web template as well as directly into a web portal. 25

Using standard protocols to render in Microsoft tools 1. BEx Workbooks is Excel 'on steroids'. 2. BW queries can be opened in Excel 3. Queries can be refreshed and standard Excel functions can be used This is a great tool for financial analysts and power users. How does it work? Think ODBO…… 26

BEx web reports • The most common way of deploying BW queries are through a standard, or customized web template with built-in navigational features. • Global web templates can be enhanced with logos, colors, fonts and additional features at a low cost of development and ownership. 27

Building i. Analytics using a Web Application Designer (WAD) BEx Analyzer/ Query Designer Web Browser BEx Web Application Designer WAD is a tool to build powerful i. Analytics applications and to interact with portals and custom templates. OLAP Processor Web Service Charting Engine HTLM Templates Info. Provider Source: SAP AG BW queries can be manipulated in WAD 28

Analysis Process Designer • Using the BW analytics toolkit, it is possible to build association data sets that can be fed back into the OLTP engine. The way to approach this by using APD (analysis process designer). This is a visual data processing composition engine that enables organization of data flows and updates into the OLTP database (transaction system). Images: Bhanu Gupta, ASUG 2005 Illinois chapter 29

Batch reporting • Batch reporting refers to the mass creation of reports such as account statements, that otherwise would be impossible to create on-demand in a data warehouse with billions of records. • Crystal is the leader of batch reporting of "pixel controlled' formatted reports. 30

Different Needs and Broadcaster Tools Reporting needs Reporting tools KPI & Scorecard Formatted • Simple • Easy to view • Limited nav • Aggregates Flat Reporting • Formatted • Print • Form based • Static • Predictable access OLAP Reporting • Drill Down • Slice and Dice • Analyse • Data Mining • Search and discover “Casual” users, non-DW users, traveling users, and executives are all prime candidates for broadcasted reports! 31

Scheduling Broadcasts and exception reports We can schedule the job to run every time the infocube is changed (the data load process chain has run) We can limit the users to when they can schedule jobs to avoid stress on the system. We can provide multiple pre-defined times when they can schedule the reports. We can also allow the users to create their own scheduling times (Only authorized users see this) Source: Bjarne Berg, “SAP Be. X Broadcaster - How to make it work in practice ” - SAP Professional Journal, Philadelphia, Dec 2005 32

Data Visualization from the Data Warehouse Visual Composer tools are used to visualize high volumes of data. It is a GUI modeling and design tool for rapid application development and prototyping in a code-free development environment. Most Visual Composer tools supports both JSP and HTMLB; and some also support Web Dynpro metadata as well. SAP Net. Weaver Visual Composer 33

Example: Data Visualization from the Data Warehouse Offers the integration with BI Info. Providers (e. g. Info. Cubes, BEx Queries and Query Views, BEx Web Applications) via WEB API as well as with heterogeneous data sources (including OLAP and relational data sources) via Java Connectors 34

Data Mining Technologies Multi-Strategy IBM Intelligent Miner SAS Thinking Machines Darwin Neural Network HNC Data Mining Workstation Neural. Ware Predict Other Algorithms Data. Mind Information Discovery IDIS Tree-Based Agnoss Knowledge Seeker Pilot Discovery Server SPSS Silicon Graphics Mine. Set Visualization Advanced Visual Systems: AVS Express Neo. Vision Hypersystems: Heatmaps Visible Decisions: Discovery Visual Neumerics: PV Wave

The Data Warehouse and BI Application Testing Since the data is used for decision support and can result in poor decisions being made, testing is a big part for a data warehouse development or BI application project. Typical testing include: Unit test Integrated unit test System test Integration test Performance test 36

After go-live: Track data warehouse load performance During the first 6 weeks after each go-live, organizations should formally track the load performance by process chain to detect any systematic issues This is a real example from a Fortune-50 manufacturing company that had 77 nightly load jobs (process chains). 38

After go-live: Track Data Warehouse load performance A stabilization period after each go-live is normal until the new process chains has been tuned in the production box. This is a time when active monitoring of process chains should occur 39

Metadata – What to track • Data data sources data models physical data structures extraction, integration, and transformation rules data currency data definitions data ownership • Business Processes functions algorithms and business rules • Organization reporting structure IT initiatives and contacts • Technology hardware and software application systems query access tools databases and networks • Administrative security profiles version control back-up and recovery

Resources Five Core Metrics: The Intelligence Behind Successful Software Management – by Lawrence H. Putnam & Ware Myers Enterprise Architecture Planning : Developing a Blueprint for Data, Applications, and Technology; by Steven H. Spewak ISBN: 0471599859 Publisher: Wiley Rapid Development by Steve Mc. Connell Paperback: 680 pages ; Publisher: Microsoft Press; ISBN: 1556159005 Start to Finish Guide to IT Project Management by Jeremy Kadlec, Digital: 109 pages. Publisher: Net. Impress; ISBN: B 0000 W 86 H 2 41

More Resources Beck, Kent (1997) “Chrysler Comprehensive Compensation with XP”. , published Feb. 2001: The Agile Software Development Alliance Beck, Kent (2000). , Extreme Programming Explained: embrace change. , Addison Wesley Longman, Inc. , 2000 Berg, Bjarne (1997) “Introduction to Data Warehousing”. , module 2. , pp. 88 -107. New York. , NY. , Price Waterhouse LLP. October 1997. Berg, Bjarne (2004) “Managing BW projects – part-2”. , SAP project management conference, Las Vegas, NV, WIS publishing, November. Boehm, Barry (1986) "A Spiral Model of Software Development and Enhancement", ACM SIGSOFT Software Engineering Notes, August. 42

More Resources Botkin, John (1998). , "Customer Involved Participation as Part of the Application Development Process. " University of Maine. Caristi, James (2002) “Extreme Programming: Theory & Practices”. , Valparaiso University. , SIGSEE 2002 conference tutorial. Damian, Adrian. , Hong Danfeng. , Li, Holly. , Pan, Dong (1999) "Joint Application Development and Participatory Design". University of Calgary, Dept. of Computer Science. Dennis, Alan R. , Hayes, Glenda S. , Daniels, Robert M. Jr. (1990) "Business process modeling with group support systems". Journal of Management Information Systems. 115 -142. Spring. Gilb, Tom (1989). , “Principles of Software Engineering Management”. , Addison. Wesley Longman. Jennerich, Bill (1990). , "Joint Application Design -- Business Requirements Analysis for 43

More Resources Kettemborough, Clifford (1999). , “The Prototyping Methodology“. , Whitehead College, University of Redlands Larman , Craig. Victor R. Basili (2003). , Iterative and Incremental Development: A Brief History”. , IEEE Computer, pp. 47 -56 Martin, James (1990). , “Information Engineering Planning & Analysis, Book II”. , Prentice-Hall, Inc. Nobuhiro, Kataoka. , Hisao, Koizumi. , Kinya, Takasaki. , & Norio Shiratori (1998). "Remote Joint Application Design Process Using Package Software". 13 th International Conference on Information Networking (ICOIN '98). , January. , pp. 0495 Soltys, Roman. , Crawford, Anthony (1998) "JAD for business plans and designs". , The Process 44

More Resources The University of Texas at Austin (2004) "Joint Application Development (JAD) What do you really want? " http: //www. utexas. edu/admin/ohr/is/pubs/jad. html. Accessed on October 24. Yatco, Mei (1999) “Joint Application Design/Development”. , School of Business, University of Missouri-St. Louis. Wood, J. and D. Silver (1995), . “ Joint Application Development”. , 2 nd ed. , New York : Wiley. Wetherbe, James C. (1991). , "Executive Information Requirements: Getting It Right", MIS Quarterly, March. , p. 51. 45

Your Turn!!! How to contact me: Bergb@lrc. edu Web page: http: //csc-studentweb. lrc. edu/swp/Berg/BB_index_main. htm 46