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?
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
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?
Hi Nika_Hadzhikidi - Anything further for qkenny? Thanks!
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 22.214.171.124 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_existsFROM 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'.
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.
Nika - I've actually done that already. Case number and Title:
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.
We've experienced this as well. Case number 00469777. 14.3 fixpack 7 MSSQL.
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.
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.
Hi Robert, Nika,
do you have any information whether this issue has been already fixed for 14.4.?
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
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
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
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