Clarity PPM

Expand all | Collapse all

DWH Jasper reports not showing results for 2018

Jump to Best Answer
  • 1.  DWH Jasper reports not showing results for 2018

    Posted 01-29-2018 10:41 PM

    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



  • 2.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-29-2018 11:24 PM

    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 



  • 3.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-29-2018 11:36 PM

    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. 



  • 4.  Re: DWH Jasper reports not showing results for 2018
    Best Answer

    Posted 01-29-2018 11:42 PM

    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 



  • 5.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 01:40 AM

    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 



  • 6.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 01:56 AM

    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 



  • 7.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 02:09 AM

    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.



  • 8.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 03:50 AM

    Thats exactly I have recommended. 



  • 9.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 03:55 AM

    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.



  • 10.  Re: DWH Jasper reports not showing results for 2018

    Posted 01-30-2018 03:58 AM

    Please mark the thread as resolved as the problem has been fixed and it was due to incorrect slice setting. 



  • 11.  Re: DWH Jasper reports not showing results for 2018

    Posted 04-12-2018 02:40 AM

    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