Which database does BIRT uses? DW or CCM?
In RTC3.0.1, I thought that BIRT report uses CCM for livedata and DW for snapshot data. Though when I looked into the CCM DB directly, there were tables like "WORKITEM_SNAPSHOT.NEW__WI_COUNT", "WORKITEM_SNAPSHOT.WORKITEMS" and others that I can see through the BIRT design tool. So I'm confused about which database does BIRT really use.
It's important to understand the DB structure, in order to estimate the size and create backup/restore plans. I'd really appreciate for someone who can advice clearly on this.
It's important to understand the DB structure, in order to estimate the size and create backup/restore plans. I'd really appreciate for someone who can advice clearly on this.
Accepted answer
BIRT can get data from two places:
1. Directly from the data warehouse. We produce SQL queries which map to tables in the data warehouse for historical data.
2. Live data from the repository. In this case, we do not produce SQL queries on the repository database, rather we use public Java APIs (the "Query Model") to expose live data... we translate the input of the data set into Java calls to these APIs, and process the results and turn them into rows. At no point are we getting data directly from the repo.
james
Comments
Thank you James. Can I ask further questions to confirm my understanding ?
- After RTC3.0.1 is installed with default settings, out of box BIRT report uses both CCM database and DW database. is this correct ?
- Out of box burndown report based on BIRT, uses both CCM and DW. is this correct ?
- When I look into the database using DB2 tools, there is a table "WORKITEM_SNAPSHOT.WORKITEM_CHNGS" in CCM DB. As my understanding, I though that the historical data is in DW DB and the BIRT is using DW DB. So what is this "WORKITEM_SNAPSHOT.WORKITEM_CHNGS" in CCM DB for ?
thanks in advance.
- Out-of-the-box BIRT reports use the DW (directly), as well as the CCM database (indirectly, through the Java APIs) - it depends on which report you are running.
- Burndown uses both, correct. The live data point at the end of the chart, as well as the "quality of planning" metric are both computed from live data. The trending data comes from the data warehouse. Apparently there is a length limitation on these comments so I will explain #3 in my next comment...
1 vote
- Good question. It's historical. In RTC 3.0 and older, we did not have a dedicated data warehouse which was a separate database. We had a "data mart" which really collected point product data as part of the CCM database, and our BIRT reports used that for historical trending. In 3.0.1 and later, we have a separate data warehouse which serves that purpose, but the legacy data mart still exists. WORKITEM_SNAPSHOT is part of the legacy data mart, and should be ignored - no report will ever read from it.
1 vote
Thank you James for your great answers and taking your time. I could clearly understand about this. So we need to backup the both DBs in our administration plan.
2 other answers
According to answer from James : "
While there are significant schema differences between the old RTC data mart and the new datawarehouse, from the point of view of a BIRT report, these differences are mostly masked by the ODA layer, which is an abstraction between the physical schema and the data warehouse. For example, all of our out-of-the-box BIRT reports will work, unaltered, on both the data mart and on the data warehouse." (please see
https://jazz.net/forum/questions/56088/rtc-datamart-vs-clm-2011-data-warehouse)
Returning to your question, I guess BIRT uses both DW and Live data but all through ODA. @jmoody, please correct me if I'm wrong.
Comments
Thank you Amelin. I'm still not confident about which physical DB that BIRT report uses, because I've found the table "WORKITEM_SNAPSHOT.WORKITEM_CHNGS" in CCM DB that suppose to be the database for the livedata....
Only description I've found if some data source exposes live or warehouse data: https://jazz.net/wiki/bin/view/Main/DataWarehouseSnapshotSchemas20 Mostly it's the same for RTC 3.x. For WORKITEMS snapshot you can see it has different types of tables - fact, live, view.
1 vote
RTC 3.0.1 ships with both the legacy data mart (in CCM DB) and the new Data Warehouse. The user can configure RTC to use one or the other and they can switch back and forth, though we recommend using the new Data Warehouse. So you should not be confused by the fact that both schemas co-exist. BIRT will use the DB that is currently the default and the ODA layer will hide the physical schema and make both the legacy Data Mart and the new Data Warehouse look similar.