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.

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.

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

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

[Deleted User]

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.