4 Replies Latest reply on Sep 29, 2016 10:56 AM by Christian Berg

    Aggregation in OBIEE Analysis


      Dear All,


      I'm having issues understanding why my measure (from one fact table) disappears from analysis when i include a non measure from another fact table.


      Consider following


      I've 1 dimension table (Agent) and 2 fact tables (Agent_Target and Agent_Vehicles) with following columns


      Agent: Agent_Id, Agent_Name


      Agent_Target: Agent_Id, Target_Amount, Target_Start_Date, Target_End_Date


      Agent_Vehicle: Agent_Id, Vehicle_Name, Registration_Number, Assignment_Date


      On physical layer, they are all joined by column Agent_Id. While on Business layer, both lookup from Agent table. So Agent becomes a conformed dimension.



                                             /             \

                                           /                 \

                                         /                     \

                               Agent Target            Agent_Vehicles


      I published this to my presentation layer.


      Now when i drag Agent_Name and Target_Amount, it works perfectly. But when i add Vehicle_Name in this analysis, Target_Amount disappears.


      Can anyone please tell me how can i make it work?



        • 1. Re: Aggregation in OBIEE Analysis
          Thomas Dodds

          dimensional modeling would have changed it to 1 fact and multiple dimensions...


          Really the only measure you have is target amount.  So you should have a logical fact table with that and the agent_id key.  Then you have 3 dimensions:


          Dim Agent: Agent_Id, Agent_Name


          Dim Target: Agent_Id, Target_Start_Date, Target_End_Date


          Dim Vehicle: Agent_Id, Vehicle_Name, Registration_Number, Assignment_Date


          Fact Agent_Target: Agent_Id, Target_Amount


          Now you have a true star with the fact in the middle and 3 dimensions all joined on the agent_id key in the fact.

          1 person found this helpful
          • 2. Re: Aggregation in OBIEE Analysis
            Gianni Ceresa


            Vehicle_Name has nothing to do into the Agent_Vehicles logical fact table.

            A logical fact table is supposed to contains only aggregated columns, any attribute column is not supposed to be there but must be modeled as a degenerated dimension. In that way you can set content level correctly to manage this non conformed dimension that will be the one containing the vehicle name.


            EDIT: writing when Thomas posted as well, in the end the conclusion is similar: a logical fact table contains aggregated measures, any attribute must be into a logical table being a dimension ....

            1 person found this helpful
            • 3. Re: Aggregation in OBIEE Analysis

              Hi Thomas and Gianni,


              Thank you very much for your reply and for the correction. I wanted it to work as we make manual query.


              Here's what i did


              I made three dimensions (AGENT_DIM, AGENT_VEHICLES_DIM, AGENT_TARGET_DIM) by keeping all non measure columns in them. Then i made one Fact table naming AGENT_TARGET_DETAILS and made a pure star. It worked perfectly.


              To keep things business user friendly at Presentation Level, I made 3 tables there. Agent, Agent Target Details, and Agent_Vehicle_Details.


              In Agent Table, I dragged Agent_Id and Agent_Name from AGENT_DIM in BM.

              In Agent Target Details, I used Target_Start_Date from AGENT_TARGET_DIM and used Target_Amount from AGENT_TARGET_DETAILS

              In Agent Vehicle Details, I used Vehicle_Name, Registration_Number, Assignment_Date from AGENT_VEHICLES_DIM


              So at BM, it is pure start but business user friendly at the presentation layer.


              Whats your thought on this. For me, It'll work perfectly.


              Thank you again



              • 4. Re: Aggregation in OBIEE Analysis
                Christian Berg

                ark86 wrote:


                Now when i drag Agent_Name and Target_Amount, it works perfectly. But when i add Vehicle_Name in this analysis, Target_Amount disappears.


                Correct content level definition for all LTSs as well as the measures.