Cross Join in SAP MDX using Web I

[Former Member]

Hi there,

I am facing an issue. I am currently using BO universe on the top of BW infocube. Now whenever i am dragging two dimensions from the universe in my web i report it is creating cross join , however whenever i add any key figures , it changes the cross join to Non Empty Cross Join.

Now while testing the mdx query in MDXTEST i found that while running the MDX query with two dimensions only is giving me a cross join in MDXTEST also, however if i modify the cross join to non empty cross join it gives me the correct data.

Is it possible to do something in BO or SAP side so that my query starts creating non empty cross join rather then mere cross join.

Is it something to do with the transports we run before installing SAP integration kit

Thanks for your support

[Former Member]

It's very luck to see this message, because I have met the same problem with Kavitha.

I helped our customer to migrate about thouands reports from R2 to xi3.1 sp3

Some of them doesn't work,i found that some problem came from the master data query.too many data of cross join made the webi server down.when i drag a measure to the report, the problem sloved.

I know we can slove it by drag a useless keyfigure like row count to the report,but there're too many reports to modify.

Here comes the question:

The report without measures worked well on the XI R2 platform,but doesn't on xi3.1

if MDX generation rules are different between them?

if there are some fixpack canbe used for generate "NON EMPTY CROSSJOIN"

hope for the answer

Thanks

[IngoH]

Hi,

the logic behind a report without a measure has not changed and the MDX is always asking for non empty. if you don't use a measure in the report you basically creating a report for the master data.

ingo

[Former Member]

Hi Ingo:

Thank you,but as what you said,how to explain the result to me?

I tested it again a moment ago,I made the SAME webi query without measure based on the SAME universe connected to the SAME bw query ,XIR2 returned the correct result and XI3.1 returned cartesian products

I traced the mdx of xi3.1 and found there is just crossjoin without "no emptyu201D.because of the protection for the production system,I can't logon the system which the xir2 was installed on and I can't trace the mdx from it.but I am preparing to set up a test XIR2 system to compare the mdx that generates with which come from xi 3.1

Thank you and could you give me some support for the result?

Edited by: sheng yuan on Dec 31, 2010 2:28 AM

[IngoH]

Hi,

lets make it a very simple rule. if you do not include a keyfigure it will show just the master data. if you include a keyfigure then it will always ask for NON EMPTY and there is no option to influence the behavior

ingo

[Former Member]

Hi Ingo:

My test result shows the two query(from r2 and xi3.1) which does'nt include the keyfigure and r2 returns with NON EMPTY ,but xi3.1 returns without.

I think maybe you can test it and i wish in some day of the future it will be a version that has a option to choice no empty or not.

Edited by: sheng yuan on Jan 2, 2011 7:38 PM

Edited by: sheng yuan on Jan 5, 2011 3:33 AM

[IngoH]

Hi,

sorry but is not 100% clear on when you would like to do the NON EMPTY part. When you include a keyfigure you will always receive a NON EMPTY. When you not include a key figure you basically creating a "master data" style report.

Ingo

[Former Member]

So what about given an example i would like to see a sale order and what all material associated with it.

Well both of them are master tables and if we donot include any key figures it simply gives me a cross join between the table, however when we add a key figure it gives us the right dataset.

It also gives the right dataset when we manually change the mdx query and use non empty crossjoin in place of cross join

thanks

Edited by: Kavitha Rajan on Apr 22, 2009 2:45 AM

[IngoH]

Hi,

so let me paraphrase it:

you would like to see a report being created on top of a BI query using a InfoCube show a Sales Order ID with all Materials associated with it independent of the keyfigures ?

so when you run this without any keyfigures you would run a masterdata report. so whats the problem with that ?

You don't have any influence on the MDX that Web Intelligence will generate.

Ingo

[Former Member]

Thanks for your input Ingo. I am however creating a report in web intelligence using infocube as the base. Connected via integration kit and a universe. The universe is on the top of the infocube.

So when i create a web i report with two dimensions it is simply doing cross join however when we add a key figure it gives the correct data. The data which comes out of a report with two dimensions only is different then the data coming out of a bex query using the infocube as the base simply because the web i report is creating cross join (in database terminology) whereas the bex query is joining the two dimensions via the fact table to give us relevant data ( e.g. customers for a particular material)

Now when we analyzed the query coming out web i, we found out that instead of "crossjoin" if it uses "Non Empty Crossjoin" it solves the purpose.

Coming to the point the query getting generated from web i is incorrect. I was hoping to get some kind of fix if available from the forum else ask the SAP support people to provide with a hotfix or something

Thanks

[IngoH]

Hi,

I hope you are aware that you are losing several items when connecting directly to the cube. take a look here:

https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/008d15dc-f76c-2b10-968a-fafe5a12...

If you include the keyfigure you will receive the data that is in the cube. If you not including any keyfigures you will receive the masterdata.

Ingo

[roland_hoeller2]

Hi

I haven't tested this but it is worth a try:

Create a Bex query rather then using the Infocube and a new universe on top of Bex.

Add a calculated KF in the bex query that is = to 1 and add this into every report and then hide it. that would force the MDX to bring everything back although the NON EMPTY is included.

Kind Regards

Roland

[Former Member]

Kavitha,

May be you can add the key figure and hide it in the report until you find the solution.

[former_member539471]

but, not always we create universe on query only....My SAP BI guy says, there will be times when we have to report on cube.they cant create queries all the time.

what do we do in that case...?

afterall we we lose some functionalities if universe is created against cube.

any one reported only on cubes other than queries?what did u do abt those issues?

[IngoH]

when build a universe on top of a cube you will lose items : navigational attributes, variables, structures, restricted keyfigures, calculated keyfigures... because those items are not in the cube

and most important you are losing the authorization variables.

ingo

[former_member539471]

thanks ingo....

i am trying to understnad this...so in order to accomodate missing authorization variables, filter etc.....we need to do something in BO desinger......am i right?

please correct me

[IngoH]

Hi,

you can create your own calculation or filters - sure - but you can not rebuild everything from the BI query in the Universe.

example: when connecting to the cube you will not see the navigational attributes and you can not rebuild them

you can also not rebuild authorization variables

and that is why the recommendation is to connect to the BI query layer

ingo

[former_member539471]

thanks ingo,

but we will have to build reports on cube also along with queries.

what should i do then.i understand that query is recommended.but we should also be able to do reports on any multiprovider .isnt it?

What will i have to do when i am asked to achieve things with cube, when i cannot do anything about it in designer.....i am sure i will be asked to .

pls help

thnaks

[IngoH]

Hi Pali,

as mentioned in the other entry - please do not spread the same question over several entries - that means more work for people to answer you.

the recommendation is to leverage the BI query and you can build a query on top of the MultiProvider.

I gave you already several reasons why we recommend to use the Bi query in the previous response.

I would suggest you take a look at the best practice material available here at SDN to understand fully the differences between connecting to a cube and a query.

ingo