DWH load job - high processing time

[Lukas Jezek]

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

[Suman Pramanik]

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 

[Lukas Jezek]

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

[Nika Hadzhikidi]

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

[Suman Pramanik]

Thanks Nika_Hadzhikidi for your response.

 

Regards

Suman Pramanik 

[Nika Hadzhikidi]

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

[venvzw]

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

[Suman Pramanik]

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

[venvzw]

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

[Suman Pramanik]

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

[venvzw]

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?

[Suman Pramanik]

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