Questions about behavior of aggregations with parent-child hierarchy

Jerry S.

OBIEE 11g.

I have been experimenting with parent-child hierarchies, and now have a question about

aggregations and how they work.

CONDITIONS:

There is an Agency dimension, based on an Agency table, that includes Agency information as well as a parent key which points to the parent agency, if it exists. For Federal agencies, the parent key points to a dummy record (“No Higher Level”), and this dummy record has null in the parent key.

                                                         Agency $        Agency and Child $

Federal Agency A                                  $250                $700

   |__State Agency B                              $150                $450

       |__County Agency C                       $200                $300

          |__Local Agency D                      $100                 $100

             |__Clients

                |__Client Case Number (Fact level)

Clients can apply at any level, depending on what their qualifications and needs are.

Clients can have multiple applications, and each application is assigned a unique case number that references one agency.

Grant dollars are present in each fact record, and are set to aggregate as Sum.

CONSTRAINTS:

(1). The data warehouse may not be altered in any way.

(2). An aggregation table for the agencies is not practical because the fact record contains multiple date fields, and each date would need to be aggregated differently.

QUESTION/ISSUE:

Without the Agency Hierarchy in place, when you look at Federal Agency A, you see $250 as the amount, and do not see any dollars associated with child agencies. It works the same at whatever level you look at.

With the Agency Hierarchy in place, you see $700 as the amount for Federal Agency A, which includes the $250 for Federal Agency A as well as $450 for all child agencies. It works the same for whatever level you look at.

When users look at Federal Agency A, they want to be able to see the $250 specific to the agency, as well as the $450 that is coming from the child agencies, with a total of $700.

If I can get any two of the three numbers for Federal Agency A ($250, $450, $700), I can calculate the third number.

Is it possible to get two of the three numbers?

Thanks for any thoughts/suggestions anyone cares to offer.