TMCnet News
Case Studies in Improving Application Performance with Solix Database Archiving SolutionsMany organizations run their businesses on Oracle (News - Alert) E-Business Suite and PeopleSoft. These software packages generate vital business information and any disruption or delay in application access can be very costly. End-of-month financial closings, inventory reporting and manufacturing processes are often built around these systems—making any performance or availability issues a priority for senior IT and business managers. One of the biggest risks to information access is poor application response time, and one of the causes of poor response time is the size of the underlying application databases. In some Oracle E-Business Suite modules such as General Ledger, there can be several thousand transactions added to a database in a day. Over a few months, running queries and reports on these large database instances can take a while to complete. To address this issue, organizations may run batch processing over the weekend or, in some cases, overnight. This way, the reports are finished when employees return to work and secondary jobs do not negatively impact application performance. With enterprise application usage on the rise and the amount of information generated and stored within a database increasing, organizations can no longer rely on off-hours processing to generate the reports needed to run the business. Additionally, with more global business operations requiring constant access to application data, there may never be a good time to run reports and batch processes. Further complicating matters are complex, integrated application environments where access equates to running regular queries to capture the latest manufacturing plan or other operation. These normal processes may be delayed because the size of the database prevents results from being returned in a reasonable period of time. To address ongoing enterprise application performance due to the growth of databases, organizations have a few options. This includes creating a new database partition or utilizing archive / purge features within the software packages in addition to leveraging a fully integrated enterprise application archival solution. Permanently deleting the information is rarely an alternative as organizations are constantly finding new ways to leverage it—which is just as well as compliance with ever-increasing demands for record retention regulations means saving database transactions for extended periods of time anyway. Moving aged data to a separate environment via archiving or partitioning reduces the size of the primary databases, expediting queries and access times run against these instances. Before determining the method of data management, there are several considerations that organizations must evaluate in order to ensure that they balance information access requirements with potential performance enhancements. The need to easily access older information for reporting or additional queries as well as the need to preserve information for compliance reasons may help determine the best method of application performance optimization via archiving. Data growth within enterprise applications cannot be ignored due to its direct impact on information access, and information access in turn directly impacts business performance and productivity. This paper briefly examines the reasons why organization should archive data and details how one solution, Solix Technologies’ Enterprise Data Management Suite (EDMS), has helped customers improve application performance while maintaining information access. The Database Archiving Choice Unfortunately many organizations view application and database archiving solely as a means to lower storage costs by moving old data from one environment to another. In reality, there are several reasons why organizations do extract aged or compliance-related database records and move them into a separate environment. Backups of the primary database are expedited when the instances are smaller. More importantly, if a recovery is needed, IT can restore the database much faster with a reduced data set. Quicker backup and restores help IT keep critical enterprise applications available with minimal risk of data loss. Recent ESG research, as shown in Figure 1, highlights all of the reasons why organizations have deployed database archiving solutions. However, it is important to note that improved data protection and enhanced performance were the two leading reasons. The performance reasons for archiving are very similar to the data protection rationale: The smaller the primary database, the faster queries, reporting and other operations can be completed. ESG estimates that primary database instances are growing approximately 25%, which can kill application performance and wreak havoc on backups. Additionally, organizations create multiple copies of these primary instances for test and development purposes, amongst other things. Existing methods of buying new infrastructure, such as faster servers or more storage is not a long term solution. Information growth is the reason why archiving is not a choice—it is a necessity. The choice for organizations lies within the method of building and executing an archive strategy. Both Oracle E-Business Suite and PeopleSoft have some archiving and purge capabilities, as do the underlying Oracle databases. While these features make it easy for application administrators to perform archive operations within the application or database, they are not available for all modules. In addition, after data is archived by these applications, organizations may have to write custom queries and reports to access information. In contrast, Solix Technologies, through its Metadata Manager, can archive any module of Oracle E-Business Suite and PeopleSoft. The integration with these prepackaged applications also ensures that users can seamlessly access archived information without having to write their own scripts, queries or reports. Organizations may also evaluate native database tools to improve performance. For example, additions to Oracle Database enable organizations to partition data that can be stored on the most cost-effective media. Because it is done at the database level, partitioning is most effective when data is accessed via custom reports and queries such as a data warehouse environment. In addition, partitioning is not available for certain Oracle applications, making it hard for customers to consistently archive data. Organizations want to take archiving much further and improve performance while maintaining application access. This is only feasible if customers use a solution that understands the construct of the application, the relationships amongst the data models and stores the archived data in an accessible format—one that does not require any custom work from IT. Native database tools may help lower storage costs, but the data may not be readily available for use. Database Archiving Success Stories Solix EDMS allows customers to consistently archive application and database information to a separate environment. Archiving can be done on a per module basis or across the application for even greater performance benefits. The key for organizations is to generate that performance gain without compromising information access. ESG interviewed four Solix customers to identify the specific performance benefits and process efficiencies that are achievable with the EDMS portfolio. Traco Incorporated The Business Challenge Traco is a manufacturer of commercial and residential windows, including custom windows used in skyscrapers. The company utilizes Oracle E-Business Suite for its financials and manufacturing data. Due to a business model that includes handling both custom orders as well as volume production requests, Traco designed its Oracle business information flow to be very flexible. This necessitated every order have one-of-a-kind information. Traco requires that every order be preserved so that it can be recreated at a later date, but storing such a large amount of information caused storage capacity to reach its maximum thresholds very quickly. Reporting operations were being run against hundreds of gigabytes of data daily. Not only did the capacity accumulation affect system performance, but it adversely impacted the daily production of windows, which did not help the top line. The Benefits of Database Archiving Solix for Oracle E-Business Suite was selected and data from Traco’s manufacturing database was archived, offloading unneeded data from production disk to archive disk. One of the more important benefits of deploying Solix is that the risk that operations are impacted by system performance is eliminated. Traco was able to shave off five to seven hours from the time required to perform certain processes, like plant planning and management of its manufacturing process. Real-time processing performance was cut in half, providing a higher degree of customer satisfaction. International Technology Components Supplier The Business Challenge This international technology components supplier is a publicly-traded company with over 4,200 employees worldwide. The company uses Oracle E-Business Suite, specifically the Advanced Supply Chain Planning, Inventory and General Ledger modules to support and run its financial and manufacturing operations. The company’s corporate expansion became evident in the size of its database, which was growing at 100% year over year. When its production Oracle database reached 700 GB and users experienced performance issues, IT decided it needed to institute policies for archiving some of its historical transactions. Due to the compliance and audit requirements to which publicly traded companies are subjected, the company needed to maintain three fiscal years of financial data online and up to five years in a near-line archive in order to maintain accessibility for queries and reporting. The Benefits of Database Archiving The company deployed Solix for Oracle E-Business Suite. In addition to using Solix to archive data based on retention policies, the company was able to extract attachments to transactions for archive, while keeping them linked to the original transaction. Only one fiscal year of attachments was maintained on production storage and the rest were archived. As a result, there was an impressive return on investment for the Solix solution in less than two years. In addition to the expected savings in storage (reducing production system capacity from 700 to 300 GB), the company saw 40-50% application performance improvements. Archiving transaction attachments (items such as shipping instructions) contributed about a 20% reduction in database size. Finally, the company’s test environment was greatly simplified since only the production system data needed to be copied for developers to do their jobs. Helen of Troy L.P. The Business Challenge Helen of Troy is a global, publicly-traded company that designs and manufactures personal care and household products under brand names such as Revlon, Vidal Sassoon, Dr.Scholls and more. Maintaining a 2 TB production environment, plus four other instances of 2 TB non-production environments, Helen of Troy needed to better manage storage space and primary database table space. Query response timeliness was problematic, negatively affecting both the supply chain and daily reporting activities. The company uses several modules of Oracle E-Business Suite, including Accounts Receivables, Accounts Payable, Bill of Material, General Ledger, Inventory, Material Requirement Planning, Order Management, Purchasing, Workflow and Work-in-Process. Helen of Troy required different retention policies, depending on the Oracle E-Business Suite module—two fiscal years for financials, one fiscal year for inventory and three to nine months for order transactions. The ability to run queries on data—including archived data—in a timely fashion was critical for the organization, especially considering the company’s tremendous data growth rate. For example, last year the total number of rows archived for just the inventory module, which consists of ten operating units, was 56 million. The Benefits of Database Archiving Helen of Troy’s IT team embarked on an active archiving project for their ERP system. The company selected Solix for Oracle E-Business Suite to reduce the volume of data maintained on production systems, while still allowing access to production and archive data for queries. Solix was selected over competitors due to its rapid installation, ability to mask sensitive data, extensive reporting capabilities and its ability to de-archive by batch or transaction. Helen of Troy not only improved transaction processing, reduced time to run supply chain reports and accelerated backup/recovery and upgrades, but it also reduced its costs of performance tuning, storage, memory and processors. Robertson Ceco Corporation The Business Challenge Robertson Ceco is the leading manufacturer of metal buildings and components, and uses several Oracle E-Business Suite applications, including General Ledger, Accounts Payable, Accounts Receivable, Cash Management, Fixed Assets, Purchasing and Inventory. The company’s financial department requires access to data for the previous seven years for internal compliance, as well as SEC (News - Alert) and audit purposes. These retention requirements caused the inflation of data sets, which led to performance degradation. The Benefits of Database Archiving The company selected Solix’s archiving solution for its Oracle business applications. Robertson Ceco was able to archive about 25% of the data off its production system, which was in the 270 GB range before Solix and 200 GB after. Archiving data onto less costly systems and storage instead of production-class systems and storage helped rationalize the investment. Robertson Ceco has also seen performance improvements of 20 to 25% since deploying Solix’s database archiving solution. All of the aforementioned organizations realized that they needed more than just storage savings as a result of archiving. In fact, all of the customers did not deploy any form of tiered storage with the archiving solution. The primary reason for archiving was improving application performance, and all organizations required that the newly created archive environment be easily accessible so that reports could be run or data could be restored back to the primary system if necessary. The impact of utilizing an enterprise archiving application that could consistently archive various modules of Oracle E-Business suites ranged from a 25% performance improvement to an increase in customer satisfaction. It was also important to note that the sizes of the underlying databases ranged from 40 GB to multiple terabytes. This underscores that there are also opportunities to enhance application performance through archiving, and the size of the data may not matter as much as its importance to a specific business process. The Solix Archiving Approach The Solix Technologies Enterprise Data Management Suite leverages a common metadata repository to organize, retain, secure and manage enterprise information. The EDMS foundation is the Enterprise Metadata Manager, which captures metadata from packaged enterprise and custom applications as well as several databases. This enables database administrators to map metadata between applications and define conversion rules used as the basis for policy-based archiving. The mapping construct also ensures that the archived information can still be accessed by the primary application and database. A central location for metadata provides organizations with the necessary foundation to more effectively manage information—especially databases and associated applications—by enabling administrators to establish retention policies and quickly locate content that has been archived and secured. The core Solix Enterprise Metadata Manager creates a central knowledgebase by analyzing the customer’s environment, which can be supplemented by pre-populated modules and tables provided by Solix for Oracle E-Business Suite and other business applications. For example, Solix enables Oracle customers to see the various data growth rates of all application modules, such as General Ledger or Accounts Payable. After this initial assessment, customers can determine specific classification policies and templates that need to be created to manage the information more effectively. Solix Enterprise Archiving includes retention solutions for Oracle E-Business Suite, Oracle PeopleSoft, Oracle JD Edwards and custom applications. This wide range of support enables seamless extraction and movement of database information amongst all tiers of data storage, while simultaneously ensuring the data can be accessed for query or reporting purposes by the various applications. Conclusion There is no question that organizations realize the importance of enterprise applications such as Oracle E-Business Suite and PeopleSoft. As these applications are relied upon to execute orders, complete manufacturing processes and prepare financial reports, it is imperative that performance and accessibility are constantly monitored and improved. Organizations, including the aforementioned Solix Technologies’ customers, are starting to realize the impact of archiving. It was very clear that during the Solix customer interviews that the data growth within Oracle E-Business Suite was causing application performance concerns and some of the customers had to address compliance and storage management concerns. These customers, just like any other organization, could have selected a native database partitioning solution or used archiving capabilities within their enterprise application. However, they selected Solix EDMS because readily available information access was important. Running reports, de-archiving information, and conducting reports against new and archived information were integral parts of business processes. Additionally, all of these organizations ran multiple modules of their enterprise application and wanted to archive information consistently across them. This may not be feasible with the packaged applications archiving features. Solix has demonstrated that information archiving, especially across complex enterprise application deployments, requires more than a few features. Understanding data models, assisting with meta-data management and integrating with existing business processes are required if customers are going to manage their information more efficiently while expediting access. Organizations should understand the various vendor definitions and methods of enterprise archiving before making their choice. However, when it comes to deciding whether or not to archive, organizations only need to read the aforementioned case studies to see that the benefits are real and the risks of poor information access can be extremely costly. As a community-building service, TMCnet allows user submitted content which is not always proofed by TMCnet editors. If you feel this entry is of inferior quality or wish to report it for some reason, please forward the URL to "webedit [AT] tmcnet [DOT] com" with your comments. |