Clarity PPM1

Expand all | Collapse all

DWH load job - high processing time

  • 1.  DWH load job - high processing time

    Posted 06-07-2017 09:32 AM

    Hello everyone,

    I have a question about any experience with very slow processing of DWH load JOB. What is quite interesting is that full load takes 1/4 of processing time of incremental one. We had experience that full load took more then incremental one but now it is opposite. It started after we have changed and increased amount of slices for DWH. It was not big change and even there are no data for those time periods.

     

    Any idea or recommendation? Because it is oscilating from 90 to houndreds of minutes.

    thank you



  • 2.  Re: DWH load job - high processing time

    Posted 06-07-2017 09:37 AM

    Hi Lukas,

     

    Can you please let me know what is the exact PPM version?

     

    Also run the below query to check what is taking time to populate

     

    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

     

    This will help to understand more.

     

    Regards

    Suman Pramanik 



  • 3.  Re: DWH load job - high processing time

    Posted 06-07-2017 09:56 AM

    it is 14.2 version, and it is showing that DWH_CMN_MV_LOOKUP table type MVL. takes more then 2/3 of processing time. From where is data coming to this table? thank you



  • 4.  Re: DWH load job - high processing time

    Posted 06-07-2017 10:26 AM

    Hi Lukas

     

    This table contains the multivalued lookups - please see if you can disable some of your custom multivalued lookups from Data Warehouse and see if the performance improves.

     

    Thanks -Nika



  • 5.  Re: DWH load job - high processing time

    Posted 06-07-2017 10:38 AM

    Thanks Nika_Hadzhikidi for your response.

     

    Regards

    Suman Pramanik 



  • 6.  Re: DWH load job - high processing time

    Posted 06-07-2017 10:25 AM

    Hi Lukas,

     

     

    In release 14.2 it is normal on large datasets for Incremental Load to take as long or even more than the Full Load. The reason for this is the following:

    -- Full Load truncates the tables and reinserts all data from source PPM views

    ---Incremental Load goes record by record, compares the data and updates/delete/inserts the different records

     

    On large datasets the Incremental may take longer and this is normal. You may have started seeing the difference when the data had some growth or when you enabled more fields for Data Warehouse.

     

    Now, we have worked on this issue and improved the Load Data Warehouse job performance, and the large part of the updates, specifically for Incremental Load logic and performance was included in version 15.1.

     

    So for now what you can do is:

    1. Run Full Load only if that's possible
    2. Reduce the amount of custom attributes included in DWH
    3. Reduce the amount of languages included
    4. Ensure you are on latest fix pack

     

    Hope this helps -Nika



  • 7.  Re: DWH load job - high processing time

    Posted 08-08-2017 10:23 AM

    Hi Nika,

     

    We upgraded to Clarity Version 15.1 Patch 3 last week and we are seeing Incremental Job taking an hour more than Full Load job. Any ideas why that would be? Removing Custom attributes and custom multivalued lookups isn't an option for us as they are being used in Reports.

     

    Thanks,
    Venu



  • 8.  Re: DWH load job - high processing time

    Posted 08-08-2017 11:46 AM

    Hi Venu,

     

    Can you please let me know how long the incremental load is taking v/s full load. If its during roll over its expected but not other wise.

     

    You can run the queries to check which table took longer to populate 

     

    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

     

    Also what database you are using and it would be worth checking at database to see what is causing the slowness as we havn't seen this behavior in 15.x 

     

    Regards

    Suman Pramanik



  • 9.  Re: DWH load job - high processing time

    Posted 08-08-2017 03:36 PM

    Hi Suman,

     

    Thanks for your Reply!

     

    Full Load is taking around 2 hours and Incremental took more than 3 hours.

     

    Below are the time consuming tables:

     

    TABLE                                               TYPE  TIME RECORDS

    DWH_LKP_NPD_GATE3_BROWSE LOOKUP 1073 234451
    DWH_LKP_NPD_GATE2_BROWSE LOOKUP 1050 234451
    DWH_LKP_NPD_GATE4_BROWSE LOOKUP 1004 234451
    DWH_RES_PERIOD_FACTS FACT 888 36855707
    DWH_LKP_NPD_GATE1_BROWSE LOOKUP 859 234451
    DWH_INV_TASK_HIERARCHY 780 7862247
    DWH_LKP_NPD_GATE5_BROWSE LOOKUP 727 234451
    DWH_INV_PERIOD_FACTS FACT 717 1521832
    DWH_LKP_NPD_GATESCORE_GA_00 LOOKUP 621 234451
    DWH_INV_TEAM_PERIOD_FACTS FACT 562 11644174
    DWH_X_RES_AVAIL_PER_FACTS FACT 368 36853075
    DWH_LKP_VZW_BUSINESS_SPO_00 LOOKUP 137 234451
    DWH_LKP_NPD_RESOURCE_BRO_00 LOOKUP 132 234451

     

     

    We are on Oracle 12c Databse



  • 10.  Re: DWH load job - high processing time

    Posted 08-09-2017 03:22 AM

    Hi Venu,

     

    Thank you, these are look up and fact tables and the time takes is not that much based on the number of records, can you compare your database setting with twhat we have mentioned in the installation guide. Can you confirm if Oracle is hosted on virtual box or physical machine.

     

    Regards

    Suman Pramanik



  • 11.  Re: DWH load job - high processing time

    Posted 08-09-2017 08:28 AM

    THank you Suman! our Oracle is hosted on the Virtual Box. 

     

    Can you pleas be more specific on the database setting you are talking about from installation guide?



  • 12.  Re: DWH load job - high processing time

    Posted 08-09-2017 08:56 AM

    Hi Venu,

     

    We have seen inconsistent performance on database running on virtual box, so please take a look at the init.ora recomendation and the supported platform with database.


    Also ask the DBA to see where is the slowness coming into as in product we did not see any slowness as such.

     

    Regards

    Suman Pramanik