Updating By Default/specific Values

In my datawarehouse fact table I have a column (revenue) that I want to populate based on the values of number of columns, for simplicity, say just 2 columns, 'productid' and 'affiliateid'.

I have a revenue lookup table, with those same 2 columns and the amount. So far so simple, but rather than have one row for every possible combination, I use 0 to mean default. For instance, all the affiliates have the same revenue value apart from a couple, so instead of 200 rows identical except for the affiliateid, I have one row with a '0' for the affiliateid and 4 rows with specific affiliateIDs where it differs from the default.

E.G.

AffiliateID, TypeID, Revenue
0, 1, £50
22, 1, £55
33, 1, £57
0,2,£60
22, 2, £66
33, 2, £69


To update the values, I join to the revenues table twice, one for both columns matching, and once for the default. I.E.

UPDATE facttable SET revenue = ISNULL(rev1.revenue, ISNULL(rev2.revenue,0))
FROM facttable FT
LEFT OUTER JOIN revenues rev1 ON FT.AffiliateID = rev1.AffilateID and FT.TypeID = rev1.TypeID
LEFT OUTER JOIN revenues rev2 ON rev1.AffilateID = 0 and FT.TypeID = rev1.TypeID
(In fact, this is over-simplified, because in fact there are 3 columns, so I have to have 8 joins like this).

This works very well, and cuts down the management of revenues significantly, there are a few 100 rows instead of the more than 100,000 there would be if I put every possible combination of values in its own row.

However, now there is a requirement to increase the granularity of the revenue allocation up to 5 columns, which makes 36 joins and there could well be more columns added later.

Has anyone come across a situation like this (and found a neater solution).