Forum Discussion
Dim table’s id with nulls
I am creating a data model based on a star schema. But one of my dimension tables id column has 10 null rows. What is the best solution for this.
options:
1. i could replace values but afraid if newer rows are added that has null values for the id column.
2. Filter null rows for the id column but what if i need to show the other values in that row that are not null in my report as a table.
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.
8 Replies
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.
I would replace null IDs in your fact table with a value, say "AB123", and then replace your nulls in the dimension table with the same "AB123". Then remove your duplicates from the dimension column. All of your nulls will show up under ID Number "AB123".
But yeah, don't leave the nulls in your dimension tables.
--Nate
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
But it's a dim table with multiple nulls.
Thanks MattAllington and Anonymous both are helpful solutions. I will try them both out in parallel. While i'm working on the fixing the data model i will also try to fix the values at the source of the data.
