Extract, transform, and load (ETL) process

Running the ETL jobs, builds, and streams.
Investigating the reasons for ETL builds failing
  • If the connection on which the error occurred is an XML ODBC data source, see the ODBC and JDBC log files, and the sections on the data service and XML ODBC driver.
  • If the connection on which the error occurred is IBM® Rational® Insight data warehouse, see the installation logs of the data warehouse.
Investigating the reasons for data being rejected
See the logs in the [rational_insight_installation_directory]/cognos/datamanager/data that contain information on how many records are acquired and merged or rejected.
Investigating the errors in a specific build in a job
Duplicate the job that includes the build, remove all builds not related to the build you want to investigate, and run the job. The job will set the variables correctly.
Re-using ETL jobs
An ETL job associated to a resource category is automatically applied to all resource groups within that resource category. To reuse an ETL job, use IBM Rational Insight XML data configuration to define a new resource group and place all identical schemas under the same resource category. During the ETL process, data will be automatically loaded or removed, depending on whether the resource category exists or does not exist in that resource group.
Handling double-counting problems
Typically, a fact table contains multiple dimensions. Fact tables store data in minimum grain, that is, each combination of every dimension is stored as a row. But each report might not use every dimension. When there are many-to-many relationships between the fact and a dimension, data for even the dimensions not used in the report will also be aggregated, thus resulting in inaccurate reports. To prevent such a scenario:
  1. Create a base fact table. Make sure that the fact tables does not have many-to-many relationships between the fact and any of the dimensions.
  2. For each many-to-many relationship with the fact, create a new fact table that includes the dimensions from the base fact table, and define the many-to-many relationship in the new table.
Windows Running ETL jobs on a non-English system with Oracle 11g database
  1. Create a catalog database schema by running the following command:
    catmanage.exe ORACLE UID/PWD@ServiceName -c
    where UID and PWD are the user name and password of a valid account that can connect to the ServiceName.
  2. Start IBM Cognos® Data Manager Designer, open the catalog database, and restore the catalog.
Linux Running ETL jobs in a Linux® environment
IBM Cognos Data Manager does not have a graphical user interface for Linux environments. To run ETL jobs in a Linux environment, use Linux shell commands.
Note: You need create the related DSNs and make sure the connections are established.
Linux When running an ETL job, you get the following error:Data name not found, and no default driver specified.
  1. Check the odbcinst.ini file:
    1. Open the odbcinst.ini file in a text editor.
    2. Go to the [IBM Rational Insight XML ODBC Driver] slot.
    3. Make sure the Driver property points to the correct file path of libratlxml.so
  2. Check the odbc.ini file:
    1. Open the odbc.ini file in a text editor.
    2. Make sure the desired system DSN exists and the value of its Driver property is IBM Rational Insight XML ODBC Driver. If the DSN does not exist, add it as shown in the example:
      [ClearCase]
         Driver = IBM Rational Insight XML ODBC Driver
         DataSource = ClearCase
         Description = A DSN example
         Configuration = /opt/ibm/RationalInsight/dataconfig/Configuration/clearcase.xdc
  3. Verify the JVM path
    1. On the console, run the export command.
    2. Make sure the variable "LD_LIBRARY_PATH" includes the path of IBM Java™ Virtual Machine carried by IBM Rational Insight.An example is /opt/ibm/RationalInsight/AppServer/java/jre/bin;/usr/local/lib.
    3. Make sure the variable "PATH" includes the path of IBM Java Virtual Machine carried by IBM Rational Insight. An example is /opt/ibm/RationalInsight/AppServer/java/jre/bin/classic.

Feedback