Clarity

  • Private Community
Back to discussions
Expand all | Collapse all

DWH and JS report

Suman Pramanik

Suman PramanikJan 25, 2017 04:32 AM

This was what I was mentioning

  • 1.  DWH and JS report

    Posted Jan 24, 2017 05:49 AM

    Hi all,

     

    I want to ask how tables are populated in DWH? I have difficulties in gettin resource related reports (resource availability, capacity vs demand etc). When I look at the dataset from the report for resource availability report, it uses a few tables. Resources are ok, I can select records from the table.  For the other 3 tables the results are;


    select * FROM dwh_res_period_facts pf (no record)

    select * FROM dwh_cmn_period c (1 fiscal period)

    select * FROM dwh_cmn_period_ln cln (1 fiscal period)

     

    Previously only one fiscal was defined (01-01-16 / 31-01-16) and the fiscal period is in the table. Then we have defined all fiscal periods(monthly) for years 2016-2017-2018 and activated the year 2017.

     

    Then we have run the update reports tables and LDW jobs. But the tables are not populated. What am I missing?

     

    Regards.



  • 2.  Re: DWH and JS report

    Broadcom Employee
    Posted Jan 24, 2017 06:29 AM

    Hi Bican,

     

    I would need few more information 

     

    PPM version & database version

     

    The DWH should populate the data based on the setting in the PPM --> administrations -- Data ware house options

     

    DWH doesn't populate if the PPm and DWH time zone mismatch. Apart from this if your Load DWH job is running successfully then we need to take a look at data and debug. A support case would be beneficial.

     

    Regards

    Suman Pramanik 



  • 3.  Re: DWH and JS report

    Posted Jan 24, 2017 06:47 AM

    PPM is 14.3

    SQL is Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

     

    Actually Projects, ideas, resources are populating. So the settings should also be ok. I mean some tables are not in popuşating I guess.

     

    regards



  • 4.  Re: DWH and JS report

    Broadcom Employee
    Posted Jan 24, 2017 06:50 AM

    Hi Bican,

     

    14.3 what patch, you might hit a bug where job runs completely in SQL but fact tables are not populated because materialized view might have been deleted some how.So its worth getting a case open and we can take a look directly.


    Regards

    Suman Pramanik 



  • 5.  Re: DWH and JS report

    Posted Jan 24, 2017 10:05 AM

    The exact version is : 14.3.0.298. I opened a case...

     

    Regards.



  • 6.  Re: DWH and JS report

    Posted Jan 25, 2017 03:29 AM

    Hi,

     

    I want to share what I found up to now while waiting response from support. 

     

    from [ppm_dwh].[DWH_RES_PERIOD_FACTS_LOAD]  I have checked DWH_X_RES_PER_FACTS_V

     

    ppm_dwh.DWH_RES_RESOURCE  is OK. I can see all the resources.
    ppm_dwh.dwh_cmn_period  is NOK. Only one fiscal period is there (I am not sure if this is OK or NOT, I am saying NOK because I have defined 2016-2017-2018 and activated 2017)

    Now I am looking how dwh_cmn_period is populated. Can anyone assist me about this ?

     

    As I understood dwh, facts and time slices are deeply related... We had a problem last week and the time slice job didnot worked for time. But load datawarehouse did. May this cause a synchronization problem? If so can we catch synchronization again?

     

    Regards

     



  • 7.  Re: DWH and JS report

    Broadcom Employee
    Posted Jan 25, 2017 04:33 AM

    We will work with you via support ticket and fix it. Also I would like you to apply the latest patch as soon as possible



  • 8.  Re: DWH and JS report

    Posted Feb 01, 2017 09:35 PM

    Hi ,

     

    Were you able to resolve the issue? We are also facing the same issue. The dwh_cmn_period table somehow is not getting updated. Please let me know the steps you followed to resolve the issue.

     

    Regards



  • 9.  Re: DWH and JS report

    Posted Feb 03, 2017 09:32 AM

    Actually no, 14.4 upgrade is adviced from support.

     

    Regards



  • 10.  Re: DWH and JS report

    Posted Feb 09, 2017 05:26 AM

    We raised a case with CA Support and we were able to resolve the issue by following the below steps as suggested by CA Support.

     

     

    -Go to Administration > General Settings > System Options > Data Warehouse Options
    1.Unset the field: Entity for Fiscal Time Periods
    Click Save.
    Reset the field and Click Save.

    2. Run the following jobs in the following order:
    a. Time Slicing job
    b. Update Report Tables job
    c. Load DWH job (full)
    d. Load DWH Access Rights job 



  • 11.  Re: DWH and JS report

    Posted Jan 25, 2017 03:52 AM

    Hi SumanPramanik, I don't see Dataware house option under Administration with CA PPM v14.3. Is that an option that needs to be enabled? or a secure page to be accessed only by Admin? Any link or thread with regards to this would be of great help. Thanks in advance.

     

    Monica



  • 12.  Re: DWH and JS report

    Posted Jan 25, 2017 04:02 AM

    A section in the "Administration - General Settings - System Options" page.

     

    Regards



  • 13.  Re: DWH and JS report

    Broadcom Employee
    Posted Jan 25, 2017 04:32 AM

    This was what I was mentioning 

     



  • 14.  Re: DWH and JS report

    Posted Jan 26, 2017 09:52 AM

    Hi Suman,

     

    After seeing the post, thought of checking my system. can't see  include weekly/monthly... kind of period options.

    what is the relevance of these options?.

     

    In our system, all dwh table populates correct data and reports provides data as expected.

     

    Thanks,

    Prakash



  • 15.  Re: DWH and JS report

    Broadcom Employee
    Posted Jan 27, 2017 01:57 AM

    If you are on 14.3 then these options were not there



  • 16.  RE: Re: DWH and JS report

    Posted Dec 20, 2019 04:11 AM
    Hi Suman

    I am hitting the same issue on 15.7 ( Patch 1).

    I am not seeing any records for WEEKLY or MONTHLY in the dwh_cmn_period table in ppm_dwh schema.

    We just upgraded a customer from 14.2 to 15.7 ( applied patch 1 on 15.7 ).

     

    We also did a new install Jaspersoft , ran the Load DWH ( full mode ) and the access rights jobs.

     

    Basic OOTB reports like Resource Availability, Capacity vs Demand etc are returning zero records.

     

    Next step of debugging was to take the report query and try running it.

     

    On analyzing further , it was found that the DWH_CMN_PERIODS table in ppm_dwh schema does not have any records with period_type_key MONTHLY or WEEKLY.

     

    This is the reason for the reports to return zero records.

     

    I checked the following

     

    • System Option has all period types enabled
    • Time slice dates are fine

     

    Anything else that you can think of?

     

    Environment : UAT

     

    Clarity Version 15.7 Patch 1 with Jaspersoft 7.1

     

    Oracle 12.1.0.2.x with RHEL 7.6 app servers

     

    Subject : OOTB DWH reports are not displaying any data

     

    Description

     

    Upgraded the environment from 14.2 to 15.7

    • Initial upgrade from 14.2 to 15.2
    • Upgrade from 15.2 to 15.6.1
    • Upgrade from 15.6.1 to 15.7

    Ran the Load Datawarehouse ( Full Load ) and Load DWH access rights jobs successfully. No errors in the DWH log file

    Time Slicing job was run successfully

    Time slices are configured correctly

    Ran a lot of reports and all of them gave "No matching records" which indicates that the reports are not finding data

    Examples are

    • Resource Availability
    • Capacity vs Demand by Role
    Capacity vs Demand by Resource
    Original Message


  • 17.  RE: Re: DWH and JS report

    Broadcom Employee
    Posted Dec 24, 2019 11:08 AM
    Hi Ram

    There is an health report in clarity that tells you if you slices are in order to populate the data, can you check that to see if those are showing as green

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------

    Original Message


  • 18.  RE: Re: DWH and JS report
    Best Answer

    Posted Dec 25, 2019 03:20 AM
    Hi All

    I was able to resolve the issue by ensuring that all weekly time slices start on Saturday which is defined as first day of week in the Administration > Project Management > Settings.

    Thanks Ram



    Original Message


  • 19.  RE: Re: DWH and JS report

    Posted Feb 19, 2020 10:55 PM
    Hi Suman

    We hit the same issue again on the same environment

    Datawarehouse OOTB PMO reports like Resource availability do not return any data for MONTHLY or WEEKLY slice periods

    Clarity 15.7 Patch 1

     

    RHEL 7u6 application/jaspersoft servers

     

    Database : Oracle 12.2.0.1.x

     

     Description

     

    We have configured and run our DWH Full Load job successfully. Even checked the health report and see that all our time slices for weekly / monthly etc are set up correctly.

    However we do are unable to see any data when running any of the OOTB reports like Resource availability or Capacity related reports.

     

    These reports use the Monthly and weekly slices.

     

     

    Some further analysis reveals that the DWH_CMN_PERIOD table on the PPM_DWH schema has no records with PERIOD_TYPE_KEY as MONTHLY or WEEKLY.

     

    This is what is causing the reports to return no data.

     

    Please advise on what steps could be taken to fix this.

     

    Thanks Ram



    Original Message


  • 20.  RE: Re: DWH and JS report

    Broadcom Employee
    Posted Feb 19, 2020 11:09 PM
    Hi Ram,

    Are you running the update report tables before running DWH?

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------

    Original Message


  • 21.  RE: Re: DWH and JS report

    Posted Feb 19, 2020 11:14 PM
    Hi Suman

    My understanding was that this step is not needed on versions like 15.7.

    I can run the job though



    Original Message


  • 22.  RE: Re: DWH and JS report

    Broadcom Employee
    Posted Feb 19, 2020 11:19 PM
    Hi Ram,

    Not that I am aware, i believe the period will still read from RPT_CALENDAR, so to populate that you need to run the update report tables 

    ------------------------------
    Thanks & Regards
    Suman Pramanik
    Sr. Principal Support Engineer | Customer Success & Support, Enterprise Software Division
    Broadcom
    ------------------------------

    Original Message


  • 23.  RE: Re: DWH and JS report

    Posted Feb 19, 2020 11:23 PM
    Ok thank you, let me try that .



    Original Message


  • 24.  RE: Re: DWH and JS report

    Posted Feb 20, 2020 12:17 AM
    Hi Suman

    Tried running the Update Report Tables followed by Load DWH full load.

    However still don't see MONTHLY or WEEKLY data added to the DWH_CMN_PERIOD table 



    Original Message


  • 25.  RE: Re: DWH and JS report

    Posted Feb 20, 2020 02:26 AM
    Hi Suman

    I will get a Broadcom Support Case raised on the behalf of the client.

    Would appreciate if you can assist or have someone review this at the earliest. We are planning to upgrade Prod next week and this may be a problem if it persists



    Original Message