With the release of MicroStrategy Analytics Enterprise 9.4.1, the Analytical Engine logic has been enhanced with respect to joining data from multiple datasets in a Report Services Document. One of the features that is available with this release is the ability to use objects (e.g., attributes, metrics) from multiple datasets in a single grid in a document.

If an attribute on a grid has elements that can be obtained from multiple datasets used in the document, the elements displayed will be from the global lookup table. Additionally, if one or more of the datasets containing the attribute has missing attribute form data or has different attribute form from the other datasets, the Analytical Engine will follow the rules noted below to compose the final output:

Rule 1:

If there is attribute form with null value, the Analytical Engine will use the non-null form value from other datasets instead of the null form.

Rule 2:

If several datasets have different attribute form information for the attribute element, the Analytical Engine will use the attribute form from the biggest dataset.

Rule 3:

If several datasets have different attribute form information for the attribute element, and those datasets have same number of rows, the Analytical Engine will use the first dataset in the document for the attribute form value (according to the dataset adding sequence).

NOTE: Users should note that the rules are applied for each individual attribute element in the result at the row level rather than at the dataset level.

Example 1:

Users may consider the following datasets – C01 is a dataset with Customer City, Customer and Order:

 

C02 is a dataset with Customer, Order and a profit metric. Users may note that the Customer attribute is missing the DESC form in the second dataset:

If a Report Services Document is built with both these datasets, and the attributes are placed on a grid, the following results may be seen. As noted in Rule 1, the Analytical Engine will display the non-Null values from C01 for the Customer attribute elements:

Example 2:

Now users may consider a different dataset as C02 – similar to the initial dataset, but here the Customer name (DESC) form contains values instead of NULLs. This time the values for the attributes are not consistent – see that Customer ID ‘1’ has different values for the DESC form for different Orders (1 & 6).

Customer Name	Customer ID	Order	Profit
Customer D	1	1	100
Customer B	2	2	200
Customer C	3	3	300
Xia D	4	4	400
Kris Du	5	5	500
Customer A	1	6	610
Customer E	2	7	720
Customer F	6	8	860
Customer G	7	9	970
Customer H	8	10	1080

If a report is built for this dataset users will observe that the first attribute element value in the dataset is used as as the DESC form for the Orders 1 & 6 even if the value is different in subsequent rows (this is the same as previous Analytical Engine behavior).

When these datasets are used in the grid in a Report Services Document, the Analytical Engine will choose the attribute element values from dataset C02 to display in the attribute element values from. This is because of Rule 2 explained above.

Example 3:

Consider the following dataset:

Customer Name	Customer ID	Order	Profit
Customer D	1	1	100
Customer E	2	7	720
Xia D	4	4	400
Kris Du	5	5	500
Customer G	7	9	970

A report built off this dataset appears as follows:

After replacing the dataset ‘C02‘ from the previous example with the new dataset, the following results are seen. As noted in Rule 3, because both C01 an C02 have the same number of rows, the elements displayed for the Customer attribute will be filled from from the first dataset to be added to the document – in this case C01. However for the first row in the results, where there is no corresponding customer in the dataset C01, Rule 1 will be applied and instead of a NULL value, the non-null Customer Name field ‘Customer G’ is picked from C02. (Rules are applied at the individual element level).

Next: Why are some metric values blank in documents using multiple datasets in MicroStrategy Analytics Enterprise 9.4.1

———————————————————————————-

References:

[1] MicroStrategy Knowledgebase, Engine behavior for grids on a Report Services Document or dashboard with multiple datasets where some attribute forms are missing or have different values the datasets in MicroStrategy Analytics Enterprise 9.4.1 and newer releases, TN Key: 45463, 03/13/2014, https://resource.microstrategy.com/support/mainsearch.aspx.

NOTE: You may need to register to view MicroStrategy’s Knowledgebase.