Business Intelligence

Business Intelligence is an important part of an ERP. Its gives an overview of your business data. It can show a broad overview in dashboards and charts but also dig deep into your numbers and key performance indicators (KPI).

For now this page starts as a result of a insightful discussion in the IDempiere_workshop_2025. See IDempiere_workshop_2025#BI to read the transscript of the BI discussion.

Our goal is to have a more useful interface for BI integration for different needs with examples and documentation. The goal is to have like a basic standard of BI capabilities and best practices to use some tools. That allows users to get fast access to these tools and allows implementors to build on that to adapt it to special needs. This shall be so easy and standardized that we use it even in the demo servers of the iDempiere project to get a better first impression experience for new users.

This page (hopefully) will collect results of our work on these parts:

build an BI interest group

Layda Salas initiated this effort and wanted to lead this group. In the workshop Juan Carlos Lin and Thomas Bayen were interested to join the group. Carlos Ruiz liked to join also. We invite everyone with ideas and knowledge to write to us and join.

Our firsst meeting will be tuesday, 27.5.25 15:00 UTC (17:00 CET/Hungary/Germany, 12:00 Paraguay). You are welcome.

use cases

Main use case for the BI

Operation Assist:

As as widget [or quick info format] to help sales representative and purchase team for quick answer /decisions

Operation dashboards as:

Pending to pickups,

Pending to ship, wave release

Pending to invoice,

Pending to collect,

Close to expire productos,

Expiration maps,

Warehouse heating map,

Manager:

Quick dashboard, to help decision making, adding dynamic filters, so and supervisor, mananger can take fast insights

Operations costs,

Sells breakdown,

Indirects Costing

Fix Costings vs operation qty/amount

Sells:

Integrate Commission, and Goals, so any sales representative and see how his own perfomance, and supervisor scan se the team performance, and Sales managers can see whole organization perfomance, and General manager can se tenant perfomance, in a same place, fast en easy

Purchase:

Integrate Stock availability, Sells, to see how performance is the buyer, and how impact to the sells team.

In a integrate dasboard, show stock level, sells level, purchase in progress, sales forecasting, and stock braking in past and future.

May be using last 6 month or same period last year, or same week last year.

Financial:

Show how perfomance in the financial view, how we turn over, how we apply the modey, where they go, and how their performance each dollar we apply on the bussines

Show how will be, if we add more sells with same expenses, o less sells with same expense, or if more discoutn but more sells, will impact in profit [what if model]

Accounting:

Show pending posting, posting issues, posting documents, etc, with a single view can be sure if all documentos are processed

Show Assest grow, Liability Grow,

Show KPI for starndar accounting analysing and company health reatios

General perfomance:

KPI for each sector

Customer dashboard:

When customer login, also have the posssibility to show customer performance so customer also can try to understand how importnat their are for our bussines.

new dimensions

My experience is that as more columns a data cube has as more useful and powerful a visual tool like Superset becomes.

Do we need new dimensions in core?

TBayen and Juan Carlos had some ideas. CarlosRuiz wants to include some of them into core tables if we agree that these are useful. But we will also have to think how we integrate other columns.

• Brand of a product
• Product line
• BPartner Grouping [mor effetive way, or multi grouping possibility like a aditional groupng tab]

How to find the way to transform attribute as dimension quick and fast?

For example in a beverage company will be nice to know market breakdown by alcohol degree, or, orgin degree, or vendor group degree,

Some times, BPartner Group, applies to BPartner as Vendor but not as Customer.

If one customer is also vendor, the classification of Group in most time is conflictive or exclusive.as we can mar BPartner as Vendor, customer, Employee, Sales Representative, is all marked, what BPartner Group should attache to?

We have tab for vendor accounting, employee acoutnign, customer accounting, but we do not have possiblity to have Grouping in the same dimension as accounting. What we do now is is mostly customer goes to customer, or to vendor.

define tables and cubes

We want to define basic queries that can be used as a basic dataset for several usage areas by our BI software. These queries should not only be an example for GardenWorld but solve 80% of the average customer's needs. They are extendable by implementors for special needs.

implementation details

One part of that question is technical: How do we implement that? On the conference workshop in Budapest the attendees agreed that we create a database view as a base definition of columns. Depending on needs, size and resources of the erp implementation one can:

• use that as it is (seems good enough for an example in GardenWorld),
• change it to a materialized view (more useful for real implementations),
• copy it to special tables as a data cube (allows indexing, compression, grouping, etc.),
• or to a specialized BI database server (like Gaurav Sontakke showd us in the Conference 2025 where he uses the proprietary Snowflake database).

After some talk with Carlos Ruiz we realized that we might need two views (or queries) per base table: A base view and a detailed view. One important reason is that a detailed view may use a very big amount of memory and a lot of the data will be redundant. E.g. in a sales table we will have a lot of lines for our sales (one per each C_OrderLine) and all of these will have the same content in fields like bp_name, bp_name2, bp_address1, bp_city and so on. In simple cases a view with everything at hand lokks like the faster sulution. But these redundant fields may bloat our view up to the point where the memory consumption affects performance. It can make a huge difference if the whole view fits in our postgres server's memory.

As both cases can be useful in some implementations based on the nature of the data and the tools we use we should leave the decision to the user which of both views he/she materializes or uses where. Our abstract approach should be that we create

• A base view that may be intended to be materialized (or copied to a seperat table) and contains all dimensions for our data cubes to filter or group the data. It contains only ID fields and dedicated dimension fields. (That means it contains M_Product_ID but not M_Product.Value or M_Product.Name but it contains M_Product.Category.) This view should definitely be defined as an iDempiere table. In an average installation this view might be a good candidate to optional materialize it. (Is there already a Jira ticket to deal with materialized views?)
• A detailed view (or detailed query) that contains all possible data fields that can be extracted and shown in the output. We are not yet sure if it makes sense to provide this "view" just as a query to be used inside superset as the dataset query. In my opinion we should also create it as a table/view inside iDempiere to be able to use these detail table with internal reporting.

Some thoughts about dates

When we think about what to do with dates I see a lot of uses for different kinds of intervals. Sometimes I like to filter my data by day or by month and sometimes by the calendar week. Last month I did a analyze of logistics based on weekdays that was very useful. Doing that in a tool like superset is not always as easy as one may think (I dont' know much about other tools). Does that mean our detail view should contain several fields per date column to allow it to filter/group/sort by all these intervall types? Like dateordered, dateordered_day, dateordered_month, dateordered_year, dateordered_dow, dateordered_woy, dateordered_woyy, dateordered_quarter. I would say the answer is yes. (See here for example: https://help.apteco.com/faststats/Content/Topics/Cubes/Cubes_How_do_I_create_a_Cube_with_Date_Variables.htm)

There is a ticket to add a time dimension table. https://idempiere.atlassian.net/browse/IDEMPIERE-6569

capabilities of iDempiere

The queries should be prepared for the advanced capabilites of iDempiere: multi-tenant (do not allow to mix data of other tenants), multi-user (do we want access rights to data?), multi-currency, etc.

defined base tables

The more interesting part (for our BI interest group) is: Which kind of tables/cubes we want to define to help in our use cases:

• Sales
• Purchases
• Material
• Costing
• Human Resources
• Accounting

It may (or may not) be useful to add data cubes, what is a densed version of some of the given views (e.g. monthly turnover) to speed up things and show how to use these.

base table for dates

As written in IDEMPIERE-6569 it can be useful to have a base table for dates. That allows to add a lot of date dimensions (like months, day of week, week of year, name strings, etc.) to the detail tables in an easy and performant way. The best way is a prepared table with all these dimensions.

This is my first draft for such a table. There are some open thoughts here:

• How do we deal with translations? A german user wants to choose the month "März" in e.g. Superset and not see an english word there. As this table is about performance it is not so good to search for translations later - on the other hand this takes some memory. Or we use a table more to translate things. Hmmm...?!? The multi-language nature of iDempiere makes it complicated to find the "right" translation. As the majority of implementations still has only one main language used by users I like to propose a compromise: I create one column in english (to allow access with standardized "international" queries) and one in a default language that has to be defined somehow.
• How do we decide what is the default translation language? (Solved - see below)
• What is a good start- and enddate for this table? My idea was that customers (with birthdays) are up to 100 years old and for now it seems ok to think 15 years in the future. If we use this to create a materialized view we can make it more flexible or automated.
• A lot of dimensions are missing but thinkable. See e.g. Norberts example in the above Jira ticket. But which dimensions are really useful in real world? Which diemensions do you use?
• When is is a bit more matured this query has to be transfered either in a view definition inside iDempiere or we need a script or so to fill a table with it.

My draft query:

 1create materialized view BI_Date as
 2select
 3extract(epoch from d) as BI_Date_ID,
 4d::date as date_actual,
 5extract(epoch from d) as date_epoch,
 6extract(day from d) as date_dom,
 7to_char(extract(day from d), 'FM00')::varchar(2) as date_dom2,
 8extract(month from d) as date_month,
 9to_char(extract(month from d), 'FM00')::varchar(2) as date_month2,
10to_char(extract(year from d), 'FM0000')::varchar(4) as date_year,
11extract(dow from d) as date_dow,
12extract(doy from d) as date_doy,
13extract(week from d) as date_week,
14extract(isoyear from d) as date_isoyear,
15extract(quarter from d) as date_quarter,
16rtrim(to_char(d::date, 'Day'))::varchar as date_day_string,
17substring(to_char(d::date, 'Day') from 1 for 2)::varchar(2) as date_day_short,
18-- days translation is already there and I use it
19-- How do I select the locale? (I can't find out the choosen locale of recent logins)
20(select lt.name::varchar from ad_ref_list l 
21    join ad_ref_list_trl lt on lt.ad_ref_list_id = l.ad_ref_list_id and l.ad_reference_id = 167
22    where l.name = rtrim(to_char(d::date, 'Day')) and lt.ad_language = lang.ad_language) 
23    as date_day_string_trl,
24(select substring(lt.name from 1 for 2)::varchar(2) from ad_ref_list l 
25    join ad_ref_list_trl lt on lt.ad_ref_list_id = l.ad_ref_list_id and l.ad_reference_id = 167
26    where l.name = rtrim(to_char(d::date, 'Day')) and lt.ad_language = lang.ad_language) 
27    as date_day_short_trl,
28rtrim(to_char(d::date, 'Month'))::varchar as date_month_string,
29substring(to_char(d::date, 'Month') from 1 for 3)::varchar(3) as date_month_short,
30(select lt.name::varchar from ad_ref_list l 
31    join ad_ref_list_trl lt on lt.ad_ref_list_id = l.ad_ref_list_id and l.ad_reference_id = 200221
32    where l.name = rtrim(to_char(d::date, 'Month')) and lt.ad_language =lang.ad_language) 
33    as date_month_string_trl,
34(select substring(lt.name from 1 for 3)::varchar(3) from ad_ref_list l 
35    join ad_ref_list_trl lt on lt.ad_ref_list_id = l.ad_ref_list_id and l.ad_reference_id = 200221
36    where l.name = rtrim(to_char(d::date, 'Month')) and lt.ad_language = lang.ad_language) 
37    as date_month_short_trl
38--from generate_series(date '2025-07-01', date '2025-07-10', '1 day'::interval) d
39from generate_series(date '1930-01-01', date '2040-12-31', '1 day'::interval) d
40cross join (SELECT ad_language FROM ad_client WHERE ad_client_id>0 GROUP BY ad_language ORDER BY count(*) desc limit 1) lang
41;

Tools

These tools can be used to analyze and visualize the data.

• internal charts
• QuickInfo (can be used for context aware analysis)
• Superset
• Metabase
• Pentaho
• LibreOffice Base

It seems that Layda wants to create an example configuration for Superset. Our goal is to get that working so well that it will be included in the demo servers.

BI plugin

We want to have a plugin that includes missing dimensions (if not in core), the views and cubes and some charts examples and QuickInfo examples.

Demo server

Our goal is to include our working solution into the Jenkins scripts that start our demo server.

A missing part is to have demo data. As Carlos told us there is a plugin that creates demo data but we did not try out until now.

Also missing is to check and fill more data to get good dimensions (including our added dimensions) with useful data so the demo can be as impressive as it can.