Friday, July 22, 2011

Working with Dynamic Filtering Part 2

If we recall, dynamic filtering is a Cognos function allowing us to limit a general target report with contextual information from the drill source. Filter the target by the source.  Like this, drilling from an Analysis intersection to a details report.

In the previous segment we went through how dynamic filtering works in Cognos 8 and 10. In this one I'll details some issues I had with the way that this works. When I initially looked into this I was working in Cognos 8.2, at that time there was one show stopper (#2) in analysis studio and a few others in Power Play Web, but they've corrected some issues as of 8.4.1 fix pack 3 and Cognos 10. Here's some of the issues I discovered with this latest attempt. 

1.       Expanded vs Drilled
(In analysis studio) Drilling down into a hierarchy doesn’t bring that entire hierarchy along. The drill appears to only pass what’s currently in the work area intersection. This behavior is consistent in Cognos 8 and 10. Analysis Studio is a hierarchy exploration and analysis tool, but anytime you drill from an intersection in analysis studio only the current 'work area' is passed! Information about the place in the hierarchy of the current intersection is important too!

For example, drilling through on Camping Gear / Jan 2007 should be passing some information about the parents of these summary levels. Really we are saying "Give me all the revenue for products in the Camping Gear line that are Cooking Equipment sold in the Year 2007, first quarter, Jan 2007". I can drill into those dims like this. But, you can see the passed parameters only include the current intersection not the hierarchy.

Expected parameters:
Product Line = Camping Equipment
Product Type = Cooking Gear
Year = 2007
Quarter = Q1
Month = Jan

Result parameters:
Product Type = Cooking Gear
Year = 2007/Jan

Now, Cognos' Example database, gosales is really an ideal world. In go sales we don't need to know the parents because 'Cooking Gear' doesn't exist under any other grouping than Camping Equipment. But in a scenario where we had an alternate hierarchy or different dimension which also included the Cooking Gear level in a different context, our filter would fail if it only said "Product Type = 'Cooking Gear'". Like above.

But, if I expand the dimension so that the parent information is included in the work area this context is passed along with the intersection. So this is one way to get all the context. But I don't like the idea having to training users to be aware of this.

Result Parameters:
Product Line = Camping Equipment
Product Type = Cooking Gear
Year = 2007/Jan

A potential solution to this case could be to create aggregate keys at every level of the drill source such that from any one source key you could tell where and in what hierarchy the reference came from. In the above example the Cooking Gear key would be the Product Line Key + the Product Type key "CampingEquiptmentCookingGear".. This would add some complexity to the model as the drill target report would need to anticipate this.

2. Context Filters

In Cognos 8.4.1 (fp3) Context filters result in an 'OR' appended to the SQL rather than an 'AND'. This is bad! If I pull a sales region into my previous analysis in the context filter section, say 'United States' My analysis numbers will be sliced accordingly. BUT, if I drill through in this state the resulting SQL will include 'United States' as an OR condition not an AND. This means I would bring back the set of rows from the 'United States' region OR of product type 'Camping Gear'. This is not what I want. Cognos 10 appears to have corrected this issue, by default items placed into the Context Filters area will generate an AND condition in the resultant SQL logic. This is a big improvement in my opinion.

3. Scope Setting
The Scop setting allows you to select a dimensional item and require that present in the 'work area' before the drill definition is avaliable. Here I've set the scope to the products dimension.

But the Scope Setting doesn’t really work the way I’d like it to. There is a setting in the drill through property that is supposed to be used to limit from where a drill through can initiate, the idea being that you wouldn’t want someone to pull in all years and cash and drill through to transactions; But the setting only allows you to set the scope for 1 hierarchy. I'd like to be able to write more complex rules. Cognos seems to want you to create 1 scope relationship for one dimension for one drill through definition. I suppose more complex rules can be defined by multiple targets.

On the bright side, you only need to include the scoped dim somewhere (in an axis, context filter, nested dimension, etc) to enable drill through. This could be useful if we had a dimension that we wanted to use as a limiter such that we’d be able to cut down the output set. (say, setting the scope to the lowest level of the product dim.)The other issue with not being able to set scope is the potential for someone to drill through to details at a very high level and resulting in a very large output set.  For example, a high intersection like Revenue for all of 2011 would bring up all the transactions for 2011, a very large list indeed.

4. Filter Display
This one is incredibly annoying. If anyone comes up with a better way that'd be great. 

When you dynamically generate prompts you might want to display what those prompts were on the resulting report right? (I usually set a reporting standard saying we shouldn’t display a report output with incomplete information about what that output set includes) Well Cognos doesn’t make this simple.  This task ends up adding a lot of overhead and potential maintenance when the source objects change to these drill objects.

First, back to our previous explanation of what's happening on drill through (See part 1 for a more complete description). When a dynamic drill is requested two things happen, a set of values is used to modify the executed sql (a) and a placeholder parameter  to be used in report rendering (b) is created named after the from data element in the source.  The drill target report’s definition is meshed with (a) bringing back the filtered set of data. The (b) parameter can be grabed by the reporting engine using the displayParamValue() function IF (b) is REFERENCED in the drill target it is appended to the target report’s SQL. Similar to other optional paramaters, they remain undefined if they are not used in the report definition. So in short, we need to setup the drill target to expect and use (b) so that we can display "Filter Name" in the header.

Well what's all that mean? Lets walk through an example. A drill through at the following intersection will generate the following: 

1. The destination report will have a paramter defined ?Products1? with a display and use value of "TrailChef Canteen". *Note also that the paramater is products1 rather than just products. I'll talk about this in the next IssueThis is because the dimension in the OLAP source is named products as well, so the level name 'products' gets a 1 appended to it.

So, by dynamic drill, the report will be filtered for Product 'TrailChef Canteen' and display those rows. But it will not, by default, display those filter conditions in the header. In fact, we can’t just use the paramvalue() Cognos report function to display the parameter at this point. (I assume because it’s not referenced in the report xml it is discarded in the same way unsupplied optional parameters are) To do this we create an optional filter in the query that anticipates #1 above. Defined as"?Products1? = ?Products1?". And then we can use our report functions to display the parameter like normal.

2. This filter can be displayed on the report page with a report expression like this:

if (ParamDisplayValue('Products1') <> '') then ('Product: ' + ParamDisplayValue('Products1') + ', ' + ParamValue('Products1'))

3. So simple right? Now do that for every possible intersection in your cube and you'll have an optional parameter for every possible parameter. This is where the maintenance headaches start, you end up with a report that's query items,optional filters and report expressions must be updated for each cube change.

5. Dimensions named the same as a Level
If a Level Name in an OLAP object is named the same as an overall dimension name, a # will be appended to the level name. This appears to be an attempt to create uniqueness within the cube. The issue is that a report developer would need to know that though his cube contains a dimension ‘Products’ with a level named ‘Products’ and his drill target report needs to include a query item and filter ‘Products1’ to match this at drill time.

That's all I've noticed so far. By and large, Cognos does not make drill through simple for your developers and pretty much unheard of for your technically proficient business users. So maybe have your modeler set it up. If you're unsure of your dimensional structure I would hold off on implementing drill through until you're sure it's stable given that it's so much work to setup and maintain.

1 comment:

  1. many business events particularly in specialized medical and academic fields charge sustainable registration fees it is hard to put a price on peer-to-peer networking. event marketing and conference biography