MicroStrategy documents and dossiers include a join behavior setting on datasets as seen in the screenshot below. This setting is built to control joins between the two datasets but only in specific scenarios. This can lead to confusion when using the product and results other than what is expected in some scenarios.
The main misunderstanding centers around reporting scenarios with lack of metrics. The MicroStrategy product has been built from the ground up to focus on metrics and analysis of aggregated and calculated values. In terms of join behavior this means the setting applies to metric joins between datasets. To be more specific the product creates a metric dataset for the join between datasets and then outer joins that to the attributes. With no metric dataset the attributes are just outer joined to each other. This leads to situations where join behavior is set to secondary (inner join) between datasets but an outer join is still seen as no metric is used from the datasets.
This is best illustrated with an example. In the example below there are two datasets join1 and join2. Join1 has data for 2014 and 2015 while Join2 has data for 2015 and 2016. When just the attributes are added from both datasets (notice at least one attribute is included from each dataset that is only sourceable from its dataset) a full outer join is seen between the data. This is despite both datasets being set to inner join for their join behavior.
Now if the metrics from each dataset are added to the grid the data can be seen to now perform the inner join specified in the join behavior.
This means in scenarios where an inner join is desired between datasets it is key to make sure metrics are used from each dataset by the grid. This can be done directly through being placed on the grid, through hiding the metrics on the grid or by building view filters based on the metrics.
The main misunderstanding centers around reporting scenarios with lack of metrics. The MicroStrategy product has been built from the ground up to focus on metrics and analysis of aggregated and calculated values. In terms of join behavior this means the setting applies to metric joins between datasets. To be more specific the product creates a metric dataset for the join between datasets and then outer joins that to the attributes. With no metric dataset the attributes are just outer joined to each other. This leads to situations where join behavior is set to secondary (inner join) between datasets but an outer join is still seen as no metric is used from the datasets.
This is best illustrated with an example. In the example below there are two datasets join1 and join2. Join1 has data for 2014 and 2015 while Join2 has data for 2015 and 2016. When just the attributes are added from both datasets (notice at least one attribute is included from each dataset that is only sourceable from its dataset) a full outer join is seen between the data. This is despite both datasets being set to inner join for their join behavior.
Now if the metrics from each dataset are added to the grid the data can be seen to now perform the inner join specified in the join behavior.
This means in scenarios where an inner join is desired between datasets it is key to make sure metrics are used from each dataset by the grid. This can be done directly through being placed on the grid, through hiding the metrics on the grid or by building view filters based on the metrics.
