Difference between revisions of "Odata Overview"

From SmartWiki
Jump to: navigation, search
m (→‎How to setup an authenticated user to consume a private OData Service)
 
(71 intermediate revisions by 4 users not shown)
Line 1: Line 1:
−
What is OData?
+
==What is OData?==
−
OData is described in the following article: [https://en.wikipedia.org/wiki/Open_Data_Protocol]
+
OData is described in the following article: [https://en.wikipedia.org/wiki/Open_Data_Protocol Wikipedia] article.
 +
 
 +
OData provides clients with the ability to publish their data to their communities. It's as if each clients has their own API for their own communities. For example, a client can expose their annual grant/funding information so tax payers can retrieve in real time data for further analysis.
  
 
==How does it work==
 
==How does it work==
 +
SmartSimple provides OData V2, V3 and V4 services.
 +
 +
These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET.
 +
 +
==SmartSimple Entities Exposed to OData Services==
 +
Currently Reports are the only entities exposed through Odata. In the future other entities may be added. Reports were chosen for the following reasons:
 +
 +
# Easier to control security and data format such as fields and fieldname
 +
# Most clients wish to consume read only data
 +
# Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships
 +
# Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine
 +
# Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations
 +
# Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability
 +
 +
==OData Services Endpoints==
 +
SmartSimple OData provides two endpoints: Private and Public Access Endpoints.<br />
 +
Endpoints will return a list of collections (Reports that are OData enabled).
 +
Enable [https://wiki.smartsimple.com/wiki/OData_Connector OData Connector] to use these endpoints.
 +
 +
===Private Access Endpoint===
 +
A private endpoint will need basic authentication for '''authenticated access''' user to access the collection of entities.<br />
 +
Version 4: https: //mysmartsimpleurl/OData/'''V4'''/'''pri'''/424623/Service.svc/<br />
 +
Version 3: https: //mysmartsimpleurl/OData/'''V3'''/'''pri'''/424623/Service.svc/<br />
 +
Version 2: https: //mysmartsimpleurl/OData/'''V2'''/'''pri'''/424623/Service.svc/
 +
 +
===Public Access Endpoint===
 +
If you wish data to be exposed publicly without basic authentication. Note the underlying report must be Internet and OData enabled.<br />
 +
Version 4: https: //mysmartsimpleurl/OData/'''V4'''/'''pub'''/424623/Service.svc/<br />
 +
Version 3: https: //mysmartsimpleurl/OData/'''V3'''/'''pub'''/424623/Service.svc/<br />
 +
Version 2: https: //mysmartsimpleurl/OData/'''V2'''/'''pub'''/424623/Service.svc/
 +
 +
==How to setup an authenticated user to consume a private OData Service==
 +
For [[JSON API (SmartConnect) Prerequisite|'''authenticated access''']], you will need to create a new user with the API Access attribute.
 +
 +
# Create the new user and set access to API Access.
 +
# Set a password for this user.
 +
 +
For public access there is no requirement to create a user.
 +
 +
==Accessing a Report with OData==
 +
SmartSimple OData support requests for data via HTTP GET request.
 +
Private and Public Endpoints exposes all collections (Reports enabled by OData Connector).
 +
 +
Syntax below is an example to view OData records from a report named '''my_Neo_Report''' 
 +
 +
<pre>http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/my_Neo_Report_records
 +
http://mysmartsimpleurl/OData/V3/pub/424623/Service.svc/my_Neo_Report_records
 +
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/my_Neo_Report_records</pre>
 +
 +
==OData Primitive Data Types==
 +
Primitive type representations in XML request is based on the [[Custom Field Type IDs|Data Type]] of the [[Standard Field|Standard Fields]] / [[Custom Fields – General Information|Custom Fields]]<br />
 +
Column headers from a report with the following data types will not be successfully transformed in the OData Service Report:
 +
 +
* Display Only
 +
* Link
 +
* Read Only
 +
* Store Values
 +
 +
==Syntax of OData Queries==
 +
1. Service Metadata Document - describes the data model exposed as HTTP endpoints by the service.
 +
 +
<pre>http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/$metadata</pre>
 +
 +
2. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only
 +
 +
<pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$search=steve</pre>
 +
 +
3. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol)
 +
 +
<pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$filter=first_name +eq+steve  </pre>
 +
 +
4. Ordering: the URL below orders by first name
 +
 +
<pre>http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name+asc
 +
or 
 +
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name
 +
</pre>
 +
 +
5. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return)
 +
 +
<pre> http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$top=2&$skip=3</pre>
 +
 +
6. $count, used to determine how many rows in a collection
 +
 +
<pre> http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records/$count</pre>
 +
 +
==Consuming SmartSimple OData Feed==
 +
The following examples show how to consume OData.
 +
 +
* [[SmartSimple_OData_Feed_to_Excel|Pulling OData feed to Excel]]
 +
* [[SmartSimple_OData_Feed_to_SQL_Server_Database|Import OData feed to SQL Server Database using SQL Server Integration Services]]
 +
 +
==Troubleshooting==
 +
* Report name '''must not''' contain special characters such as question marks, underscore, number sign. Some characters, such as hyphens, are supported.
 +
* Column Headings '''must not''' contain special characters such as question marks, number sign or brackets. Some characters, such as hyphens, are supported.
 +
* Some applications, such as older versions of MS Excel / .NET , may not support the appropriate level of TLS Encryption (i.e. https) required.
 +
 +
=See Also=
 +
* [[Reports: SmartSimple Reports]]
 +
* [[OData Connector]]
 +
* [http://www.odata.org More information on OData]
  
−
Smartsimple provide OData V4 services - Can be consumed by OData clients like Excel, MSSQL server,Safeforce, or used as API (.NET has very good support) etc. What SS Entities exposed to OData Services ?    Report Only (however it does not exclude us to expose raw entities like people, organization, Levelone, notes in the future) Why Report Only ? 1. Easier to control security and data format like  fields and fieldname, any end user can perform this task 2. Most clients consume  ready only data anyway 3. Data relationships are handled in SS, external client does not need to understand underlying data relationships 4. Easy to implement as no need to configure  OData service and metadata enpoints(these are required by OData protocol), it is handled by SS report engine automaticaly 5. More flexible as SS user are able to create dummy tables (like SQL views)  by combining tables , applying formulas and aggregrations 6. Filtering ans ordering  are  pre-defined in SS report, eliminate SQL injection vulnerability Where is my OData services endpoint ?  https://mysmartsimpleurl/OData/V4/Services.svc/ How to setup in SS ? 1. Create a new user and set access to "web access" 2. Set a password 3. Edit report in neo builder and enable OData checkBox 4. Done How to connect from Salesforces ?  I will add more details with screen shots How to connect from MSExcel ?  I will add more details with screen shots
+
[[Category:Integration]][[Category:API]]

Latest revision as of 10:06, 8 April 2024

What is OData?

OData is described in the following article: Wikipedia article.

OData provides clients with the ability to publish their data to their communities. It's as if each clients has their own API for their own communities. For example, a client can expose their annual grant/funding information so tax payers can retrieve in real time data for further analysis.

How does it work

SmartSimple provides OData V2, V3 and V4 services.

These can be consumed by OData clients such as MS Excel, MS SQL server, SaleForce, or used though another API such as .NET.

SmartSimple Entities Exposed to OData Services

Currently Reports are the only entities exposed through Odata. In the future other entities may be added. Reports were chosen for the following reasons:

  1. Easier to control security and data format such as fields and fieldname
  2. Most clients wish to consume read only data
  3. Data relationships are handled in SmartSimple, the external client does not need to understand underlying data relationships
  4. Easy to implement as there is no need to configure OData service and metadata endpoints (as required by the OData protocol), these are automatically handled by the SmartSimple Report Engine
  5. Greater flexible as the SmartSimple user is able to create dummy tables (similar to SQL views) by combining tables, applying formulas and aggregations
  6. Filtering and ordering are pre-defined in SmartSimple Report, eliminate SQL injection vulnerability

OData Services Endpoints

SmartSimple OData provides two endpoints: Private and Public Access Endpoints.
Endpoints will return a list of collections (Reports that are OData enabled). Enable OData Connector to use these endpoints.

Private Access Endpoint

A private endpoint will need basic authentication for authenticated access user to access the collection of entities.
Version 4: https: //mysmartsimpleurl/OData/V4/pri/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/V3/pri/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/V2/pri/424623/Service.svc/

Public Access Endpoint

If you wish data to be exposed publicly without basic authentication. Note the underlying report must be Internet and OData enabled.
Version 4: https: //mysmartsimpleurl/OData/V4/pub/424623/Service.svc/
Version 3: https: //mysmartsimpleurl/OData/V3/pub/424623/Service.svc/
Version 2: https: //mysmartsimpleurl/OData/V2/pub/424623/Service.svc/

How to setup an authenticated user to consume a private OData Service

For authenticated access, you will need to create a new user with the API Access attribute.

  1. Create the new user and set access to API Access.
  2. Set a password for this user.

For public access there is no requirement to create a user.

Accessing a Report with OData

SmartSimple OData support requests for data via HTTP GET request. Private and Public Endpoints exposes all collections (Reports enabled by OData Connector).

Syntax below is an example to view OData records from a report named my_Neo_Report  

http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/my_Neo_Report_records
http://mysmartsimpleurl/OData/V3/pub/424623/Service.svc/my_Neo_Report_records
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/my_Neo_Report_records

OData Primitive Data Types

Primitive type representations in XML request is based on the Data Type of the Standard Fields / Custom Fields
Column headers from a report with the following data types will not be successfully transformed in the OData Service Report:

  • Display Only
  • Link
  • Read Only
  • Store Values

Syntax of OData Queries

1. Service Metadata Document - describes the data model exposed as HTTP endpoints by the service. 

http://mysmartsimpleurl/OData/V2/pub/424623/Service.svc/$metadata

2. Search syntax i.e. criteria on report using “???”: the URL below returns OData records with firstname equal ‘steve’ only 

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$search=steve

3. Filter for dynamical search: the URL below returns OData records with firstname equal ‘steve’ only (eq stands for equal in OData protocol) 

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$filter=first_name +eq+steve

4. Ordering: the URL below orders by first name 

http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name+asc 
or  
http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$orderby=first_name

5. $top and $skip, used mainly for pagination: The syntax below skips 3 records and return 2 records only (i.e. only 4th and 5th will return) 

 http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records?$top=2&$skip=3

6. $count, used to determine how many rows in a collection 

 http://mysmartsimpleurl/OData/V4/pub/424623/Service.svc/report_Neo_records/$count

Consuming SmartSimple OData Feed

The following examples show how to consume OData.

Troubleshooting

  • Report name must not contain special characters such as question marks, underscore, number sign. Some characters, such as hyphens, are supported.
  • Column Headings must not contain special characters such as question marks, number sign or brackets. Some characters, such as hyphens, are supported.
  • Some applications, such as older versions of MS Excel / .NET , may not support the appropriate level of TLS Encryption (i.e. https) required.

See Also

Retrieved from ‘https://wiki.smartsimple.com/index.php?title=Odata_Overview&oldid=42505’