SAP BI - Hiearchies

Develop a Service-Based Approach to Building and Maintaining a Scalable, High-Performing SAP BW Solution : 

Develop a Service-Based Approach to Building and Maintaining a Scalable, High-Performing SAP BW Solution Sergei Peleshuk ABSS Europe

What We’ll Cover … : 

1 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

How Can You Increase the Performance of SAP BW? : 

2 How Can You Increase the Performance of SAP BW? Learn what kind of business benefits you can achieve by implementing Key Performance Indicators (KPIs) and Measurements We will use working capital management as an example See how to address performance issues for master data updates and daily and weekly transactional data loads See how extractions from the source system are adjusted to fit multiple project purposes This relieves data traffic and reduces processing time on the transactional system side

How Can You Increase the Performance of SAP BW? (cont.) : 

3 How Can You Increase the Performance of SAP BW? (cont.) Get tips for improving performance Such as how changing the design and reducing the number of InfoCubes increases efficiency Learn how to automatically generate hierarchies using ABAP routines in the master data update rules

In This Session See ... : 

4 In This Session See ... How SAP BW can automate timely management of working capital Practical tips on how to approach optimal design in SAP BW using multi-layer data structures How to automate product hierarchy generation with the InfoObject update rules script

What We’ll Cover … : 

5 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

Working Capital Business Case : 

6 Working Capital Business Case What is a working capital? Current Assets – Current Liabilities $1 billion of inventory 20 manufacturing facilities 1 million raw material codes 100,000 vendors 10,000 suppliers 5,000 buyers

Working Capital KPIs : 

7 Working Capital KPIs Key Performance Indicators Days of supply Inventory value for a certain date divided by demand value per production plan for 30 days ahead and multiplied by 30 Minimum and maximum values for days of supply Minimum and maximum values for stock quantity Exceptions by supplier and site Exceptions by buyer and site

Working Capital Business Benefits : 

8 Working Capital Business Benefits Improved inventory management Days of supply Exceptions by supplier and site Exceptions by buyer and site Stock quantity Exceptions by supplier and site Exceptions by buyer and site Timely reaction to inventory changes Centralized management and control of inventory 10% leverage on $1 billion inventory releases $100 million cash

What We’ll Cover … : 

9 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

Old Reporting Setup : 

10 Old Reporting Setup

Disadvantages of the Old Setup : 

11 Disadvantages of the Old Setup Inflexible reporting layer Data load performance issues Poor scalability High maintenance cost Refresh takes over 24 hours No possibility for daily update No authorizations in place

New Reporting Setup : 

12 New Reporting Setup PSA = Persistent Staging Area

New Reporting Dimensions and Levels : 

13 New Reporting Dimensions and Levels MultiProvider Dimensions Commodity Supplier Buyer Site Time Level of Detail Stock quantity level vs. Days of supply level

Design: Reporting MultiProvider : 

14 Design: Reporting MultiProvider

Design: Reporting Layer InfoSources : 

15 Design: Reporting Layer InfoSources Improved performance Vendor or buyer allocation at daily level Daily level to weekly aggregation using Storage Loc/Type filter All reporting dimensions and KPIs in MultiProvider

Advantages of the New Setup : 

16 Advantages of the New Setup Easy to update reporting layer Data flows between SAP R/3 and SAP BW Improved data load performance Optimized business logic on SAP BW side Scalability and lower maintenance cost No need for other applications support/modifications Hierarchy for commodity codes Automated hierarchy updated Authorizations SAP BW authorizations to segregate user access New level of detail in the reports Vendor code Daily refresh

What We’ll Cover … : 

17 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

Performance: Load Same Data Only Once : 

18 Performance: Load Same Data Only Once Share objects among several services

Shared Objects for Multiple Projects : 

19 Shared Objects for Multiple Projects Master data G/L accounts Cost centers Profit centers Vendors Customers Plants Material codes Transactional data FI-GL line items PO header PO line items SO header SO line items Material movements

Performance: Implement Filters in the Update Rules : 

20 Performance: Implement Filters in the Update Rules Filter Daily Filter Weekly

Performance: Inventory Classification : 

21 Performance: Inventory Classification Plant/Site Storage Location Storage Type Daily Reporting Weekly Reporting Select storage types and locations For daily reporting For weekly reporting

Daily vs. Weekly Reporting Filters : 

22 Daily vs. Weekly Reporting Filters

What We’ll Cover … : 

23 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

Concept for Commodity Hierarchy : 

24 Concept for Commodity Hierarchy Master data mapping is specified in the text file Process all materials in the code ranges specified in the From-To segment and assign corresponding hierarchy codes Exceptions to ranges are listed in a separate text file Apply exceptions right after ranges have been processed Ranges for Material Codes Exceptions

Commodity Code Hierarchy Example : 

25 Commodity Code Hierarchy Example

Dummy Nodes to Be Generated Automatically : 

26 Dummy Nodes to Be Generated Automatically

Commodity Hierarchy Issues : 

27 Commodity Hierarchy Issues Hundreds of code ranges Hundreds of code exceptions Ranges and exceptions are modified regularly Over one million material codes Daily delta updates How do you automatically create or update hierarchy nodes based on changes in ranges, exceptions, and material codes?

Generate Hierarchy Automatically! : 

28 Generate Hierarchy Automatically! Code ranges Code exceptions Allocate material codes to hierarchy nodes and update the hierarchy Commodity codes Comm code hierarchy R/3 MM

Master Data Upload Sequence : 

29 Master Data Upload Sequence Upload commodity code ranges from flat files to the SAP BW InfoObject Upload commodity code exceptions from flat files to the SAP BW InfoObject Upload material codes from SAP R/3 to the commodity code SAP BW InfoObject When processing material codes, update the commodity codes hierarchy using the Start Routine in the update rules

Hierarchy Table Structure : 

30 Hierarchy Table Structure Hierarchy table records must be generated or updated for each commodity code level

Hierarchy Generation Script Logic : 

31 Hierarchy Generation Script Logic Fill in original material codes for lookup by name (internal table) Identify maximum hierarchy node ID Look up for sub-commodity in the exceptions list first Look up for sub-commodity in the ranges list Check if combination commodity area + sub-commodity is already in the hierarchy

Hierarchy Generation Script Logic (cont.) : 

32 Hierarchy Generation Script Logic (cont.) Identify maximum hierarchy node ID (cont.) If commodity area is new, create a new node L2 Update NextID or ChildID L2 If sub-commodity is new add sub-commodity to the hierarchy Update NextID or ChildID L3 Check if material code is already in the hierarchy Update parent ID for the existing material code node in the hierarchy Create a new node in the hierarchy for material code Update NextID or ChildID L4 Activate hierarchy — System Identifier (SID) recalculation

Hierarchy Generation Lessons : 

33 Hierarchy Generation Lessons 100,000 commodity codes 15 minutes of processing time Delta update Improves performance and reduces processing time Creating new nodes takes less time than browsing through hundreds of thousands of existing nodes (material codes)

What We’ll Cover … : 

34 What We’ll Cover … Introduction Business case example: Working capital KPIs Old inefficient data flows vs. new data flows Addressing performance in a multi-layer BW model Automatic hierarchy generation Wrap-up

Resources : 

35 Resources General tips on MultiProviders architecture http://help.sap.com/saphelp_nw04/helpdata/en/52/1ddc37a3f57a07e10000009b38f889/frameset.htm SAP Business Information Warehouse > Data Warehousing > Modeling > InfoProviders > MultiProviders General tips on editing hierarchies http://help.sap.com/saphelp_nw04/helpdata/en/52/1ddc37a3f57a07e10000009b38f889/frameset.htm SAP Business Information Warehouse > BI Platform > OLAP > Special OLAP Functions and Services > Hierarchies BW Expert Multiple articles on BI design, performance, and hierarchies Sergei Peleshuk, “6 Ways to Optimize SAP BW in Business Objects” (BW Expert, March 2006). Want to learn more? A free SAP Experts Sampler CD containing more than 20 relevant articles from BW Expert and SAP Professional Journal is available at the WIS/SAP Insider booth in the Solutions Showcase.

7 Key Points to Take Home : 

36 7 Key Points to Take Home Design InfoCubes and MultiProviders corresponding to end-user reporting requirements Create several DataStore Object layers when implementing complex business logic Generate master data hierarchies automatically whenever possible Produce tangible business benefits by delivering needed KPIs Reduce number of applications and databases participating in data processing whenever possible

7 Key Points to Take Home (cont.) : 

37 7 Key Points to Take Home (cont.) Improve performance by efficient step-by-step data processing When designing BW structures, keep in mind related projects and future enhancements allowing for data model scalability

Your Turn! : 

38 Your Turn! How to contact me: Sergei Peleshuk serpuk@yahoo.com