Clarity

Expand all | Collapse all

Data Warehouse Completes, but is Incomplete

Jump to Best Answer
  • 1.  Data Warehouse Completes, but is Incomplete

    Posted 08-15-2017 10:01 AM

    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.



  • 2.  Re: Data Warehouse Completes, but is Incomplete

    Posted 08-15-2017 10:39 AM

    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.



  • 3.  Re: Data Warehouse Completes, but is Incomplete

    Posted 08-15-2017 04:21 PM

    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



  • 4.  Re: Data Warehouse Completes, but is Incomplete

    Posted 08-15-2017 04:41 PM

    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?



  • 5.  Re: Data Warehouse Completes, but is Incomplete
    Best Answer

    Posted 08-15-2017 04:50 PM

    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



  • 6.  Re: Data Warehouse Completes, but is Incomplete

    Posted 08-15-2017 06:01 PM

    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!



  • 7.  Re: Data Warehouse Completes, but is Incomplete

    Posted 10-27-2017 11:37 AM

    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



  • 8.  Re: Data Warehouse Completes, but is Incomplete

    Posted 10-27-2017 11:42 AM

    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



  • 9.  Re: Data Warehouse Completes, but is Incomplete

    Posted 08-16-2017 12:12 PM

    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.



  • 10.  Re: Data Warehouse Completes, but is Incomplete

    Posted 10-27-2017 09:37 AM

    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')