Skip navigation
All People > Nika_Hadzhikidi > Nika's Blog > 2017 > June
2017

Hello fellow community members

 

 

Here a quick tip on what you can do to reset your Data Warehouse Timeslices. For example, if you see that some of the slice data is either not in Data Warehouse, or some Time periods are not getting in, or any issue that seems to be caused by the slices, you can try the following:

 

 

Prior to applying the solution:

Ensure the slices are configured correctly (DWH slices for different periods should start and end on the same day for the same period type). In example:

all Weekly DWH slices will start on Jan 2 2017 which is Monday (Weekly start date should also be Monday)

all Monthly slices start on 1/1/2016

etc.

 

  1. Connect to CA PPM UI - Administration - System Options
  2. In Data Warehouse settings in Entity for Fiscal Periods note your entity name
  3. Now hit Delete
  4. Save
  5. Run Timeslicing until completion
  6. Run Update Reports Tables job
  7. Reenter the entity name in System Options (same as you noted in step 2)
  8. Run Load Data Warehouse - Full Load

(Note both Timeslicing and Load Data Warehouse job may take significantly longer so make sure you are aware of this and planning around this accordingly).

Recently we have had few issues with DWH_INV_HIERARCHY and similar tables not getting updated when the Load Data Warehouse job - Full Load runs.

 

This can happen on both Oracle and MSSQL

 

Symptoms

Load Data Warehouse job - Full completes successfully, but one or more tables have no data in there.

When we check the corresponding PPM source view, it contains data.

 

I.e. DWH_INV_HIERARCHY does not contain data, we have to check the corresponding view (DWH_INV_HIERARCHY_V) on PPM and we see the data is there.

 

Sometimes, on MSSQL it could be caused by DWH_CMN_PERIOD not updating, and in that case, more tables such as DWH_INV_SUMMARY_FACTS could be impacted.

 

Cause:

Data Warehouse job requires the app server clock and the db clock to be the same, if possible, to the second. If there is a time difference between the server that is higher than 30 sec, that could lead to data discrepancies. We have documented this as a requirement and the newest checkinstall would check for any possible discrepancies before continuing with the upgrade.

 

Solution:

  1. Correct the clock on the app or db server to match the other server to the second or as close as possible.
  2. Run Load Data Warehouse job - Full

 

This should resolve your issue and populate your tables.

 

Documentation reference:

https://docops.ca.com/ca-ppm/14-4/en/reference/ca-ppm-jobs-reference#CAPPMJobsReference-LoadDataWarehouse 

Load Data Warehouse

Server Time, Timezone, Date, and Time

Fellow Community members,

 


This is instructions to deal with a known issue when a custom dynamic lookup that is not having a DWH friendly query is enabled.
Data Warehouse is having strict constraints to ensure the data is always unique and the job will fail if any of the queries attempt to insert duplicates.

 


Error Message:

[CA Clarity][Oracle JDBC Driver][Oracle] ORA-01427: single-row subquery returns more than one row


Workaround:
1. Check the exact error message in Home - Job - Log or in bg-dwh.log to see which exact lookup fails.
2. Take the lookup table name, for example here is a part of an error: ERROR: THERE WERE ERRORS DURING LOOKUP JOB EXECUTION FOR THE TABLE - DWH_LKP_CUS_BUS_VAL_00 Lookup table is DWH_LKP_ CUS_BUS_VAL _00
3. Remove the DWH_LKP_ and the _00 if present. You need the lookup name only.
4. Modify the query below to include the corrected value, example for CUS_BUS_VAL (note the second line from the bottom):

 

select object_name as Object_id, column_name as Attribute_code, lookup_type as Lookup_id
from ( select clt.id, clov.id, oca.id,   
clt.lookup_type, clt.source,   clov.sql_text_id, clov.object_code,  
 oca.object_name, oca.internal_name, oca.column_name, oca.data_type,   
oca.partition_code, oca.is_multivalued, oca.default_value, oca.derived_object_code,oca.internal_name
from cmn_lookup_types clt
join cmn_list_of_values clov on clt.lookup_type = clov.lookup_type_code
and clov.is_system = 0
join odf_custom_attributes oca on oca.lookup_type = clt.lookup_type
and clt.lookup_type like 'CUS_BUS_VAL%' )
group by object_name, column_name, lookup_type order by object_name, lookup_type


It has to include the % at the end of the lookup type name as sometimes not the full lookup name is used in the table name.

 

5. The query will give you the exact object name and attribute that has to be unchecked from Data Warehouse. Connect to Administration - Objects - <object> - Attributes - Select it, Uncheck Include in Data Warehouse field, Save
6. Run Load Data Warehouse - Full Load and then Incremental Load. This should fix the issue for both Full and Incremental

 

Do not reenable the lookup back until the lookup query is corrected. Alternatively we suggest you modify the DWH lookup view and the DWH object view to make sure only unique records are returned. Once you do this, on Test environment and ensure it works you can promote the changes to Production.

 

Hope this helps -Nika