I've designed a jaspersoft report using DWH db. I run "Load DWH" every minute to sync the Clarity db with DWH db. All the new projects, new tasks which was created in 2018 are updated in DWH db, except the timeperiods. Time periods of 2018, didn't get updated in DWH tables(DWH_CMN_Period). I've opened the Time period for January month and created & activated the fiscal period for the whole year 2018. I ran the time slicing(newjob), update BO reports job, Load DWH access rights and Load DWH(Full and non-full Load) jobs. But still the DWH_CMN_period is not updated.
Please advise how do I proceed to get entries in DWH
please find the query(if in case)
SELECT RES.RESOURCE_NAME ,RES.IS_ACTIVE ACTIVE ,INV.INVESTMENT_KEY INVESTMENT_KEY , INV.INVESTMENT_NAME , TASK.TASK_NAME TASK_NAME ,TASK.TASK_KEY TASK_ID , TASK.WBS_LEVEL WBS_LEVEL , TASK.WBS_SEQUENCE WBS_SEQUENCE , ASSIGNT.ASSIGNMENT_KEY , PERIOD.PERIOD_TYPE_KEY , PERIOD.MONTH_START_DATE , TO_CHAR(PERIOD.MONTH_START_DATE, 'YYYY/MM') TIME_PERIOD , SUM(TEF.TIME_HOURS) / 8 pending_Actuals FROM DWH_TME_ENTRY TE INNER JOIN DWH_TME_ENTRY_FACTS TEF ON ( TEF.TIMEENTRY_KEY = TE.TIMEENTRY_KEY ) INNER JOIN DWH_CMN_PERIOD PERIOD ON ( PERIOD.PERIOD_KEY = TEF.PERIOD_KEY ) --AND PERIOD.PERIOD_TYPE_KEY = 'DAILY' --AND PERIOD.PERIOD_TYPE_KEY = 'MONTHLY' INNER JOIN DWH_TME_SHEET TS ON ( TE.TIMESHEET_KEY = TS.TIMESHEET_KEY ) AND TS.TIMESHEET_STATUS_KEY <> 4 AND TS.TIMESHEET_STATUS_KEY <> 5 INNER JOIN DWH_TME_PERIOD TP ON ( TP.TIME_PERIOD_KEY = TS.TIME_PERIOD_KEY ) --AND IS_ACTIVE = 1 INNER JOIN DWH_INV_ASSIGNMENT ASSIGNT ON ( ASSIGNT.ASSIGNMENT_KEY = TE.ASSIGNMENT_KEY ) INNER JOIN DWH_INV_TASK TASK ON ( TASK.TASK_KEY = ASSIGNT.TASK_KEY ) INNER JOIN DWH_RES_RESOURCE RES ON ( RES.RESOURCE_KEY = ASSIGNT.RESOURCE_KEY ) INNER JOIN DWH_INV_INVESTMENT INV ON ( INV.INVESTMENT_KEY = ASSIGNT.INVESTMENT_KEY )
AND PERIOD.MONTH_START_DATE = '01-JAN-2018' GROUP BY PERIOD.PERIOD_TYPE_KEY ,INV.INVESTMENT_KEY , ASSIGNT.ASSIGNMENT_KEY , PERIOD.MONTH_START_DATE , RES.RESOURCE_NAME , RES.IS_ACTIVE , INV.INVESTMENT_NAME , TASK.TASK_NAME ,TASK.TASK_KEY , TASK.WBS_LEVEL , TASK.WBS_SEQUENCE
DWH is not a job to run every minute, can you run a full load and you can check using the query below on what tables are updated and how many records. Also you need to run the Update report tables job before running DWH
SELECT TABLE_NAME, TABLE_TYPE, POPULATION_TIME_SEC, RECORD_COUNT, DW_LOAD_START_DATE, DW_LOAD_END_DATE, DW_UPDATED_DATE FROM DWH_GATHER_METRICS_V
SELECT TABLE_NAME, TABLE_TYPE, COMPLETED_FLAG, POPULATION_TIME_SEC, DW_LOAD_START_DATE, DW_LOAD_END_DATE FROM DWH_RUN_STATUS_V
thanks for your reply. What do I check using the query results of the query you've advised?
Also I found the Time slices with Rollover Interval and Slice Periods of Type 'Fiscal' has been not updated. it was still in 2017. So I updated the expiration date and Number of slice period and ran Time slicing. I also ran the update Report tables job and Load DWH full load job.
If the slices has not been updated then I believe you updated the slice window and ensured the Timeslice job completed. The query will give you number of records getting updated in DWH and the time spent. However most important thing is you should never run DWH every minute.
I was able to solve the issue by updating the DWh timeslices and ran the full Load DWH sync. @Suman..Thanks for your reply. Even if I schedule the load dwh(not full load) every one min, it runs every 5-7 mins accoridng to the load. We need this job to run continously,as the reports depend on the updated data
Glad to hear, however running DWH every minute has its own consequences too. Its not a real time data and its not designed to be run in that way. I do understand you need real time data and there are multiple approaches I can think by using DB link and read data from DWH without even DWH run or run reports from PPM schema also.
I guess you had raised a case at the same time you opened a community thread and based on this the case also will be closed.
Why don't you write the report based on the non-datawarehouse (aka Data Source = CA_PPM_BEAN)? You will then be accessing the latest data, rather than having to wait for the DWH job to run and finish.
Thats exactly I have recommended.
We are upgrading our Jasper, May be I will try to change it then. Thanks
for your recommendations.
Thanks & Regards
WMIS Hub Enabling Team| BNP Wealth Management
Tel: +91 44 7114 9567
Re: - Re: DWH Jasper reports not showing results for 2018
Please respond to jive-1583123511-1zz5v9-2-403qjx
Re: DWH Jasper reports not showing results for 2018
reply from Suman Pramanik in CA PPM - View the full discussion
Thats exactly I have recommended.
Reply to this message by replying to this email, or go to the message on
Please remember to Mark the Correct Answer go to the message on CA
Start a new discussion in CA PPM by email or at CA Communities
Following Re: DWH Jasper reports not showing results for 2018 in these
You are receiving this email because you are a member of the CA
If you'd like to change your email preferences, click here. If you want
your communities account to be deactivated (opt out), please send an email
Additionally, if you wish to opt out of all unsolicited commercial
communications from CA Technologies, click here.
This message and any attachments (the "message") is
intended solely for the intended addressees and is confidential.
If you receive this message in error,or are not the intended recipient(s),
please delete it and any copies from your systems and immediately notify
the sender. Any unauthorized view, use that does not comply with its purpose,
dissemination or disclosure, either whole or partial, is prohibited. Since the internet
cannot guarantee the integrity of this message which may not be reliable, BNP PARIBAS
(and its subsidiaries) shall not be liable for the message if modified, changed or falsified.
Do not print this message unless it is necessary,consider the environment.
Please mark the thread as resolved as the problem has been fixed and it was due to incorrect slice setting.
How to design the report based on CA_PPM_BEAN, as I couldn't add this datasource to Studio, where I could design the report? Please advise How I could add this datasource to the Studio, so i could avoid scheduling the Load DWH job. Also is there list of tables I could see under CA_PPM_BEAN