Spring Cleaning for the Database

Feature Article | July 26, 2006 by admin

Siemens VDO

Siemens VDO

At the beginning of 2005 the database of the Siemens VDO Schwalbach branch contained more than 1.4 Terabyte of data. In July 2005 the branch had scheduled a chart of accounts conversion with the help of SAP System Landscape Optimization Services (SLO), a team of SAP experts who provide services to customers in the areas of database and archive conversions, and migration, among others. Based on system analyses and forecasts involving the 50 largest tables of the system, SLO determined that the conversion would require much longer than the usual runtime of roughly one weekend.
By April 2005 the database had grown to 1.6 Terabyte and was increasing at a rate of 100 Gigabyte per month. The company realized that it would need to reduce the size of its database tables before it could embark on the chart of accounts conversion. “Since we had been looking to start a comprehensive data archiving project already”, so Horst Smolka, the archiving lead at Siemens VDO, “we decided that the only option we had for running a chart of account conversion in a reasonable time frame was through data archiving.” The Data Archiving Optimization Service (DAO) of SAP Active Global Support was called in.

Countdown against time

SAP Data Archiving

SAP Data Archiving

Since SAP was contacted for the DAO Service in March and the conversion was scheduled for July, the entire archiving project, from the analysis phase until the actual archiving of the data, had to be pulled off in about four months.
Archiving in an SAP solution means moving data from completed business processes from the database to archive files in a compressed format. Once the data has been archived it is still considered to be part of the system and can be accessed for viewing. SAP offers different tools and methods for accessing and viewing archived data. Options for accessing archived data directly from within the applications are also available. These options have been continuously enhanced over time, so that they are already very comfortable in SAP R/3 4.6C, which Siemens VDO currently has deployed. With SAP R/3 Enterprise display transactions were enhanced even further as part of a special data archiving standardization project.

Key factors for success

Often times the initiative to begin an archiving project within a company stems from the IT department, who understands that data archiving is the only way to quell data growth and save money on system administration and extra hardware. It is not uncommon for them to have to act as “data archiving missionaries”, in that they must convince the user departments that instead of hurting their daily work, data archiving helps them be more efficient. With Siemens VDO the key factors for success were the enthusiasm and aptitude of the archiving team, and the careful analysis, planning, and coaching that the team of the DAO service was able to offer as a result. The Siemens VDO team, led by Horst Smolka, was unusually successful as “data archiving missionaries” despite, or perhaps also because of the time pressure of the lurking conversion project. The team was very active about archiving, and with its enthusiasm it was able to include the relevant user departments, achieving close cooperation of all involved.

Classical data archiving approach

In March 2005 the database reduction was tackled using a classical data archiving approach. First the DAO team analyzed the system to see which tactic would remove the largest sets of data in the shortest amount of time, so that the system would be ready in time for the conversion. Based on the analysis results, DAO together with the Siemens VDO team set up a comprehensive project plan for how to proceed. Among the biggest tables were those of Change Documents, Product Costing Documents, Accounting Documents, and Material Documents, which were tackled first.
What followed was an extensive test phase, which was aided by the fact that Siemens VDO keeps a complete copy of the production system for test purposes. The archiving team was able to simulate the entire archiving project on this test system. As a result the team obtained precise runtime calculations and forecasts for the different archiving sessions and was able to optimize the actual archiving operation on the production system to a maximum. In addition, user-acceptance tests for the display possibilities of archived data were performed. The user-acceptance tests are a key prerequisite for the business owners to finally agree and sign-off on the archiving approach.
The test phase was also used extensively for knowledge transfer and learning purposes. The DAO team held several onsite workshops explaining the concepts to enable the Siemens VDO archiving team to implement an ongoing, long-term data management strategy and tailor archiving to their needs. Each archiving object was covered by a separate workshop, based on the archiving sessions in the test system. As a result the Siemens VDO team was soon able to convert its gained knowledge into action. For example, its programmers modified some of the display transactions for archived data to fit their particular requirements. Another example of how knowledge transfer helped optimize the archiving strategy at Siemens VDO was in the area of legal compliance and billing documents. Once billing documents have been archived, they can no longer be reprinted. However, due to legal requirements, the company must ensure that it can reproduce a paper copy of the documents in some way, for example by storing printed invoices as PDF files. Therefore, the team decided to do a retrospective creation of the PDF files before the archiving process was started, and in this way provide for legal compliance.

A two-phase archiving project

Based on the results of the tests the team decided to archive in two phases. The first phase would take place before the planned conversion of the chart of accounts data. Its goal was to remove as much data as possible from the database to reduce the runtime of the conversion. In this phase, therefore, the team focused on the largest tables and in six months was able to reduce the size of the database by around 500 GB. This was partially due to the fact that the Schwalbach branch had never archived any data out of its SAP R/3 standard system before. The most common approach for archiving is to leave the current and previous fiscal years in the system and archive the rest. Since this system contained data from as far back as 2001, the size of the tables could be reduced considerably.

Comprehensive international archiving project

Comprehensive international archiving project

The second phase was started after the chart of accounts conversion took place. The purpose of this phase was to tackle the smaller tables and to tie up any loose ends in order to round out the archiving strategy and make it as comprehensive as possible. In this phase, the archiving of master data was also evaluated. However, in the end the team decided not to archive material master data, because this would occasion severe limitations on the display functionality of already archived documents that refer to the materials such as Sales Orders, Material Documents. These limitations were not acceptable by the user community. The second phase also served to turn the data archiving project into a data archiving strategy for all of Siemens VDO branches around the world. In the end, this archiving project served as a pilot project for the company-wide archiving strategy.

Happy ending

In June 2005 the database had been reduced enough for the chart of accounts conversion to take place with a regular runtime of about 21 hours. “Without data archiving, the chart of accounts conversion would not have been possible for us”, so Horst Smolka. With the help of archiving Siemens VDO reached its target of reducing storage costs so that further investments in hardware can be avoided. The total volume archived at the end of the project amounted to 900 Gigabyte of data. After database reorganizations, which followed the initial archiving phases, a total of around 800 Gigabyte of disk space was freed up. This reduced the total system size from around 2.3 Terabyte in the middle of the archiving project to approximately 1.4 Terabyte. Through regular archiving, data growth was reduced from 100 Gigabyte to 50 Gigabyte per month.

Tanja Kaufmann

Tanja Kaufmann

Tags: , ,

Leave a Reply