When objects are merged in a Webi document there are two possible types of the merge operation: join and synchronization. The type depends on the objects selected in a data block. To achieve expectable result, it is important to understand the difference between these operations.
Let’s consider examples of each operation.
The document use two data providers. The first data provider (Reservation) has Reservation Year, Future Revenue.
The second data provider (Sales) has Invoice Year, Sales Revenue.
The list of objects in the document contains these 4 objects:
Here are the corresponding tables in the report:
We cannot use both dimensions in the same data block. If we pick one, the other is grayed. The reason for that is that BO does not know how to combine the dimensions from different data providers. It is not obvious for BO that they have the same nature (year).
To tell BO that these dimensions have the same business meaning, and that we want to merge them in the report, we need to merge the dimensions.
We merge the Invoice Year and Reservation Year.
Now we can pick Year, Sales Revenue, Future Revenue in the same table.
The values of Year are the union of Invoice Year and Reservation Year values.
Note that if we pick only measure from one data provider, all years will be displayed.
Let’s add Sales Person to the second data provider:
The table has not changed:
If we add Sales Person to the table, there will be significant change than just adding one dimension:
- Now the merged dimension is showing only values of Invoice Year.
- Sales Revenue is also split by Sales Person.
- Future Revenue is also split by Sales Person. Instead the total Future Revenue for 2012 is shown for each Sales Person for 2012.
(Note that blank values here correspond to nulls. Null is just a normal value.)
The data in the two data providers has different granularity. It is not possible to calculate Future Revenue for a specific sales person as this granularity is not available in the Reservation data provider. Therefore BO shows total for 2012 for each Sales Person in 2012.
The years for which only Reservations exist are also not displayed. It is not possible to show them as the granularity of Reservations is lower than granularity of Sales. They cannot be added with blank in the column for Sales Person. Here the blank value means that there is no sales person. But sales person is just not relevant for Reservations. (OK, in general the sales person is relevant to reservations, but according to the data providers, he is not)
This demonstrates two types of operations: Join and Synchronization. The operation depends on the dimensions selected to the data block, it does not depend on measures and details.
Two data providers are joined if only merged dimensions are selected to the data block. All combinations of dimensions from the first data provider are united with all combinations of dimensions from the second data provider. The measures are projected in the context of the merged dimensions. The details are looked up by the matching dimensions.
Two data providers are synchronized if beside the merged dimensions, one or more non-merged dimension is used in the data block. All non-merged dimensions should be from the same data provider. Let’s call this data provider the leading data provider. All combinations of dimensions from the leading data provider are displayed in the data block. The measures from the secondary data provider are projected in context of dimensions from the secondary data provider and looked up by the matching merged dimensions.
(There can be more than two data providers involved)
We considered two separate data providers. The operations join and synchronization are possible for a single data provider on a query level.
When we pick objects from the different contexts they are synchronized or joined on the query level.
For instance Future Revenue and Sales Revenue come from different contexts. Service is a common dimension (here, the common dimension is analogue of the merged dimension):
Therefore the queries are joined (note “Join” in the root):
The data provider with a joined queries acts as a data provider with a single query.
When beside the common dimensions, there is a dimension that does not belongs to all contexts, the queries are synchronized.
The synchronized queries are not actually synchronized when creating the data provider. The operation will be chosen by the dimensions selected in the data block. If only the common objects are used, the queries will be joined.
For instance, here you see result of the join operation, even though the synchronization operation was displayed for queries: