Please consider the following (simple) model:
Three data sources: products (unique key is productid), vendors (with vedorid as unique key) and purchases (measures, with productid and vendorid).
It's no problem to build a cube with the dimensions product and vendor, but I also want to include a dimension 'product by vendor'.
This dimension should allow me to drill down by the products of a vendor.
A vendor may have several products and a product can be purchased from several vendors.
How should I do this? I tried by introducing a new product-vendor datasource (containing all current combinations of productid and vendorid), but got stuck in transformer.
I can do it by using just one data source which holds all the information, but for obvious reasons want to use separate data sources.
Update:
I solved the problem by using a calculated field in the measures data source. This field concatenates the vendorid and productid. I placed it as the lowest level in the vendor dimension and that did the job.