Aggregation in OBIEE Analysis

Question

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.

[Deleted User] · Answer

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.

ark86 · Answer

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

Gianni Ceresa · Answer

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