Aggregation of Floating Keyfigures on BWA, Databas...

yogesh8984 · ‎08-10-2012

Applies to: SAP BI 7.1 SPS6 with BWA

Summary

The keyfigures with data type as FLTP gives different results with their aggregated values on BWA (Business Index Accelerators) and without BWA. This happens because FLTP numbers are stored in database as DOUBLE whereas they are stored in BWA as FLOAT. Because of different rounding techniques at database and BWA having two different number formats, we see the differences in the aggregated values (in most cases) with and without BWA.

This article explains the way to get the consistent aggregation across BWA and database

Author: Yogesh Kulkarni

Company: Infosys

Created on: 10 Aug 2012

Author Bio

Yogesh Kulkarni is presently working with Infosys. He has over five years of experience in SAP Business Intelligence and Data-warehousing space. He has worked extensively on SAP BIW starting with SAP BW 3.5 to SAP NW 7.3 with significant exposure to SAP BO tools.

Scenario

While running the reports at highest aggregated level, it was observed that there were significant differences in the numbers when the reports were run with BWA and without BWA. When the similar reports were executed at more granular level, the difference stated to vanish as the granularity increased.

After exploring all the possible reason (like filling the BWA correctly, updating the indices), it was observed that the issue is prominent at aggregated level. This was not a very ideal case as business was seeing the different numbers in the reports when the BWA was down.

Example

Here we created a sample test query at the highest aggregated level and used it to test the differences with and without BWA.

Pre Change: Query Run with BWA

When the query was run with BWA, below output was observed at InfoProvider level.

Pre Change: Query Run without BWA

When the same query was run with same selections without BWA in RSRT debug mode, below output was observed which clearly shows the difference in the output number by 61103.44 which is quite considerable.

Solution

The SAP has suggested the solution which talks about using the same format for numbers in database and in BWA.

First of all, we should identify the data type of key figures at data base level. The basis team can help here get the information about this by checking the database. In most of the cases, the DOUBLE data type is used for amount type of keyfigures. However in BWA, the data type used is FLOAT.

Here are the steps to change the BWA settings to make it equivalent to database settings.

Go to transaction RSDDBWAMON and check the current settings of Global Indexing Parameters. This can be found in the Menu à BI Accelerator à Index Settings à Change Global Parameters.

Here we can see the existing data type for keyfigures on BWA.

Change the data type here according to the data type set at database level. In this case, we changed it from FLOAT to DOUBLE.

While changing the settings, system gives the list of all Cubes which are on BWA and are using FLOAT keyfigures and also offer to delete the BWA indices on these cubes.

After successful completion of this step, we need to rebuild the indices on the cubes. If the number of cubes is more, we may create a process chain to create the BWA indices on these cubes to avoid manual work.

Result

After rebuilding the BWA indices successfully, we executed the test query again and it gave the exactly similar results with and without BWA.

Post Change: Query Run with BWA

Post Change: Query Run without BWA

Conclusion

The keyfigures with data type as FLTP gives different results with their aggregated values on BWA (Business Index Accelerators) and without BWA. This happens because FLTP numbers are stored in database as DOUBLE whereas they are stored in BWA as FLOAT. Because of different rounding techniques at database and BWA having two different number formats, we see the differences in the aggregated values (in most cases) with and without BWA.

By making the data types same across database and BWA as explained in earlier sections,we can get rid of this issue and see the similar results with and without BWA.

Copyright

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.

Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.

Microsoft, Windows, Excel, Outlook, and PowerPoint are registered trademarks of Microsoft Corporation.

IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, System z9, z10, z9, iSeries, pSeries, xSeries, zSeries, eServer, z/VM, z/OS, i5/OS, S/390, OS/390, OS/400, AS/400, S/390 Parallel Enterprise Server, PowerVM, Power Architecture, POWER6+, POWER6, POWER5+, POWER5, POWER, OpenPower, PowerPC, BatchPipes, BladeCenter, System Storage, GPFS, HACMP, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, Parallel Sysplex, MVS/ESA, AIX, Intelligent Miner, WebSphere, Netfinity, Tivoli and Informix are trademarks or registered trademarks of IBM Corporation.

Linux is the registered trademark of Linus Torvalds in the U.S. and other countries.

Adobe, the Adobe logo, Acrobat, PostScript, and Reader are either trademarks or registered trademarks of Adobe Systems Incorporated in the United States and/or other countries.

Oracle is a registered trademark of Oracle Corporation.

UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group.

Citrix, ICA, Program Neighborhood, MetaFrame, WinFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems, Inc.

HTML, XML, XHTML and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.

Java is a registered trademark of Oracle Corporation.

JavaScript is a registered trademark of Oracle Corporation, used under license for technology invented and implemented by Netscape.

SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP Business ByDesign, 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 other countries.

Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of Business Objects S.A. in the United States and in other countries. Business Objects is an SAP company.

All other product and service names mentioned are the trademarks of their respective companies. Data contained in this document serves informational purposes only. National product specifications may vary.

These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties for SAP Group products and services are those that are set forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.