Performance in SAP BW Product Management SAP NetWeaver / BI SAP AG
by user
Comments
Transcript
Performance in SAP BW Product Management SAP NetWeaver / BI SAP AG
Performance in SAP BW Product Management SAP NetWeaver / BI SAP NetWeaver Regional Implementation Group - BI SAP AG Contents Content Overview Query Performance Aggregates OLAP Cache Pre-Calculation Data Load Performance © SAP AG 2004, Performance in SAP BW Performance Tuning in BW OLTP Performance Tuning OLTP Systems Application Development and performance tuning separated Database Performance tuning by basis experts Application Performance Tuning BW BW Performance Tuning as holistic process over application design and database configuration Database Application Performance Tuning © SAP AG 2004, Performance in SAP BW Data Modeling Checklist BW Layers Operational Store Data Warehouse Layer Multidimensional Model Dimensions of InfoCubes Line Item Dimensions Logical (MultiProvider) Partitioning Time-Dependent Master Data Non-Cumulative Key Figures © SAP AG 2004, Performance in SAP BW Contents Content Overview Query Performance Aggregates OLAP Cache Pre-Calculation Data Load Performance © SAP AG 2004, Performance in SAP BW Checklist – Query and Web Performance – Overview ST03 ST03/ /Technical TechnicalContent Content Which component contributes most? OLAP OLAP Database Database Check these points Tools 1.1. 2.2. Data DataModel Model Query QueryDefinition Definition 3.3. 4.4. Aggregates Aggregates OLAP OLAPCache Cache 5.5. Pre-Calculated Pre-CalculatedWeb Web Templates Templates 6.6. 7.7. Compressing Compressing Indices Indices 8.8. 9.9. DB DBStatistics Statistics DB DB and andbasis basis (Buffer) Parameter (Buffer) Parameter 1.1. 2.2. Data DataModel Model Query QueryDefinition Definition (including (includingOLAP OLAP features) features) 3.3. 4.4. Aggregates Aggregates OLAP OLAPCache Cache 5.5. Virtual VirtualKey KeyFigures Figures/ / Characteristics Characteristics 6.6. Authorizations Authorizations SQL SQLTrace Trace(ST05) (ST05) RSRV RSRV RSRT, RSRT,RSRTRACE RSRTRACE SQL SQLTrace Trace(ST05) (ST05) RSRT, RSRT,RSRTRACE RSRTRACE ABAP ABAPTrace Trace(SE30) (SE30) © SAP AG 2004, Performance in SAP BW Frontend Frontend 1.1. 2.2. Network Network WAN WANand andBEx BEx 3.3. 4.4. Client ClientHardware Hardware VBA VBA/ /Java Java 5.5. 6.6. Documents Documents Formatting Formatting 7.7. rd ODBO party ODBO/ /33rdparty IEMON IEMON RSRT, RSRT,RSRTRACE RSRTRACE OLAP Performance Layers Portal iView Cache Pre-Calculation / Offline Analysis Performance Performance OLAP Cache Aggregates Reuse Reuse InfoCubes © SAP AG 2004, Performance in SAP BW Aggregates Definition Materialization of aggregated subsets of InfoCube fact table data Independent structures where summary data is stored within separate, transparent InfoCubes InfoCube Benefits Transparency: Users do not notice if aggregate is hit or not Improved query performance by reducing the amount of data to be read from DB Aggregates can be created … Only on top of basic InfoCubes For dimension characteristics For navigational attributes On hierarchy levels Using time-dependent navigational attributes (as of BW 3.x) Using hierarchy levels where the structure is timedependent (as of BW 3.x) © SAP AG 2004, Performance in SAP BW Aggregates Note: Aggregates can improve query performance considerably, but keep in mind that they also impact the load performance. OLAP Cache Global OLAP Cache in SAP BW 3.x Session- and user-independent Stores Query Results z In application buffer z In cluster table or flat file (highly compressed) Benefit z Re-use z For equal queries or subsets of cached query results Invalidation z OLAP Cache and actual InfoProvider data always in-sync z Invalidation of cache entries e.g. after transaction data load, master data load/change run, meta data change Strategy z © SAP AG 2004, Performance in SAP BW Complete query results with virtually no DB table access as of the second call of the query Warm-up OLAP Cache via reporting agent/Information Broadcasting Addressing Different User Types © SAP AG 2004, Performance in SAP BW Pre-Calculated Web Templates Pre-Calculated Web Templates and Information Broadcasting: Pre-calculation is a set of techniques where you can distribute the workload of running the report to off-peak hours, and have the report result set ready for very fast access to the data HTML pre-calculation can be used for Offline Analysis Information Broadcasting in SAP BW 3.5 Targeting the Information Consumer Quick access to many reports Static reporting, usually little navigation requirements Active Reporting Authorisations Note No invalidation when new data is loaded; only timestamp is available Restricted navigation: only filtering possibles © SAP AG 2004, Performance in SAP BW Contents Content Overview Query Performance Aggregates OLAP Cache Pre-Calculation Data Load Performance © SAP AG 2004, Performance in SAP BW Checklist – Data Load Performance – Overview 1 – Technical TechnicalContent, Content,Data DataLoad LoadMonitor Monitor Which component contributes most? Extraction Extraction Check these points 1.1. 2.2. Customer CustomerExits Exits Resource ResourceUtilization Utilization 3.3. 4.4. Load LoadBalancing Balancing Data DataPackage PackageSize Size 5.5. 6.6. Indices Indiceson ontables tables Flat FlatFile Fileformat format 7.7. Content Contentvs. vs.generic generic extractor extractor Extractor ExtractorChecker Checker (RSA3), (RSA3), Tools ABAP ABAPTrace Trace(SE30), (SE30), SQL SQLTrace Trace(ST05) (ST05) © SAP AG 2004, Performance in SAP BW Transfer Transfer 1.1. 2.2. Resource ResourceContraint Contraint CPU CPU/ /Memory Memory Bottleneck Bottleneck 3.3. 4.4. Network Network Application ApplicationBuffer Buffer Synchronization Synchronization SM50 SM50 SQL SQLTrace Trace(ST05) (ST05) OS OSMonitor Monitor(ST06) (ST06) Load LoadInto IntoPSA PSA 1.1. 2.2. I/O I/OContention Contention PSA PSAPartition PartitionSize Size OS OSMonitor Monitor(ST06) (ST06) DB DBMonitor Monitor(ST04) (ST04) Checklist – Data Load Performance – Overview 2 – Which component contributes most? Technical TechnicalContent, Content,Data DataLoad LoadMonitor Monitor Transfer TransferRules Rules Update UpdateRules Rules Load LoadInto IntoData DataTargets Targets Master MasterData Data Check these points 1.1. Transformation Transformation Rules Rules/ /ABAP ABAP Coding Coding 1.1. Buffering Buffering Number Number Ranges Ranges 1.1. 2.2. Roll-up Roll-up Change ChangeRun Run 2.2. Transformation Transformation Library Library Formulas Formulas 2.2. Change Change Run Run 3.3. 4.4. Compression Compression Indices Indices 5.5. Load LoadMaster Master Data before Data before Transaction Transaction Data Data Debugger Debuggerwithin within Monitor Monitor Tools InfoCubes InfoCubes ABAP ABAPTrace Trace (SE30), (SE30), SQL SQLTrace Trace(ST05) (ST05) © SAP AG 2004, Performance in SAP BW 6.6. Buffering Buffering Number Number Ranges Ranges SQL SQLTrace Trace(ST05) (ST05) ODS ODSObjects Objects 1.1. Parallel Parallel ODS ODS activation activation 2.2. Unique UniqueData Data Records Records 3.3. Flag FlagBEx BEx Reporting Reporting 4.4. Indices Indices Summary Design Designyour yourData Data Model ModelPrudently Prudently and andConsider Consider Regular RegularReviews Reviews Compress Compress Regularly Regularly Use UseAggregates Aggregates and andDesign DesignThem Them Carefully Carefully Use Usecustomer customeroriented orientedreporting reporting &&analysis analysis features features © SAP AG 2004, Performance in SAP BW Further Information © SAP AG 2004, Performance in SAP BW Î How-To Papers http://service.sap.com/BW (Alias ‚BW‘) > Services and Implementation Î SAP Service Marketplace: Use ALIAS: “BW” at http://service.sap.com/BW See Performance Folder Î SAP BW Online Documentation http://help.sap.com Questions? Q&A © SAP AG 2004, Performance in SAP BW