Cmdlets for SAP ByDesign

Build 24.0.9060

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.

Installing and Connecting

If you have PSGet, installing the cmdlets can be accomplished from the PowerShell Gallery with the following command. You can also obtain a setup from the CData site. 

Install-Module SAPByDesignCmdlets

The following line is then added to your profile, loading the cmdlets on the next session: 

Import-Module SAPByDesignCmdlets;

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'

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;

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.

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) 2024 CData Software, Inc. - All rights reserved.
Build 24.0.9060