Skip to main content

SAP Business One

Pre-requisites

  • Data Hub v10 or later

  • SAP Business One 9.0 or later

  • Install Di API when Using a non direct SQL Server source connection Details here

  • When using the HANA source connection:

    • a trusted https connection for the SLD server is required Details here

    • the HANA service package should also be deployed Details here

Versioning and terminology

Below is the required information needed to understand the services that ZAP uses to support connecting to SAP B1 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 B1 connector interfaces with the SAP B1 datasource, which may be using an API such as: SLD-based DI API, Odata or by directly connecting to the SQL Server database.

  • SAP B1 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 (using DI API), SQL Server Direct Connection (Di API not required), HANA (Di API not required)

Versioning and Support

Version Support Matrix for the ZAP SAP B1 connector:

SAP B1 Version

On-Premises

Partner-hosted SAP B1 Cloud v1.10

(with Cloud Control Center)

SAP-hosted SAP B1

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 (connect to database)

Not Supported: HANA

Supported:

  1. SQL Server (Connect to database)

Not supported:

  1. SQL Server (DI API)

  2. HANA

N/A

Every module EXCEPT Production

9.3

Supported:

  1. SQL Server (Di API)

  2. SQL Server (Connect to database)

  3. HANA (using OData Service Layer with prebuilt Semantic Layer views)

Supported:

  1. SQL Server (Connect to database)

  2. HANA (using OData Service Layer with prebuilt semantic Layer views)

Not supported: SQL Server (DI API)

N/A

Every module (Production support added in 9.3)

10.0

Supported:

  1. SQL Server (Di API)

  2. SQL Server (Connect to database)

  3. HANA (using OData Service Layer with prebuilt Semantic Layer views)

Supported:

  1. SQL Server (Connect to database)

  2. HANA (using OData Service Layer with prebuilt semantic Layer views)

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

Configuration

Non Direct SQL Server Connections

1. Install SAP Business One DI API (Only for a non direct SQL Server connection)

Important

The DI API is only required for a non direct SQL connection

3 Data source connection types are available:

  1. SQL Server - this connection requires the Di API to be installed before connecting in Data Hub.

    SAP_B1_SQL_Service_Connection.png

  2. SQL Server (Read from database directly) - does NOT require the API installation

    SApBI_Direct_SQL.png

  3. HANA - does NOT require the API installation

    SAP_B1_HANA_Connection.png

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.

Install the DI API for new SAP Business One installations

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

  • 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.

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.

Install the DI API for 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

1. SLD Secure connection for HANA

For connecting to SAP HANA, ensure that the url used for the SLD Server is a trusted connection using https. If Http is used or the connection isn't trusted due to SSL certificate warnings, the connection to HANA from Data Hub will fail.

SAP_B1_Secure_SLD_Connection.png

2. HANA Service Package deployment

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 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 in order for the ZAP data source to be able to extract data from them.

Steps

  1. Download the Data Hub Solution Package for SAP Business One on the Connect page of Data Hub.

    SAP_B1_HANA_Connection.png

  2. In SAP Business, 1 go to Modules > Administration > Setup > General > SAP HANA Model Management.

  3. Click Import.

  4. Choose the ZAP Solution Package.

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

  6. Click Deploy.

  7. Once this process is complete, the solution can be deployed in Data Hub using HANA as your source type.

Customization

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

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:

  1. Complete the following fields:

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

    2. Processor Architecture - Select the Processor Architecture of the SAP Business One DI API installed.

    3. 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.

    4. SLD User Name - Provide a user name with access to the System Landscape Directory Server.

    5. SLD Password - Provide the password for the SLD User Name.

    6. Database Server Type - Select the Version of Microsoft SQL Server, or HANA for the SAP Business One Database.

    7. 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.

    8. 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).

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

    10. 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.

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

  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.

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

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.

Troubleshooting

In this section: