I have a flat table which I am modelling into a star schema.
Example: salesdate, productname, salesamount, agentname
I want to make Sales as fact table. Product and Agent as dimension tables.
Step 1: get data from dafasource. This gives me a query with salesdate, productname, salesamount, agentname
Step 2: duplicate the above query and name new query as Agents.
Step 3: in the query from step 2, remove the salesdate, productname, salesamount. Remove duplicate agentname. Add index stating with 1.
Step 4: merge query (left join) from step 1 with Agent on agentname. Remove agent name from query 1. Expand merged query so we now have agentid in fact table.
Problem: there are couple of sales for whom agentname is unknown. So they are appearing as null agentid in fact table. How can I add an unknown record in agent dimension for example id 1 as Unknown Agent. This is so that I can map the numm agentid to unknown record.
