Analytics – BI

Analytics Cubes, Reports, Widgets and Views


Openbravo Analytics provides several different instruments to help you with your reporting and analytics requirements.
The Analytics Reports visualize key sales information. Allowing you to look at anything from best sellers, discounts, cashup information, detailed return overviews.
The Analytics Windows provide detailed insight in the sales facts which are used to fill the reports and widgets. You can use the standard Openbravo tooling to filter, sort, re-organize your data before exporting it to excel.
The Analytics Widgets give you direct insight in best performing products (categories) across stores and provides usefull KPI’s on sales rep productivity.
These data analysis tools are based on a set of extendable cubes provided out-of-the-box with Openbravo Analytics. Openbravo Cubes will help you in creating reports with information such as:
Sales by product and product category for a specific period, net sales as well as gross sales, by business partner.
Discounts and promotions applied with total discount amount by period/product category
Daily/weekly sales report showing sales and returns by product and product category for different customer types
Margin by sales
Sales information by zipcode/region

Sales Terminology – Fields
When using the analytics cubes/reports you will see the same terminology/field names repeatedly being used. This section describes the meaning of those fields, especially sales versus returns and negative versus positive numbers is important to understand:
Sales: is the sales amounts excluding returns, excluding taxes but including discounts. It is always a positive number or zero.
Returns: is the return amounts including discounts and excluding taxes, it is a positive number or zero.
Net Sales: is Sales – Returns, including their discounts, excluding taxes, can be a negative number if more is returned than sold.
Quantity: is the sold quantity excluding returns, is always a positive number or zero
Returned Quantity: is the returned quantity represented as a positive number or zero
Net Quantity: is Quantity – Returned Quantity, can be negative if more is returned than sold
Cost: is the cost of the sold products, computed for the time of being shipped, excludes returns, is always a positive number or zero
Returns Cost: cost of the returned items, is a positive number or zero
Net Cost: cost of goods sold minus cost of goods returned, can be negative if more is returned than sold
Discount: discount amount of goods sold, excluding returns, is a positive number or zero
Returns Discount: discount amount of returned goods, is a positve number or zero
Net Discount: discount of goods sold minus discount on returned goods, is a negative number if more is returned than sold

This same base terminology is also used for other definition, for example Tax: Tax, Returned Tax, Net Tax.
In addition several cubes have these computed measures:
Gross Margin: net sales minus net cost
Gross Margin %: Gross Margin/Net Sales
Mark Up %: (Net Sales – Net Cost)/Net Cost
Discount %: Net Discounts / (Net Sales + Net Discounts)
Return Quantity %: Returned Quantity/Quantity


Understanding Analytics: hiding empty results
Analytics reports are very flexible and will try to only show relevant information to you. As a default the reports will hide empty rows and columns. If your filter parameters result in no results then nothing is shown.
Another reason that your report won’t show data is that you have a date filter which is too wide, for example setting a start date to 1 Jan 1970. So the date filtering should use dates which are within a sensible timeframe corresponding to the operational time frame of your business data.
To show also the empty cells/values click this button which is shown in the toolbar above the analytics report:


Analytics Reports
This section gives an overview of the standard reports delivered as part of the Openbravo (Retail) Analytics modules. The reports are based on the cubes which are described at the end of this wiki page.

Report Sales by Organization – Payment Method

Shows the net sales amount by payment method across different organizations. This report is based on the Sales Details Cube.
You can filter by business partner and business partner category. If you don’t filter by business partner or business partner category the report is shown for the all business partner category, in the report you can zoom to the business partner categories by left-clicking on the ‘all business partner category’ and selecting show children.


Report Sales: Best Sellers & Sales by Product Category

This report is based on the Sales Details Cube. It shows two analytics reports:
sales, cost, return and margin information for the top 10 best selling products
sales, cost, return and margin information by product category, you can zoom into the products for each product category
Both reports are sorted descending by net sales.
Top 10 best selling products:


Sales by product category, note you can zoom to product level by left clicking on a product category and select show children:


Report Sales Productivity by Sales Rep

Shows for a selected store the sales productivity KPI’s by sales rep. Productivity KPI’s are order/ticket count, average quantity/amount etc. This report is based on the Sales Header Cube.


Report Sales Productivity by Store

Shows for a selected store the sales productivity KPI’s by sales rep. Productivity KPI’s are order/ticket count, average quantity/amount etc. This report is based on the Sales Header Cube.


Report Sales Returns Overview by Sales Rep

Provides a view on the returns by sales rep showing order count and average return amounts and quantities. This report is based on the Sales Header Cube.


Report Cashup Summary

Gives a summary of the cashup information by closing user/sales rep. You can zoom in on the business date by left-clicking the all business date in the column header and select show children. This report is based on the Sales Cashup Cube.


Report Discounts by Store, Sales Rep and Product dimensions

The report shows the discount amounts by type of discount against several dimensions.
See the screenshot below, you can zoom to the details of each of the dimensions by left-clicking and selecting show children.


Analytics Windows
The Analytic Windows offer a great way to download/filter the fact data using standard Openbravo grid tooling. The fact table views support things like:

  1. column filtering
  2. saved views
  3. sorting
  4. column hiding/showing/re-organizing
  5. summary functions and computed columns
  6. grouping (for smaller datasets)

One of the most important features is that you can easily export the filtered data to excel (through the csv option).
An important additional feature provided by the fact table views is that you can edit the facts directly.
This makes is possible to correct unrealistic data facts which occurred to possibly correct data.
You can not remove fact records as the system would just create new one. If a fact later on changes in the transactional tables it is possible that any of your manual overwrites is overwritten.
The fact table views do not do advanced data validation. So you should be aware that there is a lot of freedom in updating information. This is allowed because there are no critical business processes reading the fact tables. The fact tables stand really apart from the transactional tables.

Analytics Sales Details View

Provides a direct view of the individual records which form the basis of the data in the Sales Details Cube. You can not delete records but edit the measure fields and some of the dimension fields.


Analytics Widgets
The (Retail) Analytics module provides several widgets which can be added to the OB workspace. Most of the widgets have a date from and to parameter. Other dimensions are pre-set.
Note: The widgets use the currency of the current organization or client, which ever is set.




Leave a Reply