View Only
  • 1.  Load Data warehouse - Incremental Schedule

    Posted Oct 08, 2015 10:07 AM

    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




  • 2.  Re: Load Data warehouse - Incremental Schedule
    Best Answer

    Posted Oct 08, 2015 10:47 AM

    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



    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.



    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?

  • 3.  Re: Load Data warehouse - Incremental Schedule

    Broadcom Employee
    Posted Oct 09, 2015 01:35 PM

    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 :
    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

  • 4.  Re: Load Data warehouse - Incremental Schedule

    Posted Oct 11, 2015 09:58 PM

    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.