Taming WITH clause with Relational Dimension and HUGE fact

[rob.hollenbeck]

I'm trying to get a Drill through report tied in with a relational hierarcy into a DMR Model and Package however whenever I use the Dimensional objects in the report I get the WITH clause hitting the dimension first then linking through the Fact but ends up with a less than ideal explain plan and my base fact is in the BILLIONS

I can link to the Database layer and it joins and filters "nicely" but I loose my functionalty to link while driling up/down

We've done DMR previously with smaller tables and the performance isn't great and I need to crack this from the beginning before I build forward

We are stuck on 8.3 even thought I've heard 8.4 has the "Dynamic Drill" or whatever... thanks in advance for any feedback/direction

[rockytopmark]

I doubt the WITH clause is the culprit.  BTW, you can prevent a WITH by changing a setting in the Query properties, for the query in question in the report design.

Can you elaborate more on your drill-scenario?  What is the basis of the Drill-FROM report, as well as the targeted report of the desired Drill-Through?