SYMPTOM:
A Cartesian join of metric data is seen instead of an aggregation from the attribute relationship in the second dataset when using the new multiple datasets on a single grid feature with many to many related attributes on different datasets.
This is best illustrated through the example below.
A report containing two many to many related attributes and a metric shows the data as it exists in the database and is related per the appropriate relationship table.
If a document is built off of just this report and then aggregated up to the level of catalog the following behavior is seen:.
Design view
PDF view
As can be seen the data is aggregated per the relationships in the base data.
If instead the report is broken up into two reports. One containing both attributes while the other contains just the child attribute and the metric (only the child attribute is on the fact table) then a Cartesian join like below is seen instead of a join through the relationhip seen in the behavior above.
Design view
PDF View
As can been seen here the metric is ignoring that the secondary dataset is capable of relating together the catalog and item attributes and instead does a cross join between them.
Neither calculation here is actually correct because in this scenario the data doesn't exist at the level of the parent (catalog) so it is unknown which part of each items revenue belongs to each catalog as that is not contained in the fact table or the datasets. Using the relationship aggregates each items entire revenue under each catalog it uses (counting the data multiple times) while the cross join just focuses on showing the total item revenue to avoid multiple counting of the data but not breaking the data by catalog at all. The correct data could only be shown/retrieved if both the fact table and the dataset in question had both the child and parent attributes on them so that the data can be correctly grouped without multiple counting.
This setup could be useful in order to decrease the size of the dataset containing metrics by only containing attributes that are in the appropriate fact tables if multiple counting is desired per the data model in question. There are some data models where the multiple counted data is correct however this is not generally true for all many to many related attributes, hence why Strategy could not make the assumption that it is valid in general.
CAUSE:
This is currently expected behavior. The problem is that Strategy cannot guarantee that the secondary dataset is fully equivalent to the relationship table in the warehouse. Therefore Strategy cannot guarantee correctly related data will be displayed if the secondary dataset is used as a relationship table. So per the notes above the first of the two options (doing multiple counting) can't be done consistently leaving only the option to do a cartesian join of the data to the parent attribute.
ACTION:
Upgrade to to Strategy 10 to take advantage of an enhancement that allows the join to work with the data present.
WORKAROUND:
The only workaround to get the multiple counting behavior is to place all the attributes onto a single dataset.
