How to handle null values for dimensions

[cristian2]

Hi all!
I have a fact and dimensions table. The fact table have a measure. When I group the measure by a dimension from the dimensions table I could get a null value if the dimension doesnt have a key to the fact table. Is there a way of giving this null value a default value?
My result:

Dimension1, Measure
Null, 123
Dim1, 234
Dim2, 567

I do a left join between fact and dimension.

Cheers,
Cris

[izzymiller]

Wrapping the field being aggregated by the measure in a coalesce would do this, I think. You could just COALESCE(${field},0), or to whatever default value you’re after.

Where SUM(table.id) returns null, SUM(COALESCE(table.id,0)) would return 0.

[cristian2]

Hi,
Thanks for the suggestion. I don’t think it works though. The Null-value is on the dimension and not the aggregation itself. I would need to have a key with coalesce which doesn’t work.

[izzymiller]

Got it, I had it flipped.

The solution here is probably to start with the dimension table, and then left join the fact table to that— Not the other way around, which I think you might have it as.

That way, if you return Dimension1, it’ll return all the possible results, and just return 0’s for the measure where there isn’t a match in the fact table.

Example: There’s a userid 46 in my “User” table, but not in my “userfacts” table.

If you return the user_id dimension from the Userfacts table (the right side of the join), you see the null values you described

If you return the user_id dimension from the User table (the left side of the join), you see the correct values

[cristian2]

Thanks. Will try that.
Would be good though if there was a possibility to set a default value when value is null. In my case I have one fact table and 21 dimension tables. I would need to change all my joins.

Cheers,
Cris