Introduction
A few months ago, I got involved in an SAP Business One project for an hotel in Luxembourg 
. Together, with a local SAP partner that took care of the functional analysis and provided me with the requirements, we were able to deliver a tailor-made solution for the customer.
The aim was to build a web portal to manage the stock of the hotel restaurant that would eventually be used by people without any knowledge of SAP. The users would be able to create purchase orders, purchase requests and inventory transfers amongst several other features that would enhance the standard B1 behavior, making it both easier and faster for employees to perform their daily tasks.
SAP Business One client is indeed full of features which can be overwhelming even after years of use. The portal was thought to shorten this list of features, said otherwise, to keep it simple and targeted for a set of employees. Two different positionings, as seen in the chart below:
In this article, I would like to take you through the different steps and thought process that I had during the making of this web application. I will also discuss about the several possibilities you have when designing a custom solution on top of SAP Business One (see Customizing SAP B1: Why and how?).
It is good to write about it but better to show it, there are several screenshots of the application throughout the article (data displayed is from a B1 demo database). So you’ll get a glimpse of the portal! Also make sure you check out the gallery at the bottom of the page for more screenshots 🧐.
This article is indeed technical but will also focus on general concepts around SAP B1 customization. If you’re curious about this topic or simply looking for inspiration for your project, this may be a good starting point? 😉
Alright, let’s get down to it! 👀
Customizing SAP B1: Why and how?
Customization in SAP B1 comes in different flavors and depends on the complexity of your project:
- Standard or Light customization through the creation of User-Defined Fields (UDF), User-Defined Tables (UDT) and User-Defined Objects (UDO) which allows you to extend the data-structure of the ERP.
- Modifying the Transaction Notification (TN) which is an SQL procedure that is triggered everytime a business object (let’s say an item or an order) is added, updated or deleted. This allows you to add your own checks on top of the B1 logic.
- Add-on development using the UI-API to build custom screens.
- Some add-ons also allow you to customize the screens without the need to code anything
It is very common to see these three components interacting with each other in order to achieve the desired enhancements of the solution. Historically, people would customize SAP exactly like that.
Today, with the addition of the Service Layer, which is a REST API. It is now very easy to interact with the ERP from the outside. Coupled with SAPUI5 for the UI, SAP provides two technologies that blend nicely together.
Think of it as an outside add-on that you can use without the need of having an opened B1 Client, which is pretty convenient.
Now, we all know that an ERP never comes out of the box for a customer and that it needs to be rightly setup and very often customized to fit to the business requirements. This leads to the creation of many rules that need to be implemented on top of the ERP standard rules. But how exactly can you add them?
In our case, the custom business rules were added inside an API that is eventually used by the portal itself.
Why is that a good thing? Because it provides one easily accessible entry point for all potential applications that would want to interact with the ERP with the same rules and constraints as the ones we implemented for the portal.
In this scenario, a request goes first through our custom API and undergoes custom checks before being forwarded to the Service Layer which abides to B1 standard rules & checks.
The Portal
As mentioned in the introduction, the goal was to create a portal where users would be able to create documents such as purchase orders, purchase requests or inventory transfers.
Since B1 screens can be overwhelming at first, we wanted to make it as easy as possible for the user to create the desired documents but in the same way, to keep the basic features such as the possibility to add text lines.
You may wonder why such a portal was built for something as standard as creating and modifying documents. The answer is simple: if the global behavior was to be kept as standard as possible, the solution would also come with a great number of custom business rules, specific to the hotel business.
Make sure you also check the Gallery section (click to access) at the bottom of this page for more screenshots!
Let us now take at a look at the technology stack that was used to build the portal!
SAPUI5
The application UI was built using SAPUI5.
There are two main reasons for that:
- The portal was thought to be used alongside the B1 client. And since the B1 client for HANA comes with a UI5 look, this blended nicely with the ERP.
- Designing screens can be time-consuming. And that is precisely one of the strengths of UI5. The framework comes with a very wide sets of controls that you only need to provide data to (usually as JSON or XML if you’re consuming OData services). The controls are then rendered accordingly in HTML. The framework implements what’s referred as “Fiori guidelines” which are a set of UX rules to provide the user with a nice, friendly and clear UI.
In a nutshell, the developer can focus on his code rather than on the UI itself.
Service Layer
Luckily for me, this was a brand-new SAP Business One solution and therefore, the customer opted for an SAP HANA version, giving me access to integration features that do not (yet) exist in the MSSQL version, one of them being the Service Layer.
I’ve already written some stuff about the Service Layer in my previous articles but just to sum it up, it is an out-of-the-box REST API provided by SAP that allows you to Create, Read, Update and Delete (when possible) business objects (such as invoices, business partners, items etc.…) in the ERP.
Since we’re talking about creating a web-application, you can easily understand that a REST API is perfectly fitted for this kind of development. 😊
At the moment, the Service Layer is only available for the HANA version. It’s undoubtedly a huge lack in the MSSQL version because it closes the doors for many integration possibilities.
ASP .NET Core
In the previous sections, we’ve discussed the general concept of customizing SAP B1 (see section Customizing SAP B1: Why and how?) and we’ve seen that we often needed a layer on top of the B1 logic to implement additional rules.
In this case, these so-called custom rules were written inside an ASP .NET Core Web API that is consumed via the SAPUI5 page. The Web API can then forward the request to the Service Layer when data needs to be updated in the B1 system.
Simply put: it’s an API calling another API (the service layer). This concept is represented below:
From left to right:
- SAPUI5
- Sends AJAX requests to the ASP .NET Core API
- ASP .NET Core
- Processes the request
- Performs additional checks
- Forwards the request to the Service Layer
- Service Layer
- Adds/Updates a business object in SAP B1
As you can see, the UI never queries directly the Service Layer. That is for two main reasons:
- First of all, the Service Layer does nothing more than using B1 standard logic to add or update the system, it doesn’t implement any customer-specific logic (except the additional checks you would put in the Transaction Notification).
- It would be a security flaw to give access to the Service Layer directly in the UI as the user could forge his own requests to the system without any proper authorization flow. The API also performs additional authorization checks based on the authenticated user.
If you’re still up for some good old UML sequence diagram, another representation of this concept is the following:
This works whenever you need to add data into the ERP. You just need to make sure that you automatically renew your Service Layer access token whenever it expires.
Fetching data from SAP B1: What I didn’t do
When it comes to fetching data from the ERP such as a list of business partners, sales orders, or items, the most straightforward approach would be to open a (pool of) SQL connection(s) to the server (in this case, the HANA server) and read data out of SQL queries.
We usually don’t want to use the Service Layer (or any API in general) for strict reading purpose because:
- It is slower than executing SQL Queries
- It doesn’t expose all the fields. Technical fields for instance aren’t returned (such as DocEntry of OCRD Table), and yes, OCRD Table does have a DocEntry field (I bet you didn’t know that, did ya?)
- It isn’t as flexible as an SQL Query, particularly if you’re performing complex filtering operations.
So, a common approach would be to have two communication channels:
- One (or a pool of) connection(s) between the API and the Database for reading operations.
- One connection between the API and the Service Layer for writing operations.
Nowadays, people tend to use Object-Relational Mapping tools (abbreviated ORM) to access the data from a database.
If you’re not familiar with the concept of ORM, just think of it a tool that allows you to transform database rows into in-memory objects (and vice-versa).
In the example above, a typical use of the ORM would be to fetch a customer along with all of his nested objects, such as his addresses (Table CRD1) and his contact persons (Table OCPR). This can be achieved without writing a single line of SQL.
This is particularly useful when complex operations and checks are applied on the business objects which is the case for this project.
Such a tool greatly increases productivity and drastically reduces development time.
Since we’re working on top of an existing system, some ORMs include a Database-First approach, which consists in scanning the tables and generating class code to represent them in memory.
The problem with SAP Business One data model is that it does not include foreign keys at all. So, you basically end up with a bunch of tables that you manually need to link together through joins… which eventually leads to the same result as writing SQL queries.
I ended up not using this approach but if you still want to build something with an ORM linked to the HANA database, just make sure that you choose Entity Framework 6 over Entity Framework Core which is not compatible with SAP HANA.
Fetching data from SAP B1: What I did
ERPs have become more than just a piece of software in a company, it is often used a central hub for all the services of the company (inventory, sales, purchases, human resources etc…)
Building a window to your ERP system is not insignificant as it gives access to your data. Extra precautions and analysis need to be done and even more if you’re building an extranet application or if the current application is likely to be exposed publicly in the future.
Having that in mind and knowing that the data structure of SAP Business One couldn’t be easily manipulated via ORM (at least, not the way that I wanted), I decided to go for a totally different approach.
The idea was to take the data out of B1, transform it, and store it in another database, using, this time, a code-first approach.
When using a code-first approach, the first step is to define your model in the code through classes (in our case, C# classes), define the relations between your different entities, and let the ORM translates the classes and relations into tables and foreign keys. This is shown in the diagram below:
Wow, hold on there! MariaDb? Where’s SAP HANA?
That’s right, I used a MariaDb database to store my translated B1 data schema for several reasons: I needed something small, reliable and compatible with the ORM that I chose: Entity Framework Core!
You may legitimately ask yourself:
What is the benefit of extracting the structure of SAP Business One to rebuild it later on ?
The key thing to understand is that, we’re not necessarily building the same data structure. It actually has several advantages:
- The data structure is created specifically to fit with our business needs. On a conceptual side, it’s possible to create links between entities that we know will be useful. This can fasten both the development and the overall performance of the API.
If you’re not convinced, just think about the serial number allocation table (OITR/ITR1) in SAP Business One and how hectic it can be to link it to the corresponding documents. A tailor-made-data-structure makes it both easier & faster to query data.
- On top of that, it allows you to restrict the data that is made available outside of the ERP. This is particularly important if you’re thinking about opening the application to the extranet. What’s more, this restriction can be applied at two levels.
- Let us take as an example the Business Partners table (OCRD). You’re not necessarily exposing the whole set of business partners and inside of this set, you’re not necessarily exposing all the columns.
It comes with two drawbacks:
- You need a synchronizer to fetch and transform the data from the ERP to this second database.
- The data is not necessarily real-time with SAP, you have to wait for the synchronizer to fetch the latest updates.
And I can honestly say that it’s hugely compensated by the benefits it brings.
Global Architecture
And… There we are with our final architecture. Let’s recap:
- A request is being sent to our custom API, written in ASP .NET Core
- This request is being forwarded either
- To the service layer when data needs to be added/updated in B1.
- To an ORM (Entity Framework Core) for reading purpose.
- Additionally, a synchronizer program runs every x minutes to transform, filter, and store B1 data into a second database.
And guess what, it works like a charm!
Conclusion
This is the first time I am able to build (top to bottom) an enterprise-grade solution with an architecture that I had in mind since I started developing my small B1 web client (you can read about that in my previous articles, in this blog)
I can’t stress how rewarding it feels to see it come to life and get used in order to make people’s life easier in their daily tasks.
At the same time, I was also busy working on another project, but this time, with an existing MSSQL installation. If you’re curious to see how I got it working without the Service Layer, stay tuned! 😊
Thanks for reading and hope you liked the article! Cheers. 🤙
Gallery
These are screens with data from a SAP B1 Demo database.
