Data import, export, and update in Rational Focal Point
Overview of various methods for working with a large volume of complex data
IBM® Rational® Focal Point™ helps you make value-based decisions for project, product, and portfolio management. This article gives a comprehensive overview of the best practices for the various data import, update, and export techniques available in Rational Focal Point. It will be most useful to readers who are looking for more information before deciding which import and export technique suits their needs best and what optimization steps are available during import and export operations.
External data model
The data model of Rational Focal Point contains these elements (also see Figure 1):
- Servers, which are Rational Focal Point server instances that contain a set of workspaces and data for global users.
- Workspaces, which contain a collection of related modules.
- Modules, which are used to define the elements as attribute definitions. Modules contain a set of elements.
- Elements, which contain a set of attribute values.
- Views, which are a selected subset of elements and attributes within a module.
Figure 1. Rational Focal Point high-level, external data model
Import, export, and update options available
The following are the types of capabilities available, based on the formats used:
- Import and export by using the XML format
- Import, export and update by using the Microsoft Excel format
- Import, export, and update by using configurable REST (Rational Focal Point 6.5.2 and later)
- Import, export, and update by using REST APIs
Import and export by using the XML format
Export and import of all Rational Focal Point attribute types is completely supported in an XML format. A workspace or a module can be exported individually or imported by using an XML format. The XML import creates a new workspace or a module. In other words, you cannot use an XML import to update an existing workspace or module. Three options are available to determine the export content:
- Full export: The entire workspace will be exported, including all elements, history logs, and the attribute configurations.
- Limited export: The exported file is a compressed file that contain the modules in XML format and attachments as plain files.
- Attribute configuration export: Only the top folder and the attribute configuration will be exported. Elements and the history log will be excluded.
Figure 2. XML workspace export page in Rational Focal Point
The most common use of this type of export and import operation is to migrate the workspace or module from a test environment to a production environment or from one database instance to another. The exported file is a compressed file that contains the modules in XML format and attachments as plain files.
Already exported Rational Focal Point workspace XML file can be imported as a new Rational Focal Point workspace by selecting General > Workspaces (see Figure 3), and using the ImportWorkspace command button.
Figure 3. XML Import Workspace page in Rational Focal Point
Import, export, and update by using the Microsoft Excel format
This method is commonly used to import any spreadsheet data that is managed outside of Rational Focal Point into Rational Focal Point to enable limited offline updates of elements or to generate a report by using the Microsoft Excel format. Collections of elements can be exported, imported, or updated using this method. A user interface for import is available for choosing the options for selecting and configuring the column-level mapping between input data and target elements in Rational Focal Point.
Figure 4. Excel module import configuration page
Import operations that use the Microsoft Excel format do not support the links type attribute.
Import, export, and update by using configurable REST
Rational Focal Point 6.5.2 can be configured as a REST client to retrieve data from external web applications that support the REST API. Using this feature, you can retrieve a single file or a collection of external resources and map the data to create Rational Focal Point elements or to update existing ones. You can use the user interface to define the mapping of input data values to element attributes. This feature also supports composing XML by using selected elements attribute values and using the content to update the external systems.
Import, export, and update by using REST APIs
Rational Focal Point 6.4.0 and later versions support the REST XML API, which you can use to retrieve or update Rational Focal Point elements as XML data. Rational Focal Point 6.5.2 and later versions support the REST Resource Description Framework (RDF) API, which you can use to retrieve or update Rational Focal Point elements as RDF files. Follow the link to Rational Focal Point API Reference in the Resources section for details.
Challenges with importing and updating large volumes of XML data
Importing a large volume of data in XML format is a resource-intensive operation. The complexity of and time that it take for import depends on the volume of data, as well as the number of links and number of expressions used in data that you are importing. During the import, it is likely that other users will experience some slowness in their operations. In some situations, the slowness remains even after the import operation is successfully finished. This can happen if the data is complex and triggers many expressions. These expressions are handled in the background, so until these background operations finish, more system resources are used.
Based on the complexity and amount of data to import, the whole import operation can take a few minutes to a few hours. For example, in a local test environment, the import of a module with 200 elements and 20 attributes each took 40 seconds to finish, but the import time increased to 140 seconds when there were 1000 elements.
Best practices for preparing large volumes of XML data
This topic covers some of the best practices to consider when preparing for import and update of large volume of data
- Ensure that input data is of optimal volume
- Allocate enough operating system memory
- Validate in a test environment before going into a live environment
- Back up your data
- Choose an appropriate time
- Run only one import operation at a time
Ensure that input data is of optimal volume
Make sure that the data for import and update is optimal. Rational Focal Point provides three options for exporting a workspace or module as XML data: Full export, Limited export, and Attribute configuration export. Make sure that you use the appropriate option for the export to avoid unwanted data in exported .zip file.
Allocate enough operating system memory
Make sure that sufficient memory has been allocated to the Rational Focal Point server. Allocating more memory enables Rational Focal Point to use more resources for frequent background operations, such as expressions execution, search indexing, and notifications, without having a negative impact on the overall performance. Although there is no rule about exactly how much memory is required, you can use the following guidelines to calculate an approximation:
- The average memory required to import an attribute is 4 KB.
- To import 10,000 elements with 10 attributes each, the estimated memory is around 400 MB (4 x 10 x 10000 KB).
To change the maximum memory, locate the fpenv.sh file (Linux systems) or fpenv.bat file (Microsoft Windows systems) in the $TOMCAT_HOME\bin\ directory, and edit the SERVER_MAXHEAP property as the example in Figure 5 shows.
Figure 5. Sample fpenv.sh memory configuration file
#!/bin/sh ######################################################### # This Script is intended to be invoked by setenv.sh # # Back up if you really need to alter # ######################################################### echo "Customizing server for Focal point " DEBUG="TRUE" ... #|------------- SERVER/JVM/JAVA MEMORY USAGE -------------- #| Used to allocate more Memory to application from Systems installed Memory i.e.,RAM #| # SERVER_MAXHEAP , SERVER_MAXHEAP # Sets Maximum amount of Memory for JAVA/Server (called Object Heap space) # SERVER_MAXHEAP Maximum heap memory of JVM running Focal Point Application # SERVER_MINHEAP Start heap memory to allocate to JVM at startup # Example for allocating 1GB: SET SERVER_MAXHEAP=1024M # SET SERVER_MINHEAP=756M # # In a production system, setting SERVER-MAXHEAP equal to SERVER_MINHEAP, can save some GC overhead costs # SERVER_MAXHEAP="1500M" SERVER_MINHEAP="750M"
Validate in a test environment before going into a live production environment
Test the import or update scenarios in an identical test environment before running them in a production environment. The information (imported data validity, time required, and so forth) that is collected in a test environment is useful for import operations in a production environment.
Back up your data
Make sure that the database is properly backed up before starting an import and update operation.
Choose an appropriate time
Because the XML import or update operation is a resource-intensive operation, choose the right time for performing these operations so that user inconvenience is minimal. You can choose to run these operations outside of the normal work hours when the number of requests to the Rational Focal Point server is low.
Run only one import operation at a time
Because of the resource-intensive nature of import operations, run only one import operation at a time.
Best practices during large XML import operations
Follow these guidelines for improving the import operations:
- Collect the statistics for the Rational Focal Point resources:
- Memory statistics
- Attribute cache statistics
- Expression queue statistics
- Collect the statistics for the Rational Focal Point database tables.
Collect the statistics for the Rational Focal Point resources
During large operations, it is important to monitor the health of the Rational Focal Point server and collect several statistics of interest. You can get this information from the Rational Focal Point application. The information includes memory use, expression queue monitoring, and attribute cache statistics. Because the status of the operation is also shown in the same browser session, you might have to create a new browser session with a different user ID to monitor the information.
Rational Focal Point memory
You can monitor Rational Focal Point memory by selecting General > About Rational Focal Point. Shortage of available memory, if any, is indicated with a red warning. To change the allocated memory, follow the steps in the Allocate enough operating system memory topic, and restart the server.
It is always a good practice to test large operations in a test environment first.
Figure 6. Operating system resources statistics for the instance that is running
Attribute cache statistics
Rational Focal Point uses a cache to improve performance. You can monitor the attribute cache statistics by selecting Advanced>Caches and checking the details of the attribute cache. For faster operation, the attribute cache hit rate must be more than 95%. If the cache is full and the cache hit rate is falling below 95%, increase the attribute cache size. Also, if there is a significant increase in the attribute cache size, increase the allocated memory for Rational Focal Point. See the Allocate enough operating system memory topic for how to increase the allocated memory based on the cache size increase.
Figure 7. Cache use statistics
Monitor expression queue statistics
You can monitor the expression queue can by selecting Advanced > Expression Queue and viewing the expression queue history graph. The steep increase in the initial phase in the example shown in Figure 8 indicates that expressions are getting added to the queue as the import operation progresses. The graph shows a downward curve because the expression execution rate is greater than the incoming expressions rate. In some cases, you might also see a plateau in the middle of the graph. This state remains for a long time or decreases slowly. If the expression queue size does not reduce fast, it is likely to strain the Rational Focal Point and database servers, and this can slow down the import process. The next section discusses how to increase the speed of expression evaluation.
Figure 8. Expression queue statistics history graph
Collect the statistics for the Rational Focal Point database tables
It is important to monitor the expression queue evaluation, especially in the middle phase. You can make expression evaluations faster by collecting database statistics frequently for these Rational Focal Point tables, where xxx varies according to the table ID of the project where the import is happening:
Collecting the statistics for these tables ensures that indexes are up-to-date and that the query optimizer picks the best evaluation plan. You can also schedule a job to collect statistics at regular intervals, but you might need database administrator rights to do that. If the import operation runs for more than an hour, collect the statistics every hour.
Follow the "Expression queue tuning" link in the Resources section for information on how to configure expression queues for optimal expression evaluation.
- Citations in this article:
- For more about Focal Point:
- Browse the Rational Focal Point overview page, as well as the developerWorks page, where you'll find data sheets, system requirements, specific benefits, and related articles and white papers.
- Watch these brief demos:
- Check the Rational Focal Point 6.5 Information Center for the user guide and links to other useful resources.
- Subscribe to the developerWorks weekly email newsletter, and choose the topics to follow.
- Stay current with developerWorks technical events and webcasts focused on a variety of IBM products and IT industry topics.
- Improve your skills. Check the Rational training and certification catalog, which includes many types of courses on a wide range of topics. You can take some of them anywhere, any time, and many of the "Getting Started" ones are free.
- Download a free trial version of Rational software.
- Evaluate other IBM software in the way that suits you best: Download it for a trial, try it online, use it in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement service-oriented architecture efficiently.