I have recently been playing around with creating a centralised logging mechanism to be used across all components of a BI solution. On some projects you can have many different modular and separately developed components that make up the solution as a whole and each one of these components require logging. This brings with it the headache of designing Error and Audit tables for each one of the components and also puts you in a position where these Audit and Error tables should be identical if you want to easily do reporting off the auditing and error data for the solution as a whole. If you do not have a centralised logging mechanism then this will cause a lot of headache as you will end up with tables of different structures to access when the time comes to do the reporting.
- Component - Stores the text values for each component.
- LogLevel - Stores the log level value that links to the LogStore table.
- LogStore - Contains the actual message logged as well as foreign keys to data in the other tables.
- SystemUser - Stores the user name of the system users logging to the LogStore table.
- Tag - Stores the text value of each tag used when logging a message to the LogStore.
- LogView - The view that is used to query the LogStore which links all the text from all the tables together with the message logged.
- uspPopulateLogLevel - Populate the LogLevel table with the default log levels after deployment.
- uspDebug - Log a debug message.
- uspError - Log an error message.
- uspInfo - Log an information message.
- uspVerbose - Log a verbose message.
- uspWarning - Log a warning message.
Component – This is a varchar(255) field that is the name of the BI component, for example ETL or Reconciliation. This value is stored in the Component table with a foreign key reference in the LogStore table and is used to tie all the different messages for a particular component together.
Tag – This is a varchar(255) field and is used to define tags for the messages of a component. This value is stored in the Tag table with a foreing key reference in the LogStore table. Using tags is important to ensure that you log messages can be reported on later since you will use this to filter on when doing reporting. Tags can be things like “Audit” or maybe a sub step inside an audit routine would be tagged as “AuditSubStep”. A tag is a way of giving a text description to further describe logging for a component.
Text – This is a varchar(max) field. The reason for Varchar(max) is to ensure that in the off chance that we need a very long string the solution will cater for it. This value is stored in the Text field of the LogStore table. It should contain the detailed message that you want to log to the LogStore. You can also delimit the message so that it can later be parsed when you want to do reporting on the messages. For example you could put a pipe “|” between each line in the text when you construct your message. When it then comes to reporting you will be able to separate each of those line easily and use them in reporting.In order to use the logging a user simply needs to call the relevant stored procedure and pass in the values for it, for example if you want to log an error message it would look as follows:
exec log.uspError 'DemoComponent', 'ValidationTables', 'The table "TestTable" does not exist.'When this is executed the result from querying the Log.LogView is shown below.
The user of the system does not need to concern themselves with anything other than passing values to the stored proc and the rest of the data that is logged is handled by the stored procedures that form part of the solution.
By using this logging format it makes logging messages easier across the entire BI solution and also ensures that it is done in a consistent manner, allowing reporting to be done from one central place.
The script to create these objects can be downloaded here or can be found at GitHub.