1 Reply Latest reply on Aug 5, 2013 6:35 AM by Shankar S.-Oracle

    Multiple hierarchies single dimension single report


      I am trying to add multiple hierarchies from a single dimension in a single report. To create the hierarchy in BI admin I followed the instructions found here

      Oracle BI EE – One Dimension – Multiple Hierarchies « Business Intelligence &am…

      and here



      My Patient hierarchy looks like the this


      All Patients->Gender->Patient ID

      All Patients->Age Group->Patient ID


      When I drag and drop the second hierarchy in the Criteria Oracle BI Answers says the following "...you can only add one hierarchy per dimension to a report". I deduct that what I am trying to do is not possible!:-)

      Q1: Is this feature indeed not supported or am I doing something wrong?

      Q2: If this is not supported can someone explain why?


      Version Oracle Business Intelligence

        • 1. Re: Multiple hierarchies single dimension single report
          Shankar S.-Oracle

          a) Reporting across levels of 2 hierarchies is not supported. As explained below, you cannot use OLAP to get aggregate values at cross combination of levels from hierarchies: H1 and H2.


          However if you wish to, you can report at the lowest level, Patient, common to both hierarchies and display the Age Group as well as Gender attributes corresponding to each patient. This becomes reporting at Patient/lowest level. You can of course use the capabilities of the reporting tool like obiee to use a Pivot object (say) and exclude Patient columns like Id, name etc. and get obiee to perform localized summaries within the reporting layer and get summarizations of interest. OBIEE Pivot measures allow for aggregation using Min, Max, Sum, Count, Running Sum etc. OLAP facilitates this solution but does not particularly help in the aggregation (since aggregation is now being done in obiee and not as per cube definition rules in olap aw). It acts as a source for information at the lowest level stored in the cube. Some performance degradation would result but it may be acceptable/manageable depending on the data volumes/report criteria.


          NOTE: For workaround/solution, use the regular fields, not hierarchical columns, from OBIEE presentation layer.


          b) Not supported: OLAP Cubes contain pre-calculated (or dynamically calculated) summaries at various level of each hierarchy. This aids in faster reporting performance. Allowing 2 hierarchies at the same time defeats this feature/goal. A cross combination of levels across different hierarchies requires a dynamic recalculation of the detail records to get the right values for, say, Age Group: 30-39 and Gender: Male. H1 summaries at AgeGroup level for Age Group: 30-39 cover all genders and cannot be split up into male/female w/o recalculation. Similarly H2 summaries at Gender level for Gender: Male cover all age groups. Splitting it up is not possible except via workaround/ solution explained in (a).


          Hope that helps.