Data warehouse debugging


While I was investigating the data warehouse grooming internals, I found a neat debugging feature that can be turned on to check the internals of DW stored procedures. In the dbo.StandardDataSet table, for every dataset, there’s a DebugLevel column. By default its value is 0 or no debugging info at all. The higher the value the more is logged accordingly with the stored procedure logic. Every stored procedure defines what needs to be logged and at what debug level. The debugging info are stored in the dbo.DebugMessage table.

image

Beware of some caveats:

  1. debugging as always has a cost, it is not designed to be always on. Be careful on production systems.
  2. the DebugMessage table needs to be manually groomed, once you get the debugging info you need
  3. there’s a bug in the EventProcessStaging stored procedure, if you turn on debugging for events the event insertion process will be blocked. The EventProcessStaging stored procedure tries to insert NULL messages into dbo.DebugMessage, there’s a constrain that prevents it. So to debug the Event dataset you need to remove the constrain from the DebugMessage table or patch the EventProcessStaging stored procedure. Obviously neither of the two is supported in a production environment. But if you need it badly I’d suggest to remove the constrain.

image

– Daniele

This posting is provided "AS IS" with no warranties, and confers no rights.

Technorati Tags: ,,
Advertisements

, ,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: