Add 'Unknown' row to dimension tables

[bw_chec]

Hi,

I have a fact table with a service_id to dim_service. Some historic services are now no longer used, so are not in dim_service. I want to create a 'No longer used' row to group all of these rows in the fact when it joins to dim_service.

E.g. service_id in fact = 13, no service_id =13 in dim. I want this to be 'No longer used', but there are multiple service_id this applies to, so i want a 'catch all'.

If i add a row to the dim table with null service_id, will this catch all service_id that are not in dim_service?

Thanks,

BW

[Tahreem24]

@bw_chec Please share the sample data example not the real data for both tables and expected output in order to understand your query and accordingly we could support you.

[Sergii24 · Accepted solution]

Hi @bw_chec, assuming you have the following semantic model:

all unmatched rows in fact will be grouped under row "blank" in dimension.

• You don't need to add any blank row to dim table to obtain this result.
• Depending on visual, measure(s) used and etc. you might need to forse its visibility, because by default blank row is hidden

If you want to gorup all unmatched values under a row with a name different from blank (like "no longer used") you'd need to add these rows to dim table (there are multiple ways to do it) and then group them together (i.e. having a row with ID 13 and a column with a value "no longer used". For existing values, depending on your use case you can group them by writing in a new column "exist" or referencing ID, so writing there value 12 and etc.)

Good luck with your project!