Cross Join or Cartesian Product

[Former Member]

Hi Expert,

I'm not sure if I'm posting in the right place, because this problem involve between BW/BEx, Universe and BO; but I think there's a possibility 'a wrong' in the universe.

Right now I want to create a report in Webi from BEx Query via Universe.

When I put 2 query item in Webi, let's say, PERNR and TRFST. The result looks like this (example) :

PERNR TRFST

12345 S1

12345 S2

54321 S1

54321 S2

Ok, that's the appetizer. Here's the main course.

For some reason, I'm using function module in ABAB to create a data source table as transaction data in BW. I create a Fact Table that contain all field that I need to create dimension tables. This, in my opinion, for eliminate the possibility that the cross join appear because there's something wrong in the relationship between the dimension and the fact when creating the cube. This is a little example of my Cube result :

PERNR TRFST KEY FIGURE

12345 S1 XXX

54321 S2 ZZZ

As I explain above, I create Person dimension and TRFST (in my case I called 'Eselon') dimension and 1 Key Figure. So, from here, I assumed that there's no problem with the BW because the cube resulted the content as I'm expected.

Then I moved to BEx designer. I create a query from that cube, creating a pivot table that contain Person, Eselon, and the key figure. When I open the query in the BEx Analysis, the excel create a pivot table like this :

KEY FIGURE

12345 S1 XXX

Result XXX

54321 S2 ZZZ

Result ZZZ

From here, again I assumed, there's no problem in BEx, since there's no cross join between the dimension. Then I create the universe, then I tried to create the Webi report, and DANG!! a cross join appear.

Did you guys have any idea or solution about this? This is for the first time I created a universe using BEx, so maybe I miss something.

I will appreciate every helpful comment from you

Thank You

Rully

[amitrathi239 · Accepted solution]

If you are using two queries in webi then check dimensions are merged.if this is fine then pull one query data in the report block and see you are getting correct result.

[Former Member · Accepted solution]

Hi Amit,

Thank for your comment..

Well, where exactly to check if the dimension is merged or not? is it in the universe?

if yes, how can I do it? I already check in the universe for a while but I didn't find it.

Thank you,

Rully

[amitrathi239 · Accepted solution]

Hi,

If you are pulling data based on two webi queries and displaying both queries dimensions in the report at that time Dimension objects should be merged.There is option in the webi report to do manually merge the objects..

or let me know which objects you are using in the report and from one query or multiple query.

[Former Member · Accepted solution]

You can check that in the document properties u201Cautomatic merge dimensionsu201D

Uncheck it.

Just an update if you use objects from two different queries in webi it will create a full outer join by default in the two queries.

Regards

Kultar

[Former Member · Accepted solution]

Hi,

Here in this scenario, the universe is created on top of BEX query. So you can't see any schema from universe side.

So you can't actually controll cross joins from BO side on BEX query.

Here are the possibilities you could check:

1. check whether BEX query is correct or not.

2. Check out simple webi report from the universe. check data validity

3. check parameters section for sql proprties.

Hope this helps!

[Former Member · Accepted solution]

Hi Gowtham,

thanks for your comment.

Yes, this is one of those thing that makes me frustrate. I can't check the universe, because I think I can do nothing with BEx query in the universe.

1. If in the query Analyzer the query resulted right, no cross join, can I say that the BEx query is "ok" ? or maybe there's another thing that I should check?

2. In the Webi, like I said above, the data getting cross joined. it's totally cross join. I meant, if I put the 3rd query item, it will multiply as so much row from those query.

3. where exactly I check this?

thank you

Rully

[Former Member · Accepted solution]

Hi,

So, you have two data providers in webI.

1. Frist check the data correctness by checking data independently for each provider by creating tables for each data provider.

2. Once the data is correct, Identify the dimensions which are common between data providers.

3. If the names are same, BO WebI automatically does merging.

4. You can use merge dimension method, if you want to combine both result sets. The result sets will be combined using dimensions.

[Former Member · Accepted solution]

Well.. ummm.. actually I don't using 2 query nor 2 data provider for my report. It came from 1 query. But that's ok. It's just misunderstanding between us. Maybe because I'm using the '2 query item' word. I'm sorry.

Just for your information, finally I can solve it. Like I said in the first post, I'm just trying to use 2 characteristic (I think this is better word rather than query item), but I don't pull the key figures to the report. When I put at least 1 key figure to the report, pam pam pa ra pam... the cross join gone. I don't know why, but it happen.

Than, I examine this case by creating a simple report using eFashion universe. When I put 2 characteristic without key figure, there's no cross join. I don't know why, but it happen.

well, thank you for your comment, even mostly there are misunderstanding, but I really appreciate it. Thank you.

Rully

[Former Member · Accepted solution]

Hello,

The reason you are getting a Cartesian result is because you are not using any key figure/Measure in your query result.

It is a known issue with the webi - below is a OSS note which describes it

1476986 - If no key figures is used in the query, the Web Intelligence Report will return unexpected data

I am not sure if this issue is resolved in BI 4.0 from a webi/universe perspective.

Note says that if a webi is build directly on the query than the Cartesian product issue is resolved and we dont need to always have a key-figure while running the Webi report in BO.

Thanks & regards,

CD.

[Former Member]

Hi,

thanks for your comment.

I think there's a little misunderstanding between us.. =D

the '2 query item' that I said above doesn't meant from 2 query. Both comes from 1 universe, from 1 BEx query, from 1 Infocube.

It doesn't come from 2 query. Now I understand what you mean about merge dimension.

Ok maybe I refine my problem. In BW Infocube result, i don't get cross join. In BEx query analyzer i don't get cross join. But in Webi, I got that.

I really don't know where should exactly I must explore. is it BW? Universe? or the Webi?

And how can I fix it?

Thank You..

Rully