Forum Discussion
Fact table Including records not in dimension table
I have a data model where I have a Fact table is joined to a Dimension table. They are in a 1:1 relationship with Both direction filter. When I am returning data it is returning data from the Fact table for which there is no corresponding record in the Dimension table. It's like the model is operating like a left join rather than an inner join is the best language I know to use. Why would this be happening?
2 Replies
I figured out the problem, but created another one. I had another table in the model that was in a 1:1 relationship with the fact table that contained the records. In order to solve the problem, I remove that second table form the model, but not the Fact and Dimension table I have in a 1:1 relationship the Cross Filter Direction set to Both. Now instead of operating like a left join, it is operating like a right join when I am wanting it to operate like an inner join.
A dimension table should control a fact table in a single direction 1:* relationship a 1:1 bidi scenario mainly means that "this could have been a single table".
Missing values in a key column are something you will want to mitigate. RI violations are costly for the performance of your data model.
