Clarity

Hello Clarity Users,

Currently we have below schedule for Load Data Warehouse.

We have full load every night and it takes around  5 hours. We have three times Incremental Mode in a day. We were planning to stop Full load everyday and run it once a week. But we noticed that Incremental Load sometime misses the data. Hence we have to go with  Full load again.

Our Full Load completes at 5.30 am. We have first incremental scheduled at 10 am. Surprisingly it also takes around 100 mins. and then remaining two takes 35 mins.

I was wondering how other Clarity Admin managing this or what is there experience about Load Data Warehouse.

Kind Regards,

Abhijit

Our Full Data warehouse job takes around 5mins, and is run hourly on the hour.

How big is your environment?

We are slowly moving critical reports to report directly from the database instead of the warehouse, as the hourly wait can still be frustrating for some staff.  You may want consider doing something similar.

I would second roland.parrotte's feedback. Our job is taking only 5 min. At my previous company when we first got onto Jaspersoft it was hovering at over 30mins, where we made some changes with SaaS hosting and brought that down to 12min.

What I can't tell from your post is whether you are hosted or SaaS. Either way, 5 hours is a pretty fundamental problem and I would advise you to raise a ticket with CA Support to have them assist in investigating.

Guidance that I have received from CA Support in the past was to reduce the time on the full load to only daily. If you go to a weekly schedule, I am not sure what impact that will have to other reporting that you do.

The other part of what Roland's response about cutting the report over to the database, especially for high frequency reports such as the Project Storyboard, has immediate benefit with the Clarity users who don't have to wait for the updates to flow through to the reporting engine. I would highly recommend driving this, particularly if you status report weekly.

Thanks Roland and Christopher! We are on-premise with 700 Active Projects and 5000 active resources . I am surprised to see how your Full Load get finished below 15 mins. It is really great achievement and we are missing something as our Incremental also do not finish below 15 mins. How big is your environment. We have built our custom reports on database only. Currently our two critical reports are Project Status Detail and Missing Time and these are Data Warehouse based. We have daily Full Load in the night and incremental during the day.

Hi Abhijei

Couple of things I can pitch in, run the below queries and see how long its taking 

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

From the output you will find which tables are taking longer. Then we can go down the path to see what sizing etc. 

Can you please mention what version of PPM you are running now

Regards

Suman Pramanik 

Thanks Suman. We are on 15.3. DWH_RES_PERIOD_FACTS is taking longer time. I am not able to attach XLS.

What is the database, is it SQL or oracle. I suggest you open a case and we will have some of our expert look into it and suggest a way forward 

Hi Abhijei, the number of projects and resources you have on your deployment is of medium size. My previous install was larger and loaded quicker (but through SaaS). I think there are a number of strategies, particularly around infrastructure sizing for your report server that could address this. I will leave you in Suman's hands on this one.

Hi Abhijei, we are a SaaS environment with 4,200 Investments and 7,700 Resources.  Our full DWH reload takes about 13 minutes and is scheduled to run twice daily.  Our incremental DWH reload takes about 4 minutes and runs every 30 minutes.

We have not had any performance impacts with this frequency, even with one of the two full reloads running in the middle of the business day.  We deliberately scheduled that for 12:30 PM US Eastern Time, as that is the time zone where the bulk of our user community is located, so most of them are on lunch break while it's running.

One additional advantage of the full reload -- the incremental reloads will sometimes fail due to duplicate key violations or other similar errors.  CA's recommended fix was to run a full reload, which always works -- once we do that, the incremental runs start completing successfully again.

Alan Brobst

 Thanks for response. SaaS environment is highly optimized. It will interesting to see how on-premise are doing with Medium set of data and SQL Server as database.