Skip to main content

SAP Business One

Overview

The SAP Business One data source supports connecting to either a SQL Server database or HANA database, and is chosen using the Database Type dropdown. If using a SQL Server database, see SQL Server for connection instructions. For HANA databases there are two connection types available via the HANA Service Type dropdown:

  • HANA ODBC: (recommended): the most performant and fastest way to connect to a HANA database. Requires installing a Data Gateway and a HANA database user with ODBC access.

  • HANA Service Layer: allows remote connections to the HANA database. Requires a minimum SAP Business One version of 10.0 FP2011 or higher and deploying a configuration file.

Terminology

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

  • SAP HANA Studio: The primary tool for accessing the SAP HANA database and it is an optional prerequisite for utilizing HANA ODBC.

  • SAP Business One Client Tool: The primary tool for accessing SAP Business One companies and it is a prerequisite for utilizing HANA Service Layer and HANA ODBC.

Connection Types

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

SQL Server

After setting the Database Type dropdown to SQL Server, there is a SQL Server Service Type setting for connecting using a Direct SQL Server connection or DI API. Direct SQL connections involve less configuration and is the recommended choice over DI API. SQL login credentials require access to the SBOCOMMON database and all required company databases. Refer to DI API (Legacy) for configuring DI API connections.

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

    DirectSqlSettingConnection.png

  • 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

HANA

SAP HANA provides an Open Database Connectivity (ODBC) driver for accessing the SAP HANA database. The requirements to connect are the HDBODBC32 driver installed on a Data Gateway machine, and credentials for a HANA database user with access. Note: the driver should already be available if the Data Gateway has been deployed to a machine that has the SAP Business One Client Tool already installed. If a HANA database user is not already available, follow the steps in Creating ODBC user to create one. Note this is not the same account used to sign in using the SAP Business One Client Tool.

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

    OdbcSettingConnection.png

  • Service LayerSAP 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

Connection Configuration

SQL Server

After setting the Database Type dropdown to SQL Server, there is a SQL Server Service Type setting for connecting using a Direct SQL Server connection or DI API. Direct SQL connections involve less configuration and is the recommended choice over DI API. SQL login credentials require access to the SBOCOMMON database and all required company databases. Refer to DI API (Legacy) for configuring DI API connections.

HANA ODBC

SAP HANA provides an Open Database Connectivity (ODBC) driver for accessing the SAP HANA database. The requirements to connect are the HDBODBC32 driver installed on a Data Gateway machine, and credentials for a HANA database user with access.

Note

The driver should already be available if the Data Gateway has been deployed to a machine that has the SAP Business One Client Tool already installed.

If a HANA database user is not already available, follow the steps in Creating an ODBC user to create one.

Note

This is not the same account used to sign in using the SAP Business One Client Tool.

Process architecture note:

Data Hub only supports the ODBC 32 bit driver (HDBODBC32) and requires the Process Architecture setting to be 32 bit.

Creating an ODBC user

  1. Open SAP HANA studio.

  2. Open the Systems view on the left and sign into the HANA database server using an admin user, such as the default B1ADMIN user, who has permissions to create users.

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

  4. Right-click Users then select New User.

    OdbcSetup_1.png

  5. in the new user tab, enter a user name and ensure ODBC access is enabled.

  6. Enter a valid password and set "Force password change on next logon" to "No"

    OdbcSetup_2.png

  7. Add SELECT privileges to the SBOCOMMON database and all company databased to connect to.

    OdbcSetup_3.png

  8. Click the green Deploy button near the top right to add the user. If any issues arise, they will be denoted at the top of the new user tab.

HANA Service Layer

SAP Business One HANA provides the "Service Layer" API for remote access to the HANA database. Built in to this API is a standard list of data entities but these are an abstraction on the underlying database tables and not the tables themselves. To access the HANA database tables directly, Data Hub supports the Service Layer SQL API but only some tables are available by default. All tables can be made available to Data Hub but this requires modifying the b1s_sqltable.conf configuration file on the HANA Linux server.

Service Layer configuration

A configuration file with a list of all tables required by Data Hub can be downloaded from the SAP Business One HANA Package link on the connection screen of a SAP Business One Data Source. Inside is a b1s_sqltable.conf file to either merge with or replace the same configuration file used by the HANA instance. The file will be located at <service layer installation>/conf/b1s_sqltable.conf, with <service layer installation> usually being /usr/sap/SAPBusinessOne/ServiceLayer/. If access to the Linux server through RDP is not possible, tools such as WinSCP may be necessary.

Service Layer Permissions

The user credentials used to connect must belong to all required companies and must also have Modify SQL Queries in Service Layer permission granted in order to access the Service Layer endpoints required by the data source.

Access to individual companies can be verified by using the SAP Business One Client Tool to sign in to individual companies using the same credentials provided to Data Hub.

Service Layer permissions can be configured by:

  • Sign in to the SAP Business One Client Tool using a super user.

  • Open Administration -> System Initialization -> Authorizations.

  • Select the user to be used for API Access.

  • Authorize the Modify SQL Queries in Service Layer permission.

  • Repeat for each company.

    HANA_UserAuthorizations.png

Note

Super User: If connecting to Service Layer using a Super User, granting the Modify SQL Queries in Service Layer is not required.

Table Customizations:

Data Hub does not automatically discover database customizations (new tables or new table fields) due to limitations of the Service Layer API. However these customizations can be manually added to the model using Data Hub's Source SQL feature

Depending on whether the customization is a new table or a new field, SAP requires SQL identifiers to be prefixed:

  • Custom tables must be prefixed with '@ e.g. "SELECT [Name] FROM [@CUSTOM]".

  • Custom fields must be prefixed with 'U_' e.g. “SELECT [U_CustomField] FROM [OITM]”.

Connecting to the data source

Supported Modules

  • Inventory (Inventory Transactions On-Hand, Movement, Valuation)

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

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

  • Financial

    • GL (Actuals, Budgets, Expenses, Statements)

    • Accounts Receivable

    • Accounts Payable

  • Production (v9.3+ only)

    • Issue for Production

    • Receipt from Production

    • BOM

Model-Specific Support

  • Unified Layer (Power BI support)

  • Account Segmentation

  • Currency conversion (historical, financial)

  • Company consolidation

  • Fiscal calendar consolidation

Deployment Steps

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

  • Open an existing Data Model from Resource Explorer.

  • Expand Data Sources.

  • Click Add Data Source.

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

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

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

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.

Legacy Connection details

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

DI API (Legacy) 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.

Service Layer Package (Legacy)

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.

In this section: