The idea behind real time loading a data warehouse is to propagate data directly after an event has happened in an underlying source database. Let us think of an invoice database. In most circumstances, the invoice database is loaded with records that represents invoices that are sent. During the night, a set of records in collected and they are sent as a file to a data warehouse. <\/p>\n\n\n\n
An alternative is to propagate a message from the source database directly after a new record is inserted. This message is received by the data warehouse. It can then be subsequently inserted into the data warehouse.<\/p>\n\n\n\n
In Oracle, we have a trigger mechanism to implement this idea. <\/p>\n\n\n\n
The trigger is fired, once the source table is changed. This trigger looks like:<\/p>\n\n\n\n
create or replace trigger INVOER.T$EMPLOYEESUITVOER\n after insert or update or delete on INVOER.EMPLOYEES\n for each row\n declare\n V_FLAG VARCHAR(1);\n V_EMPLOYEE_ID NUMBER(6);\n begin\n if inserting then\n V_EMPLOYEE_ID := :new.EMPLOYEE_ID;\n V_FLAG := 'I';\n end if;\n if updating then\n V_EMPLOYEE_ID := :new.EMPLOYEE_ID;\n V_FLAG := 'U';\n end if;\n if deleting then\n V_EMPLOYEE_ID := :old.EMPLOYEE_ID;\n V_FLAG := 'D';\n end if;\n\n insert into UITVOER.J$EMPLOYEES\n (\n JRN_SUBSCRIBER,\n JRN_CONSUMED,\n JRN_FLAG,\n JRN_DATE,\n EMPLOYEE_ID\n )\n select USER,\n '0',\n V_FLAG,\n sysdate,\n V_EMPLOYEE_ID\n from dual\n ;\nend; <\/pre>\n\n\n\nThe idea behind this trigger is that each insert, delete or update generates a new record in a remote table in the staging area of a data warehouse. Here, a table (UITVOER.J$EMPLOYEES) is continuously updated with each update representing a change in the source database.<\/p>\n\n\n\n
In this case, the table in the staging area only contains the primary key of the record that is changed. With only a minor modification, one could include all fields from the source table. A similar result can be achieved with a view where the UITVOER.J$EMPLOYEES is joined with the original table. <\/p>\n\n\n\n
See below:<\/p>\n\n\n\n
DROP VIEW UITVOER.JV$DEMPLOYEES;\nCREATE VIEW UITVOER.JV$DEMPLOYEES\nAS select decode(TARG.ROWID, null, 'D', 'I') AS JRN_FLAG,\n JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER,\n JRN.JRN_DATE AS JRN_DATE,\n JRN.EMPLOYEE_ID AS EMPLOYEE_ID\n ,TARG.FIRST_NAME AS FIRST_NAME,\n TARG.LAST_NAME AS LAST_NAME,\n TARG.EMAIL AS EMAIL,\n TARG.PHONE_NUMBER AS PHONE_NUMBER,\n TARG.HIRE_DATE AS HIRE_DATE,\n TARG.JOB_ID AS JOB_ID,\n TARG.SALARY AS SALARY,\n TARG.COMMISSION_PCT AS COMMISSION_PCT,\n TARG.MANAGER_ID AS MANAGER_ID,\n TARG.DEPARTMENT_ID AS DEPARTMENT_ID\nfrom (\n select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER,\n L.EMPLOYEE_ID AS EMPLOYEE_ID,\n L.JRN_CONSUMED as JRN_CONSUMED,\n L.JRN_DATE AS JRN_DATE\n from UITVOER.J$EMPLOYEES L\n ) JRN\nleft join INVOER.EMPLOYEES TARG \nON JRN.EMPLOYEE_ID = TARG.EMPLOYEE_ID\nand JRN.JRN_CONSUMED = 0;<\/pre>\n\n\n\nFinally, this can be used to update the data warehouse. <\/p>\n","protected":false},"excerpt":{"rendered":"
The idea behind real time loading a data warehouse is to propagate data directly after an event has happened in an underlying source database. Let us think of an invoice database. In most circumstances, the invoice database is loaded with records that represents invoices that are sent. During the night, a set of records in […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3196","post","type-post","status-publish","format-standard","hentry","category-allgemein"],"_links":{"self":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3196","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3196"}],"version-history":[{"count":0,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=\/wp\/v2\/posts\/3196\/revisions"}],"wp:attachment":[{"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3196"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/archief.van-maanen.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}