Load Data warehouse - Incremental Schedule

[Karthik-Reddy]

Hello Champs,

 

We have scheduled the Load Data warehouse job to run every day, but we see a requirement where it needs to be scheduled may be every hour but incremental load is fine as we just need to see some time sheet actual's.

 

Is there anyone who have already scheduled the incremental load of "Load Data warehouse" job to run more frequently in the normal business hours? Are there are any drawbacks or performance hits?

 

Thanks in advance

 

Regards,

Karthik

[Georg Schmid]

Hey Karthik,

 

depending to our customer, we chose different approaches.

Two examples:

Not so often:

a) Nightly full load

b) Incremental load in the morning and in the afternoon

 

Frequently:

a) Nightly full load

b) Incremental load hourly

 

We could not see any performance problems, the more interesting question is whether a run during working hours might lead to inconsistent data or at least to the situation where it is not clear whether your changes are already in the DWH or not.

 

@CA:

Do I have any chance as a user to check at which time the DWH had been updated while looking in the Ad hoc views?

Is the data "frozen" when the ETL jobs start or will the rows tickle in the DWH in the order they are handled by the ETL scripts? In other words: Is there any transaction concept to make the changed to the DWH atomic?

[Nika Hadzhikidi]

Hi Georg,

 

Yes it's possible for you to  see from UI Ad Hoc View when DWH was updated.

1.    Please create a domain based on one of the following CA PPM DWH BEAN Views :
DWH_CFG_SETTINGS
This is the table that is used for the System settings for DWH but also for the DWH job timestamp.

2.    Then create an Ad Hoc View and ensure the Date Format includes also the hours/minutes/seconds for DWH_DIM_START_DATE
This should help: please let me know how it goes.

 

The DWH job will go by the ETL script, table by table and it will take the data from the PPM views by last_updated_date. Once the DWH job kicks off, that timestamp is used for all data. So if you, for example, made a change to Investment record after the DW started, it wouldn't get picked up until the next load because investment.last_updated_date > dwh_cfg_settings.dwh_dim_start_date

 

Hope this helps,

 

Kind Regards

Nika Hadzhikidi
CA Technologies
Principal Support Engineer

[Karthik-Reddy]

Thanks Georg for providing the details of the schedule, This makes me think that we are good to go for an incremental load every one hour limiting to user base and usage we have today.

 

Thanks Nika for providing the information on how to how to know the DWH Last Load information.