One of the most valuable uses of good business information is to help navigate a business in times of uncertainty and change. Many of our clients are doing exactly that and seeing huge benefits from having a data mart of solid, reliable and up to date information about their business performance.

A little while ago I had lunch with one of our Retail clients and they provided a great example. Their bank had been hit hard by the financial services crash and decided that over a period of months they would scale back our client’s line of credit for working capital. Rather than panicking our client went back to a new multi-dimensional analysis that they’d been using to analyze their inventory levels. They realized they could more closely manage inventory now that they had more accurate and timely seasonal sales information. Over a period of about nine months they reduced their inventory levels and working capital by almost 50% and were able to easily accommodate a smaller line of credit. In fact, they are seeing other operational benefits from not storing and managing so much inventory. Since their customer’s economic situation has also been dramatically changed they are buying different products. Having less on-hand inventory has kept our client better positioned to respond to these product buying shifts and able to move quickly into new higher-margin products rather than having to heavily discount large inventories of older products.

Having good business information delivered in a means that facilitates analysis allows our clients to see leading indicators of change well in advance. It also allows them to explore multiple possible solutions and anticipate how these solutions will impact their business. These are essential capabilities in these times of economic change!

In Part 1 of this post we described a dilemma in reporting environments where report developers need to do complex date calculations. These frequently involve relative dates – periods that are relative to the current date such as the last 13 months or the currently closed financial periods.

The most common approaches we see to these issues are:

1. Add more prompting to the report so that the user has the burden of defining what the last x months are or what financial periods are closed. In addition to being a burden this is subject to misinterpretation. For example, what if the person executing the report doesn’t know what financial periods are closed? Do they guess?
2. Put complicated date functions and logic into each report. This usually performs badly – often the logic cannot be evaluated completely within the database engine and requires moving large amounts of data between the database server and the IBM Cognos application server. It also runs the risk that different report developers will use slightly different sets of complicated date functions to achieve what they believe are the same thing. For example, one developer may calculate how many working days (not Saturday, Sunday or Holidays) have elapsed already in the current month and think that is the same thing as another developer who simply calculated the total elapsed days in the month.

The first part of this article illustrated how we can unload the heavy lifting of building date related calculations to our overnight ETL process. This part will discuss using a very cool feature of IBM Cognos 8 called Parameter Maps to expose that logic for report developers.

A parameter map is a way to expose a scalar (single item) data value. This is a subtle point but if you think about it – all queries return sets. Even if only one row is returned – it is a set. Many useful functions and calculations cannot consume a set (even if it only has one column and one row) and they must have a scalar value.

One of the great features of IBM Cognos 8 is that Parameter Maps not only allow you to retrieve these scalar values but they allow you to reference them by an key value of your choosing. You are also allowed to define all of this centrally in the Framework Manager metadata and consume these scalar values from Parameter Maps in other centrally defined Prompts and Filters.

To create a new Parameter Map from within Framework Manager simply right-click on Parameter Maps and select Create > Parameter Map from the pop-up menu. You are given a choice to create the parameter map manually but that would make it static (which could be useful for some things like your company name!) Select the option, “Base the parameter map on existing Query Items.” instead and give it a name. This will make the Parameter Map be driven by our control table which is dynamic since our ETL process updates it nightly.

Since the control table in Part 1 of this post had columns for Integer, Character, Smalldatetime and Float data types I’d probably name this one something like pmStatus_Integer and expect to create three additional parameter maps – one for each of the data types.

Select the control table that we created in Part 1 and use the Set as Key and Set as Value buttons to make the parameter name the key and the Integer Value the value.

Finish up the Create Parameter Map wizard and you’ve successfully exposed your control values as a parameter map. Next let’s look at how you might use this within Framework Manager.

The first thing that leaps to mind is to use it with Filters. Creating Filters within Framework Manager is a really great technique because they are defined centrally and can be easily reused everywhere in reports, queries, etc. This helps you define that “single version of the truth.” Here is a snapshot from Framework Manager showing a few Filters and Calculations.

Let’s look at how the Parameter Map is used in one of these calculations. Let’s look at Last 13 Months as an example.

What this Filter is doing is filtering the date dimension so that only dates that are greater than the date 13 months ago and less than our current Reporting Effective Date are returned. The parameter map pmStatus_Smalldatetime is being referenced and I’m retrieving specific values via the two keys, “13 months prior to Reporting Effective datetime” and “Reporting Effective Datetime”. Notice that I like to use very descriptive names as keys.

If you actually look at the SQL generated when this Filter is used you’ll see it is efficiently filtering the dimension table (not the fact which may have many more rows) and that all of the filtering is being done in the SQL sent to the database server.

Here is a really simple Query Studio query before I apply the Filter. Notice how many months of data are being returned.

Now I simply drag the Last 13 Months filter to my query surface and…

Voila! Now I’ve used the power of a control table in my data mart and parameter maps in Framework Manager to deliver a very easy to use and fully dynamic set of filters that can be reused over and over.

Because the underlying control table is dynamic the date 13 months prior to the effective reporting date is being updated every night automatically. So the report developer or end user doesn’t have to do this calculation.

A common challenge that we see with all of our many IBM Cognos 8 clients is dealing with date logic. The most common forms for this challenge are around relative dates:

• How can I filter this report to only show the last x months of data?
• Can we display only the closed financial periods?
• I want to display the name of the current month in the header of my report

Unfortunately the most common approach to solving these sorts of issues is to either make the person running the report respond to a prompt to determine the needed filtering values or to put very complex date logic into functions in the report. Making the user provide the information is annoying to the user (why can’t this report just know what the last x months are?) and prone to misinterpretation at runtime (e.g. I thought I was supposed to pick the invoice dates that I wanted – not the ship dates!) Putting complex date logic in the reports is difficult to maintain, poorly performing and runs the risk that each report developer may take a slightly different and inconsistent approach to delivering a solution.

Our recommended approach is to have the Extraction, Transformation and Loading (ETL) process do the heavy lifting of date calculations and then leverage a powerful IBM Cognos feature called Parameter Maps to easily expose those calculations for use in reports.

The ETL process is crucial because it runs on a regular time interval (often on a nightly basis) so it can easily deliver calculations that involve relative dates – dates that are relative to now. We create a SQL table in our data mart that is very simple like this:

CREATE TABLE [dbo].[pcon_ctl_status](
    [status_key] [int] IDENTITY(1,1) NOT NULL,
    [status_parameter_name] [varchar](50) NOT NULL,
    [status_parameter_description] [varchar](100) NULL,
    [status_int_value] [int] NULL,
    [status_char_value] [varchar](100) NULL,
    [status_smalldatetime_value] [smalldatetime] NULL,
    [status_float_value] [float] NULL,
CONSTRAINT [pcon_ctl_status_PK] PRIMARY KEY CLUSTERED
(
    [status_key] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

The idea is that we populate this table with one row for every useful scalar value that we can think of – some examples include:

• The last time our ETL process was executed (so we know how current our data is)
• The date 13 months before the current date (if we do a lot of rolling 13 month reporting.)
• Any sort of calculated time period factors (what percentage of the working days in the current month have already elapsed?)

So the data in the resulting table looks something like this:

Every night the ETL process runs and updates the values in the status_xxx_value columns of this table. These are the four columns to the far right in the screen shot above. The most common way for the ETL process to do this is via a simple SQL script that gets executed at the end of the ETL process.

The screen shot above shows some of the update statements executing from within the IBM Cognos DecisionStream ETL tool but we also commonly do this from within Microsoft SQL Server SSIS jobs.

The next post in this series will explain how we now use this data inside our IBM Cognos 8 reporting environment.

Often as consultants we advise our clients that the best way to get data quality is by ensuring that the people who are closest to a transaction be the ones that enter the data about that transaction. It sounds trite, but we see many cases where financial department staff are trying to fill in the missing details that should have been captured by the sales force that originally put the transaction together.

Here at Perkins Consulting we aren’t a transactional organization – we’re a services organization. So we want the people who are delivering our services to have an opportunity to put the truth that they experience out there for the benefit of our clients, our partners and the world. Hence, a blog with content created by our consultants!

We hope that this blog provides highly focused and relevant information for our clients. If not, I’d like to hear suggestions on how we can make it more focused and relevant.We also want to show some of the work that we’re proud of and some of the knowledge that we’ve put together in the 15 years that Perkins Consulting has been around.