• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Different OCM Datasources and Business Views

altquark

Legendary Poster
I think we might have found a fundamental issue with OCM and conflicting Business Views. I know of this issue from in my distant past - but unsure about finding out more information. I tested this in Demo Jr as well as our Development system.

The issue is that if one places 3 or more tables in different datasources (using OCM to create the mappings of course) and then try to view those tables using a Business View - then an error appears.

If you place 2 tables in different datasources and apply a business view on the 2 tables - no error.

Since the Foundation makes many references to the ability of OneWorld to use Object Configuration Manager to place one table in one datasource - and another in a different datasource - then why is it that Business Views cannot translate the OCM information correctly ?

I think I know the answer why - and I am sure it does not affect too many people. From my initial research - out of almost 4,000 business views in Xe - there are only 100 or so business views that look at >=3 tables.

However, I am planning on performing more research - and publishing a list of table groups that should always remain in the same datasource.

Jon Steel
Xe Upgrade Specialist
erpSOURCING LLC
http://www.erpsourcing.com

ERP Sourcing
http://www.erpsourcing.com
cto@spla.sh
 

Zoltan_Gyimesi

Legendary Poster
Hi Jon,

First of all, thanks to share these really useful informations.
In the ancient time, I have also met with this problem and made me a hard head ache while I was figuring out what is the real problem and how can I resolve it.

There is a ott-00-0037 "Limits on Join Business Views" documentation on the Knowledge Garden which lists this limitations with some others. Maybe it could be also useful for the Listers.

Regards,
Zoltán
P.S.: Jon, would you be so kind as to share these informations on the Developers Forum too. As I remember, I haven't seen it yet there. Thanks.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 

cbruce

Well Known Member
Good reference to the KG Zoltan!

For best performance, it is best to keep the tables in a view in the same data source. This is because the RDBMS can manage the join. When tables are in different data sources, then JDB has to do the join itself. This is basically because JDB is the layer that makes disparate DBs talk to one another. JDB does the join by selecting each row from one table and matching it to the other tables in the join.

If you want to see an example of what I am talking about, turn on the jdedebug.log and do an access on a cross-database join. You will see many SELECT statements in the log which show the overhead of doing such a join.

I believe (though am not 100%) that in Xe, JDB loosened up the restrictions to be: if the tables in the join are on the same DB server, then the RDBMS will do the join. i.e. a DB server such as Oracle can have many OneWorld Data sources pointing to it. So even though two tables in a join are in different data sources, they could still be on the same DB server (and hence be joined by the DB server instead of JDB). Try it out and see if it works...

Cheers
 
Top