Jazz Forum Welcome to the Jazz Community Forum Connect and collaborate with IBM Engineering experts and users

ETL jobs for FocalPoint

Questions
Tags
Users
Badges
Noriyuki Egi
(74●1●18●18) Jan 23 '12, 11:50 a.m.
Hi
Thanks always.
Please tell us about ETL jobs for FocalPoint.

Environment
-FocalPoint6.5.1( using Default IT Portfolio Template)
-Insight1.1

I saw the following Technote.
http://www-01.ibm.com/support/docview.wss?uid=swg21572754


My understanding of FocalPoint integration is that data of FocalPoint will be loaded into the table "F_TP_PROJECT_FINANCIAL_METRICS" by the job "FPTPFinancial_TGCell".

When I run the FocalPoint ETL job, Job"Build_FPTPFinancial_TGHead" successfully completed and Data is stored to "RIODS"."TEMP_TIMEGRID_HEAD".
But the next job "FPTPFinancial_TGCell", the data was rejected to "F_TP_PROJECT_FINANCIAL_METRICS"

Build_FPTPFinancial_TGCell_xxxx.log is as follows.

All records was rejected.
Could anybody advise me about this cause? 


[PROGRESS   - 17:39:50] Checking Fact Data existence

[DETAIL     - 17:39:51] Fact Data found

[PROGRESS   - 17:39:51] Processing Reference Data

[DETAIL     - 17:39:51] Caching Lookup 'Lookup_TG_Date'

[INTERNAL   - 17:39:51] 0.00MB used to cache 'Lookup_TG_Date' reference data

[DETAIL     - 17:39:51] Caching Lookup 'Lookup_ProjectEK2'

[DETAIL     - 17:39:51] Cached 127 members

[INTERNAL   - 17:39:51] 0.07MB used to cache 'Lookup_ProjectEK2' reference data

[PROGRESS   - 17:39:51] Processing Dimension Domains

[INTERNAL   - 17:39:51] Dimension 'Lookup_Project' - max member domain size 128

[INTERNAL   - 17:39:51] Dimension 'Lookup_Date' - max member domain size 256

[PROGRESS   - 17:39:51] Processing Fact Data

[SQL        - 17:39:51] checking for existing data



SELECT MAX( "F_TPPROJFIN_METRIC_ID" ) FROM "RIDW"."F_TP_PROJECT_FINANCIAL_METRICS"



[EXECUTEDSQ - 17:39:51] checking for existing data



select max("F_TPPROJFIN_METRIC_ID") from "RIDW"."F_TP_PROJECT_FINANCIAL_METRICS" FOR FETCH ONLY



[DETAIL     - 17:39:51] 2942 input (in all), 0 accepted, 0 delivered

[INTERNAL   - 17:39:51] Mem(M): 0.1 [Peak=0.1] (Ref=0.1 Domain=0.0 Pool=0.0)

[PROGRESS   - 17:39:51] Delivery 'Fact1': no rows delivered to '"RIDW"."F_TP_PROJECT_FINANCIAL_METRICS"'

[INTERNAL   - 17:39:51] Dimension 'Lookup_Project' - actual domain size 0 (peak 0)

[INTERNAL   - 17:39:51] Dimension 'Lookup_Date' - actual domain size 0 (peak 0)

[DETAIL     - 17:39:51] Data Stream row limits: sample rate 1, max rows 0

[DETAIL     - 17:39:51] Data Source 'DataSource1': 1209 physical (1209 logical) rows read, 1209 used

[DETAIL     - 17:39:51] Data Source 'DataSource2': 1209 physical (1209 logical) rows read, 1209 used

[DETAIL     - 17:39:51] Data Source 'DataSource3': 131 physical (131 logical) rows read, 131 used

[DETAIL     - 17:39:51] Data Source 'DataSource4': 131 physical (131 logical) rows read, 131 used

[DETAIL     - 17:39:51] Data Source 'DataSource5': 131 physical (131 logical) rows read, 131 used

[DETAIL     - 17:39:51] Data Source 'DataSource6': 131 physical (131 logical) rows read, 131 used

[PROGRESS   - 17:39:51] Acquisition: 0 accepted, 2942 rejected

[PROGRESS   - 17:39:51] Delivery   : 0 direct, 0 summary/merge, 0 total

[PROGRESS   - 17:39:51] Done - 0 00:00:07 elapsed

databuild -- completed (20-Jan-2012 17:39:51)

0 votes

2 answers
5,910 views
0 votes

2 answers

Permanent link
wei zhang
JAZZ DEVELOPER (201●1) Feb 01 '12, 9:32 p.m.
You could find the rejected data and reason from folder named "data" which is in the same level of data manager log folder.

0 votes

Permanent link
Noriyuki Egi
(74●1●18●18) Feb 06 '12, 10:58 p.m.
Hi, cdlweiz
Thank you very much for good information!
I have resolved the issue. (But the other issue has occur...)

At first, the resolved issue and my workaround is that,,

cause
Format of "Year" is different between .YEAR and .YEAR_OF.
So, Lookup_Date Unmatched has ocured at the job"FPTPFinancial_TGCell".
(for example)
.YEAR-> 11
.YEAR_OF -> 2011

my workaround
Change the job"FPTPFinancial_TGHead"->"DataStream" ->"Derivations"->"Year".

before: ToInteger( Left( cell, 4 ) )
after: ToInteger( Concat( '20', Left( cell, 2 ) ) )


Then, the next issue I faced.
When I rerun the FocalPoint job, the error has occur.
And the result of the job, although the records are loaded to F_TP_PROJECT_FINANCIAL_METRICS, The following columns are "0.0".
- BUDGETED_REVENUE
- PLANNED_REVENUE
- ACTUAL_REVENUE
- BUDGETED_COST
- PLANNED_COST
- ACTUAL_COST 


DM-DBM-3671 A bind error occurred on column 'ACTUAL_REVENUE'; cannot convert the value 1.06E7 to precision 18, scale 2.



DM-DLV-0101 Delivery 'Fact1': failed on statement "INSERT INTO "RIDW"."F_TP_PROJECT_FINANCIAL_METRICS" ("PROJECT_ID", "DATE_ID", "BUDGETED_REVENUE", "PLANNED_REVENUE", "ACTUAL_REVENUE", "BUDGETED_COST", "PLANNED_COST", "ACTUAL_COST", "REC_DATETIME", "F_TPPROJFIN_METRIC_ID") VALUES (89, 1187, NULL, NULL, '1.06E7', NULL, NULL, NULL, '2012-02-07 11:39:41', 1480)".



DM-BLD-0005 The delivery 'Fact1' failed.



[PROGRESS   - 11:39:48] Build 'FPTPFinancial_TGCell' Failed

databuild -- failed (07-Feb-2012 11:39:48)


What I'd like to do is simply integrated to FocalPoint data. And I'm using default IT Portfolio management Template.

I really appreciate if anybody advise me about this cause.
Thank you!

0 votes

Your answer

Register or log in to post your answer.

Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.

Ask a question
Guidelines
FAQ
Search context
Follow this question

By Email: 

Once you sign in you will be able to subscribe for any updates here.

By RSS:

Answers
Answers and Comments
Question details

Question asked: Jan 23 '12, 11:50 a.m.

Question was seen: 5,910 times

Last updated: Jan 23 '12, 11:50 a.m.

Confirmation Cancel Confirm