Baking Custom Event Tables Into Your Reporting Environment

How many times have you needed to add Specialized Tracking or Processing to your Business Intelligence environment? If my last 20 years as an architect are any indication, it’s almost every time. I need to know when a business or processing condition is reached; I need to know when reports are run, or exports are generated; I need to indicate that an email needs to be created; I need validation processing to determine if an anomaly has taken place, etc. Further, I want to save the event and have something come back later to pick it up. A great way to augment your Operational Data Store, Data Warehouse, or Reporting Database is to include it in the design an Event Table. Store this information in an asynchronous manner—let me continue my baseline processing—and do something with this data later.

So, why would I want to put a transactional table in my reporting environment? Ralph Kimball, a father of modern data warehousing, loves the concept of processing metadata. I want to retain information specific to my processing activities that I can use to determine the health of my environment when specific conditions exist or when certain activities have occurred (e.g. calls to reporting procedures or executed exports). Once built into the fabric of your processing framework, this provides a lightweight solution to augment the processing information from your environment.

The event table I typically use is an Entity Attribute Value (EAV) table I can put anything into. Data is stored in rows vs typical storing in columns. This type of table is seen more in transactional systems, when you want to store data points that change over time but don’t want to have to change your data model and code. To accomplish this, you specify an event type ID/Key in addition to the normal key and value columns to identify what is being stored.

So, what can I insert into my Event table? Basically, almost anything. Here’s an example list of Event Types items that I have used in the past. If there are more, you add a new event type, and then add the stored procedure call to add the new event.

Sample Event Types

Data Model

Here is a data model representation of a sample Event subject area. This may vary, but the ones I’ve implemented have resembled what you see below. Tables included: a Source Table to indicate which subsystem, process area, data source, or focus area it is associated with. An Event Type Table to identify what type of event is being stored, and an Event table with specific event-related data.

Maintaining Data

I always recommend using stored procedures to manage table interactions across the board. Using stored procedures gives you the ability to minimize nested code and place lightweight hooks anywhere, such as your ETL packages, in other stored procedures, in job steps, etc. You still need to determine the criteria if an event is to be logged. But once you do, use a standard call that can be consistently leveraged throughout the environment.

Example stored procedure call to add an event:

Use of Event Data

You can do a variety of things once the data is stored. After all, data is data. One option is to pull reports from the event table (e.g. How may exports were generated for a given process area over time, or which validation condition-types were encountered?). Another possibility is to have other processes pick up data and perform additional processing in an asynchronous manner. This may also be used for such things as compliance auditing or for email notifications from other products, especially if database mail (or SMTP mail from ETL packages) is not an option. It’s all a question of: what do you need to do to better support your environment?

So, what do you have in your environment? If your organization needs help adding Specialized Tracking or Processing to your Business Intelligence environment or with any issue around custom event tables, please don’t hesitate to reach out to us. We’d love to help you out.