Custom Supply Dimension Table to Populate Product Category by Month. All help is much appreciated.

[Anonymous]

Hi all,

I do not have the supply listed for product category in my fct_Product table, and therefore need to populate a custom supply value for each Category in my fct_Product table, but for this to also be different by each month.

I have my fct_Product table listing all the demand with a Product and Category column, as well as a dim_Date table linked to the Date column in the fct_Product. I now have tried to insert data to create a new dim_Supply table, which lists the value for by each Category for each particular month (DD/MM/YYYY). A simple illustration is shown:

However, I understandably cannot create a relationship in my data model with this new table because when try to I link both Category columns in dim_Supply and fct_Product, “each column contains duplicate values”. Is there any workaround to this, as I feel I might be overcomplicating things? I am hoping to have something I can easily customize in the future since supply can change. 

Many thanks for any help

[Anonymous]

Yes that seems to work! Thank you for the help

[RemyO · Accepted solution]

If i understand correctly you want to create a relationship between 2 tables based on 2 columns in each table.

You cant. Instead:

Create a concatenated column (new column). For instance Date && " - " && Category

Do that for bot tables and create the join based on that new field