BI Component Improvements

[Pierre Smits-3]

Following up on recent migrations from minilang to groovy regarding the
functions in the BI component I have made various enhancements to the
component, enabling the project to move from a Proof of Concept state to
something that is starting to provide our (potential) adopters some
additional arguments to use (or select) our product.

BI (or Business Intelligence) is a key factor to measure the success of a
company (and its underlying departments), whether it is regarding sales,
purchases, inventory ageing, or employer utilisation (e.g. in
project-oriented service orgs). BI often works against a Data Warehouse,
which is fed from the transactions RDBMS using (more or less) sophisticated
ETL solutions. Often these ETL tools are quite expensive to operate, and
require - when talking about development - quite the skill set.
Unfortunately, Small and Medium Enterprises often lack the budget to deploy
such tools and services and make due with exports from the transaction
RDBMS and the use of MS Excel to do their Business Intelligence analysis.

Given the unique feature set of OFBiz catering to the needs of small and
medium enterprises, an improved BI component can deliver on an easy (and
fairly cheap) means to have a DataWarehouse populated, and - through the
population of dimension and fact tables - get data elements combined into
star schemas for in-component or external (through external tools as Excel)
analysis.

In order to bring the BI component up to a level of usability in a company,
I have implemented following changes (in a feature branch in my repo)

   - added and updated dimension tables
      - CustomerDimension, OrganisationDimension (internal organisations),
      and SupplierDimension
      - supporting: DateDimension, TimeDimension, CountryDimension,
      CurrencyDimension
   - updated fact tables to work with the added and improved dimension
   tables
      - SalesInvoiceItemFact
      - InventoryItemFact
   - updated star schemas to bring data elements from dimension and fact
   tables together
      - SalesInvoiceItemStarSchema
      - InventoryItemStarSchema
   - made the Report Builder functionality (screens and such) mode user
   friendly, by applying ajax functionality,  to have selection of the star
   schema, the field selection and the resulting overview in one page

While there are still a lot of areas (BI-wise) that can be improved, such
as:

   - SalesOrderItemFact and SalesOrderItemStarSchema - to enhance the
   analysis regarding sales,
   - Purchase (Spend) analysis functionality,
   - Utilisation analysis function (assets, employee),

enhancements currently available improve the BI component significantly.

Rather than working alone and see contributions go stale, I would like to
collaborate more via a feature branch in the official plugins repository.
As you can see in JIRA, there already quite a number of improvement tickets
(seeu [1]), and I have already many (up to the point of ready for
incorporation into the plugins code base)  in progress. The improvements
are currently available in my public fork on Github (see [2]). I invite you
to fork or clone and test-drive the enhancements.

And if you have any suggestions, questions, and/or remarks, feel free to
post them here (or directly to me), or as a comment to the master ticket
(for general aspects) or to a particular sub-task.

   1. https://issues.apache.org/jira/browse/OFBIZ-11414
   2.
   https://github.com/PierreSmits/plugins/tree/OFBIZ-11414_BI-Improvements


Looking forward.

Met vriendelijke groet,

Pierre Smits
*Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/> since
2008 (without privileges)

*Apache Trafodion <https://trafodion.apache.org>, Vice President*
*Apache Directory <https://directory.apache.org>, PMC Member*
Apache Incubator <https://incubator.apache.org>, committer
Apache Steve <https://steve.apache.org>, committer

[taher]

Hello and thank you for your work. A few review notes:

- It would be great if you can briefly explain the new dimensions and
what you're triangulating on
- Can you elaborate on the purpose for having time dimensions values
for every minute? What is the data being aggregated here?
- There is some stramge logic in DimensionServices.groovy where
essentially you're clearing the input map (effectively passing an
empty map) to a bunch of service calls. Why are you using the same map
which you keep clearing from one call to the next? Are those services
not supposed to take input parameters? If so, why not pass an empty
map instead of this logic?
- There are some files with windows EOL characters. Need to run
something like dos2unix on those
- There are also tab characters around the code base

May I also suggest breaking this work into smaller chunks to start
incorporating into the code base to make it easier to review and
integrate the work. Perhaps you can isolate the work for each new
dimension in a branch / patch / etc

On Mon, Mar 16, 2020 at 6:09 PM Pierre Smits <[hidden email]> wrote:

>
> Following up on recent migrations from minilang to groovy regarding the
> functions in the BI component I have made various enhancements to the
> component, enabling the project to move from a Proof of Concept state to
> something that is starting to provide our (potential) adopters some
> additional arguments to use (or select) our product.
>
> BI (or Business Intelligence) is a key factor to measure the success of a
> company (and its underlying departments), whether it is regarding sales,
> purchases, inventory ageing, or employer utilisation (e.g. in
> project-oriented service orgs). BI often works against a Data Warehouse,
> which is fed from the transactions RDBMS using (more or less) sophisticated
> ETL solutions. Often these ETL tools are quite expensive to operate, and
> require - when talking about development - quite the skill set.
> Unfortunately, Small and Medium Enterprises often lack the budget to deploy
> such tools and services and make due with exports from the transaction
> RDBMS and the use of MS Excel to do their Business Intelligence analysis.
>
> Given the unique feature set of OFBiz catering to the needs of small and
> medium enterprises, an improved BI component can deliver on an easy (and
> fairly cheap) means to have a DataWarehouse populated, and - through the
> population of dimension and fact tables - get data elements combined into
> star schemas for in-component or external (through external tools as Excel)
> analysis.
>
> In order to bring the BI component up to a level of usability in a company,
> I have implemented following changes (in a feature branch in my repo)
>
>    - added and updated dimension tables
>       - CustomerDimension, OrganisationDimension (internal organisations),
>       and SupplierDimension
>       - supporting: DateDimension, TimeDimension, CountryDimension,
>       CurrencyDimension
>    - updated fact tables to work with the added and improved dimension
>    tables
>       - SalesInvoiceItemFact
>       - InventoryItemFact
>    - updated star schemas to bring data elements from dimension and fact
>    tables together
>       - SalesInvoiceItemStarSchema
>       - InventoryItemStarSchema
>    - made the Report Builder functionality (screens and such) mode user
>    friendly, by applying ajax functionality,  to have selection of the star
>    schema, the field selection and the resulting overview in one page
>
> While there are still a lot of areas (BI-wise) that can be improved, such
> as:
>
>    - SalesOrderItemFact and SalesOrderItemStarSchema - to enhance the
>    analysis regarding sales,
>    - Purchase (Spend) analysis functionality,
>    - Utilisation analysis function (assets, employee),
>
> enhancements currently available improve the BI component significantly.
>
> Rather than working alone and see contributions go stale, I would like to
> collaborate more via a feature branch in the official plugins repository.
> As you can see in JIRA, there already quite a number of improvement tickets
> (seeu [1]), and I have already many (up to the point of ready for
> incorporation into the plugins code base)  in progress. The improvements
> are currently available in my public fork on Github (see [2]). I invite you
> to fork or clone and test-drive the enhancements.
>
> And if you have any suggestions, questions, and/or remarks, feel free to
> post them here (or directly to me), or as a comment to the master ticket
> (for general aspects) or to a particular sub-task.
>
>    1. https://issues.apache.org/jira/browse/OFBIZ-11414
>    2.
>    https://github.com/PierreSmits/plugins/tree/OFBIZ-11414_BI-Improvements
>
>
> Looking forward.
>
> Met vriendelijke groet,
>
> Pierre Smits
> *Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/> since
> 2008 (without privileges)
>
> *Apache Trafodion <https://trafodion.apache.org>, Vice President*
> *Apache Directory <https://directory.apache.org>, PMC Member*
> Apache Incubator <https://incubator.apache.org>, committer
> Apache Steve <https://steve.apache.org>, committer

[Pierre Smits-3]

Hi Taher,

Thank you for your review and questions.

Please see in line for specific answers.

Met vriendelijke groet,

Pierre Smits
*Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/> since
2008 (without privileges)

*Apache Trafodion <https://trafodion.apache.org>, Vice President*
*Apache Directory <https://directory.apache.org>, PMC Member*
Apache Incubator <https://incubator.apache.org>, committer
Apache Steve <https://steve.apache.org>, committer


On Mon, Mar 16, 2020 at 4:39 PM Taher Alkhateeb <[hidden email]>
wrote:

> Hello and thank you for your work. A few review notes:
>
> - It would be great if you can briefly explain the new dimensions and
> what you're triangulating on
>


   - CountryDimension - applicable when doing aggregations of amounts etc
   of orders/invoices/etc. per country and/or when doing comparisons between
   countries
   - CurrencyDimension - applicable when doing aggregations of amounts etc
   of orders/invoices/etc. per currency and/or when doing comparisons between
   currencies. Helpful for optimising cost of banking et.c
   - CustomerDimension - key to be used in conjunction with
   SalesOrderItemFact and SalesInvoiceItemFact, in order to do aggregation in
   appropriate schemas  of orders vs invoices per customer, revenues per
   customer per period and period to period comparison, etc. Currently various
   fact tables are missing this integration with this dimension. Based on
   parties with roleTypeId = "CUSTOMER";
   - DateDimension - based on ISO definitions and derived from various
   date(time) fields in transactions - used in various schemas in order to be
   able to do various aggregations of amounts etc per period (consider year,
   quarter, month, week, day, etc.), and/or comparisons of grouped amounts of
   invoices/orders/etc between periods
   - FacilityDimension - based on Facility entity - to be used in
   conjunction with not only InventoryItemFact and associated schema, but also
   applicable regarding shipment(Item)Fact and appropriate schema in order to
   improve warehousing efficiency and shipment routing.
   - OrganisationDimension - based on the accounting
   organisations (PartyAcctgPreference) as the counterpart of all 3rd parties
   in orders, invoices, etc. - key to be used in conjunction with all Fact
   tables, in order to do aggregations of orders (amounts, etc) invoices (also
   amounts etc), etc.
   - TimeDimension - based on ISO definitions and derived from various
   date(time) fields in transactions.  - e.g. used in various schemas in order
   to be able to do various aggregations of amounts etc per time slot (day
   segment e.g. morning, evening, hour, minute)



> - Can you elaborate on the purpose for having time dimensions values
> for every minute? What is the data being aggregated here?
>

This is based on the Data WareHouse Toolkit book. and is intended to enable
filtering (or roll-up) of periods based on summarised day part group, like
15 minutes intervals


> - There is some stramge logic in DimensionServices.groovy where
> essentially you're clearing the input map (effectively passing an
> empty map) to a bunch of service calls. Why are you using the same map
> which you keep clearing from one call to the next? Are those services
> not supposed to take input parameters? If so, why not pass an empty
> map instead of this logic?
>

That you for bringing this forward. I will take a look at this, and correct
where needed.


> - There are some files with windows EOL characters. Need to run
> something like dos2unix on those
>

Thanks. Will correct this.



> - There are also tab characters around the code base
>

Thanks. Will correct this.



>
> May I also suggest breaking this work into smaller chunks to start
> incorporating into the code base to make it easier to review and
> integrate the work. Perhaps you can isolate the work for each new
> dimension in a branch / patch / etc
>


Sorry, but at the moment I am not inclined to revert back to individual
patch files or development branches for each of the underlying sub-tasks
under OFBIZ-11414.
 While some may regard the contribution too overwhelming for review (and/or
testing) at one moment in time, breaking them up into smaller chunks would
lead to criticism that chunks are too small to review and lacking coherence
with other chunks. IMO, the history in the feature branch in the repo
provides enough granulated insights to enable evaluation from commit to
commit, while at the same time enable community members to test the whole
without them having to bring all appropriate chunks (patch
files/development branches per sub-task) together in a correct sequence.

I have done that (providing patch files) in the beginning to each of the
underlying sub-tasks, and the overhead to maintain the myriad (keeping
everything synced) is just too time consuming.  As well as increasing the
risk of merge conflicts dramatically. I experienced that quite a number of
times and have seen patches go stale.

> On Mon, Mar 16, 2020 at 6:09 PM Pierre Smits <[hidden email]>
> wrote:
> >
> > Following up on recent migrations from minilang to groovy regarding the
> > functions in the BI component I have made various enhancements to the
> > component, enabling the project to move from a Proof of Concept state to
> > something that is starting to provide our (potential) adopters some
> > additional arguments to use (or select) our product.
> >
> > BI (or Business Intelligence) is a key factor to measure the success of a
> > company (and its underlying departments), whether it is regarding sales,
> > purchases, inventory ageing, or employer utilisation (e.g. in
> > project-oriented service orgs). BI often works against a Data Warehouse,
> > which is fed from the transactions RDBMS using (more or less)
> sophisticated
> > ETL solutions. Often these ETL tools are quite expensive to operate, and
> > require - when talking about development - quite the skill set.
> > Unfortunately, Small and Medium Enterprises often lack the budget to
> deploy
> > such tools and services and make due with exports from the transaction
> > RDBMS and the use of MS Excel to do their Business Intelligence analysis.
> >
> > Given the unique feature set of OFBiz catering to the needs of small and
> > medium enterprises, an improved BI component can deliver on an easy (and
> > fairly cheap) means to have a DataWarehouse populated, and - through the
> > population of dimension and fact tables - get data elements combined into
> > star schemas for in-component or external (through external tools as
> Excel)
> > analysis.
> >
> > In order to bring the BI component up to a level of usability in a
> company,
> > I have implemented following changes (in a feature branch in my repo)
> >
> >    - added and updated dimension tables
> >       - CustomerDimension, OrganisationDimension (internal
> organisations),
> >       and SupplierDimension
> >       - supporting: DateDimension, TimeDimension, CountryDimension,
> >       CurrencyDimension
> >    - updated fact tables to work with the added and improved dimension
> >    tables
> >       - SalesInvoiceItemFact
> >       - InventoryItemFact
> >    - updated star schemas to bring data elements from dimension and fact
> >    tables together
> >       - SalesInvoiceItemStarSchema
> >       - InventoryItemStarSchema
> >    - made the Report Builder functionality (screens and such) mode user
> >    friendly, by applying ajax functionality,  to have selection of the
> star
> >    schema, the field selection and the resulting overview in one page
> >
> > While there are still a lot of areas (BI-wise) that can be improved, such
> > as:
> >
> >    - SalesOrderItemFact and SalesOrderItemStarSchema - to enhance the
> >    analysis regarding sales,
> >    - Purchase (Spend) analysis functionality,
> >    - Utilisation analysis function (assets, employee),
> >
> > enhancements currently available improve the BI component significantly.
> >
> > Rather than working alone and see contributions go stale, I would like to
> > collaborate more via a feature branch in the official plugins repository.
> > As you can see in JIRA, there already quite a number of improvement
> tickets
> > (seeu [1]), and I have already many (up to the point of ready for
> > incorporation into the plugins code base)  in progress. The improvements
> > are currently available in my public fork on Github (see [2]). I invite
> you
> > to fork or clone and test-drive the enhancements.
> >
> > And if you have any suggestions, questions, and/or remarks, feel free to
> > post them here (or directly to me), or as a comment to the master ticket
> > (for general aspects) or to a particular sub-task.
> >
> >    1. https://issues.apache.org/jira/browse/OFBIZ-11414
> >    2.
> >
> https://github.com/PierreSmits/plugins/tree/OFBIZ-11414_BI-Improvements
> >
> >
> > Looking forward.
> >
> > Met vriendelijke groet,
> >
> > Pierre Smits
> > *Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/>
> since
> > 2008 (without privileges)
> >
> > *Apache Trafodion <https://trafodion.apache.org>, Vice President*
> > *Apache Directory <https://directory.apache.org>, PMC Member*
> > Apache Incubator <https://incubator.apache.org>, committer
> > Apache Steve <https://steve.apache.org>, committer
>

[Pierre Smits-3]

The issues brought forward by Taher have been addressed.

Mvg

Pierre

Op ma 16 mrt. 2020 17:54 schreef Pierre Smits <[hidden email]>:

> Hi Taher,
>
> Thank you for your review and questions.
>
> Please see in line for specific answers.
>
> Met vriendelijke groet,
>
> Pierre Smits
> *Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/> since
> 2008 (without privileges)
>
> *Apache Trafodion <https://trafodion.apache.org>, Vice President*
> *Apache Directory <https://directory.apache.org>, PMC Member*
> Apache Incubator <https://incubator.apache.org>, committer
> Apache Steve <https://steve.apache.org>, committer
>
>
> On Mon, Mar 16, 2020 at 4:39 PM Taher Alkhateeb <
> [hidden email]> wrote:
>
>> Hello and thank you for your work. A few review notes:
>>
>> - It would be great if you can briefly explain the new dimensions and
>> what you're triangulating on
>>
>
>
>    - CountryDimension - applicable when doing aggregations of amounts etc
>    of orders/invoices/etc. per country and/or when doing comparisons between
>    countries
>    - CurrencyDimension - applicable when doing aggregations of amounts
>    etc of orders/invoices/etc. per currency and/or when doing comparisons
>    between currencies. Helpful for optimising cost of banking et.c
>    - CustomerDimension - key to be used in conjunction with
>    SalesOrderItemFact and SalesInvoiceItemFact, in order to do aggregation in
>    appropriate schemas  of orders vs invoices per customer, revenues per
>    customer per period and period to period comparison, etc. Currently various
>    fact tables are missing this integration with this dimension. Based on
>    parties with roleTypeId = "CUSTOMER";
>    - DateDimension - based on ISO definitions and derived from various
>    date(time) fields in transactions - used in various schemas in order to be
>    able to do various aggregations of amounts etc per period (consider year,
>    quarter, month, week, day, etc.), and/or comparisons of grouped amounts of
>    invoices/orders/etc between periods
>    - FacilityDimension - based on Facility entity - to be used in
>    conjunction with not only InventoryItemFact and associated schema, but also
>    applicable regarding shipment(Item)Fact and appropriate schema in order to
>    improve warehousing efficiency and shipment routing.
>    - OrganisationDimension - based on the accounting
>    organisations (PartyAcctgPreference) as the counterpart of all 3rd parties
>    in orders, invoices, etc. - key to be used in conjunction with all Fact
>    tables, in order to do aggregations of orders (amounts, etc) invoices (also
>    amounts etc), etc.
>    - TimeDimension - based on ISO definitions and derived from various
>    date(time) fields in transactions.  - e.g. used in various schemas in order
>    to be able to do various aggregations of amounts etc per time slot (day
>    segment e.g. morning, evening, hour, minute)
>
>
>
>> - Can you elaborate on the purpose for having time dimensions values
>> for every minute? What is the data being aggregated here?
>>
>
> This is based on the Data WareHouse Toolkit book. and is intended to
> enable filtering (or roll-up) of periods based on summarised day part
> group, like 15 minutes intervals
>
>
>> - There is some stramge logic in DimensionServices.groovy where
>> essentially you're clearing the input map (effectively passing an
>> empty map) to a bunch of service calls. Why are you using the same map
>> which you keep clearing from one call to the next? Are those services
>> not supposed to take input parameters? If so, why not pass an empty
>> map instead of this logic?
>>
>
> That you for bringing this forward. I will take a look at this, and
> correct where needed.
>
>
>> - There are some files with windows EOL characters. Need to run
>> something like dos2unix on those
>>
>
> Thanks. Will correct this.
>
>
>
>> - There are also tab characters around the code base
>>
>
> Thanks. Will correct this.
>
>
>
>>
>> May I also suggest breaking this work into smaller chunks to start
>> incorporating into the code base to make it easier to review and
>> integrate the work. Perhaps you can isolate the work for each new
>> dimension in a branch / patch / etc
>>
>
>
> Sorry, but at the moment I am not inclined to revert back to individual
> patch files or development branches for each of the underlying sub-tasks
> under OFBIZ-11414.
>  While some may regard the contribution too overwhelming for review
> (and/or testing) at one moment in time, breaking them up into smaller
> chunks would lead to criticism that chunks are too small to review and
> lacking coherence with other chunks. IMO, the history in the feature branch
> in the repo provides enough granulated insights to enable evaluation from
> commit to commit, while at the same time enable community members to test
> the whole without them having to bring all appropriate chunks (patch
> files/development branches per sub-task) together in a correct sequence.
>
> I have done that (providing patch files) in the beginning to each of the
> underlying sub-tasks, and the overhead to maintain the myriad (keeping
> everything synced) is just too time consuming.  As well as increasing the
> risk of merge conflicts dramatically. I experienced that quite a number of
> times and have seen patches go stale.
>
>
>> On Mon, Mar 16, 2020 at 6:09 PM Pierre Smits <[hidden email]>
>> wrote:
>> >
>> > Following up on recent migrations from minilang to groovy regarding the
>> > functions in the BI component I have made various enhancements to the
>> > component, enabling the project to move from a Proof of Concept state to
>> > something that is starting to provide our (potential) adopters some
>> > additional arguments to use (or select) our product.
>> >
>> > BI (or Business Intelligence) is a key factor to measure the success of
>> a
>> > company (and its underlying departments), whether it is regarding sales,
>> > purchases, inventory ageing, or employer utilisation (e.g. in
>> > project-oriented service orgs). BI often works against a Data Warehouse,
>> > which is fed from the transactions RDBMS using (more or less)
>> sophisticated
>> > ETL solutions. Often these ETL tools are quite expensive to operate, and
>> > require - when talking about development - quite the skill set.
>> > Unfortunately, Small and Medium Enterprises often lack the budget to
>> deploy
>> > such tools and services and make due with exports from the transaction
>> > RDBMS and the use of MS Excel to do their Business Intelligence
>> analysis.
>> >
>> > Given the unique feature set of OFBiz catering to the needs of small and
>> > medium enterprises, an improved BI component can deliver on an easy (and
>> > fairly cheap) means to have a DataWarehouse populated, and - through the
>> > population of dimension and fact tables - get data elements combined
>> into
>> > star schemas for in-component or external (through external tools as
>> Excel)
>> > analysis.
>> >
>> > In order to bring the BI component up to a level of usability in a
>> company,
>> > I have implemented following changes (in a feature branch in my repo)
>> >
>> >    - added and updated dimension tables
>> >       - CustomerDimension, OrganisationDimension (internal
>> organisations),
>> >       and SupplierDimension
>> >       - supporting: DateDimension, TimeDimension, CountryDimension,
>> >       CurrencyDimension
>> >    - updated fact tables to work with the added and improved dimension
>> >    tables
>> >       - SalesInvoiceItemFact
>> >       - InventoryItemFact
>> >    - updated star schemas to bring data elements from dimension and fact
>> >    tables together
>> >       - SalesInvoiceItemStarSchema
>> >       - InventoryItemStarSchema
>> >    - made the Report Builder functionality (screens and such) mode user
>> >    friendly, by applying ajax functionality,  to have selection of the
>> star
>> >    schema, the field selection and the resulting overview in one page
>> >
>> > While there are still a lot of areas (BI-wise) that can be improved,
>> such
>> > as:
>> >
>> >    - SalesOrderItemFact and SalesOrderItemStarSchema - to enhance the
>> >    analysis regarding sales,
>> >    - Purchase (Spend) analysis functionality,
>> >    - Utilisation analysis function (assets, employee),
>> >
>> > enhancements currently available improve the BI component significantly.
>> >
>> > Rather than working alone and see contributions go stale, I would like
>> to
>> > collaborate more via a feature branch in the official plugins
>> repository.
>> > As you can see in JIRA, there already quite a number of improvement
>> tickets
>> > (seeu [1]), and I have already many (up to the point of ready for
>> > incorporation into the plugins code base)  in progress. The improvements
>> > are currently available in my public fork on Github (see [2]). I invite
>> you
>> > to fork or clone and test-drive the enhancements.
>> >
>> > And if you have any suggestions, questions, and/or remarks, feel free to
>> > post them here (or directly to me), or as a comment to the master ticket
>> > (for general aspects) or to a particular sub-task.
>> >
>> >    1. https://issues.apache.org/jira/browse/OFBIZ-11414
>> >    2.
>> >
>> https://github.com/PierreSmits/plugins/tree/OFBIZ-11414_BI-Improvements
>> >
>> >
>> > Looking forward.
>> >
>> > Met vriendelijke groet,
>> >
>> > Pierre Smits
>> > *Proud* *contributor** of* Apache OFBiz <https://ofbiz.apache.org/>
>> since
>> > 2008 (without privileges)
>> >
>> > *Apache Trafodion <https://trafodion.apache.org>, Vice President*
>> > *Apache Directory <https://directory.apache.org>, PMC Member*
>> > Apache Incubator <https://incubator.apache.org>, committer
>> > Apache Steve <https://steve.apache.org>, committer
>>
>