The Big Grid – Addressing high data demands with Excel and Transaction
By Clinton Jones on Feb 22, 2012
I have had to respond to a couple of comments/questions lately on Microsoft Excel limitations that I wanted to explore, and for you to consider in the context of determining what the best options are for you with respect to Excel and SAP integration.
Some important things to consider is that Excel 2010 comes with no specific limitations on the number of rows other than the general characteristics of the machine on which it is being run. Well sort of. There have been some numbers bandied about regarding a limitation of just over a million rows, however, there are some other important factors to consider. I have included hyperlinks to the relevant MSDN pages in particular.
Starting in Excel 2007, the so called “Big Grid” increased the maximum number of rows per worksheet from 65,536 to over a million, and the number of columns from 256 (IV) to 16,384 (XFD).
If you work with large workbooks, you’d have probably found that the increased memory capacity of recent versions of Excel has meant that you hit some other Excel specification limits more frequently, especially if your machine was memory constrained.
Starting with Excel 2007, Excel includes many changes to these limits to accompany the large increase in row and column capacity. Some of these limit increases can also significantly affect calculation speed especially if you use pivots or data analysis functionality .
Starting in Excel 2010, Excel optimized functionality that relied on using the top left of the grid as the origin in order to improve the experience of working with graphics in the new regions of the grid. Rendering fidelity and performance were improved relative to Excel 2007.
With Winshuttle Transaction, you need to consider that the preview option in the mapper doesn’t leverage Excel as an embedded component to provide the preview and uses a similar approach to the Big Grid in that it renders graphics as graphics and allows you to zoom in and out of the top top left panel.
Consider that if you map to a data file that has a large amount of data, that this will take a long time to load.
Customers with ambitious expectations in terms of Excel workbook size need to consider a shift to Office 64 bit. Winshuttle products support working with 64 bit versions of Office and the installer is different to the 32 bit installer.
Applications built with the 64-bit version can use more physical memory than ever, which is especially important for those who expect to work with really large data sets. In Excel 2010, investments were made by Microsoft in the 64-bit architecture to optimize memory consumption while keeping the cell table (and related operations) as fast as possible.
One of the outcomes was the ability to create bigger workbooks using 64-bit Excel that can be too big for 32-bit Excel to open. More generally, workbooks are interchangeable between 32-bit and 64-bit. For information about 64-bit support and code compatibility, see Programmability Improvements in Excel 2010 (blog).
Another great blog on the merits and demerits of big grid spreadsheets is found here but at the same time we understand that some customers do have heavy spreadsheet demands that we try to help them address.
For customers with very large data demands, they need to consider using Access as a data source especially for temporary ETL (Extract, Transform, Load) activities.
Remember too, that we continue to be constrained by the physical throughput that the SAP rfc and the SAP transaction dialog allow.
Accordingly, using Winshuttle Transaction to load millions of changes could take days if done in a linear fashion and may not be a great approach to use. A better one, may be to use a different approach with multiple stations or sessions, or even using Winshuttle Foundation in combination with Winshuttle Runner or Transaction. Remember that the Winshuttle sweet-spot is something that needs to be flexible, relatively high performance and reliable – not necessarily something that functions as an industrial blender for your ETL activities.
Consider that one of the most highly rated SAP systems posted record throughput last year connecting 25,500 users posting 2,817,330 sales order lines an hour or roughly 110 order lines per user per hour…. not very impressive when averaged out but impressive enough when looked at in aggregate… Winshuttle Transaction will push data at variable speeds depending on the overall system availability and load combined with network latency but average rates of over 1,400 line items per hour are not unusual per session.
About the author
Clinton Jones is a Director for Finance Solutions Management at Winshuttle where he has worked since 2009. He is internationally experienced having worked on finance technologies and business process with a particular focus on integrated business solutions in Europe, the Middle East, Africa and North America. Clinton serves as a technical consultant on technology and quality management as it relates to data and process management and governance for finance organizations globally. Prior to Winshuttle he served as a Technical Quality Manager at SAP and with Microsoft in their Global Foundation Services group.
Questions or comments about this article?
Tweet @Winshuttle to continue the conversation!