Oracle integration/Import best practices

[Peter Felts]

OfBiz community:

I want to ask you all a best-practices question: putting aside my knowledge limitations of OfBiz and Oracle, what would be the best way to integrate my OfBiz applications with a separate Oracle data warehouse? Let me provide a little background: I am developing my own OfBiz application that handles customer orders. This application will serve as a tool to address the limitations of our current CRM software. The CRM we use is not extensible nor is it open, so the ability to add my own module is out of the question. The good news is that I have access to a data warehouse through Oracle which gives me access to all my company's data.

What I want to do is create an application what will query my company's customer database and product database. Do you think that I should run direct queries against the data warehouse, or should I create some type of automated import to import the data warehouse data into custom OfBiz entities daily?

Any advise you can provide as to the direction I should look will be very appreciated.

[Jacques Le Roux]

If I understand you well, you don't have the opportunity to use ESB tools like Apache ServiceMix or Mule  ?
I you only have access to the Oracle DB, I will tend to say that you should avoid importing and should rather use queries. But
that's not really an advice just my opinion, my 2 cents if you like.

Jacques

From: "Peter Felts" <[hidden email]>

> OfBiz community:
>
> I want to ask you all a best-practices question: putting aside my knowledge limitations of OfBiz and Oracle, what would be the
> best way to integrate my OfBiz applications with a separate Oracle data warehouse? Let me provide a little background: I am
> developing my own OfBiz application that handles customer orders. This application will serve as a tool to address the limitations
> of our current CRM software. The CRM we use is not extensible nor is it open, so the ability to add my own module is out of the
> question. The good news is that I have access to a data warehouse through Oracle which gives me access to all my company's data.
>
> What I want to do is create an application what will query my company's customer database and product database. Do you think that
> I should run direct queries against the data warehouse, or should I create some type of automated import to import the data
> warehouse data into custom OfBiz entities daily?
>
> Any advise you can provide as to the direction I should look will be very appreciated.

[Peter Felts]

I have actually been having very good luck so far with what  Adrian Crum suggested earlier today in this thread.I created another group-map for my legacy Oracle database which is offsite. I'm going to have to create entities for each element of my legacy database that I want to interact with of course, but that is OK with me as long as I can get OfBiz to communicate with my legacy system.




----- Original Message ----
From: Jacques Le Roux <[hidden email]>
To: [hidden email]
Sent: Thursday, March 13, 2008 3:07:16 PM
Subject: Re: Oracle integration/Import best practices

 If  I  understand  you  well,  you  don't  have  the  opportunity  to  use  ESB  tools  like  Apache  ServiceMix  or  Mule   ?
I  you  only  have  access  to  the  Oracle  DB,  I  will  tend  to  say  that  you  should  avoid  importing  and  should  rather  use  queries.  But
that's  not  really  an  advice  just  my  opinion,  my  2  cents  if  you  like.

Jacques

From:  "Peter  Felts"  <[hidden email]>

>  OfBiz  community:
>
>  I  want  to  ask  you  all  a  best-practices  question:  putting  aside  my  knowledge  limitations  of  OfBiz  and  Oracle,  what  would  be  the
>  best  way  to  integrate  my  OfBiz  applications  with  a  separate  Oracle  data  warehouse?  Let  me  provide  a  little  background:  I  am
>  developing  my  own  OfBiz  application  that  handles  customer  orders.  This  application  will  serve  as  a  tool  to  address  the  limitations
>  of  our  current  CRM  software.  The  CRM  we  use  is  not  extensible  nor  is  it  open,  so  the  ability  to  add  my  own  module  is  out  of  the
>  question.  The  good  news  is  that  I  have  access  to  a  data  warehouse  through  Oracle  which  gives  me  access  to  all  my  company's  data.
>
>  What  I  want  to  do  is  create  an  application  what  will  query  my  company's  customer  database  and  product  database.  Do  you  think  that
>  I  should  run  direct  queries  against  the  data  warehouse,  or  should  I  create  some  type  of  automated  import  to  import  the  data
>  warehouse  data  into  custom  OfBiz  entities  daily?
>
>  Any  advise  you  can  provide  as  to  the  direction  I  should  look  will  be  very  appreciated.

[Adrian Crum]

Another tip for you, (not sure if it will be useful) is that OFBiz View
Entities don't actually create database views - they simulate a database
view by doing JOINs. So, you can build views in OFBiz that won't affect
the structure of your Oracle database.

-Adrian

Peter Felts wrote:

> I have actually been having very good luck so far with what  Adrian Crum suggested earlier today in this thread.I created another group-map for my legacy Oracle database which is offsite. I'm going to have to create entities for each element of my legacy database that I want to interact with of course, but that is OK with me as long as I can get OfBiz to communicate with my legacy system.
>
>
>
>
> ----- Original Message ----
> From: Jacques Le Roux <[hidden email]>
> To: [hidden email]
> Sent: Thursday, March 13, 2008 3:07:16 PM
> Subject: Re: Oracle integration/Import best practices
>
>  If  I  understand  you  well,  you  don't  have  the  opportunity  to  use  ESB  tools  like  Apache  ServiceMix  or  Mule   ?
> I  you  only  have  access  to  the  Oracle  DB,  I  will  tend  to  say  that  you  should  avoid  importing  and  should  rather  use  queries.  But
> that's  not  really  an  advice  just  my  opinion,  my  2  cents  if  you  like.
>
> Jacques
>
> From:  "Peter  Felts"  <[hidden email]>
>>  OfBiz  community:
>>
>>  I  want  to  ask  you  all  a  best-practices  question:  putting  aside  my  knowledge  limitations  of  OfBiz  and  Oracle,  what  would  be  the
>>  best  way  to  integrate  my  OfBiz  applications  with  a  separate  Oracle  data  warehouse?  Let  me  provide  a  little  background:  I  am
>>  developing  my  own  OfBiz  application  that  handles  customer  orders.  This  application  will  serve  as  a  tool  to  address  the  limitations
>>  of  our  current  CRM  software.  The  CRM  we  use  is  not  extensible  nor  is  it  open,  so  the  ability  to  add  my  own  module  is  out  of  the
>>  question.  The  good  news  is  that  I  have  access  to  a  data  warehouse  through  Oracle  which  gives  me  access  to  all  my  company's  data.
>>
>>  What  I  want  to  do  is  create  an  application  what  will  query  my  company's  customer  database  and  product  database.  Do  you  think  that
>>  I  should  run  direct  queries  against  the  data  warehouse,  or  should  I  create  some  type  of  automated  import  to  import  the  data
>>  warehouse  data  into  custom  OfBiz  entities  daily?
>>
>>  Any  advise  you  can  provide  as  to  the  direction  I  should  look  will  be  very  appreciated.
>
>
>
>
>
>
>

[Peter Felts]

I see what you mean about the no-auto-stamp property in the externaltable entity definitions. Because these definitions were not created byOFBiz the date-updated-related fields are missing which confusesOfBiz...

----- Original Message ----
From: Adrian Crum <[hidden email]>
To: [hidden email]
Sent: Thursday, March 13, 2008 4:15:11 PM
Subject: Re: Oracle integration/Import best practices

Another tip for you, (not sure if it will be useful) is that OFBiz View
Entities don't actually create database views - they simulate a database
view by doing JOINs. So, you can build views in OFBiz that won't affect
the structure of your Oracle database.

-Adrian

Peter Felts wrote:

> I have actually been having very good luck so far with what  Adrian Crum suggested earlier today in this thread.I created another group-map for my legacy Oracle database which is offsite. I'm going to have to create entities for each element of my legacy database that I want to interact with of course, but that is OK with me as long as I can get OfBiz to communicate with my legacy system.
>
>
>
>
> ----- Original Message ----
> From: Jacques Le Roux <[hidden email]>
> To: [hidden email]
> Sent: Thursday, March 13, 2008 3:07:16 PM
> Subject: Re: Oracle integration/Import best practices
>
>  If  I  understand  you  well,  you  don't  have  the  opportunity  to  use  ESB  tools  like  Apache  ServiceMix  or  Mule   ?
> I  you  only  have  access  to  the  Oracle  DB,  I  will  tend  to  say  that  you  should  avoid  importing  and  should  rather  use  queries.  But
> that's  not  really  an  advice  just  my  opinion,  my  2  cents  if  you  like.
>
> Jacques
>
> From:  "Peter  Felts"  <[hidden email]>
>>  OfBiz  community:
>>
>>  I  want  to  ask  you  all  a  best-practices  question:  putting  aside  my  knowledge  limitations  of  OfBiz  and  Oracle,  what  would  be  the
>>  best  way  to  integrate  my  OfBiz  applications  with  a  separate  Oracle  data  warehouse?  Let  me  provide  a  little  background:  I  am
>>  developing  my  own  OfBiz  application  that  handles  customer  orders.  This  application  will  serve  as  a  tool  to  address  the  limitations
>>  of  our  current  CRM  software.  The  CRM  we  use  is  not  extensible  nor  is  it  open,  so  the  ability  to  add  my  own  module  is  out  of  the
>>  question.  The  good  news  is  that  I  have  access  to  a  data  warehouse  through  Oracle  which  gives  me  access  to  all  my  company's  data.
>>
>>  What  I  want  to  do  is  create  an  application  what  will  query  my  company's  customer  database  and  product  database.  Do  you  think  that
>>  I  should  run  direct  queries  against  the  data  warehouse,  or  should  I  create  some  type  of  automated  import  to  import  the  data
>>  warehouse  data  into  custom  OfBiz  entities  daily?
>>
>>  Any  advise  you  can  provide  as  to  the  direction  I  should  look  will  be  very  appreciated.
>
>
>
>
>
>
>

[Peter Felts]

Now that I have connected to my legacy database, I wonder if there is a way to tell OFBiz that it can only read from this database, and that all entities related to this schema are read-only...

Also, can anyone explain what the following two entities do?
    SequenceValueItem
    EntityKeyStore

[Jacopo Cappellato]

Peter Felts wrote:
> Now that I have connected to my legacy database, I wonder if there is a way to tell OFBiz that it can only read from this database, and that all entities related to this schema are read-only...
>

In the datasource settings (in entityengine.xml), you can just use a db
username with readonly access.

> Also, can anyone explain what the following two entities do?
>     SequenceValueItem

This is a central entity used to assign unique pks to many of the
entities in ofbiz.

>     EntityKeyStore

Hmmm... at the momemnt I don't remember the meaning of this (and I don't
have a chance to look at the code), sorry.

Jacopo

>
>

[Jacques Le Roux]

EntityKeyStore is used in EntityCrypto

Jacques

From: "Jacopo Cappellato" <[hidden email]>

> Peter Felts wrote:
>> Now that I have connected to my legacy database, I wonder if there is a way to tell OFBiz that it can only read from this
>> database, and that all entities related to this schema are read-only...
>>
>
> In the datasource settings (in entityengine.xml), you can just use a db username with readonly access.
>
>> Also, can anyone explain what the following two entities do?
>>     SequenceValueItem
>
> This is a central entity used to assign unique pks to many of the entities in ofbiz.
>
>>     EntityKeyStore
>
> Hmmm... at the momemnt I don't remember the meaning of this (and I don't have a chance to look at the code), sorry.
>
> Jacopo
>
>>
>>
>