Is it better to have a CASE expression or a JOIN with a dimensional table? I have a fact table which has a timestamp and I want to express the day of the week. I could do something like this:

SELECT CASE WHEN date_part(timestamp,'week') = 0 then 'Monday'
WHEN date_part(timestamp,'week') = 1 then 'Tuestday'...
FROM fact_table


Or I could create a dimensional table like the following:

day_number day_name
0          Monday
1          Tuesday


and then make a JOIN with the fact table:

SELECT day_name
FROM fact_table
LEFT JOIN dm_days
on day_number = date_part(timestamp, 'week')


Which one is better?  

What if instead of a date is some indicator (for instance where the traffic comes from: newsletter, SEO, affiliates...) that does not require any function (like date_part) to be obtained? For example, would this be better:

SELECT CASE id_channel WHEN 0 then 'SEO'
WHEN 1 then 'Newsletter'...
FROM fact_table


Than this?
    SELECT channel_name
    FROM fact_table
    LEFT JOIN dm_channels
    on channel_id = id_channel
For this problem I would use the date formatting functions (after setting lc_time to the appropriate locale)

SELECT to_char(timestamp, 'Day') 
FROM fact_table


In general the table form is, to me, cleaner (when there is no suitable function)

Perhaps with an in-line table if there is a good reason to not have a permanent table.

SELECT day_name
FROM fact_table
LEFT JOIN (VALUES (1,'Monday'),(2,'Tuesday'),(3,'Wednesday')) AS days(day_number ,day_name ) 
on day_number = date_part('dow',timestamp)::int