Direct Discovery
Oracle DB
There are 2 dimensions "Product Store 1" and "Product Store 2" as below.
Both have same products but different IDs. Both the IDs exist in the FACT table.
Product Store 1 |
Product ID Store 1 | Product Name Store 1 |
| A | Apple |
| B | Banana |
| C | Carrot |
| D | Dill |
Product Store 2 |
Product ID Store 2 | Product Name Store 2 |
| W | Apple |
| X | Banana |
| Y | Carrot |
| Z | Dill |
FACT Table |
Product ID Store 1 | Product ID Store 2 | Sales |
| A | W | 10 |
| B | X | 5 |
| C | Y | 20 |
| D | Z | 60 |
| B | Y | 5 |
| C | X | 30 |
| A | Z | 20 |
| B | Y | 40 |
| D | X | 10 |
| C | Z | 90 |
| A | W | 80 |
| B | W | 30 |
Report Requirement
Report has"Product Store Name 1" in the row.
But the sales should be of "Product Store Name 2" i.e. Lookup the "Product Store Name 1" in Store 2 and get the sales
Report Output |
Product Name Store 1 | Sales(Product ID Store 2) | Comments |
| Apple | 120 | Sum of W's |
| Banana | 45 | Sum of X's |
| Carrot | 65 | Sum of Y's |
| Dill | 170 | Sum of Z's |
Need help!!!
