Clarity

Hi

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

Hi Sreerambabu

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

Regards

Suman Pramanik 

Hi,

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.

Regards

Suman Pramanik 

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 

Hi Sreerambabu

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.

Regards

Suman Pramanik 

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

Sreerambabu

WMIS Hub Enabling Team| BNP Wealth Management

Tel: +91 44 7114 9567

Mob:+91 9840240482

Re:  - Re: DWH Jasper reports not showing results for 2018

(Internet)

communityadmin

To:

Sreerambabu PALANISWAMY

30/01/2018 14:21

Please respond to jive-1583123511-1zz5v9-2-403qjx

CA Communities

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

CA Communities

Please remember to Mark the Correct Answer go to the message on CA

Communities

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

streams: Inbox

You are receiving this email because you are a member of the CA

Communities.

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

to CustomerPrograms@ca.com.

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. 

Hi Roland,

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

Thanks

Sreeram