Friday, July 15, 2011

Working with Dynamic Filtering

So one question I usually get asked by users when I build an analysis object is 'How do I drill through to the details?'. As much as we want to talk users out of looking at the details when we're building them aggregates, the reporting task of detail dumps invariably comes up. It's also a likely a sign of an immature business process, but that's a whole different post.

In relational scenarios this is straight forward, a drill target mapping can be used to jump from any context to any other, map the parameters and there you go, from a summary level, select the details.

In IBM Cognos 7 this was not too difficult, you could setup an IMR details report, link it as a drill target and the dynamic filtering would filter that result set based on the context of your selection at the time of drill through. (based on business key). In Cognos 8 this process has become a hectic jumble of xml, sql generation, report variables and other black magic. Don't be fooled by the numbering, Cognos 7 and 8 are totally different pieces of software and handle the drill through process completely differently. This contextual filtering in Cognos 8 and Cognos 10 is handled by a setting you may have seen in drill through definitions called 'dynamic filtering'.

Cognos 8 (and Cognos 10) Dynamic Drill through, in theory, allows a user to setup a very generic drill target report and supply filter conditions to that report at the time it is requested. The idea is to allow dynamic filtering without having to setup hundreds of drill target mappings for complex cubes. It's still a pretty cumbersome process to get setup and maintain, but I'll try to detail how this handshake works. This process is outlined in this IBM Knowledge base article:

Here's how it works, we're trying to take a source and modify the destination code to filter by the source location:

Drill through is the process of combining some information from the source with some information from the target and generating a result set. So when this process happens what is grabbed from where? Take a look here at the drill through details page. (This is output Cognos generates to describe what it is passing on drill through. I have numbered a set of interesting items.)

Regular Dimensions
1.       The string “Country” comes from the Level Name property in the OLAP object. In the drill destination a report parameter will be created called “Country” that can be used to display the passed value. (More on this later)
2, 3.       The Display/Use Value is misleading here. In the drill destination the display AND use values for the parameter called “Country” will be the listed “Mexico”. (rather than something useful like say, the source key in the use value?)
4.       This entire fragment represents what is used by the dispatcher to generate dynamic filters and append them to the drill destination SQL. This value “Country” comes from the Level Name in the OLAP object. Cognos will examine the definition of the drill destination looking for a query item named "Country". This will be used as the left side of the dynamic condition.
5.   The Source or Label Value is used for the right side of the dynamic condition. If a label is not defined the source value is used. In this case "Mexico". The items from 4 and 5 are combine to create the where condition. Country = Mexico.
6.       1-5 are repeated for each unique member intersection touched by the intersection, nested elements expanded levels, etc are appended to the SQL as extra and conditions. *NOTE for a value to be present in the dynamic filter it must be present in the drill target. If you notice in step 4 the drill target is searched for a Country definition. If it's not present it won't be included in the filter set.

Time Dimensions – Cognos 8.4
Time dimensions act a bit differently.
1.       The string Month comes from the level. Same as regular dimension.
2,3.       Same as regular dimension
4.       A time dimension will read the drill destination looking for a query item named “Month” in this case (The Level name). The value of that query item will be used as the left side of the dynamic condition.
5.       A time dimension is generated as a string of the format (‘START’,’END’). This will be used as the right side of the dynamic condition. The destination sql will be appended with a combination of 3 and 4, in this case looking something like [Month] = (‘5/1/2005’,’5/31/2005’)

Time Dimensions – Cognos 10
In Cognos 10 this changes a bit.
1.       The report parameter is still generated from the level name. In the case of drilling through time on months a parameter called ‘Months’ will be created in the destination report. (See section 4 about working with these parameters.) The thing to note here is that 1 and 4 are inconsistent.
2, 3.       Same as regular dimension
4.       In Cognos 10 the drill destination report will be searched for query item named after the Dimension Name.  In this case ‘Date of Record’ that value will be used as the left side of the condition
5.       In Cognos 10 the generated fragment will contain a start and end date. These will modify the sql to become Date of Record >= START and Date of Record < END. In this case [Date of Record] >= ‘5/1/2005’ and [Date of Record] < ‘5/31/2005’. Depending on the level of the time dim at the time of the drill, a different set of to and from dates are created.

So when we drill through we can see this in the captured sql:

where "Product13"."PRODUCT_NAME"=N'TrailChef Water Bag' and "Order_header10"."ORDER_DATE">=to_date('2005-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') and "Order_header10"."ORDER_DATE"<( to_date('2005-05-31 00:00:00', 'YYYY-MM-DD HH24:MI:SS') + INTERVAL '1' DAY ) and "Country9"."Country"='Mexico'
And we get a result set of orders for the month of May 2005 for the TrailChef Water Bag from Mexico. Dang. Cool.

 Next time I'll talk about some issues I discovered in working with these 'features.'

No comments:

Post a Comment