When to use Detail filters on an OLAP cube

[parishilton]

That got your attention  :D

I'm quite happy with the fundamental law of never using detail filters with OLAP sources, but I'd like to know why. So that I can explain it to others, in particular the boss , who sees that sometimes it'd be far easier to use a detail filter, and therefore quicker to build a report.

My 'theory' is that if you apply a detail filter, the query passed to the Cube engine (TM1 in my case) , will ignore the filter, bring back all the data and then do the filtering locally, which will cause a massive performance hit which is clearly BAD.  I also believe that the results however will be correct.

I've spent quite a lot of time googling for an in depth discussion of what happens behind the scenes, or anecdotal evidence of why not to use filters. Sadly I've not found any official documentation (perhaps it's just so well known that nobody bothers to write it down).

The best 'argument' I've seen comes from the much respected PaulM

If it's a proper OLAP cube you'll need to use set functions and a slicer to achieve your goal. My own rule of thumb is "Never ever ever, on the pain of pain, are you ever allowed to use detail filters on an OLAP cube". I ensure that my students or workers follow that rule when building reports.

[CognosPaul]

I was getting all set for a nice long lecture until I saw that first line. Well trolled, ma'am. You're gonna get lectured anyway.

There is a grand total of one scenario in which I allow filters, but the filters have to be written correctly and the author has to understand the MDX being generated. It's also worth noting that the new MDX generator (the DQM) may alter the situation a bit, but I highly doubt it.

The basic claim:
Detail filters are unpredictable and tend to encourage bad behavior.

A simple question:
What is the sales growth, from 2006 to 2007, of all products whose total profit margin for the years 2004, 2005, 2006 exceed 60%?

Required materials:
Cognos report studio
Sales and Marketing sample cube

Detail filters can be separated into two groups, filters on measures and filters on hierarchies. Filters on measures tend to be things like [Cube].[Sales] > 1000, while filters on hierarchies look like [Cube].[Dimension].[Hier] in ([Member1],[Member2]) or [Cube].[Dimension].[Hier].[Level].[Attribute] in ('string 1','string 2'). Filters on hierarchies are worthless, you could just as easily put the required sets into the slicer or a data item. The measure filter will effect every innermost node.

In the simple question any attempts to use detail filters would cause inaccurate results to be returned. A filter of years in (2006,2007) may interfere with the expression dealing with profit margin in 2004-2006. A filter of [Profit Margin]>0.6 would effect the 2010,2011 set. Additionally, attempting to deal with filters the MDX generator will attempt to hijack nodes that reference the filtered level intersect(time.year.members,{2006,2007}) (that case is worthless, it would be better to simply use the {2006,2007}). Nodes that don't reference that level will not be effected.

Take a simplified example from the simple question. Rows are filter(products,[Profit Margin]>0.6), Columns are [cube].[time].[time].[years] and an automatic summary of [Year], filter is years in (2006, 2007). Profit Margin exists as a data item in the query, and is the default measure. I expect to receive 12 rows back, but I'm getting 13 rows. Aloe Relief has a profit margin in 2006 of 40.7% and in 2007 of 63.3%, the automatic summary is showing 48.3%. So why am I seeing that value? The reason is that, if you took off the detail filter, you would see that in 2004 and 2005 the profit margins were 63.1% and 63.3%. The aggregated total for all of the year is 60.2%, which is over the threshold.

Lets tweak this a bit. I want to see all the years, but all products with Profit Margin > 0.6.
Rows are [Cube].[Products].[Products].[Product], Columns are [Cube].[Time].[Time].[Years] with automatic summary. Filter is [Profit Margin] >0.6. Default measure is [Profit Margin].

When we run it, suddenly we have 16 products being returned! Shouldn't that be 13? But wait, what are these blank cells in the crosstab? Quick! Tell me what's going on.

I'll wait.

Well, no I won't.

The detail filter is suppressing all the values below 60%. The aggregated total for years is not including the suppressed values. That means any product who ever had any year with profit margin greater than 60% will be included.

By adding a detail filter, the report author is fooling himself into thinking that the report will return correct results. Once it is discovered that the reports actually aren't returning the values that should be returned, heads will roll.

Now that we have an example of incorrect results, lets go a bit further and talk about local processing. Many people forget that cubes are not relational databases. String functions won't work. Date functions won't work. Many things won't work. But people use them anyway.

Take the following filter:
substring(caption([Product]),1,3) = 'Sun'

Product level on rows, years with summary in columns and profit margin as default measure.

When we run the report we get 5 rows returned as expected. But the automatic summary isn't working. It has -- for each row. Let's take a look at the MDX if we can see what's going on...

First the MDX without the filter:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m1], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m1] AS 'AGGREGATE([Time]..[Year 2].MEMBERS, [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m1]' SELECT UNION([Time]..[Year 2].MEMBERS, {([Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)])}, ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), [Products]..[Product 4].MEMBERS DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]

A bit messy but understandable. The summary(Year) expression isn't exactly how I would do it, but it's reasonable when you realize it has to work for as many different scenarios as possible.

Now let's look at the MDX with the filter, it can't be that bad, right?

But wait! There is no option to view MDX, we can only view Cognos SQL. I wonder why... Let's take a look.

with
Query1_0_tab_oqpTabStream4 as
    (select
           Query1_0_tab_oqpTabStream.Time0key  as  Time0key,
           Query1_0_tab_oqpTabStream.Time1  as  Time1,
           Query1_0_tab_oqpTabStream.Yearkey  as  Yearkey,
           Query1_0_tab_oqpTabStream.Year0  as  Year0,
           Query1_0_tab_oqpTabStream.Products0key  as  Products0key,
           Query1_0_tab_oqpTabStream.Products1  as  Products1,
           Query1_0_tab_oqpTabStream."Product linekey"  as  Product_linekey,
           Query1_0_tab_oqpTabStream."Product line0"  as  Product_line0,
           Query1_0_tab_oqpTabStream."Product typekey"  as  Product_typekey,
           Query1_0_tab_oqpTabStream."Product type0"  as  Product_type0,
           Query1_0_tab_oqpTabStream.Productkey  as  Productkey,
           Query1_0_tab_oqpTabStream.Product0  as  Product0,
           Query1_0_tab_oqpTabStream."Profit Margin"  as  Profit_Margin,
           Query1_0_tab_oqpTabStream.generatedName1  as  generatedName1
     from
           TABLE(_ROWSET("Query1.0_tab_oqpTabStream")) Query1_0_tab_oqpTabStream
    )
select
       Query1_0_tab_oqpTabStream4.Time0key  as  Time0key,
       Query1_0_tab_oqpTabStream4.Time1  as  Time1,
       Query1_0_tab_oqpTabStream4.Yearkey  as  Yearkey,
       Query1_0_tab_oqpTabStream4.Year0  as  Year0,
       Query1_0_tab_oqpTabStream4.Products0key  as  Products0key,
       Query1_0_tab_oqpTabStream4.Products1  as  Products1,
       Query1_0_tab_oqpTabStream4.Product_linekey  as  Product_linekey,
       Query1_0_tab_oqpTabStream4.Product_line0  as  Product_line0,
       Query1_0_tab_oqpTabStream4.Product_typekey  as  Product_typekey,
       Query1_0_tab_oqpTabStream4.Product_type0  as  Product_type0,
       Query1_0_tab_oqpTabStream4.Productkey  as  Productkey,
       Query1_0_tab_oqpTabStream4.Product0  as  Product0,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin1,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin2,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin3,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin4,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin5,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin6,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin7,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin8,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin9,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin10,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin11,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin12,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin13,
       XMIN(Query1_0_tab_oqpTabStream4.Profit_Margin  for Query1_0_tab_oqpTabStream4.Time0key,Query1_0_tab_oqpTabStream4.Time1
,Query1_0_tab_oqpTabStream4.Yearkey,Query1_0_tab_oqpTabStream4.Year0
,Query1_0_tab_oqpTabStream4.Products0key,Query1_0_tab_oqpTabStream4.Products1
,Query1_0_tab_oqpTabStream4.Product_linekey,Query1_0_tab_oqpTabStream4.Product_line0
,Query1_0_tab_oqpTabStream4.Product_typekey,Query1_0_tab_oqpTabStream4.Product_type0
,Query1_0_tab_oqpTabStream4.Productkey,Query1_0_tab_oqpTabStream4.Product0 )  as  Profit_Margin14
from
       Query1_0_tab_oqpTabStream4
where
       (substring(Query1_0_tab_oqpTabStream4.generatedName1 from 1 for 3) = 'Sun')
group by
       Query1_0_tab_oqpTabStream4.Time0key,
       Query1_0_tab_oqpTabStream4.Time1,
       Query1_0_tab_oqpTabStream4.Yearkey,
       Query1_0_tab_oqpTabStream4.Year0,
       Query1_0_tab_oqpTabStream4.Products0key,
       Query1_0_tab_oqpTabStream4.Products1,
       Query1_0_tab_oqpTabStream4.Product_linekey,
       Query1_0_tab_oqpTabStream4.Product_line0,
       Query1_0_tab_oqpTabStream4.Product_typekey,
       Query1_0_tab_oqpTabStream4.Product_type0,
       Query1_0_tab_oqpTabStream4.Productkey,
       Query1_0_tab_oqpTabStream4.Product0

There is no way that anyone could ever pay me enough to analyze this. Please note, though, that what I just said is a lie, but I my price is fairly hefty and if anyone wants to give me money for saying "don't use detail filters" I'm very open to the idea.

Now, having said all of this against detail filters, lets talk about the one and only time you can use a detail filter.

Drill downs and ups are poorly handled. If you have a static set of {2006, 2007} and allow users to drill down and back up, the drill up will return all of the years in the level. It's almost as if the drill action replaces the contents of that data item with either children(member) (for a drill down) or siblings(parent(member)) (for a drill up). Well, drill down reports are the only place I will okay the use of detail filters. Grudgingly and with great gnashing of teeth. Personally I have several issues with drill downs and tend to avoid using them in my reports.

Wow, I sound like a grumpy old man.
Paris Hilton? Seriously?

Edit: cleaned the Cognos SQL a bit so you wouldn't have to scroll to read this.

[parishilton]

I am so glad I managed to tease that reply out of you Paul  ;)


A superbly written piece on why not to use detail filters on an olap cube.

The previous sentence was deliberately written for search engines to find!

I need to spend a lot of time fully getting to grips with what you've written , but your closing remarks interest me. Not because I particularly want to use detail filters, but if you are saying you avoid drill downs, are you not avoiding one of the big selling points of OLAP? I'm guessing I've missed your point somewhat, but I'm curious to know what your alternative is.

[charon]

Holy mother of BI, that's what i call a detailed answer.
Tyvm Paul, even i was able to understand that  ;D

btw...your white writing -> lulz

[CognosPaul]

I don't consider drilling down to be such a huge selling point of olap. Drill behavior can be replicated fairly easil - even on a standard relational db.

On the other hand, being able to do incredibly complex queries on top of billions of records with a run time of under a second ranks somewhat higher. Try to answer the simple question with a single select statement. Off the top of m head, I can think of three possible ways, but they all involve some sort of subselect. If your fact tables run in the millions the run time could take ages. 5 seconds, maybe even more.

Besides the reason I listed before, about drilldowns replacing the expression, there are several other reasons I dislike them.

If you have drills enabled, it is difficult (not impossible, mind you) to prevent users from drilling too far in either direction.
Drills refresh the page, but don't remember parameters. If you have a JavaScript switch to let users switch between a graph or a table, good luck finding a way to remember the state prior to the drill action (workarounds: cookies, hidden reports).
Drills don't populate parameters with the selected mun. This makes it is difficult to conditionally show or hide objects on the page.

There may be more, those are just the ones I've had to deal with in the past month.

[parishilton]

I don't consider drilling down to be such a huge selling point of olap. Drill behavior can be replicated fairly easil - even on a standard relational db.

On the other hand, being able to do incredibly complex queries on top of billions of records with a run time of under a second ranks somewhat higher. Try to answer the simple question with a single select statement. Off the top of m head, I can think of three possible ways, but they all involve some sort of subselect. If your fact tables run in the millions the run time could take ages. 5 seconds, maybe even more.

Besides the reason I listed before, about drilldowns replacing the expression, there are several other reasons I dislike them.

If you have drills enabled, it is difficult (not impossible, mind you) to prevent users from drilling too far in either direction.
Drills refresh the page, but don't remember parameters. If you have a JavaScript switch to let users switch between a graph or a table, good luck finding a way to remember the state prior to the drill action (workarounds: cookies, hidden reports).
Drills don't populate parameters with the selected mun. This makes it is difficult to conditionally show or hide objects on the page.

There may be more, those are just the ones I've had to deal with in the past month.

I've run into similar problems, mainly trying alternative solutions with drill.  I thought it was more down to my inexperience than an underlying issue.
I'm finding the control offered by drillthrough, offers a better user experience than the somewhat ad-hoc drill down / up.