Your plan to create a kartesic product with your data is especially to begin not a good idea - you should first built a simply/normal data-model. In your case the circular reference should be solved first - see an example here:
you don't need these joins - tables will be automatically linked from qv per common fields. Load first the fact-table and then step by step the dimensions-tables and built in the GUI simply tableboxes and pivots and you will see that nearly all results what you want is there. If you have then further needs, you could then thinking about more complex data-modelling.
Generally joins are difficult, often it is better to use mapping and applymap to match data to another tables.
why not? I think I couldn't explain the point of my problem enought.
I need to know all possible combinations of Customers, Products an Campaigns, because some Customers or Products even all Customers and Products can be unknown, means empty.
A case:
Normaly in a campaign you have a amount of customers and products. So, you will promote some known products to a group of customers. That's simple, and jey, no joins are needed.
BUT:
We have campaigns, where you will promote a set of products to ALL of your customers, or promote ALL products to a SET of customers.
Worst case:
We have campaigns, where ALL products are promoted to ALL customers, only the time might be different.
The result-set should not only show, which are buying customers and what they bought, no, the result-set also should show which customers and products where in this campaign, and which products where boughts from which customer over time.
Therefore I tried to join some tables, to get the max. possible combinations.
If I prevent joining, so I will get the ugly circular references, because the fact table also contains custimers and products.
Only if you really want show null-values in tables you must create such a cartesian product (all possible values) then tables could only show data there are real. But through the QV logic to associate data of green/white/grey you can see which combines from data has promotes/sales and which have not.
Cartesian products are not really recommended because the consumption from resources (CPU/RAM) is extremly high and the benefit is most often very less. There are a lot of things which are more important to analyse.
If you will nevertheless create such a cartesian product you should the sql data load first in qv and then joining them - have also a look on the taskmanager to see the ressources ...
