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:
- Establish simple and robust source data source connectivity.
- Standardisation and mapping of the data sources.
- Report Design and Configuration.
- 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:
- For reporting data feeds from different data sources existing in a company.
- For all mapping tables required in order to standardise data from each individual data source into a universal reporting format.
- For all database views used in order to collect and combine data into a single source.
- For all stored procedures which may be required in order to control mapping as well as error reporting.
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:
- SQL report queries to support easy report data extracts to Excel.
- Crystal reports for printed, PDF and displayed reports.
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:
- Reports displayed inside of SAP B1 as ad-hoc executed SAP SQL query reports.
Note: These reports should also be exported to Excel.) - Reports displayed inside of SAP or e-mailed to users as SAP query based scheduled Alerts.
- Crystal Reports which are executed ad-hoc from SAP or Crystal and can be displayed, PDF, Printed or e-mailed
- Reports produced as formatted MS SQL query based reports executed ad-hoc or as scheduled MS SQL reports.
Note: It’s best if the solution also provides various data export file options, as well as options for scheduled, e-mailed reports.
- It is also recognised that a cloud-based reporting option should be provided to provide easy report access capability to report users.
Our preferred and recommended route for this option is to utilise our own cloud platform solution, IntegriB Report Viewer.
IntegriB Report Viewer. 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 IntegriB Report Viewer.portal from where authorised IntegriB Report Viewer. 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.