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

    Aggregation in OBIEE Analysis

    ark86

      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

                                             /             \

                                           /                 \

                                         /                     \

                               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?

       

      Thanks.

        • 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

            Hi,

            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
              ark86

              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

               

              ARK

              • 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.