Skip to main content

SAP Business One

Pre-requisites

Versioning and Terminology

Below is the required information needed to understand the services that ZAP uses to support connecting to SAP Business One data.

Terminology

  • Deployment types: On-premises, Partner-hosted using Cloud Control-center SAP Cloud.

  • Platform types: (MS) SQL Server, (SAP) HANA, SAP Cloud.

  • Interfaces: Refers to how the ZAP SAP Business One connector interfaces with the SAP Business One Data Source, which may be using an API such as DI API, HANA, or directly connecting to an SQL Server database.

  • SAP Business One Versions: refers to major versions of SAP Business One, such as 9.2, 9.3 or 10.

  • ZAP Data Hub Version: assumes you are on the latest version of ZAP. This is the case if a ZAP Cloud subscription is used.

  • Solution: refers to the prepackaged ZAP data model/analytics that is also versioned.

  • Data source connection types: SQL Server (DI API), SQL Server (Direct SQL), HANA (Service Layer SQL), HANA (Service Layer Package) and HANA (ODBC).

Versioning and Support

Version Support Matrix for the ZAP SAP Business One connector:

SAP Business One Version

On-Premises

Partner-hosted SAP Business One Cloud v1.10

(with Cloud Control Center)

SAP-hosted SAP Business One

Cloud in Cloud One

Solution Support

9.0

Not supported

(Expected to work)

N/A

N/A

N/A

9.1

Not supported

(Expected to work)

N/A

N/A

N/A

9.2

Supported:

  1. SQL Server (Di API)

  2. SQL Server (Direct SQL)

  3. HANA (ODBC)

Not Supported

  1. HANA (Service Layer SQL)

  2. HANA (Service Layer Package)

Supported:

  1. SQL Server (Direct SQL)

  2. HANA (ODBC)

Not supported:

  1. SQL Server (DI API)

  2. HANA (service Layer SQL)

  3. HANA (Service Layer Package)

N/A

Every module EXCEPT Production

9.3

Supported:

  1. SQL Server (Di API)

  2. SQL Server (Direct SQL)

  3. HANA (ODBC)

  4. HANA (Service Layer Package)

Not supported: HANA (Service Layer SQL)

Supported:

  1. SQL Server (Direct SQL)

  2. HANA (ODBC)

  3. HANA (Service Layer Package)

Not supported: SQL Server (DI API)

HANA (Service Layer SQL)

N/A

Every module (Production support added in 9.3)

10.0

Supported:

  1. SQL Server (Di API)

  2. SQL Server (Direct SQL)

  3. HANA (Service Layer SQL)

  4. HANA (ODBC)

  5. HANA (Service Layer Package)

Supported:

  1. SQL Server (Direct SQL)

  2. HANA (Service Layer SQL)

  3. HANA (ODBC)

  4. HANA (Service Layer Package)

Not supported: SQL Server (DI API)

Not supported

Every Module

Supported modules:

  1. Inventory (Inventory Transactions On-Hand, Movement, Valuation)

  2. Purchasing (Orders, Goods receipts, returns, A/P Invoices, A/P Credit memos)

  3. Sales (Orders, Delivery, Returns, A/R Invoices, A/R Credit Memos, Forecast)

  4. Financial

    • GL (Actuals, Budgets, Expenses, Statements)

    • Accounts Receivable

    • Accounts Payable

  5. Production (v9.3+ only)

    • Issue for Production

    • Receipt from Production

    • BOM

Solution localization support:

  1. English

Model-specific support:

  1. Unified Layer (Power BI support)

  2. Account Segmentation

  3. Currency conversion (historical, financial)

  4. Company consolidation

  5. Fiscal calendar consolidation

Connection Configuration

Depending on your SAP Business One configuration, the following connection types are supported:

  • SAP Business One - SQL Server

    • DI APISAP Business One: This is an older way of connecting that utilizes SAP Business One's API to connect to SAP Business One.

      DiApiSettingConnection.png

    • Direct SQL: This is the preferred way of connecting as it directly connects to the SAP Business One’s SQL Server.

      DirectSqlSettingConnection.png

  • SAP Business One - HANA

    • Service Layer SQLSAP Business One: This is the preferred way of connecting as it deploys a single configuration file. To use Service Layer SQL, make sure to DISABLE Use Legacy Service Layer Model Package.

      ServiceLayerSqlSettingConnection_1.png
      ServiceLayerSqlSettingConnection_2.png

    • ODBCSAP Business One: This is another preferred way of connecting to HANA through the SAP Business One HANA ODBC Driver.

      OdbcSettingConnection.png

    • Service Layer PackageSAP Business One: This is an older way of connecting and utilizes deploying and updating a Model Package on the SAP Business One instance. In order to continue using this connection, make sure to ENABLE Use Legacy Service Layer Model Package as seen below.

      ServiceLayerPackageSettingConnection_1.png
      ServiceLayerPackageSettingConnection_2.png

DI API SQL Server Connection

Important

Installing the SAP Business One DI API on the same device as Data Hub may lead to any unsaved changes in Data Hub being lost. This is caused by the SAP Business One DI API installation resetting the Microsoft IIS Web Server.

To avoid data loss, action one of the following:

  • Install the SAP Business One DI API when Data Hub is not in use.

  • Install the SAP Business One DI API before installing Data Hub.

New SAP Business One installations

  1. Install SAP Business One using the SAP Business One Administrator's Guide.

    To access the SAP Business One Administrator's Guide, choose one of the following actions:

    • Go to the SAP Help Portal - SAP Business One and open the SAP Business One Administrator's Guide.

    • Select the More Information link on the Welcome to the SAP Business One Setup Wizard screen of the installation wizard.

  2. On the Component Selections screen, select Implementation Tools > Data Interface API check box in addition to any other components you require, then continue installing SAP Business One.

Existing SAP Business One installations

If the Data Interface API has already been installed, move on to Add the SAP Business One Data Source.

On the device hosting the SAP Business One server:

  1. Using Windows File Explorer, navigate to the folder containing the SAP Business One installer setup.exe.

  2. Navigate to Packages > DI API.

  3. In the DI API directory, Double-click on setup.exe to run the SAP Business One DI API installation wizard.

  4. Provide your System Landscape Directory Server (SLD) details, then click Next.

  5. On the Setup Type screen, select Typical, then click Next.

  6. The installation wizard will install the SAP Business One DI API. When prompted, click Finish.

HANA connections

SLD Secure Connections for Service Layer

All Service Layer connections need a SLD Secure connection.

Important

  • For connecting to SAP Business One HANA Service Layer, ensure that the URL used for the SLD Server is a trusted connection using HTTPS.

  • If HTTP is used or the connection is not trusted due to SSL certificate warnings, the connection to HANA Service Layer from Data Hub will fail

SldSecureConnectionForHana.png

ODBC

SAP HANA provides an Open Database Connectivity (ODBC) driver for accessing the SAP HANA database. Instead of making configuration changes to SAP Business One HANA instances, ODBC only requires access to the SAP HANA ODBC Driver. Currently, we only support access to the ODBC 32-bit driver (HDBODBC32).

Important

ODBC requires a data gateway on a machine that has the SAP HANA Client installed and it must include the ODBODBC32 Driver (32-bit driver).

Pre-requisites:

  1. Confirm that you have installed the data gateway on a machine with the ODBC driver.

    Note

    This driver is part of the SAP HANA Client installation, so the data gateway should be installed on that machine if possible.

  2. SAP HANA database user with full access to the SBOCOMMON database and relevant company databases.

    Note

    It is recommended to create a user that has limited permissions rather than using an admin user.

    Steps to create an ODBC user:

    1. Go to SAP HANA Studio, then on the Systems view on the left and sign into your HANA server using an admin user that has the permissions to create users such as the default B1ADMIN user.

    2. Click on the drop-down arrow of your signed-in system to expand the list and expand to Security -->> Users.

      • Right click on the Users and select New Users.

        OdbcSetup_1.png

    3. In the new user tab, enter a User Name and a valid Password.

      • The requirement for the password is at least on capital letter, one lowercase letter, and one digit.

      • Select NO for Force password change on next logon.

      • Ensure ODBC access is Enabled.

      • Set a Valid From date but NO Valid Until date.

        OdbcSetup_2.png

    4. Add the following required Object Privileges:

      • Company databases that your user wish to have access to.

      • The SBOCOMMON database.

      • Make sure the user has Select permissions for these databases.

        OdbcSetup_3.png

    5. Click the green Deploy button near the top right to add the user.

Configuration

  1. Set you Data Gateway to the gateway that includes the HDBODBC32 driver and set your Processor Architecture to 32 bits.

  2. Provide the required connection details:

    • ODBC Server which can be the server name or a fully qualified domain name (FQDN) of the host on which the system is installed with the port specified such as myServer:30015

    • the ODBC user credentials as created in the pre-requisites.

      OdbcConnection_1.png
Customization

The SAP Business One HANA ODBC service type automatically discovers customizations so long as they exist in all companies. This means if only one selected company has a custom table, then it will not appear if other companies are selected, since we only retried common tables and columns from all selected companies.

Service Layer Package

SAP Business One HANA data source can extract data from both HANA’s Service and Semantic Layers. The solution for SAP Business One HANA however, is only concerned with the Semantic Layer.

A Model Package containing the necessary semantic views is provided to customers to import into their SAP Business One application. Once imported into SAP Business One, all views need to be exposed to the Semantic Layer for the ZAP SAP Business One Data Source to be able to extract data from them.

Important

Service Layer SQL and ODBC are recommended for HANA database types before attempting to use the Service Layer Package.

If you are using a solution and it updates, please note that you may have to download and deploy a new Model Package again since it may include exposing more required tables and columns.

Steps

  1. Please contact the Support Centre to request access to the package before you download the SAP Business One HANA Package on the Connect page of the SAP Business One Data Source. Be sure to enable User Legacy Service Layer Model Package.

    ServiceLayerPackageSettingConnection_1.png
    ServiceLayerPackageSettingConnection_2.png
    ModelPackageLocation_1.png

  2. For each company in SAP Business One that you wish to use in Data Hub:

    • Go to the SAP Business One’s Client tool and then navigate to Modules -> Administration -> Setup -> General -> SAP HANA Model Management.

    • Click Import.

    • Choose the downloaded Model Package (ZAP_HanaDeployablePackage.zip).

    • Click the Expose Service Layer check box for every view.

    • Click Deploy.

  3. Once you have completed this process for each company, the solution can be deployed in Data Hub using HANA as your database type and Service Layer as your HANA service type.

    Important

    Make sure to enable the Use Legacy Service Layer Model Package as shown in step 1.

Customization

If you need to customize the view in the package, please contact the Support Center.

Service Layer SQL

Service Layer SQL for HANA supports a highly flexible SQL Query. Instead of deploying and updating Model Packages to expose Service Layer views as is the case in the legacy connection, Service Layer SQL reduces the manual effort to access these tables by completing a one-time configuration.

To expose all tables and their columns to the Data Source you must upload one configuration file to your SAP Business One HANA Linux Server.

Pre-requisites:

  1. Confirm that you have a minimum SAP Business One version of 10.0 FP2011 or higher.

  2. Admin access to your SAP HANA Linux server

    • Read/write access to the following path <service layer installation>/conf/b1s_sqltable.conf

    • Your <service layer installation folder> may be different, but the common location is the following: /usr/sap/SAPBusinessOne/ServiceLayer/

    • You must have the ability to copy and paste files into the folder. If you cannot access your Linux server through RDP, then you might require tools such as WinSCP.

  3. User account with full authorization access to Service Layer SQL Query. Create a:

    • Super user account

    • Normal user account granted with the authorization to create, update, remove, and read Service Layer SQL queries:

      1. Login to SAP Business One client with a super user

      2. Open the General Authorization window from the menu Administration--> System Initialization--> Authorizations and grant Full Authorization to the user on the subject Modify SQL Queries in Service Layer

        HANA_UserAuthorizations.png
Configuration Steps:

  1. Download the SAP Business One HANA Package on the Connect page of the SAP Business One Data Source. Ensure you have the Service Layer as your HANA service type.

    ServiceLayerSqlSettingConnection_1.png
    ServiceLayerSqlSettingConnection_2.png
    ServiceLayerSqlConfigurationFile.png

  2. Upload the configuration file b1s_sqltable.conf from the SAP Business One HANA Package you downloaded to the SAP Business One HANA Linux Server.

    TransferConfigFile.png
Customization:

The SAP Business One HANA Data Source does not automatically discover customizations due to limitations in Service Layer SQL. However, you can still manually add these customizations.

For UDTs (custom tables), to achieve this, you must modify the downloaded b1s_sqltable.conf file that we have provided and add the name of the table to the end of the table list in the file. Make sure to add ‘@’ as a prefix to the table to signify it is a custom table (e.g., @CUSTOM).

HANA_AddingCustomTable.png

For UDFs (user-defined fields), you do not require any additional manual configuration.

To access these customisations, you will have to use Source SQL as UDTs will not appear on the table list. In addition, UDFs will not appear on the column schemas of existing tables. Therefore, you must make custom Source SQL queries to access UDTs and UDFs.

For Example:

  • To access a UDT the query would be “SELECT Name FROM @CUSTOM”.

  • To access a UDF the query would be “SELECT U_CustomField FROM OITM”.

    Note

    CustomField is the name of the UDF but make sure to add ’U_’ as a prefix as it is a requirement by Service Layer SQL.

Connecting to the data source

To add a SAP Business One data source to an EXISTING Data Model:

  1. Open an existing Data Model from RESOURCE EXPLORER.

  2. Expand Data Sources.

  3. Click Add Data Source.

To add a SAP Business One data source to an NEW Data Model:

Select source

In the Select Source step:

  1. Under the Apps (On-prem) section, select SAP Business One.

  2. From the source type pop-up, click Connect on the SAP Business One entry tagged Data Source.

Connect

To set up the connection between Data Hub and SAP Business One, complete the following fields:

  1. Complete the following fields:

    • Data Gateway - Leave this item set to None unless you are using a Data Gateway.

    • Caption Language - Select the language used to display user-friendly descriptions for elements stored by SAP Business One.

    • Service Type - Select the type of service to connect to your SAP Business One instance. Direct SQL and DI API use SQL Server.

      DI API

      1. System Landscape Directory Server (SLD) - Location of the SAP System Landscape Directory Server (SLD). Use the server name if the SLD server is on the same network as the Data Hub server. Otherwise, a fully-qualified domain name (FQDN) is required.

      2. Company User Name - Specify the user name needed to access the availability SAP Business One companies.

      3. Company Password - Specify the password for the Company User Name.

      4. Database Server Type - Select the Version of Microsoft SQL Server for the SAP Business One Database.

      5. Database Server - Type the SAP Business One System database name or select the database from the drop-down. Data Hub automatically combines data from SAP Business One company databases for Configure Source and Select Data steps.

      6. Advanced Settings:

        • Database Authentication - Depending on how your instance of SAP Business One is configured, authentication credentials are provided by either

          1. SLD Authentication - Select to use SLD authentication to connect to the SAP Business One Database Server.

          2. Windows Authentication - Select to use a Windows user name and password to connect to the SAP Business One Database Server. Active Domain credentials are also accepted.

          3. SQL Server Authentication - Select to use a SQL Server user name and password to connect to the SAP Business One Database Server.

        • Database User Name - Enter the user name of a user with access to the SAP Business One instance. The user name provided may require the domain name (such as domain\user) or a User Principal Name format (user@fully-qualified_domain_name).

        • Database Password - Enter the password for the provided user name.

      Direct SQL

      1. Database Server - Type the SAP Business One System database name or select the database from the drop-down. Data Hub automatically combines data from SAP Business One company databases for Configure Source and Select Data steps.

      2. Advanced Settings:

        • Authentication - Choose the type of authentication required to connect to your SQL Server.

          1. Windows Login - Select to use a Windows username and password to connect to the SAP Business One Database Server.

          2. SQL Server Login - Select to use a SQL Server username and password to connect to the SAP Business One Data Server.

        • User Name - Specify the authentication method's username.

        • Password - Specify the user name's password.

      HANA

      1. System Landscape Directory Server (SLD) - Location of the SAP System Landscape Directory Server (SLD). Use the server name if the SLD server is on the same network as the Data Hub server. Otherwise, a fully-qualified domain name (FQDN) is required.

      2. Company User Name - Specify the user name needed to access the availability SAP Business One company.

      3. Company Password - Specify the password for the Company User Name.

      4. Service Layer Server - Type the service layer address of the SAP Business One HANA instance to which you want to connect.

      5. Advanced Options:

        • Force Legacy Model Package - HANA provides two connection types:

        • Legacy: ENABLE Force Legacy Model Package.

        • Service Layer (Recommended method): DISABLEForce Legacy Model Package.

          Note

          This is already done by DEFAULT

  2. Click Test Connection to verify that DATA HUB can connect to the SAP Business One database with the credentials provided.

  3. Click Next to continue to the Configure Source step.

Configure Source

To specify which entities (or companies) should be available for analysis in Data Hub:

  1. Specify which entities to include for analysis by selecting or clearing the check box beside the entity names.

  2. Select the Primary Company check box for the primary entity (if there is more than one entity).

  3. Click Next to continue to the Select Data step.

Select data

To add tables from the SAP Business One database to the Data Model:

  1. Select tables to be added as pipelines using the check box column.

  2. Ensure the table Zap.StringMap is selected. This table is used to provide user-friendly descriptions for elements stored by SAP Business One.

  3. To add the pipelines to the data model, there are three options:

    1. Use the Add Pipelines to add tables as individual pipelines.

    2. Use Add Pipeline as Union of Tables option from the Add Pipelines drop-down, to add a single pipeline created from a union of the selected tables.

    3. Use the Finish button to add tables as individual pipelines and finish adding the SAP Business One data source.

      Note

      If all of the tables in the data source are selected, the pipeline will always perform a union on all tables from the data source, even if the table list changes. For example, if the data source contains only tables of sales for USA, UK, and Europe, selecting them all will union the three tables into a single pipeline. If a fourth table with sales for Australia is later added, it will automatically be included in the union for the pipeline.

  4. If the Finish button was not used to add pipelines, click Finish to complete adding the SAP Business One data source.

Select data screen elements

Select Data screen contains the following elements:

  1. Select All/Clear

    1. If no table check boxes are currently selected: Select All can be used to select all of the tables in the source database.

    2. If one or more table check boxes are selected: Clear (x) can be used to clear all the selected tables.

  2. Show selected tables only - Hides all tables where the check box column is currently cleared (not selected).

  3. Show tables without rows - When selected, this item shows all source tables, including tables that do not contain data.

  4. Add Pipelines - Select Add Pipelinesto add all selected tables to the data model as individual pipelines. The Add Pipelines drop-down menu provides two options:

    1. Add Pipeline as Union of Tables creates a single pipeline from the union of selected tables from this data source.

    2. Add Pipelines is the same function as the Add Pipelines button.

      Note

      If all of the tables in the data source are selected, the pipeline will always perform a union on all tables from the data source, even if the table list changes. For example, if the data source contains only tables of sales for USA, UK, and Europe, selecting them all will union the three tables into a single pipeline. If a fourth table with sales for Australia is later added, it will automatically be included in the union for the pipeline.

  5. Finish - Adds the selected tables as individual pipelines and finalizes adding the SAP Business One data source. Once the request has been processed, Data Hub opens the Data Model Overview.

  6. The filter row - The first row of the Select Data table is the filter row. Use the fields to filter rows based on the column contents. To clear all filters entered, either delete the entered text or use the Clear filter icon at the end of the row.

  7. The Select Data step table - A table showing the tables present in the data source. Described below.

Select data table columns

The SAP Business One Select Data table contains the following columns:

  • The check box column - Select to create a pipeline for this source table.

  • Table - The database table name.

  • Caption - The descriptive table name used in SAP Business One.

  • Relationships - Shows source tables that share relationships with the current table. These relationships join tables, allowing for easier report building.

In this section: