ODBO performance

[Former Member]

Hi members,

I'm trying to create some OLAP reports which are developed by "OLAP Intelligence" (product of Business Objects) as frontend. That reports access Infocube, and it might access through MDX processor (architecture of "OLE DB for OLAP").

<b>The performance of the reports are so bad.</b>

I believe the reports refer to the definition of query, and then it generates MDX statement.

But it seems the MDX includes free characteristics.

I think that's why the reports cause the performance issue.

It caused by the architecture of "QUERY_CUBE", right? (http://help.sap.com/saphelp_bw33/helpdata/en/df/ed8c3c59021315e10000000a114084/frameset.htm)

Does anyone knows how to solve this?

Thanks,

Seiichiro

P.S.

I'm NOT good at English, very sorry for your inconvenient.

[edwin_harpino · Accepted solution]

dear Seiichiro,

try to search oss note with keyword 'mdx performance' or 'odbo performance'.

730229 725761 897505 838800 640728

hope this helps.

• your english is fully understandable.

[Former Member · Accepted solution]

Hello A.H.P,

Thanks for your info.

Actually, I'd tried to search these oss notes.

So I'm gonna plan to apply support package 14-16 into the BW system.

In case of using ODBO and 3rd party tool refers the query definition, does BW ALWAYS generates dataset "QUERY_CUBE" which includes free characteristics automatically?

Is there a good way to access only "filter, row and column in query definition"?

Also do you know any case like this(performance issue of using ODBO)?

Thanks,

Seiichiro

[edwin_harpino · Accepted solution]

hi Seiichiro,

it should not carry free characteristic in sql statement, try to check your query 'read mode' with transaction rsrt -> click 'properties' -> make sure you choose the 3rd option 'H - query to read when you navigate or expand hierarchies', i think your query may have option 1 'read all...', with option 1 query will read everything when 1st execution (include the free characteristics).

if your query setting is option 1, after change to option 3 the performance should improve significantly.

normally we will create a special query dedicated for odbo using (this also due to some formula sumgt etc).

i have used odbo in abap, .net without any significant performance issue.

hope this helps.

[edwin_harpino · Accepted solution]

dear Seiichiro,

the performance problem itself may lay on the query itself, check following to improve your query performance

oss note

557870 'FAQ BW Query Performance'

and 567746 'Composite note BW 3.x performance Query and Web'.

Prakash's weblog

/people/prakash.darji/blog/2006/01/27/query-creation-checklist

/people/prakash.darji/blog/2006/01/26/query-optimization

performance docs on query

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/3f66ba90-0201-0010-ac8d-b61...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/cccad390-0201-0010-5093-fd9...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/ce7fb368-0601-0010-64ba-fad...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/c8c4d794-0501-0010-a693-918...

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/064fed90-0201-0010-13ae-b16...

BW Performance Tuning Knowledge Center - SAP Developer Network (SDN)

[original link is broken]

hope this helps.

[Former Member · Accepted solution]

Hi A.H.P,

Thanks for your many advice.

I've checked read mode in RSRT transaction.

It's "when navigate or expand hierarchies" setting.

I think QUERY_CUBE have all characteristics (incl. free char) when using ODBO through query definition. (http://help.sap.com/saphelp_bw33/helpdata/en/df/ed8c3c59021315e10000000a114084/frameset.htm)

Isn't this correct?

Should I create particular query without free characteristics in case of using ODBO?

Thanks,

Seiichiro

[edwin_harpino · Accepted solution]

dear Seiichiro,

you may cross check if query_cube will have all characteristics with rsrt option 'execute+debug' and 'display sql ...'.

i never check the odbo statement since as mentioned before, i will create a dedicated query for odbo with only necessary characteristics and keyfigures.

yes, try to create a query without free characteristics and review the performance notes and docs if still problem. hope this helps.

[Former Member · Accepted solution]

Hi A.H.P,

Thanks and appreciate your info very much.

How do I check QUERY_CUBE with RSRT using ODBO?

Do you mean "check query performance without ODBO first"?

OLAP Intelligence have the function drill down, slice, dice etc, so I think the query without free characteristics is not useful so much.

I think this performance issue may be caused by Material number in free characteristic of the query definition.

If QUERY_CUBE has the data which is expanded by all characteristics and there is no way to solve this issue, I have an option that I will connect some particular queries without free char with RRI (ex. by organization -> by customer -> by material). I can lead user operation with this way.

Thank you very much.

Seiichiro

[edwin_harpino · Accepted solution]

dear Seiichiro,

sorry i didn't aware you are using OLAP Intelligence ...

i see, you have material number with high volume data, is it from ods ? (or cube ?)

you may try create index on table /bi0/pmaterial(0material infoobject or /bic/p[material infoobject] itself and secondary index on ods itseft with sequence infoobjects most access by user (selection), please working closely with your dba in these index creation.

hope this helps.

[Former Member · Accepted solution]

Hi A.H.P,

Yes, material number is very large of volume. It's from Infocube and Multicube.

Is index creation for P-table good for this performance issue in this case?

I think it'll be better a little, anyway.

Sorry, it's short of my explanation.

The query definition as below.

Data providor...InfoCube

Column... calendar/month(filtered by variables as interval)

Row... sales organization

Free char... material number, material group, profit center, distribution channel, sales group

The result of RSRT is good when I execute the query.

Bex or Web is also nice.

But from OLAP Intelligence is so bad.

If I change to remove material number from free char, the result in case of OLAPi, becames better as same as the result of query execution with RSRT.

So, I think QUERY_CUBE has all characteristics includes free char.

Is there a way to optimize QUERY_CUBE or way without using QUERY_CUBE?

Thanks for your helps,

Seiichiro