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:
- 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?
- 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.
Subscribe