In some cases, it may be desirable to append data to a Datamart table instead of dropping the entire table and re-creating it on each run of the Schedule Service.

In order to configure this, the Dataset Query must have a date parameter specified in it’s WHERE clause. The parameter will be defined in the dataset but is effectively a “dummy” parameter that will be replaced with today’s date adjusted for the configured intervals.

Append Date Field Identifies the Date in the query result used to identify any out-of-date data to be deleted from the Data Mart
Append Date Param Identifies the dummy Parameter which is used to pass in today’s date
Regular Schedule Number of Days to cache Identifies the number of days to be updated on a “Regular” update (e.g. a weekday update)
Major Schedule Number of Days to cache Identifies the number of days to be updated on a “Major” update (e.g. a weekend update)
Next Major Schedule Date Identifies the next Major Schedule Date
Interval between Major Schedules (Days) The number of days that exist between Major Scheduled Updates (set to 1 if all updates should update the same number of days back)

Example:

A dataset with the following query:

SELECT SALE_DATE, STORE_NAME, SALES_TOTAL
FROM SALES_DATA
WHERE SALE_DATE >= :START_DATE

Could be configured as follows:

The Scheduler would then run on two intervals:

  • A “Regular” Schedule which would run between 6pm and 8pm every night that would delete the last 90 days of data from the LGX_NIGHTLY_SALES_DM table before appending it with the data from the SALES_DATA table where the SALE_DATE is greater than or equal to the current date and time less 90 days
  • A “Major” Schedule running on a Friday Night between 6pm and 8pm that would delete the last 730 days of data from the LGX_NIGHTLY_SALES_DM table before appending it with the data from the SALES_DATA table where the SALE_DATE is greater than or equal to the current date and time less 730 days

So, in this example each scheduler run would execute the following SQL Statement:

DELETE FROM LGX_NIGHTLY_SALES_DM 
WHERE SALE_DATE >= DATEADD(dd, [INTERVAL] * -1, GETDATE())
GO;
INSERT INTO LGX_NIGHTLY_SALES_DM
SELECT SALE_DATE, STORE_NAME, SALES_TOTAL
FROM SALES_DATA
WHERE SALE_DATE>=DATEADD(dd, [INTERVAL] * -1, GETDATE())

Where [INTERVAL] would be determined based on whether the runtime fell into the Regular or Major Schedule.

Hint

In this configuration, the dataset does not create the original datamarted table. It will only work with an existing one. It also doesn’t add or remove fields. All of this must be done using SQL.

A shortcut to create the table would be to set up the dataset for normal caching (with none of the append fields set up). Execute it once and the table will create. Then set up up append fields.

Revision: 3
Last modified: Oct 02, 2019

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment