Merge Dimension / Data Synchronization
Merged dimensions are the mechanism for synchronizing data from different data providers. If the report has 2 data providers with common named dimension and same data source, those objects would merge automatically in Deski & Webi report (auto Merge option should be enabled in Webi).
Webi report has more option to do with merge dimensions function and below is the details.
Enable Auto Merge Dimensions Option:
In Document Properties tab, select Auto-merge dimensions. Automatically merges dimensions with the same name and from the same universe. You can see the merged dimension in the list of available objects with the dimensions merged in reports object panel.
Extended Merge dimensions:
In Document Properties tab to display the “Document Summary” dialog box and then select Extend merged dimension values.
- Extended Merge dimensions results when a table contains synchronized data. When Extended Merge dimensions option selected, if a table contains a dimension used in a merge, this dimension returns the value of its query plus the values of the other dimensions merged from other data providers.
- When Extended Merge dimensions is not selected, if a table contains a dimension used in a merge, this dimension returns the value of its query.
When you create a report from multiple data providers and the measure object will have some implication in the aggregation. By default, calculations do not account for merged dimensions if the merged dimensions do not explicitly appear in the calculation context.
For Example, Data provider 1 (DP1) is having Sales data and Data provider 2 (DP2) is having Country details.
If you have auto merge enabled, City dimension will merge from both the data providers and you will get the report as below. If the auto merger is not enabled, you have to merge city dimension by selecting Data – Merge Dimensions.
Force Merge function in Webi is nothing but Multicube function in Deski. This Force Merge function plays a major role when you use multiple data providers especially from two different types of data sources.
In the above example, you have merged city object and you want create a report for Country wise sales without having City in structure, you result would looks as below.
All country Sales would show the sum of Sales revenue since there is no direct relation between country and sales. To avoid this aggregation and to get correct sales for each Country you have to use forcemerge function to the Sales Revenue Measure.
Forcemerge function would help to resolve your report aggregation issues when you create a report from 2 different data providers and not using common dimension in the report structure.
Calculated context (listed below) are playing major roles when you have a report based on multiple data providers and specifically in the measure object total (SUM), difference and percentage of measure object values.
• In Report
• In Block
• In Body
By default, each report will have a calculated context for measure objects based on the dimension objects available in the table. You can change the calculated context based on your requirement to achieve the final result.
For example the has 3 objects (Country, City and Sales Revenue) in your report structure, by default the report will set the Calculated context for measure (Sales Revenue) as “<Sales Revenue> In (<Country>, <City>)”. If you remove any of the object from the report structure, the context also reflect the same.
Calculated context can be defined in measured object to get the proper result. When you have report with simple calculation you can prefer to use “In” with dimension objects. If you want to show the measure for each measure, use “ForEach” and for all dimension values to the sum of measure use “ForAll”.
“In Block” option mostly used when you have a Break / Section report and the report has complex calculation used in the variables and that should be used in report then you can go for “In Block”. “In Body” and “In Report” also perform more or less equal to the “In Block” and will be used in section with break reports.