490 likes | 739 Views
Proven Techniques for Improving Reporting and Analytics Performance Using SAP NetWeaver ® BW Accelerator. Dr. Bjarne Berg Comerit Inc . In This Session . Show what SAP NetWeaver ® BW Accelerator is and how it works
E N D
Proven Techniques for Improving Reporting and Analytics Performance Using SAP NetWeaver® BW Accelerator Dr. Bjarne Berg Comerit Inc.
In This Session ... Show what SAP NetWeaver® BW Accelerator is and how it works Look what SAP BW Accelerator can and cannot do to optimize query and dashboard performance Explore how to plan and implement SAP BW Accelerator. Take a look at SAP BusinessObjects capabilities and the new SAP BusinessObjects Explorer options Examine performance benchmarks for SAP BW Accelerator Look at the future of the SAP NetWeaver Business Accelerator tool and the roadmap
What We’ll Cover … • What is SAP BW Accelerator? • Performance benchmarks • Managing and administrating SAP BW Accelerator • Planning an SAP BW Accelerator implementation • Wrap-up
Why SAP BW Accelerator? Disk speed is growing slower than other HW components Architectural Drivers Technology Drivers 1990 2009 Improvement 1990 2009 Disk-based data storage Simple consumption of apps (fat client UI, EDI) General-purpose, application-agnostic database In-memory data stores Multi-channel UI, high event volume, cross industry value chains Application-aware and intelligent data management 0.05 MIPS/$ 8.30 MIPS/$ 166x CPU 0.02 MB/$ 8.51 MB/$ 425x Memory 216 264 248x Addressable Memory 100 Mbps 10 Gbps 100 x Network Speed 5 MBPS 400 MBPS 80x Source: 1990 numbers SAP AG 2009 numbers, Dr. Berg Disk Data Transfer Physical hard drive speeds only grew by 80 times since 1990. All other hardware components grew faster.
Where Does SAP BW Accelerator Fit in the Big-Picture? Source: Thierry Audas, SAP AG, 2009 SAP BW Accelerator is an integral part of the technology strategy for SAP and the new SAP BusinessObjects tool suite
SAP BW Accelerator Works by Indexing InfoCubes in Hardware Memory SAP BW Accelerator has been simple to install and operate for established SAP BI teams You use process chains to maintain the SAP BW Accelerator engine after each data load SAPNWBW Any tool Source: SAP AG 32 GB Blades are certified by SAP IBM, Sun, HP & Fujitsu have standard solutions ranging from $32K to $300K+ that can be installed and tested in as little as 2-4 weeks (+ SAP license fees)
How Does SAP BW Accelerator Work in Reality?? In the next SAP NetWeaver release the SAP BW Accelerator will handle more of the analytics processing, such as “top-5 products sales,” which is currently done in the OLAP analytical engine SAP BW Accelerator is not used when the result set exceeds 3 million records (max. default) When the result set is less, the data is sent as one large data package to the application server (need fast network) • Currently, SAP BW Accelerator performs aggregation and data selection for the query, all other processing is done by the OLAP analytical engine • Therefore, not all query processing will be dramatically faster
The New SAP Explorer and SAP BW Accelerator The new SAP BusinessObjects Explorer (not SAP BEx) brings together in-memory processing SAP BW Accelerator with the SAP BusinessObjects business intelligence (BI) search engine Users can search & explore large volumes of enterprise data to answer ‘on the fly’ questions, discover relationships, and uncover root cause All business users gain immediate insight without needing assistance from business analysts or IT. No training required
SAP BW Accelerator as a Tool for SAP BusinessObjects Integration Problem: In the new SAP BusinessObjects landscape there are two ways to build a semantic layer (universes) for implementing data access. Each has its own limitations and benefits. The first is an MDX-based universe for OLAP consumers. This is easy to create and is based on a SAP BW query. However, it is hard to merge external (non SAP BI) data using this method. The alternative is to build a traditional SQL based universe, but it is very hard to get access to the SAP BW data since this by-passes the BI analytical engine inside SAP NetWeaver BW. Solution: Implement SAP BW Accelerator + Facade and you can build SQL universes with SAP BW and external data. Source: SAP AG, June 2009
SAP BW Accelerator as a tool for SAP BusinessObjects Integration Notice that Facade and Data federator are separate products that are currently licensed separately from the SAP BW licenses. WEbI = SAP BusinessObjects Web Intelligence, BWA = SAP BW Accelerator Source: SAP AG, June 2009
What We’ll Cover … • What is SAP BW Accelerator? • Performance benchmarks • Managing and administrating SAP BW Accelerator • Planning an SAP BW Accelerator implementation • Wrap-up
Compression and TREX The TREX engine is NOT the same as the traditional implementation of the search engine. BW Accelerator comes its own TREX engine that collects the data. During the index build, the data is also compressed using advanced mathematical methods. The result is therefore a much smaller sized index than the original InfoCube
Performance is Near-Linear: Benchmarks SAP BW Accelerator’s strength resides in its near-linear scalability Performance is measured in terms of: BW Accelerator index creation time Multi-user throughput per hour Average report response time Average number of records touched by each report SAP BW Accelerator currently reads data from InfoCubes. DSOs and InfoObjects are still read from base/physical tables (even when the InfoObject is indexed as part of master data).
BI Analytical Engine’s Query Executing Priorities Query ExecutionWithout SAP NetWeaverBW Accelerator Query ExecutionWith SAP NetWeaver BW Accelerator Information Broadcasting /Precalculation Information Broadcasting /Precalculation Query Cache Query Cache Aggregates SAP BW Accelerator InfoProvider Aggregates can be replaced with SAP BW Accelerator, while the memory cache is still useful.
Query Performance Improvements - Real Example of 70 queries The slowest queries that spent most of their time on the database side benefited the most Overall 36 of 70 queries were at least twice as fast. Before SAP BW Accelerator, the average query execution took 58.8 seconds; after SAP BW Accelerator the average query took 17.9 seconds (295% faster overall). BI Accelerator (BWA) has been renamed to SAP BW Accelerator
Query Performance Improvements OVERALL The major improvement is to make query execution more predictable and overall faster Number of Queries Seconds Number of Queries Seconds
Workbook Performance Improvements with SAP BW Accelerator Workbooks require embedding of JavaScripts; can contain multiple queries; and may have macros, lookups, and internal logic It is therefore hard to estimate the real performance benefits of implementingSAP BW Accelerator In this real example of 20 slow workbooks, the average performance improvement for workbooks was 363% faster
Performance Info — SAP BW Accelerator Indexes Build Times Creating new indexes (refreshes) can be time consuming. The build time depends on: a) The size of the records (length) b) The number of records c) The number of SAP BW Accelerator processors d) The network transfer speed In this example, 23 InfoCubes with over 232 million records were indexed in 90.56 minutes On average, 2.56 million records per minute were indexed!
What We’ll Cover … • What is SAP BW Accelerator? • Performance benchmarks • Managing and administrating SAP BW Accelerator • Planning an SAP BW Accelerator implementation • Wrap-up
Most Customers Have Found SAP BW Accelerator Admin to Be Minimal The Admin work is done through a single interface The admin interface is available under the transaction code RSDDBWAMON. Health checks for SAP BW Accelerator are available under the transaction code RSRV Most companies plan for a maximum of 2-5 days of SAP BW Accelerator training. You need a maximum of 1-2 administrators (1 for backup).
Health-Checks and Reconciliation The SAP BW Accelerator interface allows you to compare the data in SAP BW vs. the indexes. This means that you can easily check if they are outdated. Other tools include the ability to run queries to see if the numbers in the two databases match.
Proposals and Estimations The Analysis and Repair options include many proposals and time estimation tools that you should leverage. The interface can propose delta-indexes for periodic updates (not complete builds). You can estimate the run-time of indexing the fact table of an InfoCube before you place it into a process chain or a manual job. You can also estimate the memory you need before you add new records into memory.
The SAP BW Accelerator “Reset Button” The simple way to fix most issues is to delete all indexes and rebuild them during a weekend Think of this as the ultimate “reset” button You can also rebuild master data indexes
What We’ll Cover … • What is SAP BW Accelerator? • Performance benchmarks • Managing and administrating SAP BW Accelerator • Planning an SAP BW Accelerator implementation • Wrap-up
Planning a 6 -weeks SAP BW Accelerator implementation • A BWA Project can be completed in 6-weeks. • However, due to hardware lead-times (normally 4-6 weeks), most project should plan a 12 weeks duration. • The time waiting on hardware should be used on performance tuning of existing queries and dashboards • In the this section we will look at the major tasks of implementing a BWA solution
Planning an SAP BW Accelerator implementation During project start up, make sure you have someone from basis, network, physical hardware and system admin available to coordinate schedules, access and resources
Planning an SAP BW Accelerator implementation Not all InfoCubes needs to be indexed. Use the Earlywatch reports to find the slow performing queries that have a significant number of users and are accessed at a high frequency.
Planning an SAP BW Accelerator implementation Sizing is important to determine the overall system hardware needed. You cannot use the size of the InfoCubes, since the TREX indexing also uses compression to reduce overall size.
Sizing Is Critical, but Good Tools Are Available SAP has integrated key sizing parameters for SAP BW Accelerator in the new Quick Sizer. You can give it a try at:http://service.sap.com/quicksizer (requires login credentials for the SAP Service Marketplace) Customer with existing BI systems and data loaded can get good sizing and compression estimates by running the SAP program available in SAP Note: 917803
Planning an SAP BW Accelerator implementation You should plan for at least one extra blade per environment in-case the server blade fails (you can rebuild indexes to this blade). You also contingency plans in-case the BWA indexes becomes unavailable.
Planning an SAP BW Accelerator implementation As soon as you have approvals, contact the vendor and place the BWA order. These are often built-to-order and lead-times for delivery is frequently 4-6 weeks.
Some Hardware Options Operating Systems and StorageSUSE Linux 64-bit, Intel basedMemory based and file storage (no database for Accelerator)Available Hardware PlatformsHP (HP ProLiant and HP StorageWorks)IBM (IBM Blade Center and IBM Total Storage)Fujitsu Siemens (FSC Blade Server and EMC Storage)SUN Microsystems (SUN Blade Server and StorageTek) The hardware implementation is done by the vendors. Be prepared for lead times on delivery of large systems (made-to-order is common).
Example of Hardware SAP BW Accelerator should be sized for critical applications. Most companies use SAP BW Accelerator only for Production, while others have a complete landscape.
Planning an SAP BW Accelerator implementation BWA works primarily on database fetch operations, processes in the BI analytical engine is still performed. You should therefore also tune these and redesign poorly performing queries before BWA is implemented
Planning an SAP BW Accelerator implementation BWA hardware is simple to install. The trick is to have the right people involved & make sure that the data traffic between the database server and the BWA server is not using old data networks with slow routers and shared switches.
Planning an SAP BW Accelerator implementation You need to plan for 2 system admin to be trained. One for the daily work and one for backup during vacations and travel.
Training for SAP BW Accelerator SAP BW Accelerator training is included in the SAP courses: BW-310, BW-360 and in the online course BW361 In the past, SAP has also offered a 2 day workshop called “BI Upgrade & Accelerator” (WNASAP BW Accelerator)
Planning an SAP BW Accelerator implementation Build a small sub-set of indexes and test them before you spend time on the complete system. This will help determine how long it will take to create indexes and what performance increase you will obtain.
Planning an SAP BW Accelerator implementation Test the overall solution over a weekend and compare the benchmarks against those you had in week 3 to document success and possible bottlenecks. Integrate the index creation times into the existing process chains and monitor impacts.
Team Organization Create a small team of three people dedicated to the SAP BW Accelerator project supported by one Basis person and the rest of the BW team Hardware team members supported by the vendor should also be involved in a part-time capacity Overall the SAP BW Accelerator effort should be combined with a performance tuning project that also includes building of aggregates, caching, and query redesigns
Testing Make sure you identify real benefits and benchmark with pre- and post-performance after SAP BW Accelerator is implemented This requires that all critical performance data is captured before SAP BW Accelerator is installed You should document the post performance and build times as early as possible so that you know what the performance of complex dashboards will be and how the indexing will fit into existing process chains
What We’ll Cover … • What is SAP BW Accelerator? • Performance benchmarks • Managing and administrating SAP BW Accelerator • Planning an SAP BW Accelerator implementation • Wrap-up
SAP BW Accelerator is becoming mainstream Some of SAP reference clients (source: SAP AG, 2009) SAP BW Accelerator is no longer something exotic Many of the large BI systems have already implemented SAP BW Accelerator and many more projects are under way in Europe and in the Americas Once you exceed a few hundred critical users and/or 3-4 TB of data, you should seriously consider SAP BW Accelerator
The Future of SAP BW Accelerator — A Roadmap 2009/2010 Prior to 2008 2008 • Optimized SAP BusinessObjects Web intelligence integration on top of SAP NetWeaver BW/SAP BW Accelerator • SAP BusinessObjects Polestar integration: Self-service BI based on search paradigm SAP NetWeaver BWAccelerator • Accelerator for InfoCube Read Times • Low cost of operations • High availability/ backup and recovery • Analytic capabilities (e.g., top n) • DataStore Object acceleration • Standalone SAP BW Accelerator for source-independent acceleration via SAP BusinessObjects • Agile BI: Local and source-independent innovation space for departments
Resources SAP Developer network — Large scale testing of SAP NetWeaver Business Intelligence Accelerator www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/b00e7bb5-3add-2a10-3890-e8582df5c70f SAP BW Accelerator “nerve center“ — SAP White papers, demos and technical information https://www.sdn.sap.com/irj/sdn/BWA More on SAP BW Accelerator and BOBJ tool integration and downloads www.comeritinc.com Downloads
7 Key Points to Take Home Plan to order hardware early – Lead times can be long Create benchmarks for workbooks, queries, and cockpits to document success – Be realistic, not all queries will benefit equally from SAP BW Accelerator Involve your Basis team and environment management team early Plan for testing and gradual rollout of SAP BW Accelerator to key users Make sure failovers and spare blades are part of your hardware sizing Create a small dedicated team with access to external expertise as needed Perform query tuning and potential redesign as part of a larger system performance tuning effort
Your Turn! How to contact me: Dr. Bjarne Berg bberg@comeritinc.com
Disclaimer SAP, R/3, mySAP, mySAP.com, SAP NetWeaver®, Duet™, PartnerEdge, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other product and service names mentioned are the trademarks of their respective companies. Wellesley Information Services is neither owned nor controlled by SAP.