I have a dimensional layer in a FM model that consists of a measure dimension (multiple different measures from a fact table in the database) and several regular dimensions (location, date, account, etc.)
When I run a report in Workspace Advanced I get null values from what seems to be joins that should not be occurring. See image for reference.
In this example, [Country] and [Key/Target Flag] come from different dimensions, and the measure is revenue recorded in a fact table that joins them. If there is no data at the intersection of "Albania" and "Key", then why does this row appear in the report?
I have governors set to deny outer joins and cross product joins. I know there is an option to suppress null values in the report itself but I cannot expect business users to find that option every time they run a report (and with 10s of millions of rows in our fact table, performance would be horrendous). I just need Cognos to not use a full outer...
Can anybody explain why this might be happening? Thank you!
It appears to me that you do have facts with a [Country] but not [Key/Target Flag]. So the first thing I would check is the foreign keys in [Key/Target Flag] for the fact table rows being pulled. And you might want to run a database trace and capture the SQL (since this is DMR) that is actually being sent.
Could facts that have a [Country] but no [Key/Target Flag] be getting populated during ETL with a foreign key to a dimensional attribute member '(Unmapped)'?