I have a transaction(fact) table and two dimension tables in my data source view.
Fact table - fctSales
Dimension table1 - dimCustomers
Dimension table2 - dimCategories
The Sales fact table is linked with the Customers table on Customer_Skey, however, there is no relationship between fact and Categories table; sales are not recorded at the category level, just at the customers level. I have another table(not in the data source view yet) that acts as a link between customers and categories table but the issue is that each customer could be associated with more than one category. Users do not want to look at sales by category, but they need it in the cube for other reporting purposes - example, for looking at sales by customers but filtering for just one category etc.
Any idea how to deal with this situation? Thanks for your help in advance.