How does ERP and BI work together to run a business effectively and profitably?
Before we answer that, let’s take a quick refresher on ERP and BI, and their fundamental difference.
ERP is the software that helps integrate a businesses records, transactions and processes. Delivering a real time view of what’s going on, irrespective of language or location.
BI is the software that re-packages this ‘raw’ data into user friendly formats that deliver insights and analytics.
Epicor ERP comes with standard reporting options and is an exceptionally open solution for reporting in its own right. The standard reporting options include:
- Standard Report & Dashboards / Trackers
- Customer reporting using SQL Server Reporting Services (SSRS)
- Custom Dashboards
- XL Connect 7 / Fast Close for embedded reporting in Microsoft Excel
To help understand whether you should look into BI as a solution to enhance Epicor ERP we asked Patrick Walsh, Operations Manager from Aspera Solutions what his thoughts and advice would be to a business considering BI.
Why would a business look at BI on top of the standard reporting tools within Epicor ® ERP?
“The main one is that BI gives a business the ability to slice and dice information. The standard Epicor reporting tools provide a lot of capability however you may have additional needs to meet your business requirements based on two major reasons:
- a need for greater “Slice & Dice” analysis capabilities
- a lack of technical competency in-house to write SQL reports”
We can all visualise the slicing and dicing of data to get the business answers (we have all spent hours on a certain spreadsheet during some stage of our career) but can you explain how this works with Epicor ERP?
“When we implement a BI solution with one of our customers we use Data Cubes. These cubes use the master and transactional data recorded in Epicor which are stored using Tables within a SQL Database. Like all transaction orientated systems, this database is designed to maintain the integrity of the data at all times. For example, when looking at sales orders in Epicor the data being retrieved would come from the following tables:
- Overhed – stores the order header information
- OrderDtl – stores the order lines
- Part – stores the product information, part description, UOM etc.
- Customer – stores customer data, name, address etc.
It is important to note that the above tables are efficient from a data storing perspective and ensuring that data integrity is maintained but not efficient for rapid analysis reporting. As an example, a sales order transaction volume of 500 orders per week with 10 lines per order, will result in 260,000 sales transactions being added to the database annually. So a monthly query showing year on year details for three years will have a dataset expanding to 780,000. This is a heavy burden on a system and will likely result at some stage in slow performance. This could be avoided with BI.
The data cubes provide a separate database structure, designed specifically for analysis to support a very efficient slice and dice operation. This database structure is called an OLAP cube and is a multidimensional database that is optimised for data warehouse and online analytical processing (OLAP) applications. In an OLAP cubes, data (measures, e.g. Revenue, Cost, Quantity…) are categorised by dimensions (Customer, product, Sales Rep, Time,…)”
Example of Cube Database
What would say a typical Sales Cube contain and what reporting could be achieved?
“Cubes are defined as follows:
- Dimensions = variables of data
- Measures = the data
- Elements = subsets of the dimensions and measures”
So, for a Sales Cube using the Epicor data would be set up something like below:
| Measures | Dimensions |
|---|---|
| Quantity | Epicor Company |
| Revenue | Epicor Site |
| Cost of Sale | Customer |
| Margin | Product |
| Sales Rep | |
| Fiscal Calendar | |
| Calendar | |
| Transaction Type | |
| Unit of Measure |
How will the sales cube or other BI cubes be deployed?
“We would recommend installing on premise however users will be able to access dashboards/reports from the cloud and from anywhere.”
What is the process with regard to data management and how real-time is the data within the Sales cube?
“The data will be extracted from the Epicor database and loaded into the cube. This process is usually completed nightly however the frequency can be set more frequently to reflect the business requirements.”
Is Visualisation what the end user sees?
Analysis is designed to reveal the hidden patterns and trends within business data and this is achieved most effectively through the graphical representation of that data i.e. visualisation. Providing these views helps the user to ask, and answer, questions through the slicing and dicing process.
There are several options that can be used by the end user for analysing data stored in the sales cube. We recommend the following:
MS Excel Pivot Analysis – Using the standard pivot table functionality of MS Excel, a user can connect directly to the OLAP cube and drag the measures/dimensions needed to the data. MS Excel also provides a number of graphical views that can be linked to the pivot table, such as scatter charts and bar charts to visualise that data.
Power BI Desktop – Microsoft Power BI is a suite of business analytics tools to analyse data and share insights. This gives your business the power to get answers quickly with rich dashboards available on every device. The visualisation available far exceeds MS Excel. One of the components of Power BI is Power BI Desktop – this solution puts visual analytics at your fingertips with intuitive report authoring.
Power BI in the Cloud – does require a paid subscription to Power BI Pro, the dashboards can be published to the cloud and can be analysed by using a browser or via mobile application.
In closing the interview with Pat, we asked what are the deliverables i.e. what will our customers receive if they decide Aspera Cubes is the method of reporting their business requires?
Deliverables will depend on your business requirements and Aspera’s assessment of same but a sample of what a standard delivery of a sales cube would look like is detailed below:
- Installation of PowerBI desktop on Terminal Server and also if required on other PC’s
- Installation of Sales Cube by Aspera and configuration
- Training on analysis and development of reports
- Configuration of Dashboards
- Dashboards can be accessed using PowerBI Desktop (connected to Network) and PowerBI Cloud (mobile users
