Cmdlets for SAP ByDesign

Build 25.0.9539

Establishing a Connection

With the CData Cmdlets users can install a data module, set the connection properties, and start scripting. This section provides examples of using our SAPByDesign Cmdlets with native PowerShell cmdlets, like the CSV import and export cmdlets.

This section documents how to provide authentication for your account and service type and then connect.

Authenticate a SAPByDesign Account

Set the following connection properties to connect to SAP ByDesign.

Connect to a Analytical Service

  • URL: Set this to the Url of your SAP ByDesign site. For example, https://test.sapbydesign.com.
  • ServiceType: If you want to retrieve the reports of a analytical service, specify ServiceType=AnalyticsService;.
  • ServiceName: The name of the service you want to retrieve data from. Only none or one service must be specified. If you want to specify a list of services, see BrowsableSchemas.

    If you're not sure what service to specify, you can always query the sys_schemas to list available services.

  • User: Set this to the username of your account.
  • Password: Set this to the password of your account.

Analytical Service Connection String Example: 

Url=https://test.sapbydesign.com;ServiceType=AnalyticsService;ServiceName=cc_home_analytics.svc;User=Test;Password=test;

Connect to a Custom Service

  • URL: Set this to the Url of your SAP ByDesign site. For example, https://test.sapbydesign.com.
  • ServiceType: If you have a custom service you want to retrieve data from, specify ServiceType=CustomService;.
  • ServiceName: The name of the service you want to retrieve data from. Only none or one service must be specified. If you want to specify a list of services, see BrowsableSchemas.

    If you're not sure what service to specify, you can always query the sys_schemas to list available services.

  • User: Set this to the username of your account.
  • Password: Set this to the password of your account.

Custom Service Connection String Example: 

Url=https://test.sapbydesign.com;ServiceType=CustomService;ServiceName=khsalesorder;User=Test;Password=test;

Connect to the ODataDataSource API

  • URL: Set this to the Url of your SAP ByDesign site. For example, https://test.sapbydesign.com.
  • ServiceType: If you want to query OData Data Source API( odata/analytics/ds) , specify ServiceType=ODataDataSource.
  • ServiceName: The name of the service you want to retrieve data from. Only none or one service must be specified. If you want to specify a list of services, see BrowsableSchemas.

    If you're not sure what service to specify, you can always query the sys_schemas to list available services.

  • User: Set this to the username of your account.
  • Password: Set this to the password of your account.

ODataDataSource Connection String Example: 

Url=https://test.sapbydesign.com;ServiceType=ODataDataSource;ServiceName=Customer.svc;User=Test;Password=test;

Authenticate with Azure AD (SSO)

If your organization uses Microsoft Entra ID (formerly Azure Active Directory) to manage user identities, you can authenticate to SAP ByDesign using Single Sign-On (SSO). This method allows users to connect without directly supplying a username or password.

To use Azure AD authentication, set the following connection properties:

  • URL: Set this to the URl of your SAP ByDesign site.
  • ServiceType: Specify the type of service: AnalyticsService, CustomService, or ODataDataSource.
  • ServiceName: Set this to the name of the service to connect to.
  • AuthScheme: Set this to AzureAD.
  • InitiateOAuth: Set this to GETANDREFRESH to allow automatic token acquisition and refresh.
  • OAuthClientId: Set this to the OAuth client ID of your SAP ByDesign application. This is separate from your Entra ID app registration.
  • SSOProperties: A semicolon-delimited string containing Azure AD configuration details. See below for supported keys.
Azure AD Connection String Example: 
URL=https://my999999.businessbydesign.cloud.sap;
ServiceType=AnalyticsService;
ServiceName=cc_home_analytics.svc;
AuthScheme=AzureAD;
InitiateOAuth=GETANDREFRESH;
OAuthClientId=SAP_APP_CLIENT_ID;
SSOProperties='AzureTenant=TENANT_ID;OAuthClientId=AZURE_AD_APP_CLIENT_ID;OAuthClientSecret=AZURE_AD_CLIENT_SECRET;CallbackURL=http://localhost:33333'

Supported SSOProperties keys:

The SSOProperties connection property contains a semicolon-delimited list of key-value pairs required for OAuth-based authentication with Microsoft Entra ID (Azure AD). These keys correspond to fields used in the OAuth 2.0 authorization code flow, which Entra ID implements for SSO. The cmdlet uses these values to construct the necessary requests to Microsoft's identity platform.

  • AzureTenant: The Entra ID (Azure AD) tenant ID, which can be a GUID or verified domain.
  • OAuthClientId: The client ID of your Entra ID app registration. This is not the same as the SAP app client ID.
  • OAuthClientSecret: The client secret of your Entra ID app.
  • CallbackURL: (optional) The redirect URI registered in Entra ID. The default is http://localhost:33333.
  • Scope: (optional) The OAuth scopes to request during authentication.

Note: There are two OAuthClientId values:

  • The top-level OAuthClientId refers to the app registered in SAP ByDesign.
  • The OAuthClientId inside SSOProperties refers to the app registered in Microsoft Entra ID.
For details on registering a Microsoft Entra ID application, see Creating an Entra ID (Azure AD) Application

Add a new custom ODATA service

  • In the SAP ByDesign work center view, click on "Application and User Management".
  • Select "OData Services".
  • In the dropdown list, select "Custom OData Services".
  • Now you should see a list of your current custom services. To add a new one click on "New".

SAP ByDesign URL Restrictions

The SAP ByDesign cmdlet enforces URL restrictions to avoid exceeding the maximum of allowed URI length, which causes the service to throw "URI Too Long" error"

By default, the length of the columns in the projection is limited to 1000 characters. If the columns selected in the projection surpass this limit, the driver drops the projection specification and retrieves all fields. This may impact performance because of service response delay and increased payload.

In the case of the analytical service (ServiceType=AnalyticsService), the driver throws an error indicating you must reduce the number of columns in the projection. In addition, the server-side filter length is restricted to 1000 characters for the same reasons. If the generated filter length surpasses this limit, the driver drops the additional filters and handles them client-side.

In SAP ByDesign, you can configure or disable these limitations by using the MaxSelectLength and MaxFilterLength hidden connection properties. These properties both default to 1000 characters. If your service does not have these restrictions, set the properties to -1 to disable the length limitations.

SAP IAS

SAP IAS (SAP Identity Authentication Service) is SAP's cloud-based identity provider that enables secure user authentication across SAP and third-party applications. It is a user-based authentication mechanism and requires you to set AuthScheme to SAPIAS or to SAPIASPassword.

To enable SAP IAS authentication, you must first register and configure a custom SAP IAS application as described in SAP IAS SAML App Configuration guide.

Desktop Applications

You can authenticate from a desktop application using a custom IAS application registered in SAP Identity Authentication Service (IAS).

To authenticate via SAP IAS, set the following connection properties:

  • AuthScheme: SAPIAS.
  • InitiateOAuth: GETANDREFRESH.
  • OAuthClientId: The API Key of your SAP ByDesign OAuth2 client application.
  • SAPIASBaseURL: The base URL of the SAP Identity Authentication Service (IAS) tenant.
  • SSOProperties: A semicolon separated list of key-value pairs containing:
    • OAuthClientId: The client Id assigned to your custom SAP IAS application.
    • OAuthClientSecret: The client secret assigned to your custom SAP IAS application.
    (e.g. OAuthClientId=793cf9b4;OAuthClientSecret=K77FErG5S8B;)
  • CallbackURL: The redirect URI you defined when you registered your custom IAS application.

When you connect, the cmdlet opens the SAP IAS OAuth authorization endpoint in your default browser. Log in and authorize the application.

The cmdlet completes the OAuth flow by obtaining an SAML assertion from SAP IAS and exchanging it for an SAP ByDesign access token. This token is then used to authenticate API requests to SAP ByDesign. The OAuth credentials are saved to the location specified by the OAuthSettingsLocation. property and persist across connections.

When the access token expires, the cmdlet automatically refreshes it using the refresh token provided by SAP IAS.

Headless Machines

To configure the cmdlet with a user account on a headless machine, you must first complete authentication on a separate device that has a web browser.

Install the cmdlet on a machine with browser access and perform the standard browser-based authentication flow, as described in the Desktop Applications section above. After successfully authenticating, the cmdlet will store the resulting OAuth values in the file specified by the OAuthSettingsLocation property. By default, this file is named OAuthSettings.txt.

Once you’ve verified that the connection works, copy the OAuthSettings.txt file to your headless machine.

On the headless machine, configure the following connection properties:

  • AuthScheme: SAPIAS.
  • OAuthClientId: The API Key of your SAP ByDesign OAuth2 client application.
  • SAPIASBaseURL: The base URL of the SAP Identity Authentication Service (IAS) tenant.
  • OAuthSettingsLocation: The location of your OAuth settings file. Make sure this location gives read and write permissions to the cmdlet to enable the automatic refreshing of the access token.
  • SSOProperties: A semicolon separated list of key-value pairs containing:
    • OAuthClientId: The client Id assigned to your custom SAP IAS application.
    • OAuthClientSecret: The client secret assigned to your custom SAP IAS application.
    (e.g. OAuthClientId=793cf9b4;OAuthClientSecret=K77FErG5S8B;)

Alternative: Password Grant Flow (No Browser Prompt)

As an alternative to the interactive Code Grant flow, you can use the OAuth Password Grant flow, which allows the application to authenticate without prompting the user in a browser. This flow is ideal for headless, background, or automated server-side applications.

To use the Password Grant flow, set the following connection properties:

  • AuthScheme: SAPIASPassword.
  • OAuthClientId: The API Key of your SAP ByDesign OAuth2 client application.
  • SAPIASBaseURL: The base URL of the SAP Identity Authentication Service (IAS) tenant.
  • SSOProperties: A semicolon separated list of key-value pairs containing:
    • OAuthClientId: The client Id assigned to your custom SAP IAS application.
    • OAuthClientSecret: The client secret assigned to your custom SAP IAS application.
    (e.g. OAuthClientId=793cf9b4;OAuthClientSecret=K77FErG5S8B;)
  • User: The IAS username to authenticate with.
  • Password: The corresponding password for the specified user.

Creating a Connection Object

You can then use the Connect-SAPByDesign cmdlet to create a connection object that can be passed to other cmdlets: 

$conn = Connect-SAPByDesign -URL 'https://my999999.businessbydesign.cloud.sap' -User 'username' -Password 'password' -ServiceType 'AnalyticsService' -ServiceName 'servicename'

Retrieving Data

The Select-SAPByDesign cmdlet provides a native PowerShell interface for retrieving data: 

$results = Select-SAPByDesign -Connection $conn -Table "Account" -Columns @("Id, Name") -Where "Industry='Floppy Disks'"
The Invoke-SAPByDesign cmdlet provides an SQL interface. This cmdlet can be used to execute an SQL query via the Query parameter.

Piping Cmdlet Output

The cmdlets return row objects to the pipeline one row at a time. The following line exports results to a CSV file: 

Select-SAPByDesign -Connection $conn -Table Account -Where "Industry = 'Floppy Disks'" | Select -Property * -ExcludeProperty Connection,Table,Columns | Export-Csv -Path c:\myAccountData.csv -NoTypeInformation

You will notice that we piped the results from Select-SAPByDesign into a Select-Object cmdlet and excluded some properties before piping them into an Export-CSV cmdlet. We do this because the CData Cmdlets append Connection, Table, and Columns information onto each row object in the result set, and we do not necessarily want that information in our CSV file.

However, this makes it easy to pipe the output of one cmdlet to another. The following is an example of converting a result set to JSON: 

 
PS C:\> $conn  = Connect-SAPByDesign -URL 'https://my999999.businessbydesign.cloud.sap' -User 'username' -Password 'password' -ServiceType 'AnalyticsService' -ServiceName 'servicename'
PS C:\> $row = Select-SAPByDesign -Connection $conn -Table "Account" -Columns (Id, Name) -Where "Industry = 'Floppy Disks'" | select -first 1
PS C:\> $row | ConvertTo-Json
{
  "Connection":  {

  },
  "Table":  "Account",
  "Columns":  [

  ],
  "Id":  "MyId",
  "Name":  "MyName"
}

Deleting Data

The following line deletes any records that match the criteria: 

Select-SAPByDesign -Connection $conn -Table Account -Where "Industry = 'Floppy Disks'" | Remove-SAPByDesign

Modifying Data

The cmdlets make data transformation easy as well as data cleansing. The following example loads data from a CSV file into SAP ByDesign, checking first whether a record already exists and needs to be updated instead of inserted. 

Import-Csv -Path C:\MyAccountUpdates.csv | %{
  $record = Select-SAPByDesign -Connection $conn -Table Account -Where ("Id = `'"+$_.Id+"`'")
  if($record){
    Update-SAPByDesign -Connection $conn -Table Account -Columns @("Id","Name") -Values @($_.Id, $_.Name) -Where "Id  = `'$_.Id`'"
  }else{
    Add-SAPByDesign -Connection $conn -Table Account -Columns @("Id","Name") -Values @($_.Id, $_.Name)
  }
}

Copyright (c) 2026 CData Software, Inc. - All rights reserved.
Build 25.0.9539