The purpose of this document is to describe an integration between Openbravo ERP and a business intelligence server for the purpose of providing business intelligence dashboards and reports based on Openbravo ERP content.
The scope of this project is to pilot the integration and therefore it focuses on a limited set of functional content rather than providing an exhaustive coverage of the business functionality. In particular the objectives of the project are:
- Determining the best integration architecture
- Determining the most suitable technology: JasperSoft or Pentaho
- Validating that it is possible to embed BI artifacts (dashboards and reports) within Openbravo ERP
The selected functional scope for this pilot is a Financial Analysis dashboard based on accounting information that provides intelligence of revenue and expenses.
The expected deliverables from this project are:
- A prototype that illustrates the connection between Openbravo ERP and a BI Dashboard with the functionality described here. The prototype shall be comprised of:
- A design for the most appropriate data warehouse schema (facts and dimensions) to support the functional requirement.
- A BI server with schema and meta-data to support the dashboard and the reports.
- A set of ETL scripts to move the data from the Openbravo ERP repository to the BI server. (Note: Openbravo ERP engineering can provide skills on the Openbravo ERP data model)
- An embeddable UI that can be invoked from the Openbravo ERP. (possibly a stand-alone servlet rendering the dashboards)
- A documentation of the architecture.
- A recommendation for the most suitable BI technology.
- A document that explains how to add additional content and to the BI server and how to build additional dashboards.
The following terms will be used throughout this document.
- Dashboard: A dashboard is a collection of business intelligence content oriented to a particular role. Dashboards contain regions like parameters, tables, graphs, links, and Key Performance Indicators (KPIs).
- Parameter: Dashboards contain a set of parameters that users can use to change the data that is displayed on the dashboard.
- KPI: A KPI is a strategic business factor used for reporting. KPIs are designed for comparing and judging performance on strategic business factors such as Revenue or Operating Margin. Each dashboard contains a set of KPIs based on the content of the dashboard.
User roles & profiles
The target users for this application are:
- Financial analyst: these users have a high level of education in economics or business administration and typically between one and ten years of professional experience. They are computer literate and proficient with desktop applications such as spreadsheets. They primary concern is to be able to analyze data in way they might like. Possible job titles for this role are:
- Financial analyst
- Controller (Jr./Sr.)
- Budget analyst
- Internal auditor
- Financial executive: these users have a higher degree of education in economics or business administration and several years of professional experience. They are computer and IT literate and proficient with desktop applications such as spreadsheet and presentation. Their primary concern is to be able to quickly monitor evolution of key performance indicators and to be able to export data and graphs to external documents such as presentations. Possible job titles for this role are:
- Finance Director
- Director of Revenue Management
- Chief Financial Officer
- Martin, a senior financial analyst for Big Bazaar, needs to verify the revenue that has been generated by each product distributed out a particular location in order to validate that each product is performing as projected. Martin logs in the application and opens the Financial Analysis dashboard. From the Revenue graph, he drills down in the Revenue report and builds a pivot table that allows him to see the data he his looking for.
- Mary, the CFO of Big Bazaar, is preparing a presentation for the board of directors and needs a graph to add to her slides that illustrates the revenue and expense trend YTD. She logs in the application and opens the Financial Analysis dashboard, which contains the graphs she is looking for. She selects the image, copy it and paste it directly in her presentation.
Functional requirements based on business processes
|1|| Users should be able to set the following parameters to filter the data on the dashboard:
|2|| Monetary data should be rendered in the functional currency (the currency associated with the organization and accounting schema).
Additionally, user should be able the select a different currency of representation through a currency parameter.
|Nice to have||Done|
|3|| The organization parameter should allow user to pick the organization from a widget that represent the organization hierarchy. For example:
Big Bazaar Corp.
Big Bazaar EMEA
Big Bazaar Spain
Big Bazaar Products S.L.
Big Bazaar Services S.L.
Big Bazaar France S.A.
Big Bazaar Italy SpA
Big Bazaar North America
Big Bazaar USA
Big Bazaar Canada
When an organization is selected, the dashboard should aggregate data for that organization and all the organizations below it. For example, the revenue for organization Big Bazaar EMEA should be the aggregate revenue for Spain, France and Italy.
|Nice to have||Not included|
Key Performance Indicators
|4|| Out of the box, the dashboard should be configured to contain the following key performance indicators and users should be able to compare their value with the value in the compare period:
|5||Out of the box, the dashboard should be configured to contain a revenue by product category bar chart.||Must have||Done|
|6||Out of the box, the dashboard should be configured to contain a revenue by period bar chart.||Must have||Done|
|7||Out of the box, the dashboard should be configured to contain an expense by period bar chart.||Must have||Done|
Ad hoc Reports
|8||From the dashboard, it should be possible to navigate to a page that allows to define ad hoc reports based on the accounting data||Must have||Done|
Navigation from Openbravo ERP
|9||It should be possible to access the dashboard from the Openbravo ERP menu||Must have||Done|
|10||The dashboard should be rendered within the right pan of the Openbravo ERP UI, similarly to a native Openbravo ERP window||Should have||Done|
|11||Users having a role that contains the dashboard, should automatically navigate to it upon login. In other words, upon login these users, instead of the Openbravo splash screen, should be presented with the left menu collapsed and the dashboard open.Other users should continue to be presented with the splash screen.||Nice to have||Not implemented|
User Interface Mockups
Below is a mock up of the dashboard.
The following picture illustrates the suggested architecture
Openbravo Accounting Dimensions
|12|| The data warehouse schema in the in the ETL server should contain the following data elements, to be designed in the most efficient set of facts and dimensions.
|13||Users should be restricted to only see data for organizations to which they have access. For example, a user having access to only organization Big Bazaar Spain should not be able to see data for Big Bazaar Italy, Big Bazaar EMEA or Big Bazaar USA.||Nice to have||In progress|
Integration with External Data Sources
|14|| On an implementation project, it should be possible to extend the system by integrating in the data warehouse external data sources that could be used for custom KPIs and reports.For example, it should be possible to import revenue forecast information from an external system and compare it to the actual revenue coming from Openbravo.
While these integrations are custom extension, the proposed architecture should be such that building them is not too expensive and disruptive of the standard product. In particular, it should be possible to ship maintenance updates (patches) for the standard functionality without loosing the custom extensions.
|Should have||Not started|
|15||It should be possible to extract all the translatable labels in an external file for independent translation purposes||Must have||In progress|
|16||The external file format should be PO (Portable Objects) or it should be possible to convert the external file to PO format||Must have||Not started|
|17||It should be possible to have multiple translations installed in the same instance and drive the UI language by the user's preferences||Must have||Not started|
|18||With the out of the box configuration, the dashboard should render in 2 seconds or less, independently on data volumes.||Must have||Not started|