This page provides a guide to Establishing a Connection to SAP SuccessFactors in CData Cloud, as well as information on the available resources, and a reference to the available connection properties.

Connecting to SAP SuccessFactors

Establishing a Connection shows how to authenticate to SAP SuccessFactors and configure any necessary connection properties to create a database in CData Cloud

Accessing Data from CData Cloud Services

Accessing data from SAP SuccessFactors through the available standard services and CData Cloud administration is documented in further details in the CData Cloud Documentation.

Connect to SAP SuccessFactors by selecting the corresponding icon in the Database tab. Required properties are listed under Settings. The Advanced tab lists connection properties that are not typically required.

Connecting to SAPSuccessFactors

The CData Cloud communicates to SAP SuccessFactors over the OData API, which is enabled by default. If you need to provide additional permissions, please follow the steps from SAP laid out in their support site.

You can authenticate to SAP SuccessFactors using either Basic authentication or OAuth with SAML assertion.

Basic

In order to use Basic authentication, you must set a number of connection properties and you must grant access to the API.

Required Connection Properties

• AuthScheme = BASIC.
• Url = the url of the server hosting Success Factors. Some of the servers are listed here.
• User = the username of your account.
• Password = the password of your account.
• CompanyId = your company's unique identifier.

Be aware that in Basic authentication, SAP SuccessFactors uses cookies to reuse the session after your first request to connect. This means that your credentials are only used the first time you connect to SAP SuccessFactors. After that, authentication is accomplished using cookies returned from SAP SuccessFactors.

Granting Access to the API

Once OData is enabled, you must grant access to the API to activate Basic Auth for a given user.

For an RBP System:

1. Navigate to Administrator Permissions > Manage Integration Tools.
2. Assign Allow Admin to Access OData API through Basic Authentication to the user.

For a User-Based System:

1. Navigate to Administrative Privileges > Integration Tools.
2. Assign Allow Admin to Access OData API Through Basic Authentication to the user.
3. Navigate to the Managing Administrative Privilege page.
4. Assign Employee Export and Employee Import to the user.

OAuth

SAP SuccessFactors supports OAuth authentication only. To enable this authentication from all OAuth flows, you must set AuthScheme to OAuth, and you must create a custom OAuth application.

The following subsections describe how to authenticate to SAP SuccessFactors from three common authentication flows:

• Desktop: a connection to a server on the user's local machine, frequently used for testing and prototyping.
• Web: access to data via a shared website.
• Headless Server: a dedicated computer that provides services to other computers and their users, which is configured to operate without a monitor and keyboard.

For information about how to create a custom OAuth application, see Creating a Custom OAuth Application. For a complete list of connection string properties available in SAP SuccessFactors, see Connection.

Automatic refresh of the OAuth access token:

To have the Cloud automatically refresh the OAuth access token, do the following:

1. Before connecting to data for the first time, set the following connection parameters:
   • InitiateOAuth = REFRESH.
   • OAuthClientId = the client Id in your application settings.
   • OAuthClientSecret = the client secret in your application settings.
   • OAuthAccessToken = the access token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation = the path where you want the Cloud to save the OAuth values, which persist across connections.
2. On subsequent data connections, set the following:
   • InitiateOAuth
   • OAuthSettingsLocation

Customizing the SSL Configuration

By default, the Cloud attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store.

To specify another certificate, see the SSLServerCert property for the available formats to do so.

Connecting Through a Firewall or Proxy

HTTP Proxies

To connect through the Windows system proxy, you do not need to set any additional connection properties. To connect to other proxies, set ProxyAutoDetect to false.

In addition, to authenticate to an HTTP proxy, set ProxyAuthScheme, ProxyUser, and ProxyPassword, in addition to ProxyServer and ProxyPort.

Other Proxies

Set the following properties:

• To use a proxy-based firewall, set FirewallType, FirewallServer, and FirewallPort.
• To tunnel the connection, set FirewallType to TUNNEL.
• To authenticate, specify FirewallUser and FirewallPassword.
• To authenticate to a SOCKS proxy, additionally set FirewallType to SOCKS5.

The CData Cloud models SAP SuccessFactors entities in relational Tables, Views, and Stored Procedures. The table definitions are dynamically obtained from the OData service you connect to. Any changes in the metadata, such as added or removed columns or changes in data type, can be loaded by reconnecting.

Tables

The Cloud models the writable entity sets and singletons described in the service metadata document as bidirectional Tables.

Views

Some OData entities can only be accessed through Navigation Properties. By default, the Cloud models navigation properties as separate views. See Views for more information on querying navigation properties.

Stored Procedures

Stored Procedures are function-like interfaces to the data source. They can be used to search, update, and modify information in the data source.

The Cloud exposes tables for every entity set and singleton defined on the OData service document. Entities on these tables may be inserted, updated, or deleted using standard SQL insert, update, or delete statements.

Executing Deep Inserts with SQL

The Cloud supports OData deep inserts, in which you simultaneously create a base entity and link it to related entities, by specifying navigation properties. To specify Navigation Properties for an entity, you create a temporary table for the navigation property and then reference the temporary table in the insert to the base table. Reference the temporary table in the appropriate navigation property column on the base table. Each navigation property column is prefixed with the word "Linked".

Note that you must define the temporary tables and insert to the base entity within the same connection.

Example: Deep Inserts in SAP SuccessFactors

For example, consider the ExternalUser table. When creating an ExternalUser, you may also specify an ExtAddressInfo, ExtEmailInfo, ExtPersonalInfo, and ExtPhoneInfo. To do so, you can specify the following navigation properties.

Creating Temporary Tables

Insert the related entities into temporary tables that correspond to each navigation property. You can specify an existing entity's primary key or you can insert a new entity.

• ExtAddressInfo: The following statement creates a new ExtAddressInfo:
  

  INSERT INTO ExtAddressInfo#TEMP (address1, city, country, zipCode) 
  VALUES ('123 Fake Street', 'Springfield', 'USA', '58008')

• ExtEmailInfo: The following statements add two ExtEmailInfos to the ExternalUser:
  

  INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'business', true)
  
  INSERT INTO ExtEmailInfo#TEMP (EmailAddress, EmailType, isPrimary) VALUES ('[email protected]', 'personal', false)

• ExtPersonalInfo: The following statement specifies the existing ExtPersonalInfo:
  

  INSERT INTO ExtPersonalInfo#TEMP (personalInfoId) 
  VALUES (5)

• ExtPhoneInfo: The following statement specifies the existing ExtPhoneInfo:
  

  INSERT INTO ExtPhoneInfo#TEMP (phoneInfoId) VALUES (3) 

The CData Cloud will assume that the ExtPhoneInfo and ExtPersonalInfo already exist and will only link to the existing references since only the primary keys were specified for either. When more than just the primary key is defined, such as the examples for ExtAddressInfo and ExtEmailInfo, the CData Cloud will attempt to create new entries - triggering the deep insert.

Inserting the Entity

In the INSERT statement for the base entity, reference the temporary tables in the LinkedExtAddressInfo, LinkedExtEmailInfo, LinkedExtPersonalInfo, and LinkedExtPhoneInfo columns:

INSERT INTO ExternalUser (password, productName, userId, userName, LinkedExtAddressInfo, LinkedExtEmailInfo, LinkedExtPersonalInfo, LinkedExtPhoneInfo) 
VALUES ('password', 'productname', 'newuserid', 'newusername', 'ExtAddressInfo#TEMP', 'ExtEmailInfo#TEMP', 'ExtPersonalInfo#TEMP', 'ExtPhoneInfo#TEMP')

Modeling Navigation Properties

By default, the Cloud models Navigation Properties as separate views. The views are named in the format ParentTable_NavigationProperty.

Querying Navigation Properties

For an example of working with a navigation property as a view, the JobApplication entity has a lot of navigation properties. We will look at the "education" navigation property. The CData Cloud will display a view called JobApplication_education. Retrieving data from JobApplication_education will display the JobApplicationSnapshot_Education object associated with a given JobApplication. The JobApplication_education view has a primary key made up of the Id of the parent entity and the Id of the related entity.

In OData, a navigation property is a property on an entity that is itself either a single entity or list of entities.

A single-entity navigation property signifies a one-to-one relationship. For example, SAP SuccessFactors has the entity JobApplication which contains an Eduction navigation property, which references a single JobApplicationSnapshot_Education entity.

An entity set navigation property signifies a one-to-many relationship. For example, in SAP SuccessFactors, the JobApplication has a Langauges navigation property which can reference many JobApplicationSnapshot_Languages objects.

Working with Navigation Properties Relationally

Navigation properties in OData link related entities. Similarly, in a relational database, a foreign key serves to link tables. For example, the JobApplicationSnapshot_Language record contains the applicationId column, which uniquely identifies what JobApplication the JobApplicationSnapshot_Language belongs to.

Select

It can be used to retrieve related entites that do not exist by themselves such as the emailTypeNav on PerEmail. See Views for more information on querying navigation properties.

Insert

The Cloud supports OData deep inserts. See Tables for more information on specifying navigation properties when you create an entity.

Stored procedures are function-like interfaces that extend the functionality of the Cloud beyond simple SELECT/INSERT/UPDATE/DELETE operations with SAP SuccessFactors.

Stored procedures accept a list of parameters, perform their intended function, and then return any relevant response data from SAP SuccessFactors, along with an indication of whether the procedure succeeded or failed.

CData Cloud - SAP SuccessFactors Stored Procedures

Name	Description
CreateAssociation	Creates an association between two entities based on a navigation property.
ListAssociations	Lists associations for a given table and navigation property.
ListNavigationProperties	Lists navigation properties for a given table and the tables they are associated with. Navigation properties are used by the Association stored procedures.
RemoveAssociation	Removes an association between two entities based on a navigation property.

Creates an association between two entities based on a navigation property.

Input

Name	Type	Required	Description
FromId	String	True	The Id of the entity you are creating an associations for.
FromTable	String	True	The table where the entity comes from that you are creating an association for. For example, if the FromId was from a table called Customers, set this parameter to: Customers.
ToNavigationProperty	String	True	The navigation property you are creating an association on. It can be obtained from ListNavigationProperties.
ToId	String	True	The id of the navigation entity. This will come from the table associated with the navigation property.

Lists associations for a given table and navigation property.

Input

Name	Type	Required	Description
FromId	String	True	The Id of the entity you are listing associations for.
FromTable	String	True	The table where the entity comes from that you are listing entities for. For example, if the FromId was from a table called Customers, set this parameter to: Customers.
NavigationProperty	String	True	The navigation property you are listing associations for. It can be obtained from ListNavigationProperties.

Result Set Columns

Name	Type	Description
Uri	String	The linked url.

Lists navigation properties for a given table and the tables they are associated with. Navigation properties are used by the Association stored procedures.

Input

Name	Type	Required	Description
TableName	String	True	The name of the table to list navigation properties for.

Result Set Columns

Name	Type	Description
Name	String	The name of the navigation property.
AssociatedTable	String	The table the navigation property is associated with.

Removes an association between two entities based on a navigation property.

Input

Name	Type	Required	Description
FromId	String	True	The Id of the entity you are removing an associations for.
FromTable	String	True	The table where the entity comes from that you are removing an association for. For example, if the FromId was from a table called Customers, set this parameter to: Customers.
ToNavigationProperty	String	True	The navigation property you are removing an association on. It can be obtained from ListNavigationProperties.
ToId	String	True	The id of the navigation entity. This will come from the table associated with the navigation property.

You can query the system tables described in this section to access schema information, information on data source functionality, and batch operation statistics.

Schema Tables

The following tables return database metadata for SAP SuccessFactors:

• sys_catalogs: Lists the available databases.
• sys_schemas: Lists the available schemas.
• sys_tables: Lists the available tables and views.
• sys_tablecolumns: Describes the columns of the available tables and views.
• sys_procedures: Describes the available stored procedures.
• sys_procedureparameters: Describes stored procedure parameters.
• sys_keycolumns: Describes the primary and foreign keys.
• sys_indexes: Describes the available indexes.

Data Source Tables

The following tables return information about how to connect to and query the data source:

• sys_connection_props: Returns information on the available connection properties.
• sys_sqlinfo: Describes the SELECT queries that the Cloud can offload to the data source.

Query Information Tables

The following table returns query statistics for data modification queries:

• sys_identity: Returns information about batch operations or single updates.

Lists the available databases.

The following query retrieves all databases determined by the connection string:

SELECT * FROM sys_catalogs

Columns

Name	Type	Description
CatalogName	String	The database name.

Lists the available schemas.

The following query retrieves all available schemas:

          SELECT * FROM sys_schemas
          

Columns

Name	Type	Description
CatalogName	String	The database name.
SchemaName	String	The schema name.

Lists the available tables.

The following query retrieves the available tables and views:

          SELECT * FROM sys_tables
          

Columns

Name	Type	Description
CatalogName	String	The database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view.
TableType	String	The table type (table or view).
Description	String	A description of the table or view.
IsUpdateable	Boolean	Whether the table can be updated.

Describes the columns of the available tables and views.

The following query returns the columns and data types for the SampleTable_1 table:

SELECT ColumnName, DataTypeName FROM sys_tablecolumns WHERE TableName='SampleTable_1' 

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the table or view.
SchemaName	String	The schema containing the table or view.
TableName	String	The name of the table or view containing the column.
ColumnName	String	The column name.
DataTypeName	String	The data type name.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The storage size of the column.
DisplaySize	Int32	The designated column's normal maximum width in characters.
NumericPrecision	Int32	The maximum number of digits in numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The column scale or number of digits to the right of the decimal point.
IsNullable	Boolean	Whether the column can contain null.
Description	String	A brief description of the column.
Ordinal	Int32	The sequence number of the column.
IsAutoIncrement	String	Whether the column value is assigned in fixed increments.
IsGeneratedColumn	String	Whether the column is generated.
IsHidden	Boolean	Whether the column is hidden.
IsArray	Boolean	Whether the column is an array.
IsReadOnly	Boolean	Whether the column is read-only.
IsKey	Boolean	Indicates whether a field returned from sys_tablecolumns is the primary key of the table.

Lists the available stored procedures.

The following query retrieves the available stored procedures:

          SELECT * FROM sys_procedures
          

Columns

Name	Type	Description
CatalogName	String	The database containing the stored procedure.
SchemaName	String	The schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure.
Description	String	A description of the stored procedure.
ProcedureType	String	The type of the procedure, such as PROCEDURE or FUNCTION.

Describes stored procedure parameters.

The following query returns information about all of the input parameters for the SampleProcedure stored procedure:

SELECT * FROM sys_procedureparameters WHERE ProcedureName='SampleProcedure' AND Direction=1 OR Direction=2

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the stored procedure.
SchemaName	String	The name of the schema containing the stored procedure.
ProcedureName	String	The name of the stored procedure containing the parameter.
ColumnName	String	The name of the stored procedure parameter.
Direction	Int32	An integer corresponding to the type of the parameter: input (1), input/output (2), or output(4). input/output type parameters can be both input and output parameters.
DataTypeName	String	The name of the data type.
DataType	Int32	An integer indicating the data type. This value is determined at run time based on the environment.
Length	Int32	The number of characters allowed for character data. The number of digits allowed for numeric data.
NumericPrecision	Int32	The maximum precision for numeric data. The column length in characters for character and date-time data.
NumericScale	Int32	The number of digits to the right of the decimal point in numeric data.
IsNullable	Boolean	Whether the parameter can contain null.
IsRequired	Boolean	Whether the parameter is required for execution of the procedure.
IsArray	Boolean	Whether the parameter is an array.
Description	String	The description of the parameter.
Ordinal	Int32	The index of the parameter.

Describes the primary and foreign keys.

The following query retrieves the primary key for the SampleTable_1 table:

         SELECT * FROM sys_keycolumns WHERE IsKey='True' AND TableName='SampleTable_1' 
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
IsKey	Boolean	Whether the column is a primary key in the table referenced in the TableName field.
IsForeignKey	Boolean	Whether the column is a foreign key referenced in the TableName field.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.

Describes the foreign keys.

The following query retrieves all foreign keys which refer to other tables:

         SELECT * FROM sys_foreignkeys WHERE ForeignKeyType = 'FOREIGNKEY_TYPE_IMPORT'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
PrimaryKeyName	String	The name of the primary key.
ForeignKeyName	String	The name of the foreign key.
ReferencedCatalogName	String	The database containing the primary key.
ReferencedSchemaName	String	The schema containing the primary key.
ReferencedTableName	String	The table containing the primary key.
ReferencedColumnName	String	The column name of the primary key.
ForeignKeyType	String	Designates whether the foreign key is an import (points to other tables) or export (referenced from other tables) key.

Describes the primary keys.

The following query retrieves the primary keys from all tables and views:

         SELECT * FROM sys_primarykeys
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the key.
SchemaName	String	The name of the schema containing the key.
TableName	String	The name of the table containing the key.
ColumnName	String	The name of the key column.
KeySeq	String	The sequence number of the primary key.
KeyName	String	The name of the primary key.

Describes the available indexes. By filtering on indexes, you can write more selective queries with faster query response times.

The following query retrieves all indexes that are not primary keys:

          SELECT * FROM sys_indexes WHERE IsPrimary='false'
          

Columns

Name	Type	Description
CatalogName	String	The name of the database containing the index.
SchemaName	String	The name of the schema containing the index.
TableName	String	The name of the table containing the index.
IndexName	String	The index name.
ColumnName	String	The name of the column associated with the index.
IsUnique	Boolean	True if the index is unique. False otherwise.
IsPrimary	Boolean	True if the index is a primary key. False otherwise.
Type	Int16	An integer value corresponding to the index type: statistic (0), clustered (1), hashed (2), or other (3).
SortOrder	String	The sort order: A for ascending or D for descending.
OrdinalPosition	Int16	The sequence number of the column in the index.

Returns information on the available connection properties and those set in the connection string.

When querying this table, the config connection string should be used:

jdbc:cdata:sapsuccessfactors:config:

This connection string enables you to query this table without a valid connection.

The following query retrieves all connection properties that have been set in the connection string or set through a default value:

SELECT * FROM sys_connection_props WHERE Value <> ''

Columns

Name	Type	Description
Name	String	The name of the connection property.
ShortDescription	String	A brief description.
Type	String	The data type of the connection property.
Default	String	The default value if one is not explicitly set.
Values	String	A comma-separated list of possible values. A validation error is thrown if another value is specified.
Value	String	The value you set or a preconfigured default.
Required	Boolean	Whether the property is required to connect.
Category	String	The category of the connection property.
IsSessionProperty	String	Whether the property is a session property, used to save information about the current connection.
Sensitivity	String	The sensitivity level of the property. This informs whether the property is obfuscated in logging and authentication forms.
PropertyName	String	A camel-cased truncated form of the connection property name.
Ordinal	Int32	The index of the parameter.
CatOrdinal	Int32	The index of the parameter category.
Hierarchy	String	Shows dependent properties associated that need to be set alongside this one.
Visible	Boolean	Informs whether the property is visible in the connection UI.
ETC	String	Various miscellaneous information about the property.

Describes the SELECT query processing that the Cloud can offload to the data source.

See SQL Compliance for SQL syntax details.

Discovering the Data Source's SELECT Capabilities

Below is an example data set of SQL capabilities. Some aspects of SELECT functionality are returned in a comma-separated list if supported; otherwise, the column contains NO.

Name	Description	Possible Values
AGGREGATE_FUNCTIONS	Supported aggregation functions.	AVG, COUNT, MAX, MIN, SUM, DISTINCT
COUNT	Whether COUNT function is supported.	YES, NO
IDENTIFIER_QUOTE_OPEN_CHAR	The opening character used to escape an identifier.	[
IDENTIFIER_QUOTE_CLOSE_CHAR	The closing character used to escape an identifier.	]
SUPPORTED_OPERATORS	A list of supported SQL operators.	=, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR
GROUP_BY	Whether GROUP BY is supported, and, if so, the degree of support.	NO, NO_RELATION, EQUALS_SELECT, SQL_GB_COLLATE
OJ_CAPABILITIES	The supported varieties of outer joins supported.	NO, LEFT, RIGHT, FULL, INNER, NOT_ORDERED, ALL_COMPARISON_OPS
OUTER_JOINS	Whether outer joins are supported.	YES, NO
SUBQUERIES	Whether subqueries are supported, and, if so, the degree of support.	NO, COMPARISON, EXISTS, IN, CORRELATED_SUBQUERIES, QUANTIFIED
STRING_FUNCTIONS	Supported string functions.	LENGTH, CHAR, LOCATE, REPLACE, SUBSTRING, RTRIM, LTRIM, RIGHT, LEFT, UCASE, SPACE, SOUNDEX, LCASE, CONCAT, ASCII, REPEAT, OCTET, BIT, POSITION, INSERT, TRIM, UPPER, REGEXP, LOWER, DIFFERENCE, CHARACTER, SUBSTR, STR, REVERSE, PLAN, UUIDTOSTR, TRANSLATE, TRAILING, TO, STUFF, STRTOUUID, STRING, SPLIT, SORTKEY, SIMILAR, REPLICATE, PATINDEX, LPAD, LEN, LEADING, KEY, INSTR, INSERTSTR, HTML, GRAPHICAL, CONVERT, COLLATION, CHARINDEX, BYTE
NUMERIC_FUNCTIONS	Supported numeric functions.	ABS, ACOS, ASIN, ATAN, ATAN2, CEILING, COS, COT, EXP, FLOOR, LOG, MOD, SIGN, SIN, SQRT, TAN, PI, RAND, DEGREES, LOG10, POWER, RADIANS, ROUND, TRUNCATE
TIMEDATE_FUNCTIONS	Supported date/time functions.	NOW, CURDATE, DAYOFMONTH, DAYOFWEEK, DAYOFYEAR, MONTH, QUARTER, WEEK, YEAR, CURTIME, HOUR, MINUTE, SECOND, TIMESTAMPADD, TIMESTAMPDIFF, DAYNAME, MONTHNAME, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, EXTRACT
REPLICATION_SKIP_TABLES	Indicates tables skipped during replication.
REPLICATION_TIMECHECK_COLUMNS	A string array containing a list of columns which will be used to check for (in the given order) to use as a modified column during replication.
IDENTIFIER_PATTERN	String value indicating what string is valid for an identifier.
SUPPORT_TRANSACTION	Indicates if the provider supports transactions such as commit and rollback.	YES, NO
DIALECT	Indicates the SQL dialect to use.
KEY_PROPERTIES	Indicates the properties which identify the uniform database.
SUPPORTS_MULTIPLE_SCHEMAS	Indicates if multiple schemas may exist for the provider.	YES, NO
SUPPORTS_MULTIPLE_CATALOGS	Indicates if multiple catalogs may exist for the provider.	YES, NO
DATASYNCVERSION	The CData Data Sync version needed to access this driver.	Standard, Starter, Professional, Enterprise
DATASYNCCATEGORY	The CData Data Sync category of this driver.	Source, Destination, Cloud Destination
SUPPORTSENHANCEDSQL	Whether enhanced SQL functionality beyond what is offered by the API is supported.	TRUE, FALSE
SUPPORTS_BATCH_OPERATIONS	Whether batch operations are supported.	YES, NO
SQL_CAP	All supported SQL capabilities for this driver.	SELECT, INSERT, DELETE, UPDATE, TRANSACTIONS, ORDERBY, OAUTH, ASSIGNEDID, LIMIT, LIKE, BULKINSERT, COUNT, BULKDELETE, BULKUPDATE, GROUPBY, HAVING, AGGS, OFFSET, REPLICATE, COUNTDISTINCT, JOINS, DROP, CREATE, DISTINCT, INNERJOINS, SUBQUERIES, ALTER, MULTIPLESCHEMAS, GROUPBYNORELATION, OUTERJOINS, UNIONALL, UNION, UPSERT, GETDELETED, CROSSJOINS, GROUPBYCOLLATE, MULTIPLECATS, FULLOUTERJOIN, MERGE, JSONEXTRACT, BULKUPSERT, SUM, SUBQUERIESFULL, MIN, MAX, JOINSFULL, XMLEXTRACT, AVG, MULTISTATEMENTS, FOREIGNKEYS, CASE, LEFTJOINS, COMMAJOINS, WITH, LITERALS, RENAME, NESTEDTABLES, EXECUTE, BATCH, BASIC, INDEX
PREFERRED_CACHE_OPTIONS	A string value specifies the preferred cacheOptions.
ENABLE_EF_ADVANCED_QUERY	Indicates if the driver directly supports advanced queries coming from Entity Framework. If not, queries will be handled client side.	YES, NO
PSEUDO_COLUMNS	A string array indicating the available pseudo columns.
MERGE_ALWAYS	If the value is true, The Merge Mode is forcibly executed in Data Sync.	TRUE, FALSE
REPLICATION_MIN_DATE_QUERY	A select query to return the replicate start datetime.
REPLICATION_MIN_FUNCTION	Allows a provider to specify the formula name to use for executing a server side min.
REPLICATION_START_DATE	Allows a provider to specify a replicate startdate.
REPLICATION_MAX_DATE_QUERY	A select query to return the replicate end datetime.
REPLICATION_MAX_FUNCTION	Allows a provider to specify the formula name to use for executing a server side max.
IGNORE_INTERVALS_ON_INITIAL_REPLICATE	A list of tables which will skip dividing the replicate into chunks on the initial replicate.
CHECKCACHE_USE_PARENTID	Indicates whether the CheckCache statement should be done against the parent key column.	TRUE, FALSE
CREATE_SCHEMA_PROCEDURES	Indicates stored procedures that can be used for generating schema files.

The following query retrieves the operators that can be used in the WHERE clause:

SELECT * FROM sys_sqlinfo WHERE Name = 'SUPPORTED_OPERATORS'

Note that individual tables may have different limitations or requirements on the WHERE clause; refer to the Data Model section for more information.

Columns

Name	Type	Description
NAME	String	A component of SQL syntax, or a capability that can be processed on the server.
VALUE	String	Detail on the supported SQL or SQL syntax.

Returns information about attempted modifications.

The following query retrieves the Ids of the modified rows in a batch operation:

         SELECT * FROM sys_identity
          

Columns

Name	Type	Description
Id	String	The database-generated Id returned from a data modification operation.
Batch	String	An identifier for the batch. 1 for a single operation.
Operation	String	The result of the operation in the batch: INSERTED, UPDATED, or DELETED.
Message	String	SUCCESS or an error message if the update in the batch failed.

The connection string properties are the various options that can be used to establish a connection. This section provides a complete list of the options you can configure in the connection string for this provider. Click the links for further details.

For more information on establishing a connection, see Establishing a Connection.

Authentication

---

Property	Description
AuthScheme	The type of authentication to use when connecting to SAP SuccessFactors.
URL	Set this to the URL of the server where your SAP SuccessFactors instance is hosted.
User	The SAP SuccessFactors user account used to authenticate.
Password	The password used to authenticate the user.
CompanyId	Unique identifier of your company. This is required to authenticate.

SSO

---

Property	Description
SSOProperties	Additional properties required to connect to the identity provider in a semicolon-separated list.
SSOExchangeUrl	The URL used for consuming the SAML response and exchanging it for service specific credentials.

OAuth

---

Property	Description
PrivateKey	The private key provided for OAuth authentication.
PrivateKeyType	Used together with the PrivateKey connection property. Specifies the private key type provided for OAuth authentication.
OAuthGrantType	The grant type for the OAuth flow.

SSL

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

Logging

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

Schema

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
UseDisplayNames	Boolean determining whether or not to use SAP labels.

Miscellaneous

---

Property	Description
DataFormat	The data format to retrieve data in. Select either ATOM or JSON.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Pagesize	The maximum number of results to return per page from SAP SuccessFactors.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
PurgeType	PurgeType is an optional parameter that determines whether an incremental or full update will be performed on an entity.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseClientSidePaging	Whether or not the CData ADO.NET Provider for SAP SuccessFactors should use client side paging.
UsePicklistDisplayNames	Boolean determining whether or not to use SAP labels for picklist options.

This section provides a complete list of the Authentication properties you can configure in the connection string for this provider.

---

Property	Description
AuthScheme	The type of authentication to use when connecting to SAP SuccessFactors.
URL	Set this to the URL of the server where your SAP SuccessFactors instance is hosted.
User	The SAP SuccessFactors user account used to authenticate.
Password	The password used to authenticate the user.
CompanyId	Unique identifier of your company. This is required to authenticate.

The type of authentication to use when connecting to SAP SuccessFactors.

Possible Values

AzureAD, Auto, Basic, OAuthClient, OAuthSAML2

Data Type

string

Default Value

"Auto"

Remarks

• Auto: Lets the driver decide automatically based on the other connection properties you have set.
• Basic: Set this to use BASIC user / password authentication.
• OAuthClient: Set this to perform OAuth authentication with client grant type. Available only for SAP SuccessFactors LMS instances.
• OAuthSAML2: Set this to perform OAuth authentication with saml2-bearer grant type.
• AzureAD: Set this to perform SSO authentication through AzureAD. Please see the connection property SSOProperties for more information.

Set this to the URL of the server where your SAP SuccessFactors instance is hosted.

Data Type

string

Default Value

""

Remarks

Set this to the URL of the server where your SAP SuccessFactors instance is hosted. Some of the servers are listed here, but there may be others. Some examples:

• Production instance URL: https://api2.successfactors.eu
• SalesDemo instance URL: https://apisalesdemo2.successfactors.eu
• Preview instance URL: https://api2preview.sapsf.eu

For SAP SuccessFactors LMS instances set the full URL of the micro-service in the format https://{instanceURL}/learning/odatav4/{microservice}, for example, https://api4preview.sapsf.com/learning/odatav4/public/user/curriculum-service/v1/

The SAP SuccessFactors user account used to authenticate.

Data Type

string

Default Value

""

Remarks

Together with Password, this field is used to authenticate against the SAP SuccessFactors server.

The password used to authenticate the user.

Data Type

string

Default Value

""

Remarks

The User and Password are together used to authenticate with the server.

Unique identifier of your company. This is required to authenticate.

Data Type

string

Default Value

""

Remarks

Unique identifier of your company. This is required to authenticate.

This section provides a complete list of the SSO properties you can configure in the connection string for this provider.

---

Property	Description
SSOProperties	Additional properties required to connect to the identity provider in a semicolon-separated list.
SSOExchangeUrl	The URL used for consuming the SAML response and exchanging it for service specific credentials.

Additional properties required to connect to the identity provider in a semicolon-separated list.

Data Type

string

Default Value

""

Remarks

Additional properties required to connect to the identity provider in a semicolon-separated list. is used in conjunction with the SSOLoginURL.

SSO configuration is discussed further in .

The URL used for consuming the SAML response and exchanging it for service specific credentials.

Data Type

string

Default Value

""

Remarks

The CData Cloud will use the URL specified here to consume a SAML response and exchange it for service specific credentials. The retrieved credentials are the final piece during the SSO connection that are used to communicate with SAP SuccessFactors.

This section provides a complete list of the OAuth properties you can configure in the connection string for this provider.

---

Property	Description
PrivateKey	The private key provided for OAuth authentication.
PrivateKeyType	Used together with the PrivateKey connection property. Specifies the private key type provided for OAuth authentication.
OAuthGrantType	The grant type for the OAuth flow.

The private key provided for OAuth authentication.

Data Type

string

Default Value

""

Remarks

The path to the certificate you downloaded when you registered your OAuth Client Application, or the base64-encoded content of the certificate.

If providing the base64-encoded content, the PrivateKeyType needs to be set to PEMKEY_BLOB.

The certificate should contain the private key between --BEGIN ENCRYPTED PRIVATE KEY-- and --END ENCRYPTED PRIVATE KEY--.

Used together with the PrivateKey connection property. Specifies the private key type provided for OAuth authentication.

Possible Values

PEMKEY_BLOB

Data Type

string

Default Value

"PEMKEY_FILE"

Remarks

This property can take one of the following values:

PEMKEY_FILE (default)	The PrivateKey value will be the path to the certificate you downloaded when you registered your OAuth Client Application.
PEMKEY_BLOB	The PrivateKey value will be the base64-encoded content of the certificate you downloaded when you registered your OAuth Client Application.

The grant type for the OAuth flow.

Possible Values

CODE, CLIENT, PASSWORD

Data Type

string

Default Value

"CODE"

Remarks

The following options are available: CODE,CLIENT,PASSWORD

This section provides a complete list of the SSL properties you can configure in the connection string for this provider.

---

Property	Description
SSLServerCert	The certificate to be accepted from the server when connecting using TLS/SSL.

The certificate to be accepted from the server when connecting using TLS/SSL.

Data Type

string

Default Value

""

Remarks

If using a TLS/SSL connection, this property can be used to specify the TLS/SSL certificate to be accepted from the server. Any other certificate that is not trusted by the machine is rejected.

This property can take the following forms:

Description	Example
A full PEM Certificate (example shortened for brevity)	-----BEGIN CERTIFICATE----- MIIChTCCAe4CAQAwDQYJKoZIhv......Qw== -----END CERTIFICATE-----
A path to a local file containing the certificate	C:\cert.cer
The public key (example shortened for brevity)	-----BEGIN RSA PUBLIC KEY----- MIGfMA0GCSq......AQAB -----END RSA PUBLIC KEY-----
The MD5 Thumbprint (hex values can also be either space or colon separated)	ecadbdda5a1529c58a1e9e09828d70e4
The SHA1 Thumbprint (hex values can also be either space or colon separated)	34a929226ae0819f2ec14b4a3d904f801cbb150d

If not specified, any certificate trusted by the machine is accepted.

Use '*' to signify to accept all certificates. Note that this is not recommended due to security concerns.

This section provides a complete list of the Logging properties you can configure in the connection string for this provider.

---

Property	Description
Verbosity	The verbosity level that determines the amount of detail included in the log file.

The verbosity level that determines the amount of detail included in the log file.

Data Type

string

Default Value

"1"

Remarks

The verbosity level determines the amount of detail that the Cloud reports to the Logfile. Verbosity levels from 1 to 5 are supported. These are detailed in the Logging page.

This section provides a complete list of the Schema properties you can configure in the connection string for this provider.

---

Property	Description
BrowsableSchemas	This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.
UseDisplayNames	Boolean determining whether or not to use SAP labels.

This property restricts the schemas reported to a subset of the available schemas. For example, BrowsableSchemas=SchemaA,SchemaB,SchemaC.

Data Type

string

Default Value

""

Remarks

Listing the schemas from databases can be expensive. Providing a list of schemas in the connection string improves the performance.

Boolean determining whether or not to use SAP labels.

Data Type

bool

Default Value

false

Remarks

Boolean determining whether or not to use SAP labels.

If set to true, the labels you have configured in your account for the OData properties, will serve as column names. Otherwise, the name of the property will be the column name.

This section provides a complete list of the Miscellaneous properties you can configure in the connection string for this provider.

---

Property	Description
DataFormat	The data format to retrieve data in. Select either ATOM or JSON.
MaxRows	Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.
Pagesize	The maximum number of results to return per page from SAP SuccessFactors.
PseudoColumns	This property indicates whether or not to include pseudo columns as columns to the table.
PurgeType	PurgeType is an optional parameter that determines whether an incremental or full update will be performed on an entity.
Timeout	The value in seconds until the timeout error is thrown, canceling the operation.
UseClientSidePaging	Whether or not the CData ADO.NET Provider for SAP SuccessFactors should use client side paging.
UsePicklistDisplayNames	Boolean determining whether or not to use SAP labels for picklist options.

The data format to retrieve data in. Select either ATOM or JSON.

Possible Values

AUTO, ATOM, JSON

Data Type

string

Default Value

"AUTO"

Remarks

Note that not all data sources support JSON. Other IANA content types are not supported at this time. Leave blank to use the system service default. If blank, ATOM will be used when submitting data in an insert or update.

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

Data Type

int

Default Value

-1

Remarks

Limits the number of rows returned when no aggregation or GROUP BY is used in the query. This takes precedence over LIMIT clauses.

The maximum number of results to return per page from SAP SuccessFactors.

Data Type

int

Default Value

1000

Remarks

The Pagesize property affects the maximum number of results to return per page from SAP SuccessFactors. Setting a higher value may result in better performance at the cost of additional memory allocated per page consumed.

This property indicates whether or not to include pseudo columns as columns to the table.

Data Type

string

Default Value

""

Remarks

This setting is particularly helpful in Entity Framework, which does not allow you to set a value for a pseudo column unless it is a table column. The value of this connection setting is of the format "Table1=Column1, Table1=Column2, Table2=Column3". You can use the "*" character to include all tables and all columns; for example, "*=*".

PurgeType is an optional parameter that determines whether an incremental or full update will be performed on an entity.

Possible Values

incremental, full, record

Data Type

string

Default Value

"incremental"

Remarks

Value	Description
incremental(default)	The upsert operation purges and replaces only the data specified in the request payload. The default upsert behavior is incremental purge unless you specify PurgeType=full.
full	The upsert operation purges all existing data of the entry and creates new data specified in the request payload. If there are multiple time slices for the given record, the upsert operation with PurgeType=full deletes all the time slices and creates new ones specified in the request. If you want to keep a particular time slice, make sure to include all of its data in the request payload. If some time slices are missing, then import engine will delete them.
record	Same behavior as incremental purge. This type of incremental purge is available only for MDF entities and the User entity.

Mandatory Field Governance During Upsert Since a full purge erases all existing data of an entry and creates new data, it follows the mandatory fields governance of the entity. Whether an entry exists or not in the database, you must provide all required fields in your OData Operations payload to be able to sucessfully upsert the entry.

The value in seconds until the timeout error is thrown, canceling the operation.

Data Type

int

Default Value

60

Remarks

If Timeout = 0, operations do not time out. The operations run until they complete successfully or until they encounter an error condition.

If Timeout expires and the operation is not yet complete, the Cloud throws an exception.

Whether or not the CData ADO.NET Provider for SAP SuccessFactors should use client side paging.

Data Type

bool

Default Value

true

Remarks

Some sources do not support server side paging. In these cases, set UseClientSidePaging to true. Otherwise, leave it as false. Setting UseClientSidePaging to true on a source that already supports paging can cause incomplete results.

Boolean determining whether or not to use SAP labels for picklist options.

Data Type

bool

Default Value

false

Remarks

If set to true, the labels that have been configured for the picklist options, will be pushed in a separate column. The column will have the name of the picklist field followed by _label. To achieve this, the Cloud will retrieve and store a list of all picklist options and their labels, which could be costly depending on the number of picklist options the SAP SuccessFactors has.