MicroStrategy ONE

Use the OData Driver to Connect to SAP S/4HANA

The CData OData ODBC driver has been tested with the SAP S/4HANA system using OData services to connect to MicroStrategy.

Recommendations

The following odbc.ini parameters should be used to achieve better performance.

Page Size

  • Test results suggest the optimal value to exist between 1,000 and 50,000 page size, with the best results achieved with 10,000.
  • The page size significantly influences the overall data import performance. A non-optimal page size can triple the import time.

Data Format

  • The CData OData ODBC driver can import data in either ATOM (XML) or JSON formats.
  • Switching to JSON format significantly reduces the import time.

For example: 

pagesize=10000
useclientsidepaging=true
Data Format=JSON

The recommended connection string for a DSN-less connection is as follows: 

DRIVER={CData ODBC Driver for OData};Auth Scheme=BASIC;URL=@url;UseIdUrl=True;Data Format=json;useclientsidepaging=true;pagesize=10000;

Limitations

  • The following SQL clauses and functions are pushed down to the SAP S/4HANA system.

    • Clauses available in MicroStrategy prompts
    • WHERE STARTSWITH clause
    • WHERE not STARTSWITH()
    • WHERE endswith
    • WHERE NOT ENDSWITH()
    • WHERE CONTAINS()
    • WHERE NOT CONTAINS()
    • WHERE BETWEEN
    • WHERE NOT BETWEEN
    • WHERE EQUALS
    • WHERE NOT EQUAL
    • WHERE GREATER THAN
    • WHERE GREATER THAN OR EQUAL
    • WHERE LESS THAN
    • WHERE LESS THAN OR EQUAL

  • Basic filters in the WHERE clause and column list selections are translated by the driver to an OData equivalent request executed by SAP S/4HANA.

    Clause/Function Limitation
    select Count(*) FROM

    This should be avoided as it returns the incorrect results.

    Make sure that counting is performed by MicroStrategy, rather than setting up a report that sends the SELECT COUNT() clause.

    SELECT COUNT(DISTINCT ID) FROM

    This should be avoided as only the COUNT clause is included in the OData call, while DISTINCT is omitted, resulting in incorrect results.

    Make sure that COUNT DISTINCT is performed by MicroStrategy, rather than setting up a report that sends the clause to the driver.
    select id from EntitySet where id LIKE 'a_string'

    The LIKE predicate is not supported by OData 2.0 standard.

    The WHERE LIKE clause should not be used in a MicroStrategy report.

    select CreatedByUser from EntitySet where char_length(CreatedByUser)=10 OData supports filtering on the LENGTH function (which is expressed as CHAR_LENGTH in the CData driver), but is not translated in the proper OData request.
    WHERE CEILING() clause

    OData 2.0 supports the CEILING function in the $filter clause of the request.

    CData driver does not translate the corresponding SQL query into the OData request.

    WHERE FLOOR() clause

    OData 2.0 supports the FLOOR function in the $filter clause of the request.

    CData driver does not translate the corresponding SQL query into the OData request.

    select distinct ROUND

    OData 2.0 supports the ROUND function in the $filter clause of the request.

    CData driver does not translate the corresponding SQL query into the OData request.

  • Creation of temporary tables is not supported by the driver.
  • Table joins are not pushed down to the data source by the driver. It is recommended that table joins are created via the MicroStrategy Analytical Engine.