Configuring Incremental Loading from a Source Database

You can embed a variable in a Birst Connect query.

For example, when using Birst Connect queries to extract from the source database, if the database tables have been designed to include a date column that records when the record was inserted or last updated, such as a system_modified_date datetime column, then you can include that column in your query. It will be available in the resulting Birst data source and can be used in a query-based Birst variable. The variable can be used in the WHERE clause of the Birst Connect query to restrict the query to new/updated rows only.

To configure incremental loading from a source database

In this example scenario, the initial load of data uses a Birst Connect query for the order_headers data source, as follows:
SELECT o.order_id, o.order_date, o.system_modified_date FROM orders
When you create the data model in Birst, create a dimension hierarchy named Order and target the system_modified_date on the order_headers data source to the Order hierarchy.
Create a variable in Birst named LAST_UPDATE_DATE of type Query and Single Value, with the following Birst logical query, as in the example below:

SELECT [Order.system_modified_date] FROM [ALL] Order BY [Order.system_modified_date] DESC
For the purposes of incremental data loads, add the following WHERE clause to your Birst Connect query for the order_headers data source:

SELECT orders.order_id, orders.order_date, orders.system_modified_date FROM orders
WHERE orders.system_modified_date >=V{LAST_UPDATE_DATE}