< Previous | Next >

Lesson 1.3: Create a new Data Manager build

In this lesson you will learn how to create a Data Manager build for loading additional attributes to the data warehouse.
Prerequisite: You have completed lessons 1.1 and 1.2.

In lesson 1.1, you added a new entity to the data table. The ETL process requires one build for each entity. When you load a new entity, you need to create a new build and modify an existing build to add more attributes to an existing entity. In lesson 1.2, you modified an existing build. In this lesson, you will create a new build for loading additional attributes on the data warehouse. You will be loading the data warehouse with data attributes that are outside the default schema.

Extract the data from the source
  1. Start IBM® Cognos® Data Manager and load the ETL catalog.
  2. Expand Builds and JobStreams > ODS > ClearQuest , right-click the CQEnterprise folder, select Insert Fact Build, specify the build name to be CQ_ENT_Component, and click OK.
    Add a fact build
    Concept: The naming convention is [Product Source]_[Configuration]_[Entity].
  3. Right-click inside the editor, click Insert Data Source, and type RI_Stg as the name.
  4. Click the Query tab and in the Database list, select ClearQuest.
  5. Right click the CQEnterprise.ENTComponent table in the database object tree and select Add table select statement. Modify this query as follows:
    • Replace the schema name "CQEnterprise" with a variable "{$RESOURCEGROUP}". This is done so that you can reuse the ETL job for multiple resource groups.
    • Add the following SQL code to enable delta load in the ETL:
      WHERE modifiedsince='{$MODIFIED_SINCE}'
    modify the SQL query
  6. To test the new SQL statement, click play button at the lower left of the window. In the window to specify variables, type the schema name (CQEnterprise) for the $RESOURCEGROUP entry. Leave the $MODIFIED_SINCE field empty so that the null value is used by the data service to return all records.
  7. Click the Result columns tab and click Refresh. In the window to specify variables, type the schema name (CQEnterprise) for the $RESOURCEGROUP entry. Leave the $MODIFIED_SINCE field empty so that the null value is used by the data service to return all records. Click OK.
You have now created an SQL query to extract data from the data source.

Transform the attributes

  1. Double-click the new data stream node in the editor, click Auto Map, and click OK.
  2. Right-click the Transformation Model object and click Mapping. Click Auto Map and select Create new Elements as Attributes. Click OK.
Deliver the attributes to the target table
  1. Right-click inside the editor and select Insert Relational Table Deliver. Type Component as the name.
  2. Click the Table Properties tab. In the Connection list, select the Rational Data Warehouse table, and click table icon to the right of the table name.
  3. In the normalized data area (RIODS schema), select the COMPONENT table and click OK.
  4. Replace the schema name with variable "{$ODS}".
  5. Click Import Target Table and use the following list to map the elements to columns in the target table.
    • The (Record Identity) element to the COMPONENT_ID column
    • The (Last Update Date) element to the REC_DATETIME column
    • The dbid of the component to the EXTERNAL_KEY1 column
    • The DATASOURCE_ID element to the SOURCE_ID column
    • The Component element to the NAME column
    Click OK.
  6. Save the catalog.

Add the build to the job stream

Expand the jobs under the RationalInsightETL > Builds and JobStreams > Jobs > CQJobs > Enterprise node and add the newly created build before the CQ_ENT_Defect build.

Run the build
  1. Expand the RationalInsightETL > Builds and JobStreams > Jobs > CQJobs node, right-click Enterprise, and select Execute > Execute JobStream.
  2. When prompted, click OK. The status of the execution is displayed in the console and the details are logged in the [rational_insight_installation_directory]/cognos/datamanager/log file.
You executed all builds in the Enterprise job stream. If you want to execute only the build you created:
  1. Create a duplicate of the Enterprise job stream (right-click the job stream and select Duplicate).
  2. Delete the builds that you do not want to execute.
  3. Save the changes.
  4. Run the build.

Lesson checkpoint

You created a new Data Manager build for data that is outside the default schema. You added a new attribute to the Data Manager build, specified the upload location of the attribute in the data warehouse, and updated the Data Manager build to deploy the changes.
< Previous | Next >

Feedback