An audit trail allows your business to track any changes made to your Kepion Apps and data. In this article, we'll show you how to set up an audit trail for your fact tables (F_).
Tip: Learn how to create an audit trail for your Forms in our Review Form Changes article.
Set up Audit Trail
All your current data is contained in fact tables (F_), but you can have a record of updated and/or deleted data in fact history tables (FH_). The FH_ table provides the information you need to start following an audit trail.
To set up an audit trail:
1. Select All Models.
2. Locate your target Model(s) and select its corresponding checkbox in the Audit column.
An FH_ table will be generated for any Model with the Audit option enabled. You can find the fact history table in SQL Server Management Studio (SSMS) by expanding the Tables node of your target Application.
F_ vs. FH_ tables
To understand how to track changes, we first need to understand the structural difference between F_ and FH_ tables. For context, in the example below, we have added, updated, and then deleted a record from a Model.
F_ table
FH_table
F_ and FH_ tables share the same structure, except the latter has an additional column: InitiatedActivityID. This column holds the unique values (assigned in numerical order) for sets of data that have been updated or deleted and posted.
ActivityID, on the other hand, is the unique value (assigned in numerical order) for sets of data that have been newly added or updated and posted.
Here's an example to help distinguish ActivityID from InitiatedActivityID. When you post a new or updated data to the F_ table, a new ActivityID would be assigned for all of those records, for example, 55. If you delete a record with an existing 38 ActivityID as part of that same post, that record will be moved to the FH_ table with an InitiatedActivityID of 55.
Note: Posting multiple data changes at the same time will result in the changed records sharing an ActivityID and InitiatedActivityID.
Example Scenarios
Let's see how adding, updating, and deleting values affect F_ and FH_ tables.
Add
In our Kepion Form, we've added a value of 50 to the empty cell and posted the change.
Querying the F_ table in SQL Server Management Studio shows the record and its ActivityID.
Adding a record to a blank cell will not update the FH_ table as you are not changing or deleting an existing data.
Update
We then change the value from 50 to 100 and post the change.
The F_ table does not show the previous record. It displays the updated value, which has a new ActivityID.
The FH_ table, however, shows the original record.
Delete
Let's delete the value and post the change.
The F_table does not have the original record or the updated record. It reflects the current data present in your Model.
The FH_ table has tracked the original record and the deleted record.
Generate Audit Report
1. Open SSMS and connect to your SQL Server.
2. Select New Query. Copy and paste the following into the query window:
USE [<Application Database>]
GO
SELECT
DA.[MemberLabel] [Account]
,SA.[MemberLabel] [Scenario]
,F.[TimeID]
,F.[Value]
,F.[Comment]
,CA.ActedOn [Created On]
,CU.FullName [Created By]
,MA.ActedOn [Modified On]
,MU.FullName [Modified By]
FROM
(
SELECT
[AccountID]
,[TimeID]
,[ScenarioID]
,[Value]
,[Comment]
,[ActivityID]
,-1 [InitiatedActivityID]
FROM [dbo].[F_Expense_CoreMG_Writeback]
UNION ALL
SELECT
[AccountID]
,[TimeID]
,[ScenarioID]
,[Value]
,[Comment]
,[ActivityID]
,[InitiatedActivityID]
FROM [dbo].[FH_Expense_CoreMG_Writeback]
) F
LEFT OUTER JOIN [dbo].[Activities] CA
ON F.[ActivityID] = CA.[ActivityID]
LEFT OUTER JOIN [dbo].[Users] CU
ON CA.[UserID] = CU.[UserID]
LEFT OUTER JOIN [dbo].[Activities] MA
ON F.[InitiatedActivityID] = MA.[ActivityID]
LEFT OUTER JOIN [dbo].[Users] MU
ON MA.[UserID] = MU.[UserID]
LEFT OUTER JOIN [dbo].[D_Account] DA
ON F.[AccountID] = DA.[MemberId]
LEFT OUTER JOIN [dbo].[D_Scenario] SA
ON F.[ScenarioID] = SA.[MemberId]
GO
3. Replace the following:
- <Application Database> with the name of your own Application database.
- References to F_ and FH tables (e.g., [dbo].[FH_Expense_CoreMG_Writeback]) with the name of your own fact and fact history tables.
- Column names with those in your own tables.
4. Select Execute.
You should have a full audit report in the Results tab:
Note: The NULL values in the Modified On and Modified By columns come from the current values in your F_table. They haven't been updated or deleted, hence the NULL values in those columns.
Track a Single Change
1. In SSMS, select New Query, enter a SELECT * FROM statement to see your target FH_ table, and click Execute.
2. Locate the target record, and take note of its ActivityID.
3. Select New Query once more.
4. Enter a JOIN statement that pulls the user responsible for the change and the exact date and time when the change occurred from dbo.Activities and dbo.Users.
SELECT A.[ActivityID], U.[FullName], A.[ActedOn]
FROM [dbo].[Activities] A
INNER JOIN [dbo].[Users] U
ON A.[UserID] = U.[UserID]
WHERE A.[ActivityID] = <ActivityID of record>
If we wanted to find out who deleted the 100 value shown in the FH_ table in the previous section and when the change was posted, we could use the following query:
SELECT A.[ActivityID], U.[FullName], A.[ActedOn]
FROM [dbo].[Activities] A
INNER JOIN [dbo].[Users] U
ON A.[UserID] = U.[UserID]
WHERE A.[ActivityID] = 40
5. Click Execute.
You should see something like this:
- FullName shows you who changed the record.
- ActedOn shows you when the change occurred.