Data import, export, and update in Rational Focal Point

Overview of various methods for working with a large volume of complex data

Import, export, and update of a large volume of data are important but resource-intensive operations in IBM Rational Focal Point. This article gives you an overview of various methods and guidelines to follow during these operations.

Share:

Karthikeyan Dakshinamurthy (kdakshin@in.ibm.com), Senior Software Engineer, IBM China

author photoKarthikeyan Dakshinamurthy is the integration architect for the Rational Focal Point development team in Bangalore, India. He has led the design and development efforts for Rational Focal Point integrations with Rational Collaborative Lifecycle Management applications. His diverse background and broad experience includes working in IT as a developer, team lead, and software architect for a variety of IBM Rational products.


developerWorks Contributing author
        level

Sreejith Nishanth (srnishan@in.ibm.com), Advisory Software Engineer, IBM

Author1 photoSreejith Nishanth is a lead developer on the Rational Focal Point development team in Bangalore, India. He has been on this team for the last five years. In this role, he has handled many customer and product performance issues. He is also contributing as the Globalization Lead for Rational Focal Point.



17 July 2012

Also available in Chinese Russian Portuguese

Introduction

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
High-level resources and their relationships

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
Workspace XML export page with export options

Larger view of Figure 2.

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
XML import page prompting for *.fpz file upload

Larger view of Figure 3.

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
Excel data mapped according to columns

Larger view of Figure 4.

Note:
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.

Tip:
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
List of OS resources for the instance that is running

Larger view of Figure 6.

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
Details of cache allocated to each resource type.

Larger view of Figure 7.

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
Graph of expressions execution status over time

Larger view of Figure 8.

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:

  • Attxxx
  • formulxxx
  • matirxxxx
  • formulaqueue
  • formuladependson

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.

Resources

Learn

Get products and technologies

  • 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.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Rational software on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Rational
ArticleID=824751
ArticleTitle=Data import, export, and update in Rational Focal Point
publish-date=07172012