Clarity

Hi folks.

    We've noticed for a couple weeks now that our Data Warehouse is occasionally missing data, specifically from the DWH_INV_ASSIGN_PERIOD_FACTS table. In the example below there are no rows for a whole project.

I can't determine a pattern yet. I do observe that our hourly Incremental Updates that usually take between 30 & 40 minutes are showing between 5 & 10 minutes as 'Complete'. We are 14.3 Fixpack 9 MSSQL 2012.

I've begun digging here in the forums. Any insight from others is greatly appreciated.

Walking this back the next thing to test is the source data. Although I don't know - I assume - the ETL leverages the DWH_INV_ASSIGN_PER_FACTS_V view in the CA PPM transactional database for data during the load. The view returns ~17K rows for this project.

Hi Robert,

The most common reason for a difference in the data between DWH source and destination as the one you describe is a server /database date-time mismatch.

Please ensure that app server date and time vs the database date and time match, preferably to the second. We have seen issues as the one above occur in cases the time difference goes roughly above 30 sec. If you see a difference, correct it and run the job again, this should resolve the issue.

Starting version 15.2 we have included an application healtcheck that verifies the Data Warehouse parameters as well, including the date/time on both servers.

Please check this out and I hope this helps. -Nika

Hi Nika. I found your post on the time mismatch this morning & checked but I did not see a visual difference (I watched the system clock on the app server turn over a minute and ran SELECT GETDATE() on the database server - they looked solid). Anything else I could check? Since our jobs server (where I believe the Pentaho ETL is running) is not our app server... precisely is it App Server vs Data Warehouse database time?

Hi Robert

Technically all the servers' times should be matching. So in example if you have BG server on a separate server, or PPM database that's separate, those should also be checked to ensure the times are matching. Let's see if that doesn't cause the issue.

You can use the command:
time

on Windows servers to compare to getdate() precisely.

If you don't see any difference, make sure you run a Full Load of Data Warehouse and not Incremental, see if that changes the way things display.

Last thing if everything looks good would be to provide us a database copy so we at Support test the DWH job and see if it populates on our server, this would help seeing the root cause and would require a Support issue.

Hope this helps -Nika

All times match & a full load fixed it.

I have a case open right now (00817246). I'm going to walk away for the evening and check this all again in the morning. Thanks!

Regarding 'full load fixed it' - we've been told that 'incremental' is broken, with no fix available.  We are on 15.2.  We had been running one full load per day, then moved to incremental every 2 hours.  Appeared to work ok for a couple weeks, so modified some management reporting processes.  Then, incremental job started failing.

We are back to one full load per day - with an 'emergency' full load on Tuesdays at noon, so that I may continue using DWH content for our weekly Management review - thankfully, avoided having to go back to portlet exports to Excel, to be graphed and copied to PPT...

No word on root cause, fix, yet.

Dale

Hi Dale,

I'd assume you hit the defect we have on 15.2:

DE37008 Load Data Warehouse - Incremental fails with ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found ORA-06512: at "PPM_DWH152.DWH_INV_TASK_PERIOD_FACTS_LOAD", line 56

We are working on a fix for it. Have you raised a case with us in Support?

Thank you -Nika

Hi Nika.

    Thanks again for your help. Things have been stable in the overnight so I'm going to call this one 'resolved' for now.

    I'm in the Community Sandbox reviewing the Data Warehouse Health Report. Thank you Aurora_Gaimon for the great Deep Dive into the New Health Report. I think I see opportunity but want to make sure I understand the Health Report first. To keep the proper discussions together I'm going to post up on Aurora's Health Report blog entry.

Capturing this here from the CA Education Administer Data Warehouse 300 course for future reference.

SELECT COUNT (1) FROM DWH_CMN_PERIOD
WHERE DW_UPDATED_DATE >
(SELECT DW_LOAD_END_DATE
     FROM DWH_CFG_AUDIT
     WHERE TABLE_NAME = 'DWH_CMN_PERIOD')