Clarity

Expand all | Collapse all

[ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

  • 1.  [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Aug 24, 2016 03:42 PM

    The view mentioned in the title will occasionally disappear from our database. It looks like the Incremental DWH job is dropping it and sometimes rebuilds it, but sometimes not. Is this something that others have experienced?



  • 2.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Aug 24, 2016 06:21 PM

    Hi Kenny

     

    We did face similar behavior before, let's see if that's something that is concerning or not.  Are you on MSSQL or Oracle? If the view does not exist, does the job complete or does it fail? Please confirm.

    If the job is failing then please raise an issue with CA Support so we look into it further.

     

    Thank you -Nika



  • 3.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Aug 25, 2016 10:25 AM

    We are on MSSQL. The job is running to Success.

     

    The view exists prior the process running. Then the process runs and seems to drop it. Sometimes it rebuilds the view, but other times the view no longer exists once the process has completed. Does that fit a pattern you've seen?



  • 4.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

     
    Posted Sep 06, 2016 05:47 PM

    Hi Nika_Hadzhikidi - Anything further for qkenny? Thanks!



  • 5.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Sep 19, 2016 09:04 AM

    I've just noticed that we've been having this issue in our test instance (14.3.0 patch 7 on SQL server) recently.  Of the last 20 daily runs it has failed with the below error 15 times.  There's no pattern to the successful runs other than 3 of the 5 happened on Mondays.

     

    It's not happening on our production instance (also 14.3.0.7 on SQL).

     

    Query if atleast 1 record exists - org.pentaho.di.core.exception.KettleDatabaseException:
    An error occurred executing SQL:
    SELECT CASE WHEN (SELECT DISTINCT 1 FROM DWH_X_INV_PER_FACTS_V src) = 1 THEN 1 ELSE 0 END AS record_exists
    FROM   DWH_CFG_SETTINGS

     

    --SELECT
    -- CASE WHEN
    -- (0+0+0+0+0 > 0)
    -- THEN 1 ELSE 0 END RECORD_EXISTS
    --FROM DWH_CFG_SETTINGS
    [CA Clarity][SQLServer JDBC Driver][SQLServer]Invalid object name 'dwh_x_inv_plan_per_facts_mv'.



  • 6.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Sep 29, 2016 01:44 PM

    Hi Kenny/Owen,

     

    This looks like it might be an issue. Could you please raise a Support case and provide your dataset for testing? I’ll want to look into this in depth. Some of the views are dropped and recreated but here it looks like it was not recreated when it was expected to happen.

     

    Thank you -Nika



  • 7.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Sep 29, 2016 02:03 PM

    Nika - I've actually done that already. Case number and Title: 

    00503556: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

     

    They advised of some tracing we might turn on to try and catch this happening but it wasn't very clear where or how to turn that tracing on. I replied asking for a bit more detail but haven't heard back yet. If you can help with that, that would be great. The view continues to drop a few times per week.



  • 8.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Sep 29, 2016 03:36 PM

    We've experienced this as well. Case number 00469777. 14.3 fixpack 7 MSSQL.



  • 9.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Oct 04, 2016 11:42 AM

     

    I observe it happens after the turn of the month. September and October for us. Once is an observation. Twice is a coincidence. Three times is a pattern. I'll update this thread next month.



  • 10.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Oct 04, 2016 03:11 PM

    Thank you Robert! This is very helpful. I'll try testing this way. So far we've been unsuccessful in our attempts to reproduce the issue. If anyone on this thread who experiences the issue can provide their data backup(with the problem), please raise a ticket and refer to this so I can get it asap.

     

    Thanks -Nika



  • 11.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Oct 27, 2016 09:33 AM

    Hi Robert, Nika,

     

    do you have any information whether this issue has been already fixed for 14.4.?

     

    Kind regards

    Georg



  • 12.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Oct 27, 2016 09:46 AM

    Hi Georg

     

    No, unfortunately this issue was not yet resolved in 14.4. The problem is that we cannot seem to be able to reproduce it in house in order to log it. We are looking to get a database copy with the issue in order to debug it. Once we identify and log the issue we will request the fix in 14.4 patch.

     

    I hope that helps -Nika



  • 13.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Nov 21, 2016 05:42 PM

    Hello everyone,

     

    FYI - After working with some of you on Support cases in house, I managed to get this isolated and logged it as new defect:

    CLRT-81565 MSSQL - DWH_X_INV_PLAN_PER_FACTS_MV view missing after Timeslice rollover, fails Incremental Load with Invalid object name 'dwh_x_inv_plan_per_facts_mv'

    Workaround for this is to recreate the missing view with the DDL from same version environment.

     

    I will update you with the progress on this issue, it is currently pending on Sustaining Engineering review.

     

    Thank you to everyone on this thread! -Nika



  • 14.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Posted Jun 30, 2017 07:45 AM

    Hi

    We have started seeing this problem in TEST and the Load dwh job has failed for the last 5 days.

    We have also seen this happen in Production, although the Load dwh job has completed successfully since.

     

    In TEST (using SQL Server Mgt Studio)  I can see the view but when I try to select from it I get the following error.

        Msg 208, Level 16, State 1, Procedure DWH_X_INV_PER_FACTS_V, Line 123
        Invalid object name 'dwh_x_inv_plan_per_facts_mv'.
        Msg 4413, Level 16, State 1, Line 115
        Could not use view or function 'ppm_test_dwh.ppm_dwh.DWH_X_INV_PER_FACTS_V' because of binding errors.

    I still get this error even after running a Full load in TEST which completed successfully, 

     

    I do not see this error when trying to read the view in production

     

    Sue Tearle



  • 15.  Re: [ppm_dwh].[DWH_X_INV_PLAN_PER_FACTS_MV] view dropping

    Broadcom Employee
    Posted Jun 30, 2017 01:00 PM
      |   view attached

    Hi Sue,

     

    This issue CLRT-81565/ DE30602 has now been resolved in 14.3.0 patch 10 and 15.2. You didn't specify your version but to workaround the issue you basically have to recreate the missing view. Here is the code, attached, please ask your DBA to create it, then run Load Data Warehouse - Full and you should not face the error anymore.

     

    The bug happens when the job recreates this view, to resolve the issue for future you should consider upgrading to the releases above.

     

    Hope this helps -Nika

    Attachment(s)

    zip
    view_ddl.sql.zip   2 KB 1 version