Query optimisation

[JO]

Hi,

i wanted to optimize the query in the framework manager. First let me explain the query and report.

My report achieves a drill thru from level 1 to level 6. all the 6 reports use the same query from the framework manager. for example...
level1-channel(Highest  granularity)
level2- branch
level3- manager
level4- ...
level5- ...
level6 -Agents

Agents is the lowest granularity.

In the Report side, totally i have 6 reports(drill thru). these access the same query.

In the framework manager, the query looks like this...

basically to find the actual and target sales and ratio of that for monthly, yearly, year(jan) to till date, and lastyear(jan) to till the current day of lastyear. target data and actual data are in different fact table. The native Sql query gives the data from level1(channel) to level6(agent) with full outer join.  so this leads to performance issue, since somany outer joins and also every level of report hitting  the same query .
i thought of breaking the  query to each level(for level1 one query which access only actual and target ratio for channel... like this for every level).

Is there any other way of doing.... and also

How to reduce the joins and in what level?

Thanks in advance

JO

[JO]

No Response  :-(

[BIsrik]

hmmmm....nice one

[cognosfreelancer]

Hi JO

From what I understand of your requirements, you are running the same query over and over to get different subsets for each of the drill through reports.

You would benefit a lot from caching.

Materialized view would be the way to go. Capture all levels in the view.

You will not have to worry about outer joins any more.

NKT