Refresh Date

View previous topic View next topic Go down

Refresh Date

Post  avasile on Thu Jun 28, 2012 7:03 am

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

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  BoxesAndLines on Thu Jun 28, 2012 8:55 am

Yes, your fact and dimensions should have audit columns.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Refresh Date

Post  avasile on Thu Jun 28, 2012 9:01 am

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.

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  TheNJDevil on Thu Jun 28, 2012 9:53 am

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.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Refresh Date

Post  avasile on Thu Jun 28, 2012 9:56 am

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

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  TheNJDevil on Thu Jun 28, 2012 10:16 am

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.

TheNJDevil

Posts : 68
Join date : 2011-03-01

View user profile

Back to top Go down

Re: Refresh Date

Post  avasile on Thu Jun 28, 2012 10:20 am

Excellent.
Thank you for your help everyone.
Adrian

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  BoxesAndLines on Fri Jun 29, 2012 9:26 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Refresh Date

Post  avasile on Fri Jun 29, 2012 9:31 am

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.

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  LAndrews on Fri Jun 29, 2012 11:06 am

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.



LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Refresh Date

Post  BoxesAndLines on Fri Jun 29, 2012 2:30 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Refresh Date

Post  avasile on Fri Jun 29, 2012 2:35 pm

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.

avasile

Posts : 10
Join date : 2012-06-28

View user profile

Back to top Go down

Re: Refresh Date

Post  LAndrews on Fri Jun 29, 2012 3:04 pm

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.


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: Refresh Date

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum