How to Merge BI Universe and Excel Data in Webi

[Former Member]

Purpose of this document

This document describes the how we can merge Universe and Excel datasource in webi and issue that could occur while doing merge. Also how to validate universe data based on excel column.

Select and merge Universe and Excel files as Data Provider

We have 2 data providers one is from Universe that is built on Bex query and another data provider is excel. In order to show key figures based on single dimension from both data providers we have to merge common dimensions in webi.

Issue that can occur while merging dimensions

Since there are two different data providers in webi and we need to show each other key figures based on cross dimensions, issues which we face are:

If we try to show a key figure with any dimension variable and unmerged dimension of excel together then “#Datasync” or “#Context” error will occur.

  

Even if we drag one data provider dimension and drop beside another data provider’s dimension then system gives “Cannot drop here – the object is incompatible” error.

Solution

To resolve these issues and get accurate display of values we have to follow below steps while merging dimensions.

A) Add both data provider and their objects in Query Panel.

B) In excel data query dimension on which you are going to have your merge dimensions set their Qualification as Detail. It is not necessary to set Associated dimension of that object.

C) Run the Query.

D) Merge these Detail dimensions with Universe data provider’s object.

E) For other dimensions in excel which aren’t merged, re-create them in Detail variable assign the lowest level of merged objects as its “Associated Dimension”.

F) Now all objects are ready to be used in table of graphs with cross query key figures.

Points To Remember

a) It is good to convert Excel data provider’s merging object to Detail object in Query Panel.

b) Create detail variable of every unmerged object of excel.

c) Do not use cross key figure with cross object directly, it gives Total of key figure in all value of that object.