1 / 49

Implementing a global Data Warehouse: Approach, architecture and what you need to know before you start

Implementing a global Data Warehouse: Approach, architecture and what you need to know before you start. Dr. Bjarne Berg Lenoir-Rhyne College. What We’ll Cover…. Why build a global BW system? Designing a global BW architecture The six dimensions of global BW project management

lada
Download Presentation

Implementing a global Data Warehouse: Approach, architecture and what you need to know before you start

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. Implementing a global Data Warehouse: Approach, architecture and what you need to know before you start Dr. Bjarne Berg Lenoir-Rhyne College

  2. What We’ll Cover….. • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • An in-depth look at a global Telecom • A global industrial company • A glance at four other global BW implementations • Getting the team together • Lessons learned: global BW project management • Project Management, the team composition, the BW Product and other lessons learned • Wrap-up

  3. What We’ll Cover… • Why build a global BW system? • Business case • Scope • Approach • Performance measures • Tool selection • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Wrap-up

  4. 82% of senior executives believe that the information they need to make decisions is available in the company, but very hard to get a hold of. Source: Forbes Magazine Where Does Senior Management Think It Is? Why??

  5. 82% of senior executives believe that the information they need to make decisions is available in the company, but very hard to get a hold of. Source: Forbes Magazine, 2003 Why is Management Not Getting What It Wants? 1. Reporting is still organized around departmental functions 2. Reporting is organized around geographical boundaries 3. Tools are not standardized 4. The focus has been on standardizing processes Most importantly: A global enterprise reporting architecture has not been implemented

  6. Why Consider a Global Data Warehousing Solution? • The more access a company has to global information, the faster it can respond to opportunities, threats and risks. • Monthly performance reporting is simply not adequate to run a modern multi-national organization… Integrated Analytics BI DW (BW) Operational Data Store (BW) Transactions (i.e. R/3) “The advantages that data warehousing offers — faster market response, reduced operating costs, knowledge-based strategic decision support, and more — have made it a required tool of the global economy.” Paul Foote in “State of the Marketplace”. Faulkner Information Services

  7. BI Analytics vs. Reporting Decide early on how much analytics vs. basic reporting the team is going to deliver. Balanced scorecards based on key performance indicators require more substantial more work than creating simple financial reports. How will users access data in multiple areas? BI Analytics contains pre-developed rules to view or examine data

  8. What Facts and Activities Drives the Company? Produce Develop Manage Perform Manage Perform Products/ Products/ Logistics/ Marketing/ Customer Procurement Service Services Distribution Sales Service • Research Customer/ • Manage • Manage Engineering • Plan Inventory Levels • Develop Market Strategies • Process Customer Orders Market Needs Vendor/Contractor Changes • Manage Finished Goods • Develop Marketing Plan • Manage Product • Conduct Basic Research Relationships • Manage Product/Service Inventory • Develop Assortment/Brand Plan Packaging/Configuration • Design & Develop • Order Materials/Supplies Quality • Manage Outbound Product • Develop Product Packaging • Manage Product/Service Pricing Products / Services • Manage Inbound Logistics • Obtain, Install, & Maintain Flow • Create Demand Forecast • Manage Scheduling • Test-market Product • Receive Production Equipment • Manage Transportation • Establish & Manage Distribution • Manage Customer Credit • Develop Resource Materials/Supplies • Develop & Maintain • Perform Shipping Channels Ratings Requirements Plan • Manage Material/Supply Production Procedures • Manage • Manage Finished Goods • Handle Inquiries/Complaints • Develop & Implement Quality • Plan Capacity Warehouses/Distribution Inventory • Collect Customer Data Manufacturing Processes • Manage Raw • Plan Production Centers • Manage In - Store Merchandising • Provide Customer Service • Develop & Implement Material/Feedstock Requirements • Manage Sales Force/Brokers • Handle Service Processes Inventory • Schedule Production • Plan & Execute Promotional Warranties/Claims/Returns • Return Materials to • Produce & Package Events Vendors Products/Services • Qualify & Select • Perform Production Vendors/Contractors Control • Manage Work - In - Process Inventory • Develop & Maintain Bills of Material/Formulae STEP 1: Determine what activities in the supply chain drives the profit of your company. Regardless of organizational, geographical or system boundaries. Do not build a global system around what data is easily "available".

  9. Develop Perform Produce Products/ Services Determine Your Global Performance Measures • Changeover/Turnaround (Cycle Time) • Defects/Off-Quality (Cost) • Defects/Off-Quality (Volume/Quantity) • Engineering Design Changes (Cycle Times) • Engineering Design Changes (Volume/Frequency) • Equipment/Labor (Utilization) • Headcount • Inventory Work In Process (Level/Value) • Manufacturing (Cycle Time) • Parts/Stock Keeping Units (Number) • Process Steps (Number) • Production Lot/Batch Size • Production Schedule (Accuracy/Fulfillment) • Productivity/Throughput • Quality of Service • Rework (Cost) • Rework (Volume/Frequency) • Scheduled Maintenance (Cost) • Scheduled Maintenance (Cycle Time) • Scheduled Maintenance (Frequency) • Scrap/Waste (Cost) • Theft/Shrinkage (Cost) • Unscheduled Maintenance (Cost) • Unscheduled Maintenance (Cycle Time) • Unscheduled Maintenance (Frequency) • Equipment/Labor (Utilization) • Headcount • Process Steps (Number • Product Development (Cost) • Product Development (Cycle Time) • Product Introduction (Number) • Schedule/Cost Estimates (Accuracy) • Equipment/Labor (Utilization) • Headcount • Process Steps (Number) • Purchase Discounts (Value) • Purchase Order (Volume/Frequency) • Purchase Price Variance (Value) • Purchasing (Cost) • Purchasing (Cycle Time) • Supplier Defects (Number) • Supplier Lead Time • Supplier On-time Delivery • Suppliers (Number) STEP 2: Determine what performance measures you need to track in BW. Consider what successful companies in your industry are doing..

  10. Look to the Industry for Best Performance Measure Practices Market / Sell Products / Services Manage Customer Service Distribute Products • Advertising Effectiveness (Awareness) • Advertising Effectiveness (Perception) • Annual Purchase Volume • Closure/Conversion Rate • Customer Complaints (Volume/Frequency) • Customer Retention Rates • Customer Returns (Number) • Design/Formulation/Package Changes Distribution Channels (Number) • Event ROI • Forecast (Accuracy) • Forecast (Cycle Time) • Headcount • In-Stock Ratio on Promoted items/Rainchecks • Marketing (Cost) • Marketing (Cycle Time) • Marketing Effectiveness (Cost) • Marketing Effectiveness (Cycle Time) • Product/Brand Forecast (Accuracy) • Product/Brand Forecast (Cycle Time) • Shelf/Floor Allotment • Shopping Frequency • SKU’s (Number) • Traffic Count & Transaction Size • Variance to Plan (Market Share) • Variance to Plan (Production Cost/Volume) • Variance to Plan (Sales Value/Units) • Adjusted Orders (Volume/Frequency) • Backorders/Stockouts (Volume/Frequency) • Billing (Cost) • Billing (Cycle Time) • Credit/Debit Memos (Volume/Frequency) • Customer Satisfaction Rating • Equipment/Labor (Utilization) • Headcount • Inquiries/Complaints (Volume/Frequency) • On-time Delivery Rate • Order Fill Ratio • Order Fulfillment (Cycle Time) • Order Processing (Cycle Time) • Order Processing (volume) • Process Steps (Number) • Response/Wait Time • Warranties/Claims/Returns (Cost) • Warranties/Claims/Returns (Volume/Frequency) • Carriers (Number) • Dock-to-Stock (Cycle Time) • Equipment/Labor (Utilization) • Headcount • Inventory (Accuracy) • Inventory Finished Goods (Level/Value) • Inventory Finished Goods (Turnover) • Inventory Intransit (Level/Value) • Inventory Raw Materials (Level/Value) • Inventory Raw Materials (Turnover) • Picking (Accuracy) • Picking/Packing (Cycle Time) • Process Steps (Number) NOTE: The performance measures may be different than those you are reporting on today… Ignore organizational, geographical or system boundaries.

  11. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Wrap-up

  12. Real-time Inquiry Operational Reporting Management Information Lightly Summarized More Summarized More Ad Hoc ERP DW Dividing Line What Logically Belongs in a Global BW System? Four years ago, with version 3.0B, BW became increasingly able to report on operational detailed data. But some reports still belong in R/3 or other transactions systems…

  13. The Global Target Architecture – An Example Meta Data Source Data OperationalData Store DataWarehouse Extract Transform Access Managed Query Env. Purchasing R/3 Data ExtractionTransform and Load Processes Marketing& Sales OLAP Legacy Systems Translate Corporate Summarize Data Subsets by Segment External systems Product Line Batch Reporting Calculate Summation Location Messaging SummarizedData Attribute Finance Data Mining Synchronize Supply Internet Reconcile Data Marts Vendor Provided Data Warehouse and Decision Support Framework

  14. * Rapidly improving content Where do I start? All functional areas are not equally supported by strong standard SAP BW business content. Some areas have much you can leverage, others will require significant enhancement to meet your requirements The differences are often due to customization on the R/3-side by companies and/or industry solutions. Focus on an area that solves a problem instead of becoming a "replacement" project. Gradually, using a prioritized phased approach, solve other business problems. A good way to think of a BW rollout is in terms of business problems.

  15. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Wrap-up

  16. The Six Global Dimensions There are six core global dimensions you must consider before embarking on a global DW strategy. Project management is important, but it’s only one of these dimensions. Failure to account for the others may result in project failures. Source: Peter Grottendieck, Siemens For each dimension, articulate an approach, constraints, limitations and assumptions before you start your project.

  17. Be aware that US management styles can often come across as very aggressive and authoritative. To get local buy-in, assign meaningful leadership roles to local managers. The Six Global Dimensions (cont.) Intercultural Know How Culture, language, attitudes and politics can get in the way of a global project… Make sure you have a blend of local resources in leadership roles and consider local consultants instead of bringing in US resources…

  18. The Six Global Dimensions (cont.) One of the first steps is to make sure you have reliable connectivity and bandwidth to move the data each night… Infrastructure Prerequisites What happens if the data movement fails? How can you get access to backup tapes? Can the bandwidth handle end-of month high volumes? What infrastructure do each source site use?

  19. The Six Global Dimensions (cont.) Do all team members and end-users communicate as effective in English? Training Documentation • Do we need multi-language training and documentation? • Does basic conversational English mean that users can read and understand technical training material and documentation? • Have you installed Unicode on your BW system?

  20. Plan for solving Global SAP BI project issues Source: Leveraging resources in global software development Battin, Crocker, Kreidler, Subramanian, Software, IEEE Having IT people engaged in SAP BI global development without providing the right infrastructure, approach and management is a recipe for failure

  21. Coordination of Multiple Data Warehouse Projects Tight Central Control (24%) Loose Cooperation (38%) Independent (38%) 88% Successful 30% Successful 100% Successful How Tightly Should Multiple BW Projects be Controlled? The relationship between global control and success: Source: The Conference Board Survey

  22. Six ways to organizing the Global BI development effort The more distributed the SAP BI development effort becomes, the more difficult it is to maintain communication and get cohesive requirements.

  23. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • An in-depth look at a global Telecom • A global industrial company • A glance at four other global BW implementations • Getting the team together • Lessons learned: global BW project management • Wrap-up

  24. Let’s Look at a Global BW Project Example A case study • Fortune 100 company with operations around the world • 230 systems identified as “mission critical” • 23 installations of SAP R/3 on 6 continents • Other ERP systems: • JD Edwards • Custom-developed Oracle systems

  25. Data Warehouse Initiatives A case study These were the DW initiatives that corporate HQ knew about

  26. CONTINUE CHANGE Alternative Global BW Approaches TOP-DOWN APPROACH BOTTOM-UP APPROACH Build a global data warehouse for the company, and proceed sourcing data from old legacy systems driven from a top-down approach. Focus on a bottom-up approach where the BW project will prioritize supporting and delivering local BW solutions, thereby setting the actual establishment of the global Data Warehouse as secondary, BUT not forgotten.

  27. Bottom-Up Approach Rationale

  28. The project delivered local SAP BW solutions and packaged solutions for decision support as a first priority, and the Global Data Warehouse as a second priority. A “fixed departure approach” was applied with focus on delivering solutions rather than projects and software; specific BW solutions were developed according to a pre-defined schedule where local business units were invited or encouraged to participate. “Bottom-Up Fixed Departure” CHANGE Departure II - 3 months Departure IV - 3 months Departure I - 3 months Departure III - 3 months SAP BW Rollout Approach

  29. A Global Rollout – a Different European Example In this case, the company created both a local and global BW system for CRM data UK North West (Den Haag) Local AMC/Dev Spiridon/CRM Local AMC/Dev Spiridon/CRM BW others Ireland Spiridon CRM CRM (one client) others Switzerland Global Development Spiridon/CRM Mid South (Wien) Netherlands Local AMC/Dev e.p@ss/CRM BW e.p@ss CRM BW others South West (Madrid) Turkey Austria Local AMC/Dev Spiridon/CRM BW Belgium Spiridon CRM Portugal Spain Source: Siemens Corp information

  30. Fortune-500 Retailer Very large global telecom Co. Global oil co. Global oil co. BW version 3.1c 3.1c 3.0b 3.2 5-20 million Largest cubes have 18.8 35 million rows 120 million records in transactional records million, 18.4 million, and sales, and 230GB in Largest Volume in FI cubes 11.2 million records Sales and finance each. Keep scope and Should not have gone Data movement is Custom coding cannot development effort “live” on 1.2a, should the most complex overcome the BW focused, use more have used more than part of BW. The extractors. Integration than one one presentation tool. project would not with non-R/3 data was Lessons presentation tool, have accepted as technically easy, but The extract and load learned don’t underestimate many enhancements conceptually hard. process is the most if done again. the extract and load The team members must complex, strong BW You need a really . have solid BW skills effort experience is essential strong BW architect Standardized global Creation of corporate SAP R/3 was being Custom global reporting reporting enterprise-wide data installed, and SAP has a too-high cost Business warehouse BW is the reporting of ownership and is too drivers strategy for all key hard to manage. Want performance content and features. indicators Very happy with Overall happy. Have Very happy with the Is being rolled out to more subsidiaries and management is pleased with results implementation accomplished in 6 speed of delivery and Success added 3 more months what would user satisfaction countries last year have taken 5 years. Some Lessons Learned From Other Global Implementations The major findings highlight the need for specialized BW skills and very strong scope control…

  31. Example Summary • A conceptual architecture is the first step and the physical architecture is a product of this. It should be driven by the user needs and the types of interfaces needed, and not by an internal IT exercise. • SAP BW can now be used as an enterprise Data Warehouse and a Global rollout can be accomplished. • There are two core ways to succeed, but both require strong central control and support.

  32. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Wrap-up

  33. Practical Tips: Getting The Global Team Together • Involve relevant business departments, regardless of organizational and geographical boundaries. • Create a user acceptance team with a total of 5-7 members from the various business departments or organizations. Keep the number odd to assist with votes when decisions are made. With fewer than 5 members it can be hard to get enough members present during some meetings. • Make the team the focus of requirements-gathering in the early phase and let this team later become the user acceptance team (testing) in the realization phase. • Meet with the team at least once a month during realization to refine requirements as you are building and have something to show the team. This approach is hard to execute when also managing scope, but essential to make sure the system meets the requirements

  34. Basis and functional R/3 support Practical Tips: Getting The Global Team Together (cont.) Tip: Keep back-end developers centralized, while query developers can be de-centralized…. These are roles not positions. (sometimes one team member can fill more than one role) 15-25 team members and normally 6-18 months duration depending on scope

  35. Sleep, Travel and Time Zones….. • People crossing 4 or more time zones need over 36 hours to adjust! This increases to over 72 hours when crossing 6 or more time zones. Some simple rules to address this: • Create a "project time" in the middle. I.e. for European and US projects, middle time would be Eastern US time +3 hrs, and European central times less 3 hours. No meetings would be scheduled between 8-11am in Europe, nor between 2-5pm in the US. Source: Leveraging resources in global software development Battin, Crocker, Kreidler, Subramanian, Software, IEEE • Fly to the destination the day before, or allow at least 4 hours downtime for sleeping and showering at the hotel. • Don’t schedule meeting times around when people are traveling. • Keep each trip over 5 days minimum to adjust for sleep, or risk running the team "into the ground"… • Plan extended weekends for family time for staff after a long trip (including consultants)…

  36. Effort, Duration and Mistakes on Global BI Projects Source: “Planning and improving global software development process” by Setamanit, Wakeland, Raffo, May 2006, international workshop on Global software development Recent research have demonstrated that global projects that spends more days (duration) on similar tasks, have less defects and less re-work. Since team members are more likely to work on multiple tasks not related to the project, longer durations on developing the SAP BI system does not mean more effort (i.e. work hours).

  37. Global Project Risk Mitigation Strategies State 3 items in every design, budget and final deliverable: L- Limitations (what are the assumed, existing and design limitations) A - Assumptions (what assumptions are made, and what happens when these assumptions are no longer true?) R - Risks (what are the risks created by this approach, what are the impacts of failure, and how can these risks be minimized) Developers, designers and business analysts should be forced to write at least one paragraph on each of these item. It forces new thinking as well as the constant questioning of assumptions (which may not be accurate).

  38. Global Project Risk Mitigation Strategies Add 15% more project time for travel and adjustments Rotate travel so that the stress is more evenly distributed on the team Plan to spend 5-10 days at the beginning of the project to level set and build trust and social networks before the real work begins. Create a formal escalation process of issues related to the project and make sure one culture does not dominate. Select a project language formally and make sure all team members are proficient in it. Spend time rewarding inter-team cooperation and create opportunities for promotion within and outside both teams (“cross pollinate”)

  39. The Use of Local “Ambassadors” Getting power users involved early is important to the overall success of a Data Warehousing project. To help support the businesses that have already gone live, a strong local community of “ambassadors” is needed. If you don’t have them, on-going projects may get “bogged down” with basic support of reports.

  40. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Project management • Team composition • BW Product • Other lessons • Wrap-up

  41. Lessons Learned: Global Project Management • A user acceptance team (UAT) of 5-7 people should be created from the first day, and all acceptance criteria should be established well in advance of the implementation • Use of Rapid Application Development is the preferred development methodology • Use a phased business content approach with standard delivered content first, then customize if absolutely needed • It is hard to estimate accurately the data movement effort — 80% of delays and surprises occur in this area, and this work is often under-estimated • Treat the workplan only as a tool and adjust it as needed • Spend less time on the project preparation phase and as much as possible on the realization phase. Many issues cannot be planned, but time can be set aside to deal with them.

  42. Lessons Learned: Team Composition • Developer training should start early for all project team members • SAP R/3 skills are not easily transferable to BW — hands-on experience is needed (it’s hard to learn while being productive) • The quality of the team members is much more important than the number of members. A skilled BW developer can accomplish in one day what 3 novice developers can do in a week. • Project time and cost estimates should be based on teams’ experience levels • Plan on formal knowledge transfer from external resources starting from day one. Link inexperienced members with experienced ones • Have identified “go-to” resources available in all areas (make a list)

  43. Lessons Learned: The BW Product • The time to develop BW will depend on how much customization was done when R/3 was installed • The tool has a high learning curve and training cannot substitute for experience. • Plan on spending 10-15% of overall effort on performance tuning of queries and data loads. Test the performance as part of the development effort. • Implementation of LIS, SIS, EIS are no longer needed to use most standard extractors from BW, but most extractors are normally enhanced. Plan on using 50-60% of the project effort on data extraction, movement, validation, load, scheduling and testing.

  44. Lessons Learned: The BW Product (cont.) • Use the statistics cube to monitor system performance and don’t forget to use the cost-based optimizer if you are using Oracle as your database • Direct updates to InfoCubes (non-loads) are complex. If this is needed for reconciliation efforts, create a data staging area, make changes here and re-load the data. Direct cube updates for non SEM/ APO, SCEM cubes are hard to make work in practice. • Do not succumb to using BW as a dumping ground - some reports belongs in R/3. Finally, do not attempt to “cram” all data into one cube. Keep InfoCubes logically organized and use multi-providers as needed.

  45. Other Lessons Learned • Global user training should be custom-made and tailored to each country or region, and delivered by a local resource. • A global line support organization should be established and be part of the development effort (knowledge transfer). • Buy hardware early (international delivery times can delay the project) • Locate the users as soon as possible and take a look at the network • Finally, create “Ambassadors”, “road shows” and/or “brown-bag” sessions.

  46. Where Are We? • Why build a global BW system? • Designing a global BW architecture • The six dimensions of global BW project management • Global BW project examples • Getting the team together • Lessons learned: global BW project management • Wrap-up

  47. Resources • Global Project Management Handbook by David L. Cleland, Roland Gareis. Hardcover: 672 pages. McGraw-Hill Professional; ISBN: 0070113297 • International Journal Of Project Management, Magazine Publisher: Elsevier Ltd ASIN: B00007AYDS • The Distance Manager: A Hands On Guide to Managing Off-Site Employees and Virtual Teams by Kimball Fisher, Mareen Fisher. Hardcover: 252 pages Publisher: McGraw-Hill; ISBN: 0071360654

  48. 7 Key Points to Take Home • Use the 6 global dimensions framework to guide your BW development • Plan for a truly Enterprise Architecture that is designed, not evolved • Spend much time on getting the right resources on your team • Involve the local staff in a proactive manner and make them part of your leadership team. • Don’t re-invent the wheel — use experienced resources that have done it before and pay particular attention to management styles, politics and culture. • Conduct post-implementation reviews with each local organization in order to learn from experience and to give the subsidiaries a voice in how the project is executed. • Consider an "ambassador" concept to assist in local support and buy-in.

  49. Your Turn!!! Questions? How to contact me: Bberg@MyITgroup.com

More Related