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.

   Merge1.jpg

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.

Merge2.jpg

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.

Merge3.jpg

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

Merge4.jpg

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”.

Merge5.jpg

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.

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Louis Marco

    Hi Ankit,

    Thanks for your post.

    May I clarify, if I have 3 dimensions (and a measure) in my Excel, I should change the qualification of those 3 dimensions to Detail (without the “Associated dimension)?
    I was able to merge the 2 dimensions.  It works with 1 merged dimension in the table but when I’ve added more dimensions (from the Universe data source), my Excel measure disappears.

    My scenario:
    Excel has 3 dimensions (Product Code, Product Name and Product Grouping) and 1 measure (flat table of price rates of products)

    Universe: 10 dimensions and 2 measures (sales history table with invoice dates)

    I’ve merged the Product Name and Product Code (the Universe doesn’t have the Product Grouping)

    Cheers,

    – Matt

    (0) 
    1. Ankit Gupta Post author

      Hi Matt,

      Sorry for replying late.

      Looking at your given example I think you are doing it right, as you have taken 3 dimension and 1 measure from excel and 10 dim. and 2 meas. from universe. If you create a merge on any one dimension and create a Table view on webi canvas.

      Table View:

      Merge_Dim    Univ_Dim   Excel_Meas   Univ_Meas

      It will work fine since I am using “Merge_Dim” in table view so all measure will be displayed correctly.

      When I am not using Merge_Dim in Table view and want to see Excel_Meas on Univ_Dim then we have to create a variable on Excel_Meas

      Variable formula will be:

      var_Excel_Meas = ForceMerge([Excel_Meas])

      now create a table view which have:

      Univ_Dim    var_Excel_Meas    Univ_Meas

      It will show correct values of excel measure similarly we can do for Univ_Meas when we want to see Univ_Meas on Excel_Dim without using Merge_Dim in table view.

      Also if you want to see that record which matches in Merge_Dim or intersection of both data provider then create another variable:

      var_check = If([Excel_Dim1] = [Univ_Dim1]) then 1 else 0

      Add this variable(var_check) in table view and filter the table on value 1 and make it a hide column.

      Hope it helps to clear your doubts.

      Thanks and Regards,

      Ankit

      (0) 

Leave a Reply