Refresh Date

Hello everyone.
We just started our first DW project and I was wondering if I can ask for some advice - basically, we are reloading our data daily (delete old records and add the records again due top numbers changing) and I need a way to let our users when was data last refreshed. Now, our staging tables all have date stamps with when data was loaded, deleted, etc but nothing in our facts table.
Is it common to have a "date loaded" field in your facts table so that the end user will know when a record was last time refreshed? If not, what is the correct way to let end user now when data was last time refreshed.
Thank you in advance.
Adrian

Yes, your fact and dimensions should have audit columns.

Thank you for your reply.
So it is then common to have a DateLoaded in your Facts table. The end will see that too - my original thought was that the end user should not see that.

It is common for there to be a Audit dimension. A record in this table will have statistical information for each table's nightly load. That would include a start and end timestamp for the load process. In your case, it would be faster and much easier to just add a lastupdated timestamp field to your fact table. But, best practice would be to add a AuditDim table and just have the AuditKey in your fact.

That is a great idea - I did not think of that. It should not be that hard for us to create the dimension. Do you happen to know what would be some of the common fields for the Audit Dimension?
Thank you !
Adrian

Our Audit dimension is based on what was found as part of Microsoft Data Warehouse Toolkit 2nd ed from Kimball. http://www.kimballgroup.com/html/booksMDWTtools.html There are some downloads with plenty of examples. But basically, table name, Load start and end timestamp, insert count, update count, error count. Any piece of information you can get about your load process is a candidate to be stored in the table.

Excellent.
Thank you for your help everyone.
Adrian

The audit dimension only addresses the fact table. You still need audit columns (that the business does not see) in all of your facts and dimensions.

I am sorry - can you be more specific?
I created an Audit Dimension that will have a key in our Facts table. Then in our Audit dimension I created fields such as LoadStart and End Date, number or records, etc.
I want the end user to be able to see these records.

Audit dimension is typically used at the record level (I use it on all my tables ... Dimensions, facts and bridges).

In my case, the audit dimension is not a user facing dimension .... its used by the support teams to debug issues when they arise.

In order to display to end-users the status of the data warehouse, the ETL process maintains some audit and control tables that provide summarized information for end-users.

This type of information is part of your metadata strategy - the amount and format of information is driven by the requirements of your users and your operational team.

Where do you store the load start date for a dimension? The audit dimension stores that information for a fact (as it is related to it). Dimensions, on the other hand, require audit columns as well.

Why would a dimension require audit columns?
Here is my DimAudit:

• AuditKey, PackageID, ExecutionInstanceID, BatchDate, TableName, LoadStartDate, LoadEndDate, DatarefreshStartDate, DataRefreshEndDate, RecordsProcesses
  

and here is my Facts table :

• Factkey, some other keys, AuditKey, Measure1, Measure2
  

That's it.

I create an audit dimension record for my dimension loads as well.

Each dimension record then has 2 audit key values (Create_Audit_Key, Update_Audit_Key).

It has tended to be extremely valuable when an end-user questions the data..... particularly a type-1 or type-2 change .... the audit dimension provides the history required to determine when records were created, updated etc, and by what ETL process.

Another advantage of the audit_dimension instead of audit columns - I can increase the information captured for all dimensions simply by adding the column/logic to the audit_dimension.