Hello everybody, I am newbie and I had issues also to find a proper title but I hope you may help me with your experience.
I have a simple fact table with sales data as follow:
[Product] , [Year], [Month], [Customer], [Net], [Qty]
Then I have another crossreference table as follow:
[Product A], [Product B], [Info]
It's a table that match one(or more) Product A with another one (or more). [Product A] is the original code of a product, and [Product B] is the code of its alternative.
In the fact table in the [Product] field there could be one row with Product A, one row with Product B and so on, what I want to do is to create a model that allows me to compare the sales , for each customer and every dimension present in the fact table (year, month ..), the sales of Product A (original) versus the sales of the Product B (alternative), using the only second table that have this transcode A->B.
Is it possible?
I am trying creating, starting from the fact table, two different table filtered one for the products included in the "Product A" and one with the products included in "Product B" . Then automatically it seems there is a join but with a synthetic key and it's not showing correctly the data.
Thank you in advance
