OFBiz › OFBiz - User

Why are the OLAP and TENANT separate databases?

_‹ Previous Topic Next Topic _›

Classic

List

Threaded

8 messages Options

Mike Z

Why are the OLAP and TENANT separate databases?

Here is something that has always bugged me about the OFBiz setup, and I
was hoping that an OFBiz old-timer can please explain why the above
databases, by default, are configured as separate databases.

I can see, in theory, why TENANT is separate.

In the entityengine.xml, olap and tenant are separate databases. Is there
any reason why the tables configured by these two databases, during initial
deployment, can't be incorporated into the main ofbiz database?

OLAP tables:
public | currency_dimension | table | bigfish
public | date_dimension | table | bigfish
public | inventory_item_fact | table | bigfish
public | product_dimension | table | bigfish
public | sales_invoice_item_fact | table | bigfish
public | sales_order_item_fact | table | bigfish

TENANT tables:
public | tenant | table | bigfish
public | tenant_data_source | table | bigfish

The reason I ask is when you are configuring the DB as external to the
running OFBiz java process (not 127.0.0.1, and NOT derby), you now have to
create and maintain 3 databases if you want to externalize the DB. I would
rather just have to worry about a single database to replicate and backup.

The multi-tenant feature of OFBiz is rather an advanced feature that most
folks are not going to use, and in the event of going that route
(multi-tenant), I could then externalize this database if necessary.

Regarding OLAP, and why it is defined as a separate database is a real
mystery to me. Can anyone please explain why it MAY be dangerous to merge
the above tables into the main OFBiz database?

Thanks

BJ Freeman

Re: Why are the OLAP and TENANT separate databases?

they are not related.
olap has to do with data mining of what ever you want
it is meant for reporting.
http://en.wikipedia.org/wiki/OLAP_cube

Tenant has to do with multiple separate DBs using the same code base.

Mike sent the following on 6/26/2012 9:10 AM:

> Here is something that has always bugged me about the OFBiz setup, and I
> was hoping that an OFBiz old-timer can please explain why the above
> databases, by default, are configured as separate databases.
>
> I can see, in theory, why TENANT is separate.
>
> In the entityengine.xml, olap and tenant are separate databases. Is there
> any reason why the tables configured by these two databases, during initial
> deployment, can't be incorporated into the main ofbiz database?
>
> OLAP tables:
> public | currency_dimension | table | bigfish
> public | date_dimension | table | bigfish
> public | inventory_item_fact | table | bigfish
> public | product_dimension | table | bigfish
> public | sales_invoice_item_fact | table | bigfish
> public | sales_order_item_fact | table | bigfish
>
> TENANT tables:
> public | tenant | table | bigfish
> public | tenant_data_source | table | bigfish
>
> The reason I ask is when you are configuring the DB as external to the
> running OFBiz java process (not 127.0.0.1, and NOT derby), you now have to
> create and maintain 3 databases if you want to externalize the DB. I would
> rather just have to worry about a single database to replicate and backup.
>
> The multi-tenant feature of OFBiz is rather an advanced feature that most
> folks are not going to use, and in the event of going that route
> (multi-tenant), I could then externalize this database if necessary.
>
> Regarding OLAP, and why it is defined as a separate database is a real
> mystery to me. Can anyone please explain why it MAY be dangerous to merge
> the above tables into the main OFBiz database?
>
> Thanks
>

Jacopo Cappellato-4

Re: Why are the OLAP and TENANT separate databases?

In reply to this post by Mike Z

Mike,

there are several good reasons for maintaining a separate olap database; I am pretty sure that you will find plenty of information on the web.
However, in the process of making the codebase slimmer and easier to maintain and configure, we have a plan to pull out some of the components from the framework (and from the core distribution of OFBiz) and let them evolve as pluggable and optional components; the "bi" component and the "birt" components are probably the next ones that will be moved out; at that point, if you are not interested in these datawarehouse features and if you will use the default distribution, you will not have to worry about the additional database.
As regards multitenancy implemantation, it will probably undergo under some serious architectural review, but I can't tell you, at this point, what will be the output of this.

Kind regards,

Jacopo

On Jun 26, 2012, at 6:10 PM, Mike wrote:

Jacques Le Roux

Re: Why are the OLAP and TENANT separate databases?

Administrator

In reply to this post by Mike Z

There are mostly 3 ways to implement multitenant
Separate Databases
Shared Database, Separate Schemas
Shared Database, Shared Schema
http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_topic2

OFBiz uses the 1st. So with this way you will have as much as tenant DBs as you introduce tenants. Bright side is explained in the
document I referred above.

Jacques

From: "Mike" <[hidden email]>

Mike Z

Re: Why are the OLAP and TENANT separate databases?

In reply to this post by Jacopo Cappellato-4

Thanks for the responses and clarifications. It sounds like that it's not
a big deal if these are combined temporarily.

BJ, I thought for sure that if there is no OLAP database then the ecommerce
module produces errors.

However, it does help when you continually blow away the database and
reload it [grin].

On Tue, Jun 26, 2012 at 9:34 AM, Jacopo Cappellato <
[hidden email]> wrote:

> Mike,
>
> there are several good reasons for maintaining a separate olap database; I
> am pretty sure that you will find plenty of information on the web.
> However, in the process of making the codebase slimmer and easier to
> maintain and configure, we have a plan to pull out some of the components
> from the framework (and from the core distribution of OFBiz) and let them
> evolve as pluggable and optional components; the "bi" component and the
> "birt" components are probably the next ones that will be moved out; at
> that point, if you are not interested in these datawarehouse features and
> if you will use the default distribution, you will not have to worry about
> the additional database.
> As regards multitenancy implemantation, it will probably undergo under
> some serious architectural review, but I can't tell you, at this point,
> what will be the output of this.
>
> Kind regards,
>
> Jacopo
>
> On Jun 26, 2012, at 6:10 PM, Mike wrote:
>
> > Here is something that has always bugged me about the OFBiz setup, and I
> > was hoping that an OFBiz old-timer can please explain why the above
> > databases, by default, are configured as separate databases.
> >
> > I can see, in theory, why TENANT is separate.
> >
> > In the entityengine.xml, olap and tenant are separate databases. Is
> there
> > any reason why the tables configured by these two databases, during
> initial
> > deployment, can't be incorporated into the main ofbiz database?
> >
> > OLAP tables:
> > public | currency_dimension | table | bigfish
> > public | date_dimension | table | bigfish
> > public | inventory_item_fact | table | bigfish
> > public | product_dimension | table | bigfish
> > public | sales_invoice_item_fact | table | bigfish
> > public | sales_order_item_fact | table | bigfish
> >
> > TENANT tables:
> > public | tenant | table | bigfish
> > public | tenant_data_source | table | bigfish
> >
> > The reason I ask is when you are configuring the DB as external to the
> > running OFBiz java process (not 127.0.0.1, and NOT derby), you now have
> to
> > create and maintain 3 databases if you want to externalize the DB. I
> would
> > rather just have to worry about a single database to replicate and
> backup.
> >
> > The multi-tenant feature of OFBiz is rather an advanced feature that most
> > folks are not going to use, and in the event of going that route
> > (multi-tenant), I could then externalize this database if necessary.
> >
> > Regarding OLAP, and why it is defined as a separate database is a real
> > mystery to me. Can anyone please explain why it MAY be dangerous to
> merge
> > the above tables into the main OFBiz database?
> >
> > Thanks
>
>

BJ Freeman

Re: Why are the OLAP and TENANT separate databases?

if you don't use olap, then you can deacivate the ECA(s) that triggers
warehousing data.
In my caee the BI DB is on a seperate server I have a special app that
is used with it before ofbiz.

Mike sent the following on 6/26/2012 2:02 PM:
> Thanks for the responses and clarifications. It sounds like that it's not
> a big deal if these are combined temporarily.

> BJ, I thought for sure that if there is no OLAP database then the ecommerce
> module produces errors.
>
> However, it does help when you continually blow away the database and
> reload it [grin].
>
> On Tue, Jun 26, 2012 at 9:34 AM, Jacopo Cappellato<
> [hidden email]> wrote:
>
>> Mike,
>>
>> there are several good reasons for maintaining a separate olap database; I
>> am pretty sure that you will find plenty of information on the web.
>> However, in the process of making the codebase slimmer and easier to
>> maintain and configure, we have a plan to pull out some of the components
>> from the framework (and from the core distribution of OFBiz) and let them
>> evolve as pluggable and optional components; the "bi" component and the
>> "birt" components are probably the next ones that will be moved out; at
>> that point, if you are not interested in these datawarehouse features and
>> if you will use the default distribution, you will not have to worry about
>> the additional database.
>> As regards multitenancy implemantation, it will probably undergo under
>> some serious architectural review, but I can't tell you, at this point,
>> what will be the output of this.
>>
>> Kind regards,
>>
>> Jacopo
>>
>> On Jun 26, 2012, at 6:10 PM, Mike wrote:
>>
>>> Here is something that has always bugged me about the OFBiz setup, and I
>>> was hoping that an OFBiz old-timer can please explain why the above
>>> databases, by default, are configured as separate databases.
>>>
>>> I can see, in theory, why TENANT is separate.
>>>
>>> In the entityengine.xml, olap and tenant are separate databases. Is
>> there
>>> any reason why the tables configured by these two databases, during
>> initial
>>> deployment, can't be incorporated into the main ofbiz database?
>>>
>>> OLAP tables:
>>> public | currency_dimension | table | bigfish
>>> public | date_dimension | table | bigfish
>>> public | inventory_item_fact | table | bigfish
>>> public | product_dimension | table | bigfish
>>> public | sales_invoice_item_fact | table | bigfish
>>> public | sales_order_item_fact | table | bigfish
>>>
>>> TENANT tables:
>>> public | tenant | table | bigfish
>>> public | tenant_data_source | table | bigfish
>>>
>>> The reason I ask is when you are configuring the DB as external to the
>>> running OFBiz java process (not 127.0.0.1, and NOT derby), you now have
>> to
>>> create and maintain 3 databases if you want to externalize the DB. I
>> would
>>> rather just have to worry about a single database to replicate and
>> backup.
>>>
>>> The multi-tenant feature of OFBiz is rather an advanced feature that most
>>> folks are not going to use, and in the event of going that route
>>> (multi-tenant), I could then externalize this database if necessary.
>>>
>>> Regarding OLAP, and why it is defined as a separate database is a real
>>> mystery to me. Can anyone please explain why it MAY be dangerous to
>> merge
>>> the above tables into the main OFBiz database?
>>>
>>> Thanks
>>
>>
>

Balasubramaniam Mohan

Re: Why are the OLAP and TENANT separate databases?

IMHO based on experience, it is better the OLTP (online transaction
processing) and OLAP (Online Analytical Processing) dbs remain separate and
it has been well thought out in the case of ofbiz for the following valid
reasons.

- OLTP dbs serve the transactional nature of business (orders, invoices,
etc) and requires very rapid response time for a large set of users.
Whereas OLAP is exploratory/discovery/predictive based and often results in
huge data sets being processed for analytics purposes.

- most importantly the way the data model is done for OLTP and OLAP are
quiet different while the former is highly normalised (3NF) the later is
denormalised (dimensional-star schema).

- Traditionally for fast response to a large set of users historical data
(say 10 years) is not stored in the OLTP. It is transferred to an archive
from where the OLAP uses for any kind analytics purposes.

For more information on the same, please take a close look at the url at
http://datawarehouse4u.info/OLTP-vs-OLAP.html

Hope this helps.

Regards
On Wed, Jun 27, 2012 at 6:59 AM, BJ Freeman <[hidden email]> wrote:

> if you don't use olap, then you can deacivate the ECA(s) that triggers
> warehousing data.
> In my caee the BI DB is on a seperate server I have a special app that is
> used with it before ofbiz.
>
> Mike sent the following on 6/26/2012 2:02 PM:
>
> Thanks for the responses and clarifications. It sounds like that it's not
>> a big deal if these are combined temporarily.
>>
>
> BJ, I thought for sure that if there is no OLAP database then the
>> ecommerce
>> module produces errors.
>>
>> However, it does help when you continually blow away the database and
>> reload it [grin].
>>
>> On Tue, Jun 26, 2012 at 9:34 AM, Jacopo Cappellato<
>> jacopo.cappellato@hotwaxmedia.**com <[hidden email]>>
>> wrote:
>>
>> Mike,
>>>
>>> there are several good reasons for maintaining a separate olap database;
>>> I
>>> am pretty sure that you will find plenty of information on the web.
>>> However, in the process of making the codebase slimmer and easier to
>>> maintain and configure, we have a plan to pull out some of the components
>>> from the framework (and from the core distribution of OFBiz) and let them
>>> evolve as pluggable and optional components; the "bi" component and the
>>> "birt" components are probably the next ones that will be moved out; at
>>> that point, if you are not interested in these datawarehouse features and
>>> if you will use the default distribution, you will not have to worry
>>> about
>>> the additional database.
>>> As regards multitenancy implemantation, it will probably undergo under
>>> some serious architectural review, but I can't tell you, at this point,
>>> what will be the output of this.
>>>
>>> Kind regards,
>>>
>>> Jacopo
>>>
>>> On Jun 26, 2012, at 6:10 PM, Mike wrote:
>>>
>>> Here is something that has always bugged me about the OFBiz setup, and I
>>>> was hoping that an OFBiz old-timer can please explain why the above
>>>> databases, by default, are configured as separate databases.
>>>>
>>>> I can see, in theory, why TENANT is separate.
>>>>
>>>> In the entityengine.xml, olap and tenant are separate databases. Is
>>>>
>>> there
>>>
>>>> any reason why the tables configured by these two databases, during
>>>>
>>> initial
>>>
>>>> deployment, can't be incorporated into the main ofbiz database?
>>>>
>>>> OLAP tables:
>>>> public | currency_dimension | table | bigfish
>>>> public | date_dimension | table | bigfish
>>>> public | inventory_item_fact | table | bigfish
>>>> public | product_dimension | table | bigfish
>>>> public | sales_invoice_item_fact | table | bigfish
>>>> public | sales_order_item_fact | table | bigfish
>>>>
>>>> TENANT tables:
>>>> public | tenant | table | bigfish
>>>> public | tenant_data_source | table | bigfish
>>>>
>>>> The reason I ask is when you are configuring the DB as external to the
>>>> running OFBiz java process (not 127.0.0.1, and NOT derby), you now have
>>>>
>>> to
>>>
>>>> create and maintain 3 databases if you want to externalize the DB. I
>>>>
>>> would
>>>
>>>> rather just have to worry about a single database to replicate and
>>>>
>>> backup.
>>>
>>>>
>>>> The multi-tenant feature of OFBiz is rather an advanced feature that
>>>> most
>>>> folks are not going to use, and in the event of going that route
>>>> (multi-tenant), I could then externalize this database if necessary.
>>>>
>>>> Regarding OLAP, and why it is defined as a separate database is a real
>>>> mystery to me. Can anyone please explain why it MAY be dangerous to
>>>>
>>> merge
>>>
>>>> the above tables into the main OFBiz database?
>>>>
>>>> Thanks
>>>>
>>>
>>>
>>>
>>

Mike Z

Re: Why are the OLAP and TENANT separate databases?

Awesome reference on OLTP & OLAP, and data warehousing in general. Thanks.

On Tue, Jun 26, 2012 at 11:51 PM, Balasubramaniam Mohan
<[hidden email]>wrote:

> IMHO based on experience, it is better the OLTP (online transaction
> processing) and OLAP (Online Analytical Processing) dbs remain separate and
> it has been well thought out in the case of ofbiz for the following valid
> reasons.
>
> - OLTP dbs serve the transactional nature of business (orders, invoices,
> etc) and requires very rapid response time for a large set of users.
> Whereas OLAP is exploratory/discovery/predictive based and often results in
> huge data sets being processed for analytics purposes.
>
> - most importantly the way the data model is done for OLTP and OLAP are
> quiet different while the former is highly normalised (3NF) the later is
> denormalised (dimensional-star schema).
>
> - Traditionally for fast response to a large set of users historical data
> (say 10 years) is not stored in the OLTP. It is transferred to an archive
> from where the OLAP uses for any kind analytics purposes.
>
> For more information on the same, please take a close look at the url at
> http://datawarehouse4u.info/OLTP-vs-OLAP.html
>
> Hope this helps.
>
> Regards
> On Wed, Jun 27, 2012 at 6:59 AM, BJ Freeman <[hidden email]> wrote:
>
> > if you don't use olap, then you can deacivate the ECA(s) that triggers
> > warehousing data.
> > In my caee the BI DB is on a seperate server I have a special app that is
> > used with it before ofbiz.
> >
> > Mike sent the following on 6/26/2012 2:02 PM:
> >
> > Thanks for the responses and clarifications. It sounds like that it's
> not
> >> a big deal if these are combined temporarily.
> >>
> >
> > BJ, I thought for sure that if there is no OLAP database then the
> >> ecommerce
> >> module produces errors.
> >>
> >> However, it does help when you continually blow away the database and
> >> reload it [grin].
> >>
> >> On Tue, Jun 26, 2012 at 9:34 AM, Jacopo Cappellato<
> >> jacopo.cappellato@hotwaxmedia.**com <[hidden email]
> >>
> >> wrote:
> >>
> >> Mike,
> >>>
> >>> there are several good reasons for maintaining a separate olap
> database;
> >>> I
> >>> am pretty sure that you will find plenty of information on the web.
> >>> However, in the process of making the codebase slimmer and easier to
> >>> maintain and configure, we have a plan to pull out some of the
> components
> >>> from the framework (and from the core distribution of OFBiz) and let
> them
> >>> evolve as pluggable and optional components; the "bi" component and the
> >>> "birt" components are probably the next ones that will be moved out; at
> >>> that point, if you are not interested in these datawarehouse features
> and
> >>> if you will use the default distribution, you will not have to worry
> >>> about
> >>> the additional database.
> >>> As regards multitenancy implemantation, it will probably undergo under
> >>> some serious architectural review, but I can't tell you, at this point,
> >>> what will be the output of this.
> >>>
> >>> Kind regards,
> >>>
> >>> Jacopo
> >>>
> >>> On Jun 26, 2012, at 6:10 PM, Mike wrote:
> >>>
> >>> Here is something that has always bugged me about the OFBiz setup,
> and I
> >>>> was hoping that an OFBiz old-timer can please explain why the above
> >>>> databases, by default, are configured as separate databases.
> >>>>
> >>>> I can see, in theory, why TENANT is separate.
> >>>>
> >>>> In the entityengine.xml, olap and tenant are separate databases. Is
> >>>>
> >>> there
> >>>
> >>>> any reason why the tables configured by these two databases, during
> >>>>
> >>> initial
> >>>
> >>>> deployment, can't be incorporated into the main ofbiz database?
> >>>>
> >>>> OLAP tables:
> >>>> public | currency_dimension | table | bigfish
> >>>> public | date_dimension | table | bigfish
> >>>> public | inventory_item_fact | table | bigfish
> >>>> public | product_dimension | table | bigfish
> >>>> public | sales_invoice_item_fact | table | bigfish
> >>>> public | sales_order_item_fact | table | bigfish
> >>>>
> >>>> TENANT tables:
> >>>> public | tenant | table | bigfish
> >>>> public | tenant_data_source | table | bigfish
> >>>>
> >>>> The reason I ask is when you are configuring the DB as external to the
> >>>> running OFBiz java process (not 127.0.0.1, and NOT derby), you now
> have
> >>>>
> >>> to
> >>>
> >>>> create and maintain 3 databases if you want to externalize the DB. I
> >>>>
> >>> would
> >>>
> >>>> rather just have to worry about a single database to replicate and
> >>>>
> >>> backup.
> >>>
> >>>>
> >>>> The multi-tenant feature of OFBiz is rather an advanced feature that
> >>>> most
> >>>> folks are not going to use, and in the event of going that route
> >>>> (multi-tenant), I could then externalize this database if necessary.
> >>>>
> >>>> Regarding OLAP, and why it is defined as a separate database is a real
> >>>> mystery to me. Can anyone please explain why it MAY be dangerous to
> >>>>
> >>> merge
> >>>
> >>>> the above tables into the main OFBiz database?
> >>>>
> >>>> Thanks
> >>>>
> >>>
> >>>
> >>>
> >>
>