How to create dimension from two tables

Question

Hi, I have one fact table and two different dimension table: FACT1 and DIM1 and DIM2

Dim1 and Dim2 has data called MANUALLY_PRODUCT and SYSTEM_PRODUCT

In physical layer I have join like:

FACT1.MP_ID = DIM1.MANUALLY_PRODUCT_ID FACT1.SP_ID = DIM2.SYSTEM_PRODUCT_ID

I have requirement to create one table for PRODUCT with column NAME  and TYPE and when

I am creating analysis I want add PRODUCT_NAME column and for example sum(PRODUCT_AMOUNT) from FACT1 and see ALL product in one analysis.

How can I achieve this requirement in Business Layer?

I create fact table in business layer and one dimension with two source, but then I created analysis I see only data for PRODUCT_NAME column for only one type. The second type is null.

[Deleted User] · Answer

Gianni Ceresa wrote:Because of there is even just a single row having both values it's obvious that you are trying to do something wrong (really really wrong).

For once I am more diplomatic on a Friday :-)

Gianni Ceresa · Answer

I don't believe the behaviour or OBIEE is really wrong...

Your fact table join to these 2 tables as 2 totally separate things, and you want to merge them into one.

From a logical point of view, you will need to trick the system a bit more to make it think they could be the same thing.

Obviously you agree that it's impossible that a single row of the fact table has both a MP_ID and a SP_ID, right?

Because if there is even just a single row having both values it's obvious that you are trying to do something wrong (really really wrong).

[Deleted User] · Answer

Are the two dimension tables a snowflake like you imply with your join? Because in the next sentence you say "all products" which would mean that you actually want a UNION.

Which one is it? Be precise with your questions please, otherwise you get answers which point you in the wrong direction.