Star schema Sales and goal

Question

I have a star schema, the fact table is sales I have a product, region date dimension (date, year, month, day). Now I have a file that contains goals of sale by year and month , Now i need to compare the goal of sale with the real value (from sale fact). But I did not know what I should do I already have my sale_fact with its dimension ,Now I am thinking of creating a new fact table goal with 2 dimensions year and month ( is it a good idea) But how will I compare it with the sale (fact sale) they won't have the same dimension.

Answer 1

Yes, create a 2nd fact table for targets, this would be attached to a month dim (containing month and year data) and whatever other dimensions are relevant.

You then have to query the 2 facts by equivalent dimensions and compare the 2 result sets. For the sales fact table you would need to aggregate it by at least month to get it to align to the goals fact table; though you could, of course, create a sales monthly aggregate fact table to improve performance if necessary