SAP Reporting & Data Types!
Are they a challenge for you?
Last week I wrote about SAP reporting, and I mentioned that reports on SAP applications are generally slow. As a result many organizations are looking to offload reporting to a different database. As we are helping clients go through this process we encounter some challenges, one of which I’d like to highlight today: data types.
40 years ago it probably made sense to store almost all data in a character-based data type columns, especially if you wanted your application to easily be able to run on any database and not just any specific one. Fast forward to today SAP still caries that legacy and stores almost all data in character data types, and data validation takes place in the application layer.
Today every relational database supports numeric, date/time and obviously character data types. Data types are stored differently so that data is validated by the database irrespective of what application/process enters the data, but also because storing data in a particular format delivers storage and performance advantages. Many databases nowadays – especially columnar oriented databases – provide compression/encoding schemes that work really well on the right data type, and for numeric and date/time data types there are more options for efficient encoding schemes than there are for character-based columns.
Add that modern CPUs provide different optimizations for processing numeric data versus character data, and storing the data appropriately can simply give a significant performance boost. On top of that by storing the data appropriately it does not need to be converted every time. For example, if you want to add revenues over time then you may want to avoid the conversion from character to number every time for every row, so that you need less CPU resources for the conversion and in the end less hardware to achieve the same performance.
I am sure SAP realizes this as they are porting the applications to HANA.
With that in mind you should always pick the correct data type for the target database, and transform data types in the data integration step. This can be done as part of real-time data replication, or if subsequent transformations are performed before end-users access the data then it may be done in that step.
Last week I said that the SAP implementation probably wasn’t cheap (and took a long time to implement). Did you realize that to get the most out of your reporting solution you will be spending more (money/time) in hardware to compensate for sub-optimal data type choices by SAP, or to implement transformations and get the most out of your reporting solution?
Interested in learning how real-time replication can help your organization achieve real-time analytics? Check out our solution brief! Contact us if you would like to learn more about what HVR can do for you.