It's all about the answers!

Ask a question
QuestionsTagsUsersBadges
Guidelines | FAQ
Follow this question
Question details

×1,230

question asked: Jan 23 '12, 11:50 a.m.
question was seen: 5,781 times
last updated: Jan 23 '12, 11:50 a.m.

Related questions

ETL jobs for FocalPoint

0
Noriyuki Egi (74●1●18●18) | asked 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)

2 answers

Most liked answers ↑|Newest answers|Oldest answers

0
permanent link
wei zhang (201●1) | answered Feb 01 '12, 9:32 p.m.
JAZZ DEVELOPER
You could find the rejected data and reason from folder named "data" which is in the same level of data manager log folder.
0
permanent link
Noriyuki Egi (74●1●18●18) | answered 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!

Your answer

Register or 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.