Follow

Create an incremental view

If you have a view that are based around a very large fact table, sometimes it would be ideal, to only add the incremental values.

It is a two step process. First you create your view based on a incremental table and then you pull that into a DWH or DSA table that is also running incremental load.

You will set up the script like this.

CREATE VIEW [NAV].[GL Entry Incr] AS

WITH CTE AS (
    SELECT *
    FROM [G/L Entry] AS A
    WHERE timestamp > (
        SELECT timestamp FROM [G/L Entry_INCR] AS I WHERE A.DW_Account = I.DW_Account
    )
)
SELECT
    [DW_Account],
    [Entry No.],
    [Document No.],
    [Document Type],
    [Document Type Description],
    [G/L Account No.],
    [Closing Entry],
    [Posting Date],
    [Posting Date Only],
    [Dimension Set ID],
    [Amount],
    [DW_TimeStamp]
FROM CTE

I use the With CTE part to create a statement that only gives me lines, where the timestamp is bigger, than the timestamp in the INCR table. The INCR table contains the highest timestamp, from the previous execution. Then I use what was extracted from that in the query below.

The next step is to pull the view in to a DWH, or similar and use that in another incremental load table.

KB_1.png

Remember to pull in the DW_TimeStamp field from the view and use it as the incremental selection rule.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.