settingsLogin | Registersettings
Show Menu

How to model the Source System Audit Logging in DV

0 votes
Hi Guys,

I've have a question about modelling the Audit Logs that are stored in our Source Application. The source application is used to store Tickets. Most of the information of a Ticket is stored in the table inquiry. Next to the inquiry table there is an inquiry_audit table.

The audit table stores changes that were made to an inquiry. The structure of the table:

audit_id | action_date          | inquiry_id | action | old_value | new_value
1           | 2015-11-25 10:13 | 23            | status | new          | closed
1           | 2015-11-25 10:13 | 23            | prio    | high          | low

The idea was to store these records as a satellite of the h_ticket. For this satellite having an load_end_date
is a bit diffecult. Is it ok to leave this column out of the satellite or should we model the audit log differently? Maybe look into multi active satellites?

I'm curious about your opinions.


asked Jan 3, 2016 in Modelling by bas_vdl (140 points)

1 Answer

0 votes
I think I would model this using a satellite on a link L_TicketAudit containing both the inquiry and the action as Hubs. End-dating would occur when a subsequent matching action is made against the same inquiry.

This is assuming that action is a system level business key, if it's not then I suppose you would need to question whether this information provides any business value.

An issue with this approach as well is that it appears that status and priority as the given examples could potentially be Business Keys themselves. I assume these tickets would be searched for by priority and/or status. If this is the case then the satellite I propose is capturing Business Keys within satellite data which is bad DV practice. I would expect that this data should be reflected in your satellite(s) for h_ticket (Depending upon load frequency) but this information could be useful for data quality checks.
answered Jan 7, 2016 by liamwhales (220 points)
Scalefree Scalefree

Upcoming Trainings

  • July 01 to 03 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • August 05 to 07 - Berlin (German) Data Vault 2.0 Boot Camp and Certification

  • September 9 to 13 - Hanover WWDVC EU

  • September 16 to 18 - Brussels (English) Data Vault 2.0 Boot Camp and Certification

  • October 21 to 23 - Hanover (German) Data Vault 2.0 Boot Camp and Certification

  • November 18 to 20 - Amsterdam (English) Data Vault 2.0 Boot Camp and Certification

  • December 02 to 04 - Dusseldorf (German) Data Vault 2.0 Boot Camp and Certification

  • December 16 to 18 - Zurich (English) Data Vault 2.0 Boot Camp and Certification

  • Contact us for inhouse training engagements.
    Visual Data Vault: Logical Modelling for Data Vault - Free Download!
    Scalefree is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 United States License.
    Permissions beyond the scope of this license are available in our license. | DWH Wiki | Recent questions RSS feed | Imprint | Provided by Michael Olschimke