It is quite common to find companies or organisations with different core financial and operational data sources. There are various reasons for this. For example a company might stretch into different geographic locations, across borders and continents. Or it might have made various acquisitions and merged with other businesses over the years.
Dealing with different data sources can be quite challenging. Let’s dive into some key considerations and recommendations when working towards a solution to consolidate global reporting within a SAP Business One context.
Basic Solution Requirements
A solution needs to include the following core characteristics:
Security – User access to reports should be via a secure Cloud limited to read only functions. Separation of reporting database from the live system and firm access & report authorisation control is recommended.
Efficiency – The solution should be efficient in terms of performance and maintainability.
Flexibility – The solution has to be able to adapt to growing and changing reporting needs.Keep in mind an open list of reporting objects can be especially helpful. It can be created or updated as additional reporting phases are initiated or new requirements encountered.
Simplicity – Simplicity is king and we recommend a solution where most parts are based on standard Microsoft tools as it allows advanced users to take part in the developing work. For example: SQL extracts and objects.
Whilst always keeping the above mentioned requirements in mind we believe in order to successfully deliver a global reporting solution the following four steps are a necessity:
1. Establish simple and robust source data source connectivity.
2. Standardisation and mapping of the data sources.
3. Report Design and Configuration.
4. Report Presentation to end users and report consumers.
Without cracking open the whole box of toys, let’s take a very brief look at each if these steps.
Step One: Establish simple and robust source data source connectivity.
In order to create a solution capable of reporting information in a consolidated and standardised manner across various data sources it is necessary to ‘connect’ the various data sources.
Various approaches and tools are available to get this done and the choice of what exactly to use will depend on the particular situation of each unique company. For example if the architecture is a MSSQL & SAPB1 based landscape there are usually three options to choose from:
A – Linked Servers
B – Snapshot Data Replication (Daily Replication)
C – Transaction Data Replication
Step Two: Standardisation and mapping of the data sources.
Once data from the various data sources can be connected and gathered for reporting purposes, the next step is to ensure that data is standardised as to allow meaningful reporting.
Our preferred and recommended approach for managing mapping and data standardisation is to introduce a single Reporting Database.
This Reporting Database is intended to act as a repository for the following:
The scope of this step depends on the required reporting capability, the number of data sources to aggregate and the degree of variance between them. It is recommended to review at high level all reporting relevant data sources and structure before deciding on the most appropriate method for providing and maintaining data mapping.
Step Three: Report Design & Configuration
Once the source data is located in a central reporting database, with all appropriate data mapping performed to standardise the various data sources, it becomes a much simpler exercise to produce meaningful reports.
During this step it is helpful to identify a number of key reports to create at the outset.
In general, it is best to utilise the standard and existing reporting capability such as:
This particular approach keeps cost down as it does not require an additional report writing tool. Once data is standardised into SQL it should be possible to use any report writing tool capable of interrogating SQL data.
Step Four: Report Presentation
The last step is of course to provide options for the above mentioned reports to be presented to the report users. We assume that report users could consist of users across various levels and functions in a company.
In our opinion a solution should provide the following report presentation and delivery options:
Our preferred and recommended route for this option is to utilise our own cloud platform solution, B1ReportViewer.
B1ReportViewer is a cloud-based report presentation platform, integrated with SAPB1. It allows to display reports produced as SAPB1 query or Crystal reports as well as any other Crystal report produced on SQL based data. The solution allows one to synchronise reports from SAPB1 to the B1ReportViewer portal from where authorised B1ReportViewer portal users will be able to execute and display reports which they are authorised for, from data sources which they are authorised for. It is user-friendly and packed with great high-level features.
For any questions or thoughts about your own SAP Business One reporting efforts, you are welcome to contact us.