Outer join between dimension and fact for one report — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Outer join between dimension and fact for one report

Received Response
21
Views
4
Comments
kzane
kzane Rank 4 - Community Specialist
edited Aug 13, 2024 6:18PM in Oracle Analytics Cloud and Server

Hi everyone,

I have a problem in one of my reports that a dimension is limiting rows returned from my fact table, I'm guessing due to inner join (0,1 - N) in the BMM layer between said fact and dimension table.

As a simple example, I have Countries dimension table from where I take the field 'Country Name' and a Population fact table, which tells the 'Population count' for each country. They are joined by field country_id. Lets say that Population fact table has population count for country France (country_id: 4), but the Countries dimension table does not have a record for France (country_id:4 is missing in the table).

However, I still want to display the 'Population count' for country France, even though I will not get the 'Country Name' for it (I would just put 'N/A') in. Currently the entire row is ignored if the fact cannot join to dimension.

I know there's an option to achieve this if I change the inner join to outer join, but is that really a good way to approach this problem? Keep in mind I would have to create a separate Countries dimension logical table because I only want this in effect for one report, not all. Is there a better way to do this?

Thanks and regards,

kzane

Answers

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    Yes there is an option, go to your Business Model and Mapping Layer in your Administration Tool, and in the your Logical Model, in the relationship, you could set up

    Left Outer Join, Right Outer Join, Inner Join y Full Outer Join.

    Gracias.

    Saludos,

  • kzane
    kzane Rank 4 - Community Specialist

    Hi Cesar,

    thanks for the answer. I know about that option, but my question was if it's possible to do it for one report only, without making separate dimension table in BMM.

  • jackohug
    jackohug Rank 2 - Community Beginner

    Is there a better way to do it? Definitely – fix the dimension.

    Alternatively (I'm not sure if this would work, it's normally done the other way around where the dimension should be shown when there is no fact value for it and I can't get my head around it right now) you might be able to try something along these lines, where the dimension values are cross-joined to get the complete list. You might need to model through the Country ID and use that in the Answer (hide it or exclude it in the view) so that you can generate a complete dimension list.

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    Hello,

    There is no a option as you look for.  Defenitely you should set up based in bmm.