Merge Dimension / Data Synchronization and Force Merge in BO Report

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.

DP1

Merge Dim1DP2

Merge Dim2If 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.

Merge Dim3Force Merge (Multicube)

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.
Merge Dim4All 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([Sales Revenue])
Merge Dim5Forcemerge 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

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.

•    ForEach
•    ForAll
•    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.
Merge Dim6Calculated 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.

Advertisements

4 thoughts on “Merge Dimension / Data Synchronization and Force Merge in BO Report

    • Hi Shikha, even if you join unique id from both data providers, you née look how the other objects are present in the report. Some time dimension objects won’t perform as expected and you need to create a detail variable for those dimension objects and place them into report structure. If measure object is having issue try use forall or foreach functions.

      Thanks
      MadhesDWBI

  1. Hi,

    I have a doubt regarding merge dimension? I read the statement “If the report has 2 data providers with common named dimension and same data source”. What is data source here?
    Can we apply merge dimension with different databases(eg. One is Oracle db and another one is SQL server DB)?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s