4.3 Web Intelligence Data Lineage Challenge

[jemstar]

Hi all,

I'm looking for an opinion here.

I have recently been exploring the usability of the new Data view functionality available in BI 4.3 Web Intelligence.

One of the use cases is to build 2 queries from the same universe and join these 2 queries with joins that aren't specified in the source universe. A neat solution to an uncommon problem, but a solution all the same.

So I build a data cube with the data from the two queries with my required joins

The two queries have common objects that are not part of the joins made above and the resulting cube renames the object from the child query with a (1). This looks untidy and when I look at resulting cube, I note that I cannot actually identify the data source within the document dictionary, so I decide to rename my objects via object properties to preserve the data lineage

I was not prepared for what happened next - the renaming of an object in Query 1 renamed the object in Query 2 as well. I have not merged dimensions in this report.

The attachment shows an example of this happening using the efashion universe

I opened a ticket with Support and have been told that this is working as expected, but I don't think this makes sense

Does anyone else have an opinion on this?

regards

James

[bernhard_keimel]

Hi,

[christian_ah-soon]

Hi

We plan to add a tooltip with the object lineage so you easily identify where the object is coming from.

About the object being renamed in both queries, this is the expected behavior and it is not related to Data mode but object rename introduced in SAP BI 4.3 SP2.

Indeed, to optimize and avoid uploading the same universe several times, the universe data source is "factorized". So if you modify one universe object, it impacts all queries that use it. You can see it in the graph in Data mode since all queries are linked to the same universe "node". This is different for Excel and Text data sources for which this factorization is not done. 

Regards, 

Christian - SAP 

[jemstar]

Thanks for the response Christian.

I would challenge the assumption that the universe objects from 2 different queries albeit from the same universe are the same and should be renamed in this manner.

It effectively assumes that you are joining on all the dimensions from both queries, but what if you are only joining on one dimension in the query because you want compare the values in the other dimensions (joining in data mode is some ways similar to merging dimensions, but obviously more powerful)

So 2 queries with the same dimensions X,Y and you choose to join only on X because you want to compare the values being stored in dimension Y in both queries.

I only started to think about renaming the objects in the original queries because you cannot identify the lineage of the objects from their original queries. I ended up renaming the objects in the cube, but it's a tricky exercise. Tooltip is a start, but perhaps colorising by data source would be clearer

thanks again for engaging

James

[bernhard_keimel]

From your answer i understand that your basic problem is to understand why the join only delivers one row instead of multiple rows which you would expect - right?

Here it depends on the BI Release Version you are running. We had once a similar issue, where the "left join" had a bug that cutted off all results after the first result. This was a confirmed bug by SAP and since 4.3 SP04 P06 (?) this is fixed and working as it should.

If that sounds familiar to your problem, check your release version and this SAP Note:

3462675 - Left Join in Web Intelligence data mode do not return correct results if keys are duplicat...

BR Bernhard

[jemstar]

Thank you Bernhard for engaging on this topic.

In my use case, I am trying to compare results from the two queries.

I used efashion for its familiarity with the users of this forum

In my example, think of State as the unique ID and this is the only column that I have joined on in the creation of the data cube. I therefore want to compare the values being stored in Quarter, Year. If I join on Quarter and Year in the data cube, then the cube only presents a single column. When I don't join on Quarter and Year, it creates separate columns in the cube and this is why I need to clearly identify the lineage. In this scenario, I think Webi has to do better than placing a (1)  after the name of the object. Even it used the original query name in the brackets, it would be clearer. 

As it stands, I am faced with renaming the objects in the cube which is a workaround 

thanks

James

[bernhard_keimel]

Hi there,

I was curious so i reproduced it and yes you are right - but for me it makes totally sense:

If you take the "Quarter" from q1, and compare it to the Quarter from q2, it is essentially the same dimension object, isn't it? If the Quarter would be a totally different dimension object, the rename would not rename both as they are different objects.

Changing the name in both cubes ensures consistency on dimension objects among the report - otherwise one can get easily lost and especially other users checking the report design would have NO idea that "q1.Quarter" could be the same Quarter Dimension object in the universe as lets say "q2.IAmNotAQuarter". So yes this is a good design decision.
In the end you have the query-Qualifier and in cells/formulars etc you have to preceed the Quarter with the query qualifier q1 or q2 in that example to be precise. If you merge that dimensions later it becomes a whole different story (but when using Joins that would not make a lot sense anyway) and then you have a common name.

And when you rename the Dimension in the join result cube "q1+q2", it has not an effect on the source dimension names in q1/q2, as there both Quarters exist with different naming, which here becomes necessary to be able to distinct them. That also makes sense.

HTH