DataManager to transform from vertical to horizontal struct
We are trying to build our first report in Rational Insight 1.0.1. We have a reportable REST service in RTC and we have to use XDC to populate a star schema in the DB2 data warehouse.
The problem we have is that the reportable REST provides data in a vertical structure like -
This needs to be transformed to the following table structure in the warehouse
Can anyone suggest what is the best way to achieve this via DataManager?
Thanks,
Ankur
The problem we have is that the reportable REST provides data in a vertical structure like -
<metric workItemId="1" metricKey="Effort Variance" value="35%"/>
<metric workItemId="1" metricKey="Schedule Variance" value="45%"/>
This needs to be transformed to the following table structure in the warehouse
|workItemId|Effort Variance|Schedule Variance|
________________________________________
|1|35%|45%|
Can anyone suggest what is the best way to achieve this via DataManager?
Thanks,
Ankur
2 answers
Hello, this is a typical transformation scenario:
in one build, use 2 data streams, input the same SQL to select 3 columns, but apply 2 different filters metricKey=*** respectively, in transformation model, map 2 id to the same id column, the first value to EV, the second value to SV, in delivery, set id as key, and use insert/update mode.
in one build, use 2 data streams, input the same SQL to select 3 columns, but apply 2 different filters metricKey=*** respectively, in transformation model, map 2 id to the same id column, the first value to EV, the second value to SV, in delivery, set id as key, and use insert/update mode.
We are new to DataManager. We tried with Data Manager example DS_Advanced (Pivot1 build). Here our aim is to build the reverse way what Pivot1 is building the output.(Userguide example page no 361 - 363)
As suggested by you to create two data streams. We are able to create two data source but not two data steams. Can you tell us know the full steps with the above example which is almost same as suggested by Ankur.
Thanks in advance
Deb
As suggested by you to create two data streams. We are able to create two data source but not two data steams. Can you tell us know the full steps with the above example which is almost same as suggested by Ankur.
Thanks in advance
Deb