Forum Discussion
Dim table’s id with nulls
My view is filter them out. Don't load them. Also generate an audit query that lists any keys in your fact table that are missing in the dim table. That way you know what dims are missing, and you can fix these at the source.
update after using this solution, in a dim table all the 10 rows that had nulls in the id column, all are unique rows so if you replace all nulls with say 123456 then remove duplicates. Then you are left with 10 unique rows with the same value in the id column. So 1. that would not make sense in terms of your data as each id value should be different, 2. when you load the data into power bi desktop it won't allow you to have a star schema as it suggests using a many to many relationship. 3. if you add the id to a table then you would get in trouble for using a made up id. So I would suggest to update the data in the source. I may not filter out the 10 rows though as I want to show it in my data model I might instead remove the dim table and add the columns back to my fact table until I can resolve the issue in my data source.
Yes, all of that is true; your scenario suggested that you might want to see these values. How are you going to show them as separate rows if you have no matching ID in your dimension? What if you have 100 nulls in your fact table, worth $20,000? This at least gives you a way to quantify the value of the IDs missing from your incomplete tables. My and everyone else's advice was to fix your dimension table, or at minimum remove the nulls. Simply removing the nulls will give you incomplete totals, as of course you must know.
Had you read my earlier advice more thoroughly, you'd have removed the duplicate IDs from your dimension table so that you had unique values for your star schema.
My advice may have been beyond your grasp, but we offer advice for your "what-if" scenarios because we've dealt with these very issues long ago, many times, effectively. Our solutions make sense, and are even more precise when the question includes specifics, data, and code. If you don't want to implement or don't understand the advice for which you asked, you can simply move on, or seek clarification.
--Nate