In this lesson you will learn how to create a Data Manager
build for loading additional attributes to the data warehouse.
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- Start IBM® Cognos® Data Manager and load the ETL catalog.
- Expand , right-click the CQEnterprise folder,
select Insert Fact Build, specify the build
name to be CQ_ENT_Component, and click OK.
Concept: The
naming convention is [Product Source]_[Configuration]_[Entity].
- Right-click inside the editor, click Insert Data Source,
and type RI_Stg as the name.
- Click the Query tab and in the Database list,
select ClearQuest.
- Right click the CQEnterprise.ENTComponent table
in the database object tree and select Add table select
statement. Modify this query as follows:
- To test the new SQL statement, click
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.
- 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
- Double-click the new data stream node in the editor, click Auto
Map, and click OK.
- 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- Right-click inside the editor and select Insert Relational
Table Deliver. Type Component as
the name.
- Click the Table Properties tab. In the Connection list,
select the Rational Data Warehouse table, and
click
to the right of the table name.
- In the normalized data area (RIODS schema), select the COMPONENT table
and click OK.
- Replace the schema name with variable "{$ODS}".
- 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.
- Save the catalog.
Add the build to the job stream
Expand the
jobs under the node and add the newly created build before the CQ_ENT_Defect
build.
Run the build- Expand the node, right-click Enterprise,
and select .
- 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:
- Create a duplicate of the Enterprise job stream (right-click the
job stream and select Duplicate).
- Delete the builds that you do not want to execute.
- Save the changes.
- Run the build.