By Rick Ryan, Envisn, Inc.
Our recently published eBook, Expert Guide to Cognos Audit Data focuses on sharing our knowledge on this subject while showing readers many of the possible uses for audit data. Based on feedback we received and our own ruminations, we took on the challenge of how we could take everything we know about Cognos audit data and use it in a way that maximizes what can be done with it using IBM Cognos’ latest technology.
Our goal was not to just tweak things, but to do a full transformation of our UniVisn product and use it to show what’s possible when you use a totally new approach.
It Starts With the Data
The first step was to create dynamically modeled relational data (DMR) in the form of a FM Model for Cognos audit data. This proved to be more of a challenge than we expected. Not to just create one, but to one that really ties it all together and makes it possible to use the data in highly flexible, creative ways.
- We had to normalize the data which is nothing more than putting it all on a common plane so it can be used interchangeably for whatever you want to do. As we talked about in the eBook, there’s some work involved in doing this since the audit data was designed for a different purpose than what we want to use it for here.
- It also involved aggregating this normalized data into a single Fact Table from the many basic audit tables provided by Cognos. Done correctly, this allows you to join the audit data with multiple other dimensions making it far more useful than in its original state.
Both of these steps are required for creating dynamically modeled relational data (DMR).
- Another key part of the data process was to add managed reference data with standardized definitions for content, users, time and relative time. By standardizing definitions here we can combine it with the normalized data from the audit tables. Some examples of reference data are object type, packages, run types, object status, etc.
- We wanted this to work a global level while providing rich, specialized dimensional data at the lowest level of detail and integrate all of the audit data into a single FM model.
- Finally, all of this has been put into a fully automated process that enables the audit data to be updated on whatever interval is appropriate. This could be daily or even more frequently if needed.
The image on the left below shows the FM Model itself; its audit measures and its dimensions. The image on the right shows the exploded view of the dimensions for Object, Time, Relative Time and User. As you can see, for both Objects and Users we have two dimensions of each; a hierarchical dimension and a flat dimension. This makes sense because there are times when you’ll want to look at the data in different ways. For example, you may want to see users as part of the organizational structure and other times you may just want to use them arranged alphabetically.
Do you really need that many dimensions? Well, yes! This rich dimensionality is what makes it possible to answer virtually any question about the audit data and what’s being used in your Cognos environment. And there are a number of real world examples where data from a given dimension can be very useful. Whether it’s usage by user, by package, by dispatcher or object type.
Dynamic cubes can be added to the mix as well here for those that find them a preferred alternative to using an FM Model. They are designed to take advantage of DMR data as its presented here.
Creating a FM Model for Cognos audit data makes it possible to use it in an infinite number of ways. Most of the work involves getting the right data correctly aligned for use in a model.