2

So we have a hierarchy relationship for phases of a process:

Overall Phase
    |----Phase 1
    |----Intermediate Phase
    |   |----Phase 2
    |   |----Phase 3
    |----Phase 4

Then we have many objects that go through these phases. These objects belong to one of several different types, let's call them A, B, and C. So we can build a pivot report like this which tells us the total (or average) time spent in each phase for each different type:

Phase                       A   B   C
Overall Phase               11  11  12
    |----Phase 1            3   2   4
    |----Intermediate Phase 6   6   6
    |   |----Phase 2        2   1   1
    |   |----Phase 3        4   5   5
    |----Phase 4            2   3   2

Now the thing is, each phase has a goal associated with it, which we want to be able to compare to the actuals easily. I think we could do it where there would be a goal column paired with each different type column, but what if we wanted instead to only display the goal at the end, like this:

Phase                       A   B   C   Goal
Overall Phase               11  11  12  13
    |----Phase 1            3   2   4   3
    |----Intermediate Phase 6   6   6   6
    |   |----Phase 2        2   1   1   2
    |   |----Phase 3        4   5   5   4
    |----Phase 4            2   3   2   4

Our first thought was to do a union report, but this is not supported with hierarchical columns. Having a seperate report won't work because expanding or collapsing the hierarchy will leave the two out of sync. This is new ground for us, and we're drawing a conceptual blank about how to handle such a thing. Any ideas about what we need to do in order to accomplish this?

Current (simplified) table structure is something like this:

**Fact table**
OBJECT_FK
PHASE_FK
PHASE_START_DATE
PHASE_END_DATE

**Phase Dim**
PHASE_KEY
PHASE_NAME
PARENT_PHASE_KEY
GOAL

**Phase Hierarchy**
ID
PARENT_ID
LEVEL
IS_LEAF

**Object Dim**
OBJECT_KEY
TYPE
JCL
  • 189
  • 1
  • 2
  • 11
  • Are you looking for a single query that returns all the relevant data? – Jon Heller Mar 08 '12 at 05:56
  • No, with a query I'm fairly confident we could do it. I'm wondering how to design the RPD to support building this in a BI report. – JCL Mar 08 '12 at 13:23

1 Answers1

0

In OBIEE, you have to model a fact fragmentation.

Goal is a value that is dependent of a combination of dimension. You can have a goal by phase but you can also have a goal by phase and by department for instance.

Then create another fact table with two columns:

  • the phase id
  • your goal

And import it in your model.

You have an example here. They are talking about quota and the table is in an Excel file but the concept is the same. http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/biee/r1013/bi_admin/biadmin.html#t8

Cheers
Nico

gerardnico
  • 855
  • 11
  • 10