Outer join between dimension and fact for one report

Question

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

cesar.advincula.o · Answer

Hello,

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

jackohug · Answer

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.

kzane · Answer

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.